0

I would like to convert my df1 to df2. They are mentioned below.

df1:

    Item    totSaleAmount   category
0   apple   10               Fruit
1   orange  50               Fruit
2   apple   20               Fruit
3   carrot  60               Vegetable
4   potato  30               Vegetable
5   coffee  5                Beverage
6   potato  10               Vegetable
7   tea     5                Beverage
8   tea     5                Beverage
9   strawberry  40           Fruit

df2(o/p):

    Item    totSale     count   category
0   apple   30            2     fruit
1   orange  50            1     fruit
2   carrot  60            1     vegetable
3   potato  40            2     vegetable
4   coffee  5             1     beverage
5   tea     10            2     beverage
6   strawberry  40        1     fruit

I was able to perform a few actions separately: for example, counts I was able to get using value_counts(), and sum I was able to perform using sum() post filter df with the item. But I am not sure how to put everything together and convert df1 to df2.

SherylHohman
  • 16,580
  • 17
  • 88
  • 94
Ragesh Kr
  • 1,573
  • 8
  • 29
  • 46
  • this looks like something for [groupby](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html). have a look at the link and see if it helps – sammywemmy Mar 22 '20 at 14:45
  • yeah, but couldnt figure out , how to add up the sale amounts – Ragesh Kr Mar 22 '20 at 14:57
  • Use `df1 = df.groupby(['Item','category'], sort=False)['totSaleAmount'].agg([('totSale','sum'), ('count','size')])` – jezrael Mar 22 '20 at 15:34
  • This works , but when i print df1.columns , I am missing 'Item' and 'Category' , I get back only 'coun't and 'size'. Is it possibleto get all four 'Item', 'Category', 'count' and 'Size' ? – Ragesh Kr Mar 22 '20 at 16:16

0 Answers0