Pandas Divide Dataframe by Another Based on Column Values

Question

I want to divide a pandas dataframe by another based on the column values. For example let's say I have:

>>> df = pd.DataFrame({'NAME': [ 'CA', 'CA', 'CA', 'AZ', 'AZ', 'AZ', 'TX', 'TX', 'TX'], 'NUM':[1, 2, 3, 1, 2, 3, 1, 2, 3], 'VALUE': [10, 20, 30, 40, 50, 60, 70, 80, 90]})
>>> df
  NAME  NUM  VALUE
0   CA    1     10
1   CA    2     20
2   CA    3     30
3   AZ    1     40
4   AZ    2     50
5   AZ    3     60
6   TX    1     70
7   TX    2     80
8   TX    3     90
>>> states = pd.DataFrame({'NAME': ['CA', "AZ", "TX"], 'DIVISOR': [10, 5, 1]})
>>> states
  NAME  DIVISOR
0   CA       10
1   AZ        5
2   TX        1

For each STATE and NUM I want to divide the VALUE column in df by the divisor COLUMN of the respective state.

Which would give a result of

>>> result = pd.DataFrame({'NAME': [ 'CA', 'CA', 'CA', 'AZ', 'AZ', 'AZ', 'TX', 'TX', 'TX'], 'NUM':[1, 2, 3, 1, 2, 3, 1, 2, 3], 'VALUE': [1, 2, 3, 8, 10, 12, 70, 80, 90]})
>>> result
  NAME  NUM  VALUE
0   CA    1      1
1   CA    2      2
2   CA    3      3
3   AZ    1      8
4   AZ    2     10
5   AZ    3     12
6   TX    1     70
7   TX    2     80
8   TX    3     90

The practical approach would be to join the two dataframes on the field name and then you can divide the two columns by each other. — Michael Gardner
– Michael Gardner, Commented Nov 7, 2020 at 2:47

BENY · Accepted Answer · 2020-11-07 02:44:52Z

4

Let us do map

df['NEW VALUE'] = df['VALUE'].div(df['NAME'].map(states.set_index('NAME')['DIVISOR']))
df
Out[129]: 
  NAME  NUM  VALUE  NEW VALUE
0   CA    1     10        1.0
1   CA    2     20        2.0
2   CA    3     30        3.0
3   AZ    1     40        8.0
4   AZ    2     50       10.0
5   AZ    3     60       12.0
6   TX    1     70       70.0
7   TX    2     80       80.0
8   TX    3     90       90.0

answered Nov 7, 2020 at 2:44

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arun Joy Thekkiniyath · Accepted Answer · 2020-11-07 03:05:24Z

3

You can use merge as well

result = df.merge(states,on=['NAME'])
result['NEW VALUE'] = result.VALUE/result.DIVISOR
print(result)

NAME  NUM  VALUE  NEW VALUE  DIVISOR
0   CA    1     10        1.0       10
1   CA    2     20        2.0       10
2   CA    3     30        3.0       10
3   AZ    1     40        8.0        5
4   AZ    2     50       10.0        5
5   AZ    3     60       12.0        5
6   TX    1     70       70.0        1
7   TX    2     80       80.0        1
8   TX    3     90       90.0        1

answered Nov 7, 2020 at 3:05

Arun Joy Thekkiniyath

8848 silver badges15 bronze badges

Comments

Lemon · Accepted Answer · 2020-11-07 03:02:43Z

I feel like there must be a more eloquent way to accomplish what you are looking for, but this is the rout that I usually take.

myresult = df.copy()
for i in range(len(df['NAME'])):
    for j in range(len(states['NAME'])):
        if  df['NAME'][i] == states['NAME'][j]:
            myresult['VALUE'][i] = df['VALUE'][i]/states['DIVISOR'][j]
    
myresult.head()

Out[10]>>
  NAME  NUM VALUE
0   CA  1   1
1   CA  2   2
2   CA  3   3
3   AZ  1   8
4   AZ  2   10

This is a very brute force method. You start by looping through each value in the data frame df, then you loop through each element in the data frame states. Then for each comparison, you look to see if the NAME columns match. If they do, you do the VALUE / DIVISOR.

You will get a warring for using the .copy() method

Collectives™ on Stack Overflow

Pandas Divide Dataframe by Another Based on Column Values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related