Pivot Table with Groupby - Pandas

user_12684429 0
Viewed 55

I'm trying to make a pivot table of data to look like the expected outcome below (second table) from the data in the first;

To Date         From    To    Quantity

10/11/2020   USA   Ireland  5
10/11/2020   USA   France   4
11/11/2020   USA   France   6
12/11/2020   USA   Jamaica  6
12/11/2020   USA   France   5
.
.
.
14/02/2021   USA   Ireland  65

           01/11/2020   01/12/2020   01/01/2021  01/02/2021

Ireland    15                                    65
France     10
Jamaica    6

I can groupby() it to get the monthly figures.. I would like the pivot table to have the total amount for each country..

df.index = qathist['To Date']
df.index = pd.to_datetime(df.index)
df_m= df.groupby(by=[df.index.year, df.index.month]).sum()

..but then I'm removing other data etc and can't seem to pivot it correctly!

Any help much appreciated!!

multi-index pandas indexing group-by python
3 Answers

To get the monthly, it's better with to_period. An you can through To to groupby, then unstack:

df.groupby(['To',df.index.to_period('M')])['Quantity'].sum().unstack()
user_42384080

Use pd.crosstab:

pd.crosstab(df['To'], df['To Date'], df['Quantity'], aggfunc='sum')
user_63615310

You can use a part of your original code and mix it with the pd.pivot_table function.

For example, if your original dataframe is df :

df.index = pd.to_datetime(df["To Date"])
pivot = df.pivot_table(values="Quantity", 
                       columns="To", 
                       index=[df.index.month, df.index.year],
                       aggfunc='sum')

The output is as follows

To               France  Ireland  Jamaica
To Date To Date                          
2       2021        NaN     65.0      NaN
10      2020        4.0      5.0      NaN
11      2020        6.0      NaN      NaN
12      2020        5.0      NaN      6.0

Hope it's useful!

user_142988800
Related