How to combine two Panda dataframes with different time frames side by side, but with blanks-CodePudding

I have two Panda data frames that report the same information but occur in different timeframes. One reports the daily change and one reports the hourly change. I would like to merge the two in the specified format.

This resembles the daily panda frame called daily_df :

date         change      type
14/06/2022     50%       daily
14/07/2022     46%       daily

This resembles the hourly panda frame called sixtymin_df:

date         time         change   type
14/06/2022   12:00:00     27%      hourly
14/06/2022   13:00:00     12%      hourly

This is the desired outcome:

    date         time         change   type
    14/06/2022                50%      daily
    14/06/2022   12:00:00     27%      hourly
    14/06/2022   13:00:00     12%      hourly
    14/07/2022                46%      daily

I would like to be able to list them primarily on date, then time.

CodePudding user response：

You can concatenate the two tables and then reorder the columns and sort the rows

import pandas as pd

daily_df = pd.DataFrame({
    'date':['14/06/2022','14/07/2022'],
    'change':['50%','46%'],
    'type':['daily','daily'],
})

sixtymin_df = pd.DataFrame({
    'date':['14/06/2022','14/06/2022'],
    'time':['12:00:00','13:00:00'],
    'change':['27%','12%'],
    'type':['hourly','hourly'],
})

combined_df = pd.concat((daily_df,sixtymin_df))

column_order = ['date','time','change','type']
sort_by = ['date','time']

combined_df = combined_df[column_order].sort_values(
    sort_by,
    na_position='first',
    key=pd.to_datetime,
)
combined_df