1

I have a dataframe with two columns that are:

  • A datetime column with the following format %Y-%m-%d
  • An int column

They represent a day and an hour. I want to merge because then I want to give to prophet so I need to convert to this format:

YYYY-MM-DD HH:MM:SS

I tried this way:

example={"date":["2018-04-18","2018-04-18","2018-04-18"],"alert_h":[4,17,23]}

df=pd.DataFrame(example)

df['date']=pd.to_datetime(df['date']) #I am converting str to datetime just for creating the example, I have already in the original df a datetime format

df.assign(date_h=pd.to_datetime(df[['date','alert_h']], format='%Y-%m-%d %h'))

I receive the following error message.

ValueError: to assemble mappings requires at least that [year, month, day] be specified: [day,month,year] is missing

I read these following questions:

If this is a duplicate, feel free to close this question I will study any other link because I was not able to find a specific question to solve my issue.

Andrea Ciufo
  • 359
  • 1
  • 3
  • 19

1 Answers1

2

1st method:

newdf=df['date'].str.split('-',expand=True).assign(hour=df['alert_h'])
newdf=newdf.astype(str)
newdf.columns=['year','month','day','hour']
df['date_h']=pd.to_datetime(newdf,format='%Y-%m-%d %h:%m:%s')

2nd method:

example={"date":["2018-04-18","2018-04-18","2018-04-18"],"alert_h":[4,17,23]}
df=pd.DataFrame(example)
date_h=df['date']+' '+df['alert_h'].astype(str)+':00:00'
df['date']=pd.to_datetime(df['date'])
df['date_h']=pd.to_datetime(date_h)

Edit(3rd method):

Method by @MrFuppes (It is more efficient):

df['date_h']=pd.to_datetime(df['date']) + pd.to_timedelta(df['alert_h'], unit='h')
Anurag Dabas
  • 23,866
  • 9
  • 21
  • 41