Typeerror: Passing Perioddtype Data Is Invalid. Use `data.to_timestamp()` Instead

How can I convert a date column with format of 2014-09 to format of 2014-09-01 00:00:00.000? The previous format is converted from df['date'] = pd.to_datetime(df['date']).dt.to_per

Solution 1:

First idea is convert periods to timestamps by Series.to_timestamp and then use Series.dt.strftime:

print (df)
      date
0  2014-09

print (df.dtypes)
date    period[M]
dtype: object

df['date'] = df['date'].dt.to_timestamp('s').dt.strftime('%Y-%m-%d %H:%M:%S.000')
print (df)
                      date
0  2014-09-01 00:00:00.000

Or simply add last values same for each value:

df['date'] = df['date'].dt.to_timestamp('s').dt.strftime('%Y-%m-%d %H:%M:%S').add('.000')
print (df)
                      date
0  2014-09-01 00:00:00.000

Or:

df['date'] = df['date'].dt.strftime('%Y-%m').add('-01 00:00:00.000')
print (df)
                      date
0  2014-09-01 00:00:00.000

Solution 2:

use %f for milliseconds

df['date'] =  pd.to_datetime(df['date'], format='%Y-%m-%d %H:%M:%S.%f')

sample code is

df = pd.DataFrame({
    'Date': ['2014-09-01 00:00:00.000']
})
df['Date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d %H:%M:%S.%f')
df

which gives you the following output

Date02014-09-01

to convert 2014-09 in Period to 2014-09-01 00:00:00.000, we can do as follows

df = pd.DataFrame({
    'date': ['2014-09-05']
})
df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d')
df['date'] = pd.to_datetime(df['date']).dt.to_period("M")
df['date'] = df['date'].dt.strftime('%Y-%m-01 00:00:00.000')
df

Solution 3:

Try stripping the last 3 digits

print(pd.to_datetime(df['date']).dt.strftime('%Y-%m-%d %H:%M:%S.%f')[0][:-3])

Output:

2014-09-01 00:00:00.000

Solution 4:

In the event the other answers don't work, you could try

df.index = pd.DatetimeIndex(df.date).to_period('s')
df.index

Which should show the datetimeindex object with the frequency set as 's'