0

I have a pandas dataframe with unix time that gets converted to a timestamp (all fine). I then want to extract the timestamp column - as a list - and retain the information. But for some reason this gets converted back to unix time when I do it (with even more trailing zeros...):

import pandas as pd

data = {'servertime':[1576887840000,1576887900000,1576887960000], 'val':[1,2,3]}

df = pd.DataFrame(data)

df['timestamp'] = df['servertime'].apply(lambda x: datetime.fromtimestamp(x/1000))

t = df['timestamp'].values.tolist()
t
Out[1]: [1576887840000000000, 1576887900000000000, 1576887960000000000]

Any ideas?

fffrost
  • 1,659
  • 1
  • 21
  • 36
  • Do you need `t = df['timestamp'].tolist()` ? – jezrael Dec 22 '19 at 09:53
  • Problem is if use `.values` it convert to native format of datetimes, so it is nanoseconds unix format – jezrael Dec 22 '19 at 09:54
  • @jezrael No I don't need that line, it's just that I need to convert it. I assumed that this line was the problem, but I'm not sure how else I can achieve this. – fffrost Dec 22 '19 at 09:58
  • So you need `df['timestamp'] = pd.to_datetime(df['servertime'], unit='ms')` ? – jezrael Dec 22 '19 at 09:59
  • @jezrael thanks for that, it also works. But this was not the question - I want to now extract that column into a list of datetime.datetime() values. – fffrost Dec 22 '19 at 10:02
  • Use `t = df['timestamp'].dt.to_pydatetime()` – jezrael Dec 22 '19 at 10:05
  • @jezrael thanks again, it now works if i use `t = df['timestamp'].dt.to_pydatetime().tolist()` (I needed it as a list, not an array). I'm wondering why you closed the question though? The solution had nothing to do with the linked page... Why not reopen it and post it as a proper answer? – fffrost Dec 22 '19 at 10:09
  • Sorry, I was offline. So for first check it seems dupe, sorry. So reopened. – jezrael Dec 22 '19 at 12:16

1 Answers1

3

You can use to_datetime with unit='ms' for improve performance and for list of datetimes use Series.dt.to_pydatetime with tolist():

df['timestamp'] = pd.to_datetime(df['servertime'], unit='ms')
print (df)
      servertime  val           timestamp
0  1576887840000    1 2019-12-21 00:24:00
1  1576887900000    2 2019-12-21 00:25:00
2  1576887960000    3 2019-12-21 00:26:00

t = df['timestamp'].dt.to_pydatetime().tolist()
print (t)
[datetime.datetime(2019, 12, 21, 0, 24), 
 datetime.datetime(2019, 12, 21, 0, 25), 
 datetime.datetime(2019, 12, 21, 0, 26)]
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252