CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
qbbq
qbbq

Reputation: 367

Pandas pivot with custom columns even if no data exists in column

I have the following sample table:

df = pd.DataFrame({'Name': ['Bob', 'Bob', 'Susan', 'Susan', 'Jane','Jane'],
                   'Status': ['Paid', 'Paid', 'Paid OTP', 'Overdue', '', 'Upsell'],
                   'Amt': [100, 200, 300, 200, 0,60]})

I am trying to create a pivot table (which works) with the following code:

table = pd.pivot_table(df, values='Amt', index=['Name'],columns=['Status'], aggfunc=np.sum, fill_value=0)

however I need to include a persistent column, for example: "To Be Paid" irrespective if there is a value or not in the table. The reason for this is to show a 0 value if "To Be Paid" is filled in for that specific month.

python: 3.6.8

pandas: 0.25.0

Upvotes: 5

Views: 2506

Answers (2)

Cameron Riddell
Cameron Riddell

Reputation: 13417

You can also convert your Status column to a categorical and ensure that "To Be Paid" is a present category (even if it is not represented in the data)

df = pd.DataFrame({'Name': ['Bob', 'Bob', 'Susan', 'Susan', 'Jane','Jane'],
                   'Status': ['Paid', 'Paid', 'Paid OTP', 'Overdue', '', 'Upsell'],
                   'Amt': [100, 200, 300, 200, 0,60]})

df["Status"] = pd.Categorical(df["Status"])
if "To Be Paid" not in df["Status"].cat.categories:
    df["Status"].cat.add_categories("To Be Paid", inplace=True)


df.pivot_table(values='Amt', index='Name', columns='Status', aggfunc=np.sum, fill_value=0, dropna=False)
Status     Overdue  Paid  Paid OTP  Upsell  To Be Paid
Name                                                  
Bob     0  0        300   0         0       0         
Jane    0  0        0     0         60      0         
Susan   0  200      0     300       0       0

Upvotes: 2

jezrael
jezrael

Reputation: 863256

Use Index.union for add new value to columns names if not exist with DataFrame.reindex:

cols = table.columns.union(['To Be Paid'], sort=False)
table = table.reindex(cols, axis=1, fill_value=0)
print (table)
          Overdue  Paid  Paid OTP  Upsell  To Be Paid
Name                                                 
Bob    0        0   300         0       0           0
Jane   0        0     0         0      60           0
Susan  0      200     0       300       0           0

Upvotes: 5

Related Questions