1

Cannot covert 'YYYY-MM' string to YYYY-MM datetime using datetime64 for data in pandas DataFrame.

np.datetime64 works to convert date string(s) of 'YYYY-MM' to datetime when stored in a scalar or array, but not when same data is accessed via a DateFrame.

What I want to do is convert a column dates (format: YYYY-MM) to datetime data (with or without adding another column).

csv file data:

month, volume
2019-01, 100
2019-02, 110

Sample Code:

import pandas as pd
import numpy as np

df=pd.read_csv (r'file location')
df["date"]=df["month"].apply(np.datetime64)

# Input (month): 2013-01
# Expected output (date): 2013-01
# Actual output (date): 2013-01-01

So, the datetime64 changes YYYY-MM to YYYY_MM-01 (Also, YYYY is converted to YYYY-01-01)

user3483203
  • 50,081
  • 9
  • 65
  • 94
max
  • 41
  • 1
  • 6
  • 1
    what are you then trying to do with you `date` column. This is how pandas represents datetimes – dan_g May 15 '19 at 20:05
  • all i'm trying to do is import a file with monthly figures from csv (or excel) file. So, 2019-01, 100 {first row} would be the monthly volume for January, 2019. In order to perform further operations, I though it would be best to have dates in datetimes. What is the best practice for dates (monthly, yearly) in pandas? – max May 15 '19 at 21:13

1 Answers1

1

Perhaps you're looking for pd.Period:

In [11]: df.date.apply(pd.Period, freq='M')
Out[11]:
0   2019-01
1   2019-02
Name: date, dtype: object

Similarly, but without the apply:

In [12]: pd.to_datetime(df.date).dt.to_period(freq='M')
Out[12]:
0   2019-01
1   2019-02
Name: date, dtype: object
Andy Hayden
  • 359,921
  • 101
  • 625
  • 535
  • Thanks. I now understand that I need to convert my data into panda date period objects. Am I to use datetime rather than datetime64? I'm still unable to convert my file data (YYYY-MM) in a period objects that I can perform operations, such as incrementing date by x months, etc. – max May 15 '19 at 22:31
  • @max i think it depends what you're doing, usually datetime64 is sufficient. – Andy Hayden May 15 '19 at 22:47