How to create Aging dashboard using pandas

Question

I have below data frame of item with expiry date:

Item          Expiry Date   Stock
Voucher 1     1-Mar-2022     3
Voucher 2     31-Apr-2022    2
Voucher 3     1-Feb-2022     1

And I want to create an aging dashboard and map out my number of stock there:

           Jan Feb Mar Apr
Voucher 1           3
Voucher 2               2
Voucher 3        1

Any ideas or guides how to do something like above please? I searched a lot of resources, cannot find any. I'm very new on building dashboards. Thanks.

Answer 1

You can extract the month name (NB. Your dates are invalid. 31 Apr. is impossible) and pivot the table. If needed, reindex with a list of months names:

from calendar import month_abbr
cols = month_abbr[1:] # first item is empty string

(df.assign(month=df['Expiry Date'].str.extract('-(\D+)-'))
   .pivot(index='Item', columns='month', values='Stock')
   .reindex(columns=cols)
 )

If you expect to have duplicated Items, use pivot_table with sum as aggregation function instead

Output:

month      Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
Item                                                                 
Voucher 1  NaN  NaN  3.0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
Voucher 2  NaN  NaN  NaN  2.0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
Voucher 3  NaN  1.0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN

Answer 2

You may try like this:

import pandas as pd

# Item          Expiry Date   Stock
# Voucher 1     1-Mar-2022     3
# Voucher 2     31-Apr-2022    2
# Voucher 3     1-Feb-2022     1

data = {'Item': ['Voucher 1', 'Voucher 2', 'Voucher 3'],
        'Expiry Date': ['1-Mar-2022', '31-Apr-2022', '1-Feb-2022'],
        'Stock': [3, 2, 1]}

df = pd.DataFrame(data)

# Using pandas apply method, get the month from each row using axis=1 and store it in new column 'Month'
# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.apply.html
df['Month'] = df.apply(lambda x: x['Expiry Date'].split('-')[1], axis=1)

# Using pandas pivot method, set 'Item' column as index,
# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot.html
# set unique values in 'Month' column as separate columns
# set values in 'Stock' column as values for respective month columns
# and using 'rename_axis' method, remove the row name 'Month'
# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rename_axis.html
new_df = df.pivot(index='Item', columns='Month', values='Stock').rename_axis(None, axis=1)

# Sort the month column names by first converting it to the the pandas timestamp object 
# then using it as a key in a sorted function on all columns
new_df = new_df[sorted(new_df.columns, key=lambda x: pd.to_datetime(x, format='%b'))]

print(new_df)

And this is the output I am getting:

           Feb  Mar  Apr
Item                    
Voucher 1  NaN  3.0  NaN
Voucher 2  NaN  NaN  2.0
Voucher 3  1.0  NaN  NaN