How to convert string date to numeric in pandas

Question

I have read a csv file with the command :

df=df.read_csv("path",parse_dates=[['Local Date', 'Local Time']])

I have the following output :

             created_at     Close      Open    Volume     Day
0      01-Mar-2019 00:47  25916.00  25916.00    141383   STABLE
1      01-Mar-2019 00:21  25916.00  25916.00         1   STABLE
2      01-Mar-2019 00:20  25916.00  25916.00        74   STABLE
3      01-Mar-2019 00:19  25916.00  25916.00       222   STABLE
4      01-Mar-2019 00:18  25916.00  25916.00    108257   STABLE
...           ...           ...        ...         ...     ...

I would like to convert the column "created_at" in the following way :

           created_at     Close      Open    Volume     Day
0      01-03-2019 00:47  25916.00  25916.00    141383   STABLE
1      01-03-2019 00:21  25916.00  25916.00         1   STABLE
2      01-03-2019 00:20  25916.00  25916.00        74   STABLE
3      01-03-2019 00:19  25916.00  25916.00       222   STABLE
4      01-03-2019 00:18  25916.00  25916.00    108257   STABLE
...           ...           ...        ...         ...     ...

I would like to "convert" the month that is written in "letters" form into a "numerical" form.

Csv file

I tried to use : `df.created_at.str.replace("Mar",03)` It works but I would say it is not really efficient if I want to do it for every month :/ — stefan, Mar 04 '19 at 09:32
@jezrael I am not sure what do you mean by `parse_dates=['created_at'])` The column created is already in parse_dates — stefan, Mar 04 '19 at 09:45
@stefan - So what are original data, first 5 rows before `df=df.read_csv("path",parse_dates=[['Local Date', 'Local Time']])` ? — jezrael, Mar 04 '19 at 09:46
@jezrael - I just add a screenshoot of the csv file in the post — stefan, Mar 04 '19 at 09:51
@stefan, try using `pd.to_datetime` that should work for you. — Karn Kumar, Mar 04 '19 at 09:51
Check dupe, solution is `df['created_at'] = pd.to_datetime(df['created_at'])` — jezrael, Mar 04 '19 at 09:53
That what i mentioned `df['created_at'] =` Just suffix to add to make it effective to DF. However the Question doesn't seems to be exact duplicate what you marked it for it should `possible duplicate` ,read question again carefully and thoroughly! — Karn Kumar, Mar 04 '19 at 09:56
Thank you to everyone for your answers, the solution works well with `df['created_at'] = pd.to_datetime(df['created_at'])` — stefan, Mar 04 '19 at 10:09
@stefan, thats should work as i mentioned in my answer, you just need assign the new value to the column to see the change :-) — Karn Kumar, Mar 04 '19 at 10:11
@stefan, can you check what the column datatypes are: print(df.dtypes) — MEdwin, Mar 04 '19 at 10:35
@MEdwin It was written : `created_at object Close float64 Open object Volume object Day object dtype: object` — stefan, Mar 04 '19 at 11:00
Perfect. Try this: df['created_at_new'] = pd.to_datetime(df['created_at']).dt.strftime('%d-%m-%Y %H:%M') # Creates a new column with the format that you are after. Let me know if it works. — MEdwin, Mar 04 '19 at 11:03

score 1 · Answer 1 · answered Mar 04 '19 at 09:49

1

Try this

df['dt2'] = pd.to_datetime(df.created_at).dt.strftime('%d-%m-%Y %H:%M')

First it will convert string to date then it will convert it in the string with desired format.

answered Mar 04 '19 at 09:49

Vikrant Korde

315
2
11

Karn Kumar · Answer 2 · 2019-03-04T10:04:38.040

1

Just to test try converting your column created_at into pd.to_datetime

DataFrame

>>> df
          created_at
0  01-Mar-2019 00:47
1  01-Mar-2019 00:21
2  01-Mar-2019 00:20

Result:

>>> pd.to_datetime(df['created_at'])
0   2019-03-01 00:47:00
1   2019-03-01 00:21:00
2   2019-03-01 00:20:00
Name: created_at, dtype: datetime64[ns]

Just for the curiosity sake, as mentioned in the comment this can be changed directly to dataframe as follows:

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

edited Mar 04 '19 at 10:04

answered Mar 04 '19 at 09:49

Karn Kumar

8,518
3
27
53

thank you for your answer but unfortunately the code work but there is absolutely no changes – stefan Mar 04 '19 at 10:02
@stefan, if answers helped you mark this as accepted answer [help is here to get it :-)](https://stackoverflow.com/help/someone-answers) – Karn Kumar Mar 04 '19 at 10:17

How to convert string date to numeric in pandas

2 Answers2

DataFrame

Result: