0

In my dataframe, I have columns like: "created_at" ,"date","time";

they are something like : "1558522813000", "2019-05-22","21:00:13"

I tried : 

df['created_at'] = pd.to_datetime(df.created_at)

but it generated something like "1970-01-01 00:25:54.068778", which I know it's wrong because it doesn't match the "date" and "time" in the same row (it should be "2019-05-22....")

How is this possible?

some_programmer
  • 3,268
  • 4
  • 24
  • 59
Elena_w
  • 15
  • 5
  • 1
    Google how to use datetime.strftime to get datetime from string – dumbPy May 14 '20 at 10:41
  • 1
    Does this answer your question? [Convert Pandas Column to DateTime](https://stackoverflow.com/questions/26763344/convert-pandas-column-to-datetime) – dumbPy May 14 '20 at 10:43
  • 1
    `1558522813000` is an epoch time in ms, but pandas assumes ns by default - try passing `unit='ms'` and see if that works – Stael May 14 '20 at 10:45

2 Answers2

0

I think df['created_at'] = pd.to_datetime(df.created_at, unit="ms").dt.strftime("%Y-%m-%d") will fix your problem.

Here is a complete example:

>>> import pandas as pd
>>> from datetime import datetime

>>> df = pd.DataFrame([{"created_at": 1558522813000}])
      created_at
0  1558522813000

>>> df['created_at'] = pd.to_datetime(df.created_at, unit="ms").dt.strftime("%Y-%m-%d")
>>> print(df)
   created_at
0  2019-05-22
Anwarvic
  • 12,156
  • 4
  • 49
  • 69
  • I tried : df['created_at'] = pd.to_datetime(df.created_at, unit="ms").dt.strftime("%Y-%m-%d") this does improved to show the actual year but still doesn't match the date in some rows, any idea why this happens? Thank you – Elena_w May 14 '20 at 11:40
  • For example, created_at:"1554068778000",date:"2019-04-01",time:"07:46:18" and after I run the code: created_at : "2019-03-31" – Elena_w May 14 '20 at 11:49
  • This could happen for multiple reasons. It could be due to human error especially when the difference isn't that big. Are there any examples where the **expected date** didn't match the **actual date**? – Anwarvic May 14 '20 at 11:56
  • @ElenaWang 1554068778000 is definitely 2019-03-31 23:46:18 - seems exactly 8h off compared to your date and time, so I suppose it is a timezone problem, ie. your "date" and "time" are in local timezone while "created_at" is in UTC. – Błotosmętek May 14 '20 at 14:32
  • You might try adding 8h = 28800000 ms to all your `created_at` times and see if it fixed the "doesn't match" problem. – Błotosmętek May 14 '20 at 14:34
-1

Check this example: pd.to_datetime(1490195805433502912, unit='ns')

source: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html

You have to set the unit. For the other dates, you can slice them up and provide year, month, etc. to the to_datetime funcion.

Hope this helps.

PythonAmateur742
  • 334
  • 1
  • 7