Merging daily data with 5 min data using pandas dataframe

Question

I have 2 pandas data frame. One contains daily data (dailyDf) and one contains 5 min data (fiveMinDf). Now append add first 2 bars of fiveMinDf as columns to each row in dailyDf

Eg

DailyDf
           Open Close
01-01-2022  100  101
01-02-2022  102  103

fiveMinDf
                  Open   Close
01-01-2022-9.30  100.1   100.2
01-01-2022-9.35  100.3   100.4
01-01-2022-9:40  100.9   100.9
01-02-2022-9.30  100.6   100.7
01-02-2022-9.35  100.8   100.49
01-02-2022-9:40  100.9   100.9

MergedDF

DailyDf
           Open Close open_5min_9.30 open_5min_9.35 close_5min_9.30 close_5min_9.35
01-01-2022  100  101.   100.1         100.3            100.2          100.4
01-02-2022  102  103    100.6          100.8          100.7          100.49

Answer 1

Annotated code

# Extract date and time columns from the index
fiveMinDf[['date', 'time']] = fiveMinDf.index.str.rsplit('-', n=1).tolist()

# get the first two rows per date
s = fiveMinDf.groupby('date').head(2)

# Reshape from long to wide
s = s.pivot('date', 'time')

# flatten the multiindex columns
s.columns = s.columns.map('_'.join)

# Join with dailyDF on common date
MergedDF = DailyDf.join(s)

Result

            Open  Close  Open_9.30  Open_9.35  Close_9.30  Close_9.35
01-01-2022   100    101      100.1      100.3       100.2      100.40
01-02-2022   102    103      100.6      100.8       100.7      100.49