How to merge empty cells in a pivot table using python

Ive a pivot table like this :

       table = pd.pivot_table(
           df,
           values="D",
           index=["A", "B"],
           columns=["C"],
           aggfunc=np.sum,
           margins=True,
           margins_name="total",
           )

For this pivot table , ive found the subtotal :

       # Drop column "total" and sort index to avoid performance warning
       table = table.sort_index()

       # Calculate total for rows and add in a new column
       rows_total = table.reset_index().groupby(["A"]).agg({"C": sum})
       for idx in table.index.get_level_values(0).unique():  # bar, foo, total
           table.loc[(idx,), "total"] = rows_total.loc[idx].values[0]

       # Sort index and format values
       table = table.reindex(index=["bar", "foo", "total"], level=0).astype(int)

       # Remove repeated values
       for idx_0 in table.index.get_level_values(0).unique():  # bar, foo, total
           if idx_0 == "total":
               continue
           for idx_1 in table.loc[(idx_0,), :].index[1:]:
               table.loc[(idx_0, idx_1), "total"] = ""

In the excel the final output its coming like :

But i want it to be like :

Please help me with this as im stuck with this for so long time , ive tried many things but nothing is working.

Recommended topics

Hot tags