Dataframe reshape Long to Wide with dates in chronological order

user_18233539 0
Viewed 21

I have data arranged in long format, below.

Type Subtype Month   Amount
A      a     Jan-21    7
A      b     Feb-21    9
A      a     Jan-21    8
A      c     Mar-21    2
C      a     Dec-20    7

I'm trying to reshape to wide and order the dates in the Month column in chronological order so it looks like this;

       Month  Dec-20  Jan-21  Feb -21  Mar-21
Type Subtype  
A      a                15
A      b                          9
A      c                                   2
C      a        7

I've tried using pd.pivot_table and also pd.groupby but they keep arranging the Months in alphabetical order. I have ensured the Month column is a datetime object.

This is my code (I've filled the NaN's with 0):

pivot = CF.groupby(['Type', 'Subtype',pd.Grouper(key ='Month')])['Amount'].sum().reset_index().pivot_table(index=['Type', 'Subtype'], columns='Month', values='Amount').fillna(0)

Any suggestions welcome. Thanks!

pandas group-by date python
1 Answers

Try:

# convert Month column to datetime
df["Month"] = pd.to_datetime(df["Month"], format="%b-%y")

# do a pivot
df = df.pivot_table(
    index=["Type", "Subtype"],
    columns="Month",
    values="Amount",
    fill_value=0,
    aggfunc="sum",
)

# convert columns back to string format
df.columns = [c.strftime("%b-%y") for c in df.columns]

print(df)

Prints:

              Dec-20  Jan-21  Feb-21  Mar-21
Type Subtype                                
A    a             0      15       0       0
     b             0       0       9       0
     c             0       0       0       2
C    a             7       0       0       0
user_100359850
Related