CODEWITHSUNDEEP
pythonpandasdata-science
akshay bhatt
akshay bhatt

Reputation: 1

How to combine 3 separate columns of year(2 digit) ,month and day into single date column

I am combining 3 seoerate columns of year,month and day into a single column of my dataframe. But the year is in 2 digit which is giving error.

I have tried to_datetime() to do the same in jupyter notebook

Dataframe is in this form:

Yr Mo Dy   RPT   VAL   ROS   KIL   SHA   BIR   DUB   CLA   MUL   CLO   BEL   
61  1  1 15.04 14.96 13.17  9.29   NaN  9.87 13.67 10.25 10.83 12.58 18.50 
61  1  2 14.71   NaN 10.83  6.50 12.62  7.67 11.50 10.04  9.79  9.67 17.54 
61  1  3 18.50 16.88 12.33 10.13 11.17  6.17 11.25   NaN  8.50  7.67 12.75 


data.rename(columns={'Yr':'Year','Mo':'Month','Dy':'Day'},inplace=True)
data['Date']=pd.to_datetime(data[['Year','Month','Day']],format='%y%m%d')

The error i am getting is:

cannot assemble the datetimes: time data 610101 does not match format '%Y%m%d' (match)

Upvotes: 0

Views: 153

Answers (1)

jezrael
jezrael

Reputation: 862681

There is problem to_datetime with specify columns ['Year','Month','Day'] need YYYY format, so need alternative solution, because year is YY only:

s = data[['Yr','Mo','Dy']].astype(str).apply('-'.join, 1)
data['Date'] = pd.to_datetime(s, format='%y-%m-%d')
print (data)
   Yr  Mo  Dy    RPT    VAL    ROS    KIL    SHA   BIR    DUB    CLA    MUL  \
0  61   1   1  15.04  14.96  13.17   9.29    NaN  9.87  13.67  10.25  10.83   
1  61   1   2  14.71    NaN  10.83   6.50  12.62  7.67  11.50  10.04   9.79   
2  61   1   3  18.50  16.88  12.33  10.13  11.17  6.17  11.25    NaN   8.50   

     CLO    BEL       Date  
0  12.58  18.50 2061-01-01  
1   9.67  17.54 2061-01-02  
2   7.67  12.75 2061-01-03

Upvotes: 1

Related Questions