CODEWITHSUNDEEP
pythonpandasdatetime
Jason Smith
Jason Smith

Reputation: 21

how do i convert int64 column to datetime column

I have a pandas dataframe with column "time" which looks like:

array([43390, 43599, 43605, 43329, 43330, 43604, 43601, 43332, 43602,....43505], dtype=int64)

How do i get this into date time format? I have already tried

t_data["DATE"] = pd.to_datetime(t_data["DATE"].astype(str).str.zfill(6), format='%H%M%S').dt.time

but causes error

ValueError: unconverted data remains: 0

Upvotes: 0

Views: 1155

Answers (2)

Chris
Chris

Reputation: 29732

One way using pandas.Series.str.findall with timedelta:

from datetime import timedelta

def parse(lis):
    h, m, s = map(int, lis)
    return timedelta(hours=h, minutes=m, seconds=s)

tmp = s.str.zfill(6).str.findall(".{2}")
tmp.apply(parse)

Output:

0   04:34:30
1   04:36:39
2   04:36:05
3   04:33:29
4   04:33:30
5   04:36:04
6   04:36:01
7   04:33:32
8   04:36:02
9   04:35:05
dtype: timedelta64[ns]

Upvotes: 0

jsmart
jsmart

Reputation: 3001

The array of integers looks like dates that were extracted from Excel. Each represents the days after the base_date (on/about 1901-01-01).

Here is a way to convert to dates in pandas:

date_offsets = [43390, 43599, 43605, 43329, 43330, 43604, 43601, 43332, 43602]
base_date = pd.Timestamp('1901-01-01')  # check this!
dates = [
    base_date + pd.DateOffset(date_offset) 
    for date_offset in date_offsets
]

print(dates[0:5])

[Timestamp('2019-10-19 00:00:00'), Timestamp('2020-05-15 00:00:00'), Timestamp('2020-05-21 00:00:00'), Timestamp('2019-08-19 00:00:00'), Timestamp('2019-08-20 00:00:00')]

Please check the base date!

Upvotes: 1

Related Questions