Pivot, Reindex, and Fill: A Step-by-Step Guide for Handling Missing Values with Pandas MultiIndex

You are trying to fill missing values with 0. You could use the reindex function from pandas along with fillna and the concept of a multi-index.

Here is an example code snippet:

import pandas as pd

# Assuming 'dates_df' contains your data like below:
# dates_df = pd.DataFrame({
#     'CLient Id': [1, 2, 3],
#     'Client Name': ['A', 'B', 'C'],
#     'City': ['X', 'Y', 'Z'],
#     'Week': ['W1', 'W2', 'W3'],
#     'Month': ['M1', 'M2', 'M3'],
#     'Year': [2022, 2022, 2022],
#     'Spent': [1000.0, 1500.0, None]
# })

columns = ['Week','Month','Year']
out = (test_df.pivot(['CLient Id', 'Client Name', 'City'], columns, ['Spent'])
       .reindex(pd.MultiIndex.from_arrays(dates_df.assign(Spent='Spent').to_numpy()[:, [-1,0,1,2]].T, 
                                          names=[None]+columns), axis=1)
       .fillna(0).stack(level=columns).reset_index())
print(out)

In this code snippet, we are assuming that ‘dates_df’ is your DataFrame. The reindex function is used to re-index the columns of test_df. We use pd.MultiIndex.from_arrays() to create a multi-index from the ‘Spent’, ‘Week’, ‘Month’, and ‘Year’ columns in ‘dates_df’. Then we fill missing values with 0 using the fillna(0) method.

Last modified on 2025-04-29