s_khan92
s_khan92

Reputation: 979

How to groupby values in pandas but using lists as an index?

I have dataframe like and i need to groupby it based on fruit and value but i need to index it based on lists

  Date       ID     Age      Value        Fruits
  1.1.19     1      50         2            Apple
  2.1.19     1      50         5            Mango
  2.1.19     1      50         8            Orange
  1.1.19     2      75         6            Grapes
  4.1.19     3      20         1            Apple
  4.1.19     3      20         0            Grapes

for example i have two lists:

fruits_list = ['Apple', 'Mango', 'Orange', 'Grapes', 'Banana', 'Guava']
date_list = ['1.1.19', '2.1.19', '3.1.19', '4.1.19', '5.1.19', '6.1.19']

So if i use this:

df_filter.groupby(['Fruits', 'Date'])['Value'].mean().unstack().reset_index().fillna(0).round()

how it will be possible to use lists to re_index? There are more values list like Banana and Guava so i would like that also as in index

Upvotes: 1

Views: 37

Answers (1)

BENY
BENY

Reputation: 323226

Fix your code with reindex

df.groupby(['Fruits', 'Date'])['Value'].mean().unstack(fill_value=0).\
    reindex(columns=date_list,index=fruits_list,fill_value=0).\
     round().reset_index()
Out[172]: 
Date  Fruits  1.1.19  2.1.19  3.1.19  4.1.19  5.1.19  6.1.19
0      Apple       2       0       0       1       0       0
1      Mango       0       5       0       0       0       0
2     Orange       0       8       0       0       0       0
3     Grapes       6       0       0       0       0       0
4     Banana       0       0       0       0       0       0
5      Guava       0       0       0       0       0       0

Upvotes: 2

Related Questions