Reputation: 134
I have to dataframes df1 and df2
Contents of df1 are:
date high low close C B T R
1 2020-09-16 1.29257 1.28146 1.28884 1.287623 1.287015 1.288232 0.000000
2 2020-09-17 1.30074 1.28747 1.29662 1.294943 1.294105 1.295782 1.298102
Contents of df2 are:
time open high low
0 2020-09-16 22:54:00 1.29708 1.29711 1.29695
1 2020-09-16 22:55:00 1.29698 1.29703 1.29681
2 2020-09-17 22:56:00 1.29701 1.29709 1.29689
3 2020-09-17 22:57:00 1.29702 1.29720 1.29701
4 2020-09-17 22:58:00 1.29717 1.29720 1.29715
I want to add two dataframes. In a way that column C of df1 will be added to df2 should result in new dataframe That must be constant with respect to time.
Expected dataframe df3 will be as below
time open high low c
0 2020-09-16 22:54:00 1.29708 1.29711 1.29695 1.287623
1 2020-09-16 22:55:00 1.29698 1.29703 1.29681 1.287623
2 2020-09-17 22:56:00 1.29701 1.29709 1.29689 1.294943
3 2020-09-17 22:57:00 1.29702 1.29720 1.29701 1.294943
4 2020-09-17 22:58:00 1.29717 1.29720 1.29715 1.294943
C is 1.28763 for row 0 and 1 because c is 1.28763 for the date 2020-09-16.
C is 1.294943 for row 2.3 and 4 because c is 1.294943 for the date 2020-09-17.
Upvotes: 1
Views: 97
Reputation: 26686
Lets try map the dict
of df1.date: df1.C
to the extract of date in df2.time
.
df2['C']=(pd.to_datetime(df2.time).dt.date).astype(str).map(dict(zip(df1.date,df1.C)))
How it works
#Extract date from df2.time
df2['temp']=pd.to_datetime(df2.time).dt.date
#Create dict from df1.date and df1.C
D=dict(zip(df1.date,df1.C))
#Create new column df['C'] by mapping D to df2.temp
df2.temp.map(D)
Outcome
time open high low C
0 2020-09-16 22:54:00 1.29708 1.29711 1.29695 1.287623
1 2020-09-16 22:55:00 1.29698 1.29703 1.29681 1.287623
2 2020-09-17 22:56:00 1.29701 1.29709 1.29689 1.294943
3 2020-09-17 22:57:00 1.29702 1.29720 1.29701 1.294943
4 2020-09-17 22:58:00 1.29717 1.29720 1.29715 1.294943
Alternatively as suggested by @Erfan
#Rename columns of df1 as follows
df1=df1[["date", "C"]].rename(columns={"date": "time"})
#Coerce df2.time to date
df2['time']=pd.to_datetime(df2['time']).dt.date
#Merge df2 and df1
df2.merge(df1, how='left')
Upvotes: 1
Reputation: 904
First make sure that df['time']
is of datetime64
type
df2['time'] = df2.time.astype('datetime64')
Then extract the date into a new column
df2['date'] = df2.time.dt.date.astype('datetime64')
Then merge the two dataframes, selecting only date
and C
from df
(if C
is all you're interested in keeping).
df3 = pd.merge(df2, df[['date', 'C']], on='date')
Finally you can drop the date
column we added in step 2
df3 = df3.drop(columns='date')
Upvotes: 2