Divide two separate columns from two separate dataframes using common index

Question

I have two separate dataframes (df1 and df2) with similar columns and I am trying to divide one column in df1 by another column in df2.

dict1 = { 'sex': {6: 'SEX_M',
  7: 'SEX_M',
  8: 'SEX_M',
  9: 'SEX_M',
  10: 'SEX_M',
  11: 'SEX_M',
  12: 'SEX_F',
  13: 'SEX_F',
  14: 'SEX_F',
  15: 'SEX_F',
  16: 'SEX_F',
  17: 'SEX_F'},
 'classif1': {6: 'AGE_YTHADULT_YGE15',
  7: 'AGE_YTHADULT_YGE15',
  8: 'AGE_YTHADULT_YGE15',
  9: 'AGE_YTHADULT_YGE15',
  10: 'AGE_YTHADULT_YGE15',
  11: 'AGE_YTHADULT_YGE15',
  12: 'AGE_YTHADULT_YGE15',
  13: 'AGE_YTHADULT_YGE15',
  14: 'AGE_YTHADULT_YGE15',
  15: 'AGE_YTHADULT_YGE15',
  16: 'AGE_YTHADULT_YGE15',
  17: 'AGE_YTHADULT_YGE15'},
 'classif2': {6: 'ECO_SECTOR_TOTAL',
  7: 'ECO_SECTOR_AGR',
  8: 'ECO_SECTOR_NAG',
  9: 'ECO_SECTOR_IND',
  10: 'ECO_SECTOR_SER',
  11: 'ECO_SECTOR_X',
  12: 'ECO_SECTOR_TOTAL',
  13: 'ECO_SECTOR_AGR',
  14: 'ECO_SECTOR_NAG',
  15: 'ECO_SECTOR_IND',
  16: 'ECO_SECTOR_SER',
  17: 'ECO_SECTOR_X'},
 'obs_value': {6: 4935.407,
  7: 2476.535,
  8: 2458.872,
  9: 685.795,
  10: 1763.5,
  11: 9.577,
  12: 2551.157,
  13: 1950.196,
  14: 600.961,
  15: 245.793,
  16: 343.926,
  17: 11.242}}

df1 = pd.DataFrame(dict1)

dict2 = {'sex': {0: 'SEX_T',
  1: 'SEX_T',
  2: 'SEX_T',
  3: 'SEX_T',
  4: 'SEX_T',
  5: 'SEX_T'},
 'classif1': {0: 'AGE_YTHADULT_YGE15',
  1: 'AGE_YTHADULT_YGE15',
  2: 'AGE_YTHADULT_YGE15',
  3: 'AGE_YTHADULT_YGE15',
  4: 'AGE_YTHADULT_YGE15',
  5: 'AGE_YTHADULT_YGE15'},
 'classif2': {0: 'ECO_SECTOR_TOTAL',
  1: 'ECO_SECTOR_AGR',
  2: 'ECO_SECTOR_NAG',
  3: 'ECO_SECTOR_IND',
  4: 'ECO_SECTOR_SER',
  5: 'ECO_SECTOR_X'},
 'obs_value': {0: 7486.564,
  1: 4426.731,
  2: 3059.833,
  3: 931.588,
  4: 2107.426,
  5: 20.819}}

df2 = pd.DataFrame(dict2)

I am trying to create a new column in the first dataframe (df1) called Percentage_of_total by dividing df1['obs_value'] by df2['obs_value']. Except for the column sex and obs_value in both dfs, the values are identical in the other columns. I tried creating a common index and just dividing the columns I'm interested in, but I get the following error:

ValueError: cannot handle a non-unique multi-index!

My code is below:

df1 = df1.set_index(['classif1','classif2'])
df2 = df2.set_index(['classif1','classif2'])

df1['Percentage_of_total'] = df1['obs_value']/ df2['obs_value']

user_16343464 · Answer

You can use a left merge to align the values, then divide as numpy array:

cols = ['classif1', 'classif2']
df1['Percentage_of_total'] =  (df1['obs_value']
                               .div(df1[cols].merge(df2, on=cols, how='left')
                                    ['obs_value'].to_numpy())
                              )

output:

      sex            classif1          classif2  obs_value  Percentage_of_total
