0
$\begingroup$

I have a Pandas DataFrame with sales data and columns for year, ISO week, price, quantity, and organic [boolean]. Because each row represents a different location, dates are repeated. I would like to combine rows with matching year, ISO week, and organic. Ideally, the combined row would have the average price and sum of total quantity. Any help is much appreciated!

$\endgroup$
  • $\begingroup$ can you gives us a sample of your dataframe? df.head() $\endgroup$ – MattR Jun 7 at 19:16
1
$\begingroup$

I believe what you need is agg from pandas. You can pass a dictionary of the different aggregations you need for each column:

import pandas as pd

df = pd.DataFrame({'year':['2017','2018','2019','2019'], 
                   'ISO Week':[1,2,3,3],
                  'Price':[5,10,15,20],
                   'quantity':[1,2,3,4],
                  'organic':[True, False, True, True]})

   ISO Week  Price  organic  quantity  year
0         1      5     True         1  2017
1         2     10    False         2  2018
2         3     15     True         3  2019   #<------ combine
3         3     20     True         4  2019   #<------ combine

df.groupby(['year','ISO Week','organic'], as_index=False).agg({'Price':'mean', 'quantity':'sum'})

   year  ISO Week  organic  Price  quantity
0  2017         1     True    5.0         1
1  2018         2    False   10.0         2
2  2019         3     True   17.5         7
$\endgroup$
  • $\begingroup$ When adding a new data with having the same year as in the extant dataframe, the code doesn't work properly. for instance when I add another year '2018' it would be: ` year ISO Week organic Price quantity` 0 2017 1 True 5 1 1 2018 2 False 10 2 ` 2 2018 4 True 17 5` 3 2019 3 False 20 4 4 2019 3 True 15 3 $\endgroup$ – Fatemeh Asgarinejad Jun 7 at 20:46
  • $\begingroup$ @Fatemehhh, I don't quite understand what you are saying. the format in comments isn't that nice. $\endgroup$ – MattR Jun 7 at 20:54
  • $\begingroup$ I'm so sorry for the format. I couldn't fix it. just add another row like '2018', 1, 20, 5, False to your dataframe. then in the result, the dataframe is not grouped by year $\endgroup$ – Fatemeh Asgarinejad Jun 7 at 23:15
  • 1
    $\begingroup$ Right, because it's being grouped by more than just year, like the OP asked. If you want to group by just year, only use year in the groupby :) $\endgroup$ – MattR Jun 7 at 23:16
  • $\begingroup$ You're totally right. thanks. $\endgroup$ – Fatemeh Asgarinejad Jun 7 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.