Reputation: 979
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
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