CODEWITHSUNDEEP
pythonpandasdataframe
Roman N
Roman N

Reputation: 5110

How to aggregate and sum items by month?

I have 2 dataframes (orders and items with prices):

orders = pd.DataFrame({'id': [1,2], 'sum_delivery': [10, 0], 'date': ['2016-01-01', '2016-01-05']})
items = pd.DataFrame({'id': [1,2,3], 'order_id': [1,1,2], 'price': [100, 100, 500], 'count':[5,5,1]})

I want to aggregate data by month and get this dataframe in the end:

{'date': ['2016-01'], 'sum': [1510]}

It is possible with sql very easy, but how to do it with pandas?

Upvotes: 3

Views: 901

Answers (2)

Roman N
Roman N

Reputation: 5110

I did this and it works:

items2 = items.groupby('order_id', as_index=False)['sum'].sum()
res = pd.merge(orders, items2, left_on = 'id', right_on = 'order_id')[['date', 'sum', 'sum_delivery']]

res['sum2'] = res['sum'] + res['sum_delivery']
res.index = pd.to_datetime(res.date)
tmpdf = res.groupby(pd.TimeGrouper("M")).sum()[['sum2']]

Upvotes: 0

roman
roman

Reputation: 117380

You want to take sum_delivery into account only once per order, so you have to groupby before you join:

>>> items2 = items.groupby('order_id', as_index=False)['sum'].sum()
>>> items2
   order_id   sum
0         1  1000
1         2   500

Now you can use pandas.DataFrame.merge to use custom column names:

>>> res = pd.merge(orders, items2, left_on = 'id', right_on = 'order_id')[['date', 'sum', 'sum_delivery']]
>>> res
         date   sum  sum_delivery
0  2016-01-01  1000            10
1  2016-01-05   500             0

And now just do simple math and simple pandas.DataFrame.groupby (don't forget to use as_index=False):

>>> res['date'] = res['date'].str[:7]
>>> res['sum2'] = res['sum'] + res['sum_delivery']
>>> res2 = res.groupby('date', as_index=False)['sum2'].sum()
>>> res2
      date  sum2
0  2016-01  1510

Upvotes: 3

Related Questions