ValueError: Incompatible indexer with Series while adding date to Date to Data Frame

Question

I am new to python and I can't figure out why I get this error: ValueError: Incompatible indexer with Series.

I am trying to add a date to my data frame.

The date I am trying to add:

date = (chec[(chec['Día_Sem']=='Thursday') & (chec['ID']==2011957)]['Entrada'])
date

Date output:

56   1900-01-01 07:34:00
Name: Entrada, dtype: datetime64[ns]

Then I try to add 'date' to my data frame using loc:

rep.loc[2039838,'Thursday'] = date
rep

And I get this error:

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-347-3e0678b0fdbf> in <module>
----> 1 rep.loc[2039838,'Thursday'] = date
      2 rep

~/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in __setitem__(self, key, value)
    188             key = com.apply_if_callable(key, self.obj)
    189         indexer = self._get_setitem_indexer(key)
--> 190         self._setitem_with_indexer(indexer, value)
    191 
    192     def _validate_key(self, key, axis):

~/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in _setitem_with_indexer(self, indexer, value)
    640                 # setting for extensionarrays that store dicts. Need to decide
    641                 # if it's worth supporting that.
--> 642                 value = self._align_series(indexer, Series(value))
    643 
    644             elif isinstance(value, ABCDataFrame):

~/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in _align_series(self, indexer, ser, multiindex_indexer)
    781             return ser.reindex(ax)._values
    782 
--> 783         raise ValueError('Incompatible indexer with Series')
    784 
    785     def _align_frame(self, indexer, df):

ValueError: Incompatible indexer with Series

Answer 1

Try date.iloc[0] instead of date:

rep.loc[2039838,'Thursday'] = date.iloc[0]

Because date is actually a Series (so basically like a list/array) of the values, and .iloc[0] actually selects the value.

Answer 2

You use loc to get a specific value, and your date type is a series or dataframe, the type between the two can not match, you can change the code to give the value of date to rep.loc[2039838,'Thursday'], for example, if your date type is a series and is not null, you can do this:

rep.loc[2039838,'Thursday'] = date.values[0]

Answer 3

I was also facing similar issue but in a different scenario. I came across threads of duplicate indices, but of-course that was not the case with me. What worked for me was to use .at in place of .loc. So you can try and see if it works

rep['Thursday'].at[2039838] = date.values[0]