CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
Rods2292
Rods2292

Reputation: 675

Divide two dataframes with groupby in Python

I'm trying to figure out how to divide two columns from different datasets, grouping by a common key, in order to obtain a percentage.

Table 1

 Index   ID        sum
    0   100001      7
    1   100002      8
    2   100003      4

Table 2

     Index    ID   TYPE     sum
        0   100001  A        7    
        1   100002  A        4    
        2   100002  B        4   
        3   100003  A        2    
        4   100003  B        2

I want to divide the variable sum from table 1 by sum from table 2, grouping by ID and TYPE.

The goal is to obtain the table bellow, which represents the percentage of sum by TYPE and ID.

Table 3

  Index  ID   TYPE  sum_percent
    0   100001  A     1.0
    1   100002  A     0.5
    2   100002  B     0.5
    3   100003  A     0.5
    4   100003  B     0.5

I was trying some codes to achieve this question, like this one bellow but it throws an error message. It seems that the problem is in the groupby statement.

`table_1[['sum']].groupby('ID')/table_2[['sum']].groupby('ID')`

Upvotes: 1

Views: 542

Answers (1)

jpp
jpp

Reputation: 164713

You do not need groupby; your operation is closer to a merge. In this case, you can create a series mapping from table1:

s = table1.set_index('ID')['sum']

Then use this for your calculation in table2:

table2['sum_percent'] = table2['sum'] / table2['ID'].map(s)

Upvotes: 2

Related Questions