divide two columns group by two columns

user_14313935 0
Viewed 103

I want to divide two columns revenue from df1 and revenue from df2 grouping by id and date.

I did something like this, but am getting an error.

df.groupby(['id','date']).agg({'ratio': lambda L: df1['revenue'].div(df2['revenue'])})

data

df1 = pd.DataFrame({'id':['x1', 'x2'],
           'date':['2021-01-02',
           '2021-01-03'],
       'revenue':[50,10]})

df2 = pd.DataFrame({'id':['x2', 'x1', 'x1'],
            'date':['2021-01-03','2021-01-02', '2021-01-01'],
       'revenue':[100,100, 200]})

expected output

     id     date    ratio

0   x2  2021-01-03      0.5

1   x1  2021-01-02      0.1

2   x1  2021-01-01      0
pandas group-by python
1 Answers

Use DataFrame.merge with left join by columns id and date, then divide and replace missing values by 0:

s = df2.merge(df1, on=['id','date'], how='left')['revenue_y'].div(df2['revenue'])
df2['ratio']  = s.fillna(0)
print (df2)
   id        date  revenue  ratio
0  x2  2021-01-03      100    0.1
1  x1  2021-01-02      100    0.5
2  x1  2021-01-01      200    0.0
user_29010020
Related