Set value for particular cell in pandas DataFrame with iloc

Question

I have a question similar to this and this. The difference is that I have to select row by position, as I do not know the index.

I want to do something like df.iloc[0, 'COL_NAME'] = x, but iloc does not allow this kind of access. If I do df.iloc[0]['COL_NAME'] = x the warning about chained indexing appears.

what version of python and pandas are you using? I'm not getting a 'chained indexing' warning on PY3.4.2 with pandas 0.16.1. Is there anything special about how you construct the dataframe? — GG_Python, Jul 22 '15 at 17:02

score 171 · Accepted Answer · edited Nov 27 '20 at 06:42

171

For mixed position and index, use .ix. BUT you need to make sure that your index is not of integer, otherwise it will cause confusions.

df.ix[0, 'COL_NAME'] = x

Update:

Alternatively, try

df.iloc[0, df.columns.get_loc('COL_NAME')] = x

Example:

import pandas as pd
import numpy as np

# your data
# ========================
np.random.seed(0)
df = pd.DataFrame(np.random.randn(10, 2), columns=['col1', 'col2'], index=np.random.randint(1,100,10)).sort_index()

print(df)


      col1    col2
10  1.7641  0.4002
24  0.1440  1.4543
29  0.3131 -0.8541
32  0.9501 -0.1514
33  1.8676 -0.9773
36  0.7610  0.1217
56  1.4941 -0.2052
58  0.9787  2.2409
75 -0.1032  0.4106
76  0.4439  0.3337

# .iloc with get_loc
# ===================================
df.iloc[0, df.columns.get_loc('col2')] = 100

df

      col1      col2
10  1.7641  100.0000
24  0.1440    1.4543
29  0.3131   -0.8541
32  0.9501   -0.1514
33  1.8676   -0.9773
36  0.7610    0.1217
56  1.4941   -0.2052
58  0.9787    2.2409
75 -0.1032    0.4106
76  0.4439    0.3337

edited Nov 27 '20 at 06:42

Joe Ferndz

8,417
2
13
33

answered Jul 22 '15 at 17:14

Jianxun Li

24,004
10
58
76

My index is the result of some selection, and it is composed of not correlative integers:eg, [3,24, 34] ix[0] throws an error. – luna1999 Jul 22 '15 at 17:16
@luna1999 Then maybe try `reset_index` first so that you can use `df.loc[0, 'COL_NAME']`, which causes no confusion. – Jianxun Li Jul 22 '15 at 17:17
1

@luna1999 I've updated my code using `df.columns.get_loc`, and it should work for you. Let me know if this is not the case. – Jianxun Li Jul 22 '15 at 17:24
@luna1999 You are most welcome. Glad that it helped. :-) – Jianxun Li Jul 22 '15 at 17:28
1

@JianxunLi thanks for the ```.iloc``` tip. However, I am puzzled about a thing (correct me if I am wrong): if ```df.iloc[4:7] = x``` returns a copy ```df``` with values [4:7] set to ```x```, why ```df.iloc[4:7,df.columns.get_loc("C")] = x``` is setting to ```x``` to the original ```df```? – user6903745 Feb 17 '17 at 11:15
why does df.iloc[index]["column_name"]=value work? the syntax works to retrieve a value – Golden Lion Oct 22 '20 at 11:43

score 61 · Answer 2 · answered Sep 29 '17 at 16:15

61

One thing I would add here is that the at function on a dataframe is much faster particularly if you are doing a lot of assignments of individual (not slice) values.

df.at[index, 'col_name'] = x

In my experience I have gotten a 20x speedup. Here is a write up that is Spanish but still gives an impression of what's going on.

answered Sep 29 '17 at 16:15

ford prefect

7,096
11
56
83

old, but one thing about df.at is that you cant for example take the last index using: ```df.at[-1, 'col_name'] = x``` instead it'll add a new row with an index name of -1. – WEVERETT May 23 '23 at 07:54

score 16 · Answer 3 · edited Aug 06 '19 at 05:52

16

If you know the position, why not just get the index from that?

Then use .loc:

df.loc[index, 'COL_NAME'] = x

edited Aug 06 '19 at 05:52

Ajinkya

22,324
33
110
161

answered Jul 22 '15 at 17:08

AZhao

13,617
7
31
54

17

`df.loc` is for label-based indexing, not for position-based indexing. You may be referring to doing something like `df.loc[df.index[0], 'COL_NAME'] = x` – noe Oct 07 '16 at 12:03
An example would be nice. You may copy https://gist.githubusercontent.com/MartinThoma/e9b072c5314d3d5d2507aba8a86b0d6e/raw/7341c0adeedfcf77ef893a680cad154e4a5366ac/gistfile1.txt – Martin Thoma Oct 19 '17 at 12:21

ricmarchao · Answer 4 · 2020-09-11T17:38:08.023

14

another way is, you assign a column value for a given row based on the index position of a row, the index position always starts with zero, and the last index position is the length of the dataframe:

df["COL_NAME"].iloc[0]=x

edited Sep 11 '20 at 17:38

answered Jun 25 '18 at 12:53

ricmarchao

920
8
7

4

maybe add a brief discussion of how this is different than the other 4 answers and what the code is doing – Nate Jun 25 '18 at 17:13
1

For modern versions of pandas, you get a SettingWithCopyWarning when you try this (see https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy) – Charles Plager Nov 10 '21 at 19:32
This works when we have specific index and we don't want to use reset_index. `df.iloc[0, df.columns.get_loc('COL_NAME')] = x` doesn't change the values if we have specific index rather than usual integer indexes. – Armin Nov 04 '22 at 01:10

score 12 · Answer 5 · edited Oct 25 '21 at 18:35

12

You can use:

df.set_value('Row_index', 'Column_name', value)

set_value is ~100 times faster than .ix method. It also better then use df['Row_index']['Column_name'] = value.

But since set_value is deprecated now so .iat/.at are good replacements.

For example if we have this data_frame

   A   B   C
0  1   8   4 
1  3   9   6
2  22 33  52

if we want to modify the value of the cell [0,"A"] we can do

df.iat[0,0] = 2

or df.at[0,'A'] = 2

edited Oct 25 '21 at 18:35

feetwet

3,248
7
46
84

answered Apr 29 '19 at 15:19

DINA TAKLIT

7,074
10
69
74

4

I mentioned that set_value is deprecated be carefull and read the answer well before decide to downvote blindly – DINA TAKLIT Dec 12 '20 at 01:06
It has been seiad in this line => But since set_value is deprecated now so .iat/.at are good replacements. – DINA TAKLIT May 21 '21 at 15:31

Phil · Answer 6 · 2020-04-12T09:31:49.190

5

To modify the value in a cell at the intersection of row "r" (in column "A") and column "C"

retrieve the index of the row "r" in column "A"

    i = df[ df['A']=='r' ].index.values[0]

modify the value in the desired column "C"
```
    df.loc[i,"C"]="newValue"
```

Note: before, be sure to reset the index of rows ...to have a nice index list!

        df=df.reset_index(drop=True)

edited Apr 12 '20 at 09:31

answered Apr 12 '20 at 09:26

Phil

51
1
3

score 4 · Answer 7 · answered Dec 27 '17 at 15:32

4

Another way is to get the row index and then use df.loc or df.at.

# get row index 'label' from row number 'irow'
label = df.index.values[irow] 
df.at[label, 'COL_NAME'] = x

answered Dec 27 '17 at 15:32

Karl I.

121
5

This is the best solution when you want to set multiple rows to the same value – Riebeckite Nov 13 '20 at 18:01

score 1 · Answer 8 · answered Dec 19 '18 at 11:37

1

Extending Jianxun's answer, using set_value mehtod in pandas. It sets value for a column at given index.

From pandas documentations:

DataFrame.set_value(index, col, value)

To set value at particular index for a column, do:

df.set_value(index, 'COL_NAME', x)

Hope it helps.

answered Dec 19 '18 at 11:37

Om Prakash

2,675
4
29
50

set_value is depreciated. Better to us "at" – Sadhun Jul 24 '19 at 10:58

Set value for particular cell in pandas DataFrame with iloc

8 Answers8

Update:

Linked

Related