Stack Exchange
Pandas - Timestamp-Index handling and resampling of multiple timestamp indexes
I have a complex problem which I will try to explain:
I have the following DataFrame as Base:
                                            value
A   B                   C
111 2024-03-22 00:00:00 2024-03-22 00:00:00 1
111 2024-03-22 00:00:00 2024-03-22 01:00:00 2
111 2024-03-22 00:00:00 2024-03-22 02:00:00 3
222 2024-03-22 00:00:00 2024-03-22 03:00:00 4
222 2024-03-22 00:00:00 2024-03-22 04:00:00 5
222 2024-03-22 00:00:00 2024-03-22 05:00:00 6

As you can see there is one index A which shall be preservered.
Index B is grouped by Day. - Index C is grouped by Hour.
Besides this Base DataFrame I have some date_ranges:
a)
2024-02-05 00:00:00
2024-02-06 00:00:00

b)
2024-02-05 22:00:00
2024-02-05 23:00:00
2024-02-06 22:00:00
2024-02-06 23:00:00

The result I want to achieve is the following DataFrame:
                                            value
A   B                   C
111 2024-02-05 00:00:00 2024-02-05 22:00:00 0
222 2024-02-05 00:00:00 2024-02-05 22:00:00 0
111 2024-02-05 00:00:00 2024-02-05 23:00:00 0
222 2024-02-05 00:00:00 2024-02-05 23:00:00 0
111 2024-02-06 00:00:00 2024-02-06 22:00:00 0
222 2024-02-06 00:00:00 2024-02-06 22:00:00 0
111 2024-02-06 00:00:00 2024-02-06 23:00:00 0
222 2024-02-06 00:00:00 2024-02-06 23:00:00 0
111 2024-03-22 00:00:00 2024-03-22 00:00:00 1
222 2024-03-22 00:00:00 2024-03-22 00:00:00 0
111 2024-03-22 00:00:00 2024-03-22 01:00:00 2
222 2024-03-22 00:00:00 2024-03-22 01:00:00 0
111 2024-03-22 00:00:00 2024-03-22 02:00:00 3
222 2024-03-22 00:00:00 2024-03-22 02:00:00 0
111 2024-03-22 00:00:00 2024-03-22 03:00:00 0
222 2024-03-22 00:00:00 2024-03-22 03:00:00 4
111 2024-03-22 00:00:00 2024-03-22 04:00:00 0
222 2024-03-22 00:00:00 2024-03-22 04:00:00 5
111 2024-03-22 00:00:00 2024-03-22 05:00:00 0
222 2024-03-22 00:00:00 2024-03-22 05:00:00 6

Now my questions are:

How can I achieve all of that?
Is date_range even the correct way to provide such information? -> The only input I have is start and end-date with time for a day.

It could also happen that there will be a Index D which is grouped by minute.
If you need any more information please let me know.

Pandas Version: 2.0.1

Thanks in advance.
Regards Oliver

IIUC, you’ll need to take a couple of steps to massage the data in their current
form. From a data perspective, it is important that index level
'B' is derived from 'C' so for this solution I focus on working with
columns 'A' and 'C' and then re-derive 'B' as a final step at the end.
Data
from io import StringIO

raw = StringIO('''
A    B                    C                    value
111  2024-03-22 00:00:00  2024-03-22 00:00:00  1
111  2024-03-22 00:00:00  2024-03-22 01:00:00  2
111  2024-03-22 00:00:00  2024-03-22 02:00:00  3
222  2024-03-22 00:00:00  2024-03-22 03:00:00  4
222  2024-03-22 00:00:00  2024-03-22 04:00:00  5
222  2024-03-22 00:00:00  2024-03-22 05:00:00  6
''')


import pandas as pd

df = pd.read_table(raw, sep='\s{2,}', engine='python', parse_dates=['B', 'C'])

print(df)
#      A          B                   C  value
# 0  111 2024-03-22 2024-03-22 00:00:00      1
# 1  111 2024-03-22 2024-03-22 01:00:00      2
# 2  111 2024-03-22 2024-03-22 02:00:00      3
# 3  222 2024-03-22 2024-03-22 03:00:00      4
# 4  222 2024-03-22 2024-03-22 04:00:00      5
# 5  222 2024-03-22 2024-03-22 05:00:00      6

Solution
To solve this problem, we’ll need to take the following steps.

create the additional ranges we want to include.
create the cartesian product of the current columns 'A' & 'C'.
combine the indexes generated from steps 1 & 2.
reindex the DataFrame using the new index.
derive column 'B'
massage to reflect OP’s specific ordering of output

