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

Question

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

jezrael · Accepted Answer · 2020-09-15 07:05:28Z

5

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

edited Sep 15, 2020 at 7:05

answered Sep 15, 2020 at 6:59

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cameron Riddell · Accepted Answer · 2020-09-15 07:49:32Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related