Pandas: Enforcing consistent values for inner index across all outer index values

I have a dataset indexed by entity_id and timestamp, but certain entity_id's do not have entries at all timestamps (not missing values, just no row). I'm trying to enforce consistent timestamps across the entity_ids prior to some complicated NaN handling and resampling. But, I cannot get reindex to create the rows I was expecting, and it is leading to unexpected behavior downstream. My approach was:

import numpy as np
import pandas as pd
df = pd.DataFrame(columns = ["id", "ts", "value"])
df.loc[0,:] = [1, pd.Timestamp("2022-01-01 00:00:00"), 1]
df.loc[1,:] = [1, pd.Timestamp("2022-01-01 00:00:01"), 2]
df.loc[2,:] = [2, pd.Timestamp("2022-01-01 00:00:00"), 3]
df = df.set_index(["id", "ts"])
df

# Grab all the timestamps
timestamps = df.index.get_level_values("ts").unique().sort_values()

# Perform the reindexing
df2 = df.reindex(timestamps, level = 1, axis = 0, fill_value = np.nan)

However, this leaves my dataframe unchanged, i.e., df2 still only has 3 rows. Maybe reindexing isn't the right approach here, but I thought it would work.

Is there a best practice for this sort of operation?

Thank you!

Use:

#added sample data
df = pd.DataFrame(columns = ["id", "ts", "value"])
df.loc[0,:] = [1, pd.Timestamp("2022-01-01 00:00:00"), 1]
df.loc[1,:] = [1, pd.Timestamp("2022-01-01 00:00:01"), 2]
df.loc[2,:] = [2, pd.Timestamp("2022-01-01 00:00:00"), 3]
df.loc[3,:] = [3, pd.Timestamp("2022-01-01 00:00:04"), 4]
df = df.set_index(["id", "ts"])
print (df)
value
id ts                       
1  2022-01-01 00:00:00     1
2022-01-01 00:00:01     2
2  2022-01-01 00:00:00     3
3  2022-01-01 00:00:04     4

If need add missing consecutive datetimes by date_range with minimal and maximal values use MultiIndex.from_product with all ids and dates and pass to DataFrame.reindex:

dates = pd.date_range(df.index.levels[1].min(), df.index.levels[1].max(), freq='S')
mux = pd.MultiIndex.from_product([df.index.levels[0], dates], names=df.index.names)
out1 = df.reindex(mux)
print (out1)
value
id ts                       
1  2022-01-01 00:00:00     1
2022-01-01 00:00:01     2
2022-01-01 00:00:02   NaN
2022-01-01 00:00:03   NaN
2022-01-01 00:00:04   NaN
2  2022-01-01 00:00:00     3
2022-01-01 00:00:01   NaN
2022-01-01 00:00:02   NaN
2022-01-01 00:00:03   NaN
2022-01-01 00:00:04   NaN
3  2022-01-01 00:00:00   NaN
2022-01-01 00:00:01   NaN
2022-01-01 00:00:02   NaN
2022-01-01 00:00:03   NaN
2022-01-01 00:00:04     4

If need DataFrame.reindex by unique values of both levels of MultiIndex:

mux = pd.MultiIndex.from_product(df.index.levels, names=df.index.names)
out2 = df.reindex(mux)
print (out2)
value
id ts                       
1  2022-01-01 00:00:00     1
2022-01-01 00:00:01     2
2022-01-01 00:00:04   NaN
2  2022-01-01 00:00:00     3
2022-01-01 00:00:01   NaN
2022-01-01 00:00:04   NaN
3  2022-01-01 00:00:00   NaN
2022-01-01 00:00:01   NaN
2022-01-01 00:00:04     4

I have this solution came into my mind to make use of pd.pivot_table() and pd.melt().

Kindly find below for my code:

# Create the sample dataset
df = pd.DataFrame(columns = ["id", "ts", "value"])
df.loc[0,:] = [1, pd.Timestamp("2022-01-01 00:00:00"), 1]
df.loc[1,:] = [1, pd.Timestamp("2022-01-01 00:00:01"), 2]
df.loc[2,:] = [2, pd.Timestamp("2022-01-01 00:00:00"), 3]
# Pivot the dataset
df_pivot = pd.pivot_table(
df,
values='value',
index='id',
columns='ts'
).reset_index()
# Melt the pivoted dataset
df_result = pd.melt(
df_pivot,
id_vars='id',
value_vars=list(df_res.columns[1:]),
var_name='ts', 
value_name='value'
)

The result I got as below:

   id                  ts  value
0   1 2022-01-01 00:00:00    1.0
1   2 2022-01-01 00:00:00    3.0
2   1 2022-01-01 00:00:01    2.0
3   2 2022-01-01 00:00:01    NaN

You can fill the missing value by using fill_value param in pd.pivot_table() if you want, you may refer the documentation.
Hope this help.