additional_ranges = [                                              # ①
    date + pd.Timedelta(hours=hours)
    for date in pd.date_range('2024-02-05', '2024-02-06', freq='D')
    for hours in [22, 23]
]

new_index = pd.MultiIndex.from_product(                           # ② & ③
    [df['A'].unique(), [*df['C'].unique(), *additional_ranges]], 
    names=['A', 'C']
)

out = (
    df.set_index(['A', 'C']).reindex(new_index, fill_value=0)
    .sort_index(level=['C', 'A'])                                 # ④
    .assign(B=lambda d: d.index.get_level_values('C').floor('D')) # ⑤
    .set_index('B', append=True)                                  # ⑥
    .reorder_levels(['A', 'B', 'C'])
)

print(out)
#                                     value
# A   B          C
# 111 2024-02-05 2024-02-05 22:00:00      0
# 222 2024-02-05 2024-02-05 22:00:00      0
# 111 2024-02-05 2024-02-05 23:00:00      0
# 222 2024-02-05 2024-02-05 23:00:00      0
# 111 2024-02-06 2024-02-06 22:00:00      0
# 222 2024-02-06 2024-02-06 22:00:00      0
# 111 2024-02-06 2024-02-06 23:00:00      0
# 222 2024-02-06 2024-02-06 23:00:00      0
# 111 2024-03-22 2024-03-22 00:00:00      1
# 222 2024-03-22 2024-03-22 00:00:00      0
# 111 2024-03-22 2024-03-22 01:00:00      2
# 222 2024-03-22 2024-03-22 01:00:00      0
# 111 2024-03-22 2024-03-22 02:00:00      3
# 222 2024-03-22 2024-03-22 02:00:00      0
# 111 2024-03-22 2024-03-22 03:00:00      0
# 222 2024-03-22 2024-03-22 03:00:00      4
# 111 2024-03-22 2024-03-22 04:00:00      0
# 222 2024-03-22 2024-03-22 04:00:00      5
# 111 2024-03-22 2024-03-22 05:00:00      0
# 222 2024-03-22 2024-03-22 05:00:00      6


You can simply generate all the combinations of unique values in A, B and C this way
import pandas as pd

data = {
    'A': [111, 111, 111, 222, 222, 222],
    'B': ['2024-03-22 00:00:00', '2024-03-22 00:00:00', '2024-03-22 00:00:00',
          '2024-03-22 00:00:00', '2024-03-22 00:00:00', '2024-03-22 00:00:00'],
    'C': ['2024-03-22 00:00:00', '2024-03-22 01:00:00', '2024-03-22 02:00:00',
          '2024-03-22 03:00:00', '2024-03-22 04:00:00', '2024-03-22 05:00:00'],
    'value': [1, 2, 3, 4, 5, 6]
}

df = pd.DataFrame(data)
df['B'] = pd.to_datetime(df['B'])
df['C'] = pd.to_datetime(df['C'])

date_ranges = {
    'a': pd.date_range(start='2024-02-05', end='2024-02-06', freq='D'),
    'b': pd.date_range(start='2024-02-05 22:00:00', end='2024-02-06 23:00:00', freq='H')
}

unique_combinations = pd.MultiIndex.from_product([df['A'].unique(), date_ranges['a'], date_ranges['b']], names=['A', 'B', 'C'])
all_combinations_df = pd.DataFrame(index=unique_combinations).reset_index()
all_combinations_df['value'] = 0

result_df = pd.concat([df, all_combinations_df]).fillna(0).astype({'value': int})

print(result_df)


which gives you
       A          B                   C  value
0    111 2024-03-22 2024-03-22 00:00:00      1
1    111 2024-03-22 2024-03-22 01:00:00      2
2    111 2024-03-22 2024-03-22 02:00:00      3
3    222 2024-03-22 2024-03-22 03:00:00      4
4    222 2024-03-22 2024-03-22 04:00:00      5
..   ...        ...                 ...    ...
99   222 2024-02-06 2024-02-06 19:00:00      0
100  222 2024-02-06 2024-02-06 20:00:00      0
101  222 2024-02-06 2024-02-06 21:00:00      0
102  222 2024-02-06 2024-02-06 22:00:00      0
103  222 2024-02-06 2024-02-06 23:00:00      0

[110 rows x 4 columns]

It is not arranged in the same way as your expected output, but you can do this the way you want.
