Adding column with values from one dataframe to another based on same datetime

Question

I have a big dataframe of datetime and values so I will just be taking a sample dataframe called df1

DateTime            Values
2020-12-26 14:00:00 439.0
2020-12-26 14:00:00 441.0
2020-12-26 14:00:00 461.0
2020-12-27 08:00:00 536.0
2020-12-27 08:00:00 547.0
2020-12-27 08:00:00 484.0
2020-12-27 08:00:00 497.0
2020-12-27 14:00:00 491.0
2020-12-27 14:00:00 512.0
2020-12-27 14:00:00 529.0
2020-12-27 14:00:00 436.0

and I have another full dataframe df 2

DateTime            Trend   
2020-12-18 14:00    no trend    
2020-12-19 08:00    no trend    
2020-12-19 14:00    no trend    
2020-12-21 08:00    no trend        
2020-12-21 14:00    no trend    
2020-12-22 08:00    no trend    
2020-12-22 14:00    decreasing  
2020-12-23 08:00    no trend    
2020-12-23 14:00    no trend    
2020-12-24 08:00    no trend    
2020-12-24 14:00    no trend    
2020-12-25 08:00    no trend    
2020-12-25 14:00    decreasing  
2020-12-26 08:00    no trend
2020-12-26 14:00    decreasing  
2020-12-27 08:00    no trend    
2020-12-27 14:00    no trend

I am trying to create a new column df1[Trend] and assign the trend values based on the right date time

The result should be like this

DateTime            Values    Trend
2020-12-26 14:00:00 439.0     decreasing
2020-12-26 14:00:00 441.0     decreasing
2020-12-26 14:00:00 461.0     decreasing
2020-12-27 08:00:00 536.0     no trend
2020-12-27 08:00:00 547.0     no trend
2020-12-27 08:00:00 484.0     no trend
2020-12-27 08:00:00 497.0     no trend
2020-12-27 14:00:00 491.0     no trend
2020-12-27 14:00:00 512.0     no trend
2020-12-27 14:00:00 529.0     no trend
2020-12-27 14:00:00 436.0     no trend

Is there anyway I could it?

Pablo C · Accepted Answer · 2021-03-29 05:24:51Z

1

Use pandas.DataFrame.merge:

df1.merge(df2)

              DateTime  Values       Trend
0  2020-12-26 14:00:00   439.0  decreasing
1  2020-12-26 14:00:00   441.0  decreasing
2  2020-12-26 14:00:00   461.0  decreasing
3  2020-12-27 08:00:00   536.0    no trend
4  2020-12-27 08:00:00   547.0    no trend
5  2020-12-27 08:00:00   484.0    no trend
6  2020-12-27 08:00:00   497.0    no trend
7  2020-12-27 14:00:00   491.0    no trend
8  2020-12-27 14:00:00   512.0    no trend
9  2020-12-27 14:00:00   529.0    no trend
10 2020-12-27 14:00:00   436.0    no trend

EDIT

You're getting

ValueError: You are trying to merge on datetime64[ns] and object columns. If you wish to proceed you should use pd.concat

because df1["DateTime"] is a datetime column but df2["DateTime"] is a str column then you can't merge. To fix it convert df2["DateTime"] to datetime with pandas.to_datetime:

df2['DateTime'] = pd.to_datetime(df2.DateTime)

edited Mar 29, 2021 at 5:24

answered Mar 29, 2021 at 4:05

Pablo C

4,7612 gold badges10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DDM Over a year ago

I got this error when I do it. ValueError: You are trying to merge on datetime64[ns] and object columns. If you wish to proceed you should use pd.concat However by doing pd.concat if will just combine two dataframes horizontally and result in the remaining to have nan values which is not what I am looking for\

Vishnudev Krishnadas Over a year ago

Convert into datetime columndf2['DateTime'] = pd.to_datetime(df2.DateTime).

Pablo C Over a year ago

@DDM check it now :P

DDM Over a year ago

Thanks alot yes its due to the object type

Pablo C Over a year ago

@DDM Nice! happy coding :)

Collectives™ on Stack Overflow

Adding column with values from one dataframe to another based on same datetime

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related