pd.to_datetime doesn't respect the format

Viewed 2753

I have the following dataframe:

    month     value
0   1949-01    3
1   1949-02    4
2   1949-03    5

df['month'] = pd.to_datetime(df['month'], format= '%Y/%m')

I would like to obtain the month in the following format:

 1949/01

But the output is always this:

   month        value
0   1949-01-01    3
1   1949-02-01    4
2   1949-03-01    5

Why does it automatically add the day and doesn't respect the format?

2 Answers

That's the format that pandas datetime operates with. If you want you can transform that datetime format using dt.strftime

df['month'] = df['month'].dt.strftime('%Y/%m')

Or you could take an easier approach to start with and simply use the mapping function, no need to get involved with datetime formats

df['month'] = df['month'].map(lambda x: x.replace('-', '/'))

I think you are confusing how the information is stored (the 'dtype') and how it's being displayed to you. The example code below illustrates this:

import pandas as pd

# create sample dataframe where month is a string
df = pd.DataFrame({'month_str':['1949-01', '1949-02', '1949-03']})

# now create a new column where you have converted the string to a datetime
df['month_datetime'] = pd.to_datetime(df['month_str'])

# now convert the datetime back to a string with your desired format
df['month_new_str'] = df['month_datetime'].dt.strftime('%Y/%m')

# skip all the fooling around with datetimes and just manipulate it as a string directly

df['month_new_str2'] = df['month_str'].apply(lambda x: x.replace('-', '/'))

print(df.dtypes)
print(df)

This results in the following output:

month_str                 object
month_datetime    datetime64[ns]
month_new_str             object
month_new_str2            object
dtype: object
  month_str month_datetime month_new_str month_new_str2
0   1949-01     1949-01-01       1949/01        1949/01
1   1949-02     1949-02-01       1949/02        1949/02
2   1949-03     1949-03-01       1949/03        1949/03

Note that the original 'month_str' column has a dtype of object (it's a string). When you called to_datetime we converted it to a datetime type (no need to specify the format, pandas figures it out). But when displayed, pandas is displaying it as a full date (which is why you are seeing the day field). As @sds points out, if you are just wanting to switch out the dash for a slash then you can just manipulate the original string to produce a new string ('month_new_str2').

Related