merge two dataframe with same month and date but different year

user_15887535 0
Viewed 180

Here I have 2 dataframes. the first one contains all hours from 2020 to 2021

df['DT'] = pd.date_range('2020-01-01','2021-01-01',freq = 'H')

     DT                    
0    2020-01-01 00:00:00 
1    2020-01-01 01:00:00   
2    2020-01-01 02:00:00   
3    2020-01-01 03:00:00   
4    2020-01-01 04:00:00   
...
8780 2020-12-31 20:00:00   
8781 2020-12-31 21:00:00   
8782 2020-12-31 22:00:00   
8783 2020-12-31 23:00:00  
8784 2021-01-01 00:00:00

another dataframe contains all hours from 2021 to 2022, and some readings

df2['DT'] = pd.date_range('2021-01-01','2022-01-01',freq = 'H')
df2['Reading'] = Reading['Reading']

     DT                   Reading
0    2021-01-01 00:00:00  3
1    2021-01-01 01:00:00  2
2    2021-01-01 02:00:00  4
3    2021-01-01 03:00:00  3
4    2021-01-01 04:00:00  2
...                  ...
8756 2021-12-31 20:00:00  6
8757 2021-12-31 21:00:00  4
8758 2021-12-31 22:00:00  3
8759 2021-12-31 23:00:00  7
8760 2022-01-01 00:00:00  1

The reading for everyday in 2020 is the same as 2021, so I wanna duplicate the reading column to the 2021 to 2020. however, 2020 is a leap year which means a whole day will be having no readings. I want to leave them as nan bc i have another file to fill in the 24 hours. in order to do that, i want to do something like df2 = df2.merge(df1,on="DT",how = 'left'),which will leave the Feb29th nan, however they have different years. how can i extract the date and month (and hours) from the DT column and merge them?

Tried strftime before but it doesn't really work when I tried to merge

pandas dataframe datetime python
1 Answers

Something like this might work for you.


import pandas as pd

df = pd.DataFrame()
df2 = pd.DataFrame()

df['DT'] = pd.date_range('2020-01-01','2020-12-31',freq = 'H')
df2['DT'] = pd.date_range('2021-01-01','2021-12-31',freq = 'H')

df['merg_col'] = df['DT'].dt.strftime("%m%d%H")
df2['merg_col'] = df2['DT'].dt.strftime("%m%d%H")

df2 = df2.merge(df, on='merg_col', how='left')

print(df2)

                    DT_x merg_col                DT_y
0    2020-01-01 00:00:00   010100 2021-01-01 00:00:00
1    2020-01-01 01:00:00   010101 2021-01-01 01:00:00
2    2020-01-01 02:00:00   010102 2021-01-01 02:00:00
3    2020-01-01 03:00:00   010103 2021-01-01 03:00:00
4    2020-01-01 04:00:00   010104 2021-01-01 04:00:00
...                  ...      ...                 ...
8756 2020-12-30 20:00:00   123020 2021-12-30 20:00:00
8757 2020-12-30 21:00:00   123021 2021-12-30 21:00:00
8758 2020-12-30 22:00:00   123022 2021-12-30 22:00:00
8759 2020-12-30 23:00:00   123023 2021-12-30 23:00:00
8760 2020-12-31 00:00:00   123100 2021-12-31 00:00:00

I changed your date range to only include a single year in each DF, instead of going Jan 1 to Jan 1 its Jan 1 to Dec 31. otherwise, the merge column won't be unique.

user_148986970
Related