CODEWITHSUNDEEP
pythonpandas
boioboi
boioboi

Reputation: 67

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

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.

Upvotes: 0

Views: 128

Answers (1)

mitoRibo
mitoRibo

Reputation: 4548

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

Upvotes: 1

Related Questions