6   SEX_M  AGE_YTHADULT_YGE15  ECO_SECTOR_TOTAL   4935.407             0.659235
7   SEX_M  AGE_YTHADULT_YGE15    ECO_SECTOR_AGR   2476.535             0.559450
8   SEX_M  AGE_YTHADULT_YGE15    ECO_SECTOR_NAG   2458.872             0.803597
9   SEX_M  AGE_YTHADULT_YGE15    ECO_SECTOR_IND    685.795             0.736157
10  SEX_M  AGE_YTHADULT_YGE15    ECO_SECTOR_SER   1763.500             0.836803
11  SEX_M  AGE_YTHADULT_YGE15      ECO_SECTOR_X      9.577             0.460012
12  SEX_F  AGE_YTHADULT_YGE15  ECO_SECTOR_TOTAL   2551.157             0.340765
13  SEX_F  AGE_YTHADULT_YGE15    ECO_SECTOR_AGR   1950.196             0.440550
14  SEX_F  AGE_YTHADULT_YGE15    ECO_SECTOR_NAG    600.961             0.196403
15  SEX_F  AGE_YTHADULT_YGE15    ECO_SECTOR_IND    245.793             0.263843
16  SEX_F  AGE_YTHADULT_YGE15    ECO_SECTOR_SER    343.926             0.163197
17  SEX_F  AGE_YTHADULT_YGE15      ECO_SECTOR_X     11.242             0.539988

user_4248842 · Answer

As you have already set the index, you can simply join the results -

df1.join(df1['obs_value']/ df2['obs_value'], rsuffix='_Percentage_of_total')

Output

classif1           classif2                                                        
AGE_YTHADULT_YGE15 ECO_SECTOR_AGR    SEX_M   2476.535                      0.559450
                   ECO_SECTOR_AGR    SEX_M   2476.535                      0.440550
                   ECO_SECTOR_AGR    SEX_F   1950.196                      0.559450
                   ECO_SECTOR_AGR    SEX_F   1950.196                      0.440550
                   ECO_SECTOR_IND    SEX_M    685.795                      0.736157
                   ECO_SECTOR_IND    SEX_M    685.795                      0.263843
                   ECO_SECTOR_IND    SEX_F    245.793                      0.736157
                   ECO_SECTOR_IND    SEX_F    245.793                      0.263843
                   ECO_SECTOR_NAG    SEX_M   2458.872                      0.803597
                   ECO_SECTOR_NAG    SEX_M   2458.872                      0.196403
                   ECO_SECTOR_NAG    SEX_F    600.961                      0.803597
                   ECO_SECTOR_NAG    SEX_F    600.961                      0.196403
                   ECO_SECTOR_SER    SEX_M   1763.500                      0.836803
                   ECO_SECTOR_SER    SEX_M   1763.500                      0.163197
                   ECO_SECTOR_SER    SEX_F    343.926                      0.836803
                   ECO_SECTOR_SER    SEX_F    343.926                      0.163197
                   ECO_SECTOR_TOTAL  SEX_M   4935.407                      0.659235
                   ECO_SECTOR_TOTAL  SEX_M   4935.407                      0.340765
                   ECO_SECTOR_TOTAL  SEX_F   2551.157                      0.659235
                   ECO_SECTOR_TOTAL  SEX_F   2551.157                      0.340765
                   ECO_SECTOR_X      SEX_M      9.577                      0.460012
                   ECO_SECTOR_X      SEX_M      9.577                      0.539988
                   ECO_SECTOR_X      SEX_F     11.242                      0.460012

user_2307698 · Answer

You can either use a merge as stated in an answer above. Another option can be applying this function along the rows of df1:

def func(x):
    x['pct'] = x.obs_value / np.sum(df2.query('classif1==@x.classif1 & classif2==@x.classif2').obs_value)
    return x
df1.apply(func, axis=1)

What happens here is you iterate over the rows and add a percentage column to the series.

The output is as follows:

sex classif1    classif2    obs_value   pct
6   SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_TOTAL    4935.407    0.659235
7   SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_AGR  2476.535    0.559450
8   SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_NAG  2458.872    0.803597
9   SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_IND  685.795 0.736157
10  SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_SER  1763.500    0.836803
11  SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_X    9.577   0.460012
12  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_TOTAL    2551.157    0.340765
13  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_AGR  1950.196    0.440550
14  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_NAG  600.961 0.196403
15  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_IND  245.793 0.263843
16  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_SER  343.926 0.163197
17  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_X    11.242  0.539988

user_13921399 · Answer

@mozway's solution is definitely cleaner and more general. However, if you - for some reason - don't want to use merge or join, you could also use the following logic for your specific data frame:

import numpy as np

df1['percentage'] = df1.obs_value.values / np.array(df2.obs_value.to_list() * 2)
df1

    sex     classif1            classif2          obs_value percentage
6   SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_TOTAL  4935.407  0.659235
7   SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_AGR    2476.535  0.559450
8   SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_NAG    2458.872  0.803597
9   SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_IND    685.795   0.736157
10  SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_SER    1763.500  0.836803
11  SEX_M   AGE_YTHADULT_YGE15  ECO_SECTOR_X      9.577     0.460012
12  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_TOTAL  2551.157  0.340765
13  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_AGR    1950.196  0.440550
14  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_NAG    600.961   0.196403
15  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_IND    245.793   0.263843
16  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_SER    343.926   0.163197
17  SEX_F   AGE_YTHADULT_YGE15  ECO_SECTOR_X      11.242    0.539988

Divide two separate columns from two separate dataframes using common index

4 Answers