Datetime column coerced to int when setting with .loc and slice

user_10586400 0
Viewed 356

I have a column of datetimes and need to change several of these values to new datetimes. When I set the values using df.loc[indices, 'col'] = new_datetimes, the unaffected values are coerced to int while the new set values are in datetime. If I set the values one at a time, no type coercion occurs.

For illustration I created a sample df with just one column.

df = pd.DataFrame([dt.datetime(2019,1,1)]*5)
df.loc[[1,3,4]] = [dt.datetime(2019,1,2)]*3
df

This produces the following: output

If I change indices 1,3,4 individually:

df = pd.DataFrame([dt.datetime(2019,1,1)]*5)
df.loc[1] = dt.datetime(2019,1,2)
df.loc[3] = dt.datetime(2019,1,2)
df.loc[4] = dt.datetime(2019,1,2)
df

I get the correct output: output

A suggestion was to turn the list to a numpy array before setting, which does resolve the issue. However, if you try to set multiple columns (some of which are not datetime) using a numpy array, The issue arises again.

In this example the dataframe has two columns and I try to set both columns.

df = pd.DataFrame({'dt':[dt.datetime(2019,1,1)]*5, 'value':[1,1,1,1,1]})
df.loc[[1,3,4]] = np.array([[dt.datetime(2019,1,2)]*3, [2,2,2]]).T
df

This gives the following output: output

Can someone please explain what is causing the coercion and how to prevent it from doing so? The code I wrote that uses this was written over a month ago and used to work just fine, could it be one of those warnings about future version of pandas deprecating certain functionalities?

An explanation of what is going on would be greatly appreciated because I wrote a other codes that likely employ similar functionality want to make sure everything works as intended.

pandas slice datetime
2 Answers

The solution proposed by w-m has such an "awkward detail" than the result column has also the time part (it didn't have it before).

I have also such a remark, that DataFrames are tables not Series, so they have columns, each with its name and it is a bad habit to rely on default column names (consecutive numbers).

So I propose another solution, addressing both above issues:

To create the source DataFrame I executed:

df = pd.DataFrame([dt.datetime(2019, 1, 1)]*5, columns=['c1'])

Note that I provided a name for the only column.

Then I created another DataFrame:

df2 = pd.DataFrame([dt.datetime(2019,1,2)]*3, columns=['c1'], index=[1,3,4])

It contains your "new" dates and the numbers which you used in loc I set as the index (again with the same column name).

Then, to update df, use (not surprisingly) df.update:

df.update(df2)

This function performs in-place update, so if you print(df), you will get:

          c1
0 2019-01-01
1 2019-01-02
2 2019-01-01
3 2019-01-02
4 2019-01-02

As you can see, under indices 1, 3 and 4 you have new dates and there is no time part, just like before.

user_73884770

[dt.datetime(2019,1,2)]*3 is a Python list of objects. This particular list happens to contain only datetimes, but Pandas does not seem to recognize that, and treats it as it is - a list of any kind of objects.

If you convert it into a typed array, then Pandas will keep the original dtype of the column intact:

df.loc[[1,3,4]] = np.asarray([dt.datetime(2019,1,2)]*3)

I hope this workaround helps you, but you may still want to file a bug with Pandas. I don't have an explanation as to why the datetime objects should be coerced to ints in the first output example.

user_4637960
Related