How do I properly modify a cell in a dataframe?

Question

I've got a code where I'm modifying cells like this: IBM["PNL"][2]=3. It works, but it shows up a warning:

A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

From what I can read in the article, a proper way of modifying the value would had been IBM.loc[2,"PNL"]=3. However, that doesn't work for me, and it fails with following error:

Traceback (most recent call last):

  File "<ipython-input-25-10debbad977d>", line 1, in <module>
    IBM_dataframe.loc[0,"PNL"]

  File "C:\Users\menkaur\Anaconda2\lib\site-packages\pandas\core\indexing.py", line 1310, in __getitem__
    return self._getitem_tuple(key)

  File "C:\Users\menkaur\Anaconda2\lib\site-packages\pandas\core\indexing.py", line 796, in _getitem_tuple
    return self._getitem_lowerdim(tup)

  File "C:\Users\menkaur\Anaconda2\lib\site-packages\pandas\core\indexing.py", line 922, in _getitem_lowerdim
    section = self._getitem_axis(key, axis=i)

  File "C:\Users\menkaur\Anaconda2\lib\site-packages\pandas\core\indexing.py", line 1482, in _getitem_axis
    self._has_valid_type(key, axis)

  File "C:\Users\menkaur\Anaconda2\lib\site-packages\pandas\core\indexing.py", line 1409, in _has_valid_type
    key = self._convert_scalar_indexer(key, axis)

  File "C:\Users\menkaur\Anaconda2\lib\site-packages\pandas\core\indexing.py", line 196, in _convert_scalar_indexer
    return ax._convert_scalar_indexer(key, kind=self.name)

  File "C:\Users\menkaur\Anaconda2\lib\site-packages\pandas\tseries\base.py", line 591, in _convert_scalar_indexer
    self._invalid_indexer('index', key)

  File "C:\Users\menkaur\Anaconda2\lib\site-packages\pandas\indexes\base.py", line 1284, in _invalid_indexer
    kind=type(key)))

TypeError: cannot do index indexing on <class 'pandas.tseries.index.DatetimeIndex'> with these indexers [2] of <type 'int'>

Now, I'm confused

What am I doing wrong?

ayhan · Answer 1 · 2017-05-14T20:39:53.053

Now that .ix is deprecated, you have two alternatives:

IBM.loc[IBM.index[2], "PNL"] = 3

This is label based indexing. Since you need the label but you have the position, you use IBM.index[2] to return the label. Or,

IBM.iloc[2, IBM.columns.get_loc('PNL')] = 3

This is position based indexing. To get the position of column PNL, you use get_loc.

The error was because you were trying to use position based indexing for rows but label based indexing for columns. .ix was designed to handle such cases but it is deprecated. Here are details as noted by @jezrael.

piRSquared · Accepted Answer · 2017-05-14T20:54:52.337

Assuming IBM is a pd.DataFrame, IBM["PNL"] is a pd.Series. The [] (square brackets) calls the __getitem__ method and returns a series object. You then call the __getitem__ method on the series that was returned with IBM["PNL"][2], namely the [2] part. That's fine for now, even if a bit confusing. The problem occurred when you tried to assign to it. IBM["PNL"][2] = 3 was telling pandas to assign to the 2nd element of the pd.Series IBM["PNL"] which is a view of the "PNL" column inside the IBM dataframe... dizzy yet?

So the answer is to assign directly to the IBM dataframe with loc, iloc, at, iat, or set_value using proper indexers.

loc
Allows you to pass 1-D arrays as indexers. Arrays can be either slices (subsets) of the index or column, or they can be boolean arrays which are equal in length to the index or columns.

Special Note: when a scalar indexer is passed, loc can assign a new index or column value that didn't exist before.

# described by @ayhan
IBM.loc[IBM.index[2], 'PNL'] = 3

iloc
Similar to loc except with positions rather that index values. However, you cannot assign new columns or indices.

# described by @ayhan
IBM.iloc[2, IBM.columns.get_loc('PNL')] = 3

at
Works very similar to loc for scalar indexers. Cannot operate on array indexers. Can! assign new indices and columns

IBM.at[IBM.index[2], 'PNL'] = 3

iat
Works similarly to iloc. Cannot work in array indexers. Cannot! assign new indices and columns.

IBM.iat[2, IBM.columns.get_loc('PNL')] = 3

set_value
Works very similar to loc for scalar indexers. Cannot operate on array indexers. Can! assign new indices and columns

IBM.set_value(IBM.index[2], 'PNL', 3)

set_value with takable=True
Works similarly to iloc. Cannot work in array indexers. Cannot! assign new indices and columns.

IBM.set_value(2, IBM.columns.get_loc('PNL'), 3, takable=True)

This would be very good at this question too http://stackoverflow.com/questions/28757389/loc-vs-iloc-vs-ix-vs-at-vs-iat — ayhan, May 14 '17 at 20:58

How do I properly modify a cell in a dataframe?

2 Answers2