Home Take columns from a dataframe and join them with another dataframe based on the similar 'DateTime' between the 2 dataframes using "isin()" fonction

Questions

Take columns from a dataframe and join them with another dataframe based on the similar 'DateTime' between the 2 dataframes using "isin()" fonction

byMR

March 30, 2022

Good morning,

I have a small problem with using the isin() function. I’m currently working on this code :

import pandas as pd

da = pd.DataFrame()
da['Date'] = ["29/07/2021", "29/07/2021", "30/07/2021", "30/07/2021", "31/07/2021", "31/07/2021", "01/08/2021", "01/08/2021", "02/08/2021"]
da['Time'] = ["06:48:00", "06:59:00", "07:14:00", "08:12:00", "08:42:00", "08:57:00", "05:45:00", "05:55:00", "06:05:00"]
da['DateTime'] = pd.to_datetime(da.pop('Date')) + pd.to_timedelta(da.pop('Time'))
da['DateTime'] = da['DateTime'].dt.strftime('%Y-%m-%d %H:%M')
print(da.head())

df = pd.DataFrame()
df['Date'] = ["29/07/2021", "29/07/2021", "29/07/2021", "29/07/2021", "30/07/2021", "30/07/2021", "30/07/2021", "30/07/2021", "31/07/2021", "31/07/2021", "01/08/2021", "01/08/2021", "02/08/2021"]
df['Time'] = ["06:48:00", "06:53:00", "06:56:00", "06:59:00", "07:14:00", "07:18:00", "07:40:00", "08:12:00", "08:42:00", "08:57:00", "05:45:00", "05:55:00", "06:05:00"]
df["Column1"] = [0.011534891, 0.013458399,  0.017792937, 0.018807581, 0.025931434, 0.025163517, 0.026561283, 0.027743659, 0.028854, 0.000383506, 0.000543031, 0.000342, 0.000313769]
df["Column2"] = [8.4021, 8.4421, 8.4993, 8.545, 8.3627, 8.5518, 8.6266, 8.6455, 8.485, 8.545, 8.415, 8.475, 8.505]
df["Column3"] = [0.000270475, 0.000313769,  0.000383506,  0.000414331,  0.000533619,  0.000505081,  0.000533131,  0.000543031,  0.000342, 0.011534891, 0.013458399, 0.025931434, 0.025163517]
df['DateTime'] = pd.to_datetime(df.pop('Date')) + pd.to_timedelta(df.pop('Time'))
df['DateTime'] = df['DateTime'].dt.strftime('%Y-%m-%d %H:%M')
print(df.head)

filter1 = da['DateTime'].isin(df['DateTime'])
print(df.loc[filter1].head(10000))

I’m trying to see if the datetime in the da dataframe are present in the df dataframe, and if yes, i want to take the column1, column2, column3 values and join them with the da dataframe.

Thank you for your time and have a great day !

>Solution :

No need to check anything, just perform a left merge directly:

da.merge(df, on='DateTime', how='left')

output:

           DateTime   Column1  Column2   Column3
0  2021-07-29 06:48  0.011535   8.4021  0.000270
1  2021-07-29 06:59  0.018808   8.5450  0.000414
2  2021-07-30 07:14  0.025931   8.3627  0.000534
3  2021-07-30 08:12  0.027744   8.6455  0.000543
4  2021-07-31 08:42  0.028854   8.4850  0.000342
5  2021-07-31 08:57  0.000384   8.5450  0.011535
6  2021-01-08 05:45  0.000543   8.4150  0.013458
7  2021-01-08 05:55  0.000342   8.4750  0.025931
8  2021-02-08 06:05  0.000314   8.5050  0.025164