Dataframe column str to Dataframe column date

Question

I have a dataframe column1 dtype obj in format of: ==> this is one column of the dataframe

Col1
02.11.2017
11.11.2017
02.12.2017
25.12.2017

this colum i want to convert to dates column by adding a new column to the df: ==> this is the code to convert the dataframe col1 to a new df column NewDate that generate the error below

df['NewDate'] = pd.to_datetime(df['Col1'], format='%d.%m.%Y')

I want my results to be: ==> this is what i want my result to look like.

Col1          NewDate
02.11.2017    2017-11-02
11.11.2017    2017-11-11
02.12.2017    2017-12-02
25.12.2017    2017-12-25

But my ERROR is:

A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

But when i try this I still get the same error: ==> this is the code i tried because of the warning/error above but get the same warning/error doing so

df.loc[:, 'NewDate'] = pd.to_datetime(df['Col1'].values, format='%d.%m.%Y')

Can someone point me in the right direction? Any help is much appriciated! PS: Very new to python and pandas.

Thanks alot!

What is code above, bellow? Maybe need `df['NewDate'] = pd.to_datetime(df['Col1'], format='%d.%m.%Y')` — jezrael, Jan 25 '18 at 09:47
@Maki This might help https://chrisalbon.com/python/data_wrangling/pandas_time_series_basics/ — Rakesh, Jan 25 '18 at 09:48
Because this error is a bit confused - problem is in line of code above obviously, but error message is about bellow problematic line :( — jezrael, Jan 25 '18 at 09:49
Maybe help [this](https://stackoverflow.com/a/20627316) or [this](https://stackoverflow.com/a/46728170/2901002). — jezrael, Jan 25 '18 at 10:01
@Rakesh thanks for ur reply. but that link aint gonna help. i know the basics. please read my question carefully to avoid clutter thanks. — Maki, Jan 25 '18 at 10:02
@jezrael thats the code. nothing less nothing more. but ofc i have a lot more of records of Col1 theres no "0" or "NaN" values in Col1 that i know for sure. I just want to convert "01.12.2017" to "2017-12-01" to a new df col thats all. — Maki, Jan 25 '18 at 10:14
@Maki - So your all code is only `df['NewDate'] = pd.to_datetime(df['Col1'], format='%d.%m.%Y')` ? But how is created `DataFrame` ? Why do you cannot add your all code to question? Because it is really hard guessing what is your problem :( Or do you ashamed about your code? — jezrael, Jan 25 '18 at 10:53
@Maki - soory, I dont believe. In your program is only one line `df['NewDate'] = pd.to_datetime(df['Col1'], format='%d.%m.%Y')`, nothing else? no `import` no `read_csv` ??? — jezrael, Jan 25 '18 at 11:03
@jezrael ofc theres import os , sys etc. but thats not the part that is getting the problem. — Maki, Jan 25 '18 at 11:07
Yes, I am saying about this. And you think there is no problem, but you are wrong. Exactly there is problem. So is necessary add all codes of your program ;) — jezrael, Jan 25 '18 at 11:08

zipa · Answer 1 · 2018-01-25T10:23:34.393

0

It might seem impractical but this should help you avoid the warning:

df['NewDate'] = df.loc[:, 'Col1'].apply(lambda x: pd.to_datetime(x, format='%d.%m.%Y'))

edited Jan 25 '18 at 10:23

answered Jan 25 '18 at 10:12

zipa

27,316
6
40
58

hi there! i tried this code still gets me the warning/error – Maki Jan 25 '18 at 10:21
Can you please try the edit, I never had warning with the previous version but this one should do it. – zipa Jan 25 '18 at 10:25
No luck :( my pandas version is: 0.21.0 whats urs? – Maki Jan 25 '18 at 10:29
so thats not the problem then. :/ im out of ideas. – Maki Jan 25 '18 at 10:35

score 0 · Answer 2 · 2018-01-25T10:51:56.940

0

The solutions mentioned in comments are correct. I think your problem is with not using copy. Please see the below example.

If you have an original dataframe df and then filtering and getting a subset of it df2. you should use copy to have the values copied properly.

In [8]: df
Out[8]: 
     Col1    NewDate
0  02.11.2017 2017-11-02
1  11.11.2017 2017-11-11 
2  02.12.2017 2017-12-02
3  25.12.2017 2017-12-25

In [9]: df2 = df.head(2)

In [10]: df2.loc[:, 'NewDate'] = pd.to_datetime(df2['Col1'].values, 
         format='%d.%m.%Y')
         /home/ks/miniconda2/envs/citius/lib/python2.7/site-
         packages/pandas/core/indexing.py:476: SettingWithCopyWarning: 

         A value is trying to be set on a copy of a slice from a 
        DataFrame.
        Try using .loc[row_indexer,col_indexer] = value instead

        See the caveats in the documentation: 
        http://pandas.pydata.org/pandas 
        docs/stable/indexing.html#indexing-view-versus-copy
        self.obj[item] = s

In [11]: df2 = df.head(2).copy()

In [12]: df2.loc[:, 'NewDate'] = pd.to_datetime(df2['Col1'].values, format='%d.%m.%Y')

EDIT:

If you are not doing any chaining operation and just want to turn off this warning , add the below line

pd.options.mode.chained_assignment = None

edited Jan 25 '18 at 10:51

answered Jan 25 '18 at 10:13

thanks! but it wont help me if the NewDate column is on another df2. I want to add a new dataframe col NewDate where the Col1 is and not make a new df2 with a NewDate column – Maki Jan 25 '18 at 10:18
Actually there is nothing wrong with your snippet `df2.loc[:, 'NewDate'] = pd.to_datetime(df2['Col1'].values, format='%d.%m.%Y')` The only issue i can forsee is that you might have not used `copy` and might be trying to set value to a slice than an actual copy. – Jan 25 '18 at 10:21
i tried: df2.loc[:, 'NewDate'] = pd.to_datetime(df2['Col1'].values, format='%d.%m.%Y') AND df2.loc[:, 'NewDate'] = pd.to_datetime(df2['Col1'].copy(), format='%d.%m.%Y') nothing seems to work – Maki Jan 25 '18 at 10:25
First tell me if this is the first operation that you are performing on this dataframe or are you slicing it before ? – Jan 25 '18 at 10:28
if selecting a column in a DF called slicing then i am. I have a DF with columns and in one of the columns are the values of: "02.11.2017" which i want to convert to a new column in the DF with a datetime dtype. thats all i wanna do. – Maki Jan 25 '18 at 10:33
If that's all , then the solutions explained here works well. I just tried it in my machine as well `In [24]: df['NewDate'] = pd.to_datetime(df['Col1'], format='%d.%m.%Y') In [25]: df Out[25]: Col1 NewDate 0 02.11.2017 2017-11-02 1 11.11.2017 2017-11-11 2 02.12.2017 2017-12-02 3 25.12.2017 2017-12-25 ` One final question is what is your pandas version and you might want to upgrade if it is a really really old version. – Jan 25 '18 at 10:38
that doesnt work for me. because im getting that error/warning my pandas version is 0.21.0 – Maki Jan 25 '18 at 10:39
Other than the warning do you see that the dataframe is having the required colum and date field correctly formatted? – Jan 25 '18 at 10:44
yeah it does the job but i get the warning. im just afraid that if i have a lot more records than 1000+ that it reproduce and convert false records. – Maki Jan 25 '18 at 10:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/163860/discussion-between-krishna-sangeeth-k-s-and-maki). – Jan 25 '18 at 10:52
what is chain_assignment? – Maki Jan 25 '18 at 11:08

Dataframe column str to Dataframe column date

2 Answers2