How To Set Value Of A Cell In Pandas Dataframe?

Pandas Dataframe is a two-dimensional array that allows you to store data in rows and columns format. It is extensively used for data manipulation activities using python. During data manipulation activities, you may need to set the value of a cell in a pandas dataframe.

In this tutorial, you’ll learn how to set value of a cell in a pandas dataframe.

If You’re in Hurry…

You can use the below code snippet to set cell value in the pandas dataframe.

Snippet

df.at[7, 'Product_Name'] = 'Test Product'

df

Now, when you print the dataframe, you’ll see the cell at the row index 7, and the column Product_Name will be set to Test Product. Since this is a new row, all the other cells of this row are set to NaN which denotes a missing value.

Dataframe Will Look Like

Product_NameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_DateRemarks
0Keyboard500.0005511/5/2021NaT
1Mouse200.000564/23/2021NaT
2Monitor5000.235101008/21/2021NaT
3CPU10000.55020Not Available09/18/2021NaT
4CPU10000.55020Not Available09/18/2021NaT
5Speakers250.5008NaT01/05/2021NaT
6HeadsetNaNNaTNaTNaTNaT
7Test ProductNaNNaNNaNNaNNaT

If You Want to Understand Details, Read on…

In this tutorial, you’ll learn the different methods available to set the value of a cell in pandas dataframe along with examples.

To get the value of a cell from a dataframe, read How to Get Value of A Cell From Pandas Dataframe.

Sample Dataframe

This is the sample dataframe used throughout the tutorial.

Snippet

import pandas as pd

data = {"Product_Name":["Keyboard","Mouse", "Monitor", "CPU","CPU", "Speakers","Headset"],
        "Unit_Price":[500,200, 5000.235, 10000.550, 10000.550, 250.50,None],
        "No_Of_Units":[5,5, 10, 20, 20, 8,pd.NaT],
        "Available_Quantity":[5,6,10,"Not Available","Not Available", pd.NaT,pd.NaT],
        "Available_Since_Date":['11/5/2021', '4/23/2021', '08/21/2021','09/18/2021','09/18/2021','01/05/2021',pd.NaT],
        "Remarks":[pd.NaT,pd.NaT,pd.NaT,pd.NaT,pd.NaT,pd.NaT,pd.NaT]
       }

df = pd.DataFrame(data)

df = df.astype({"Unit_Price": float})

df

Dataframe Will Look Like

Product_NameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_DateRemarks
0Keyboard500.0005511/5/2021NaT
1Mouse200.000564/23/2021NaT
2Monitor5000.235101008/21/2021NaT
3CPU10000.55020Not Available09/18/2021NaT
4CPU10000.55020Not Available09/18/2021NaT
5Speakers250.5008NaT01/05/2021NaT
6HeadsetNaNNaTNaTNaTNaT

Set Cell Value Using at

You can set cell value of pandas dataframe using df.at[row_label, column_label] = ‘Cell Value’. It is the fastest method to set the value of the cell of the pandas dataframe.

Dataframe at property of the dataframe allows you to access the single value of the row/column pair using the row and column labels.

It accepts two parameters.

  • row_label – String or Integer based label of the columns
  • column_label – String or Integer based label of the columns

While using the at, If you specify only the row label and leave the column label as empty, then all cells in that row will be set to the new value.

Use the below snippet to set the cell value at the row position 7 and the column Product_Name to the value Test Product.

Snippet

df.at[7, 'Product_Name'] = 'Test Product'

df

Now, when you print the dataframe, you’ll see the cell at the row index 7, and the column Product_Name will be set to Test Product. Since this is a new row, all the other cells of this row are set to NaN which denotes a missing value.

Dataframe Will Look Like

Product_NameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_DateRemarks
0Keyboard500.0005511/5/2021NaT
1Mouse200.000564/23/2021NaT
2Monitor5000.235101008/21/2021NaT
3CPU10000.55020Not Available09/18/2021NaT
4CPU10000.55020Not Available09/18/2021NaT
5Speakers250.5008NaT01/05/2021NaT
6HeadsetNaNNaTNaTNaTNaT
7Test ProductNaNNaNNaNNaNNaT

This is how you can set the cell value of a dataframe using df.at.

Set Value Using IAT

You can set cell value of pandas dataframe using df.iat[row_index, column_index] = ‘Cell Value’.

Dataframe at property of the dataframe allows you to access the single value of the row/column pair using the row and column indexes.

It accepts two parameters.

  • row_indexInteger based label of the columns
  • column_indexInteger based label of the columns

You can get the index of the rows or columns by using the get_loc() method available in the df.index attribute. For example, to get the column location, you can use df.columns.get_loc('Column_Name').

While using the iat, If you specify only the row index and leave the column index as empty, then all cells in that row will be set to the new value.

Use the below snippet to set the cell value at the row position 3 and the column Remarks to the value No stock available. Will be available in 5 days.

Snippet

df.iat[3, df.columns.get_loc('Remarks')] = 'No stock available. Will be available in 5 days'

df

Where,

  • 3 – Row index of the cell for which the value needs to be set
  • df.columns.get_loc('Remarks') – To identify the index of the columns remarks.

Now, when you print the dataframe, you’ll see the cell at the row index 3 and column Remarks available at index 6 will be set to No stock available. Will be available in 5 days.

Dataframe Will Look Like

Product_NameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_DateRemarks
0Keyboard500.0005511/5/2021NaT
1Mouse200.000564/23/2021NaT
2Monitor5000.235101008/21/2021NaT
3CPU10000.55020Not Available09/18/2021No stock available. Will be available in 5 days
4CPU10000.55020Not Available09/18/2021NaT
5Speakers250.5008NaT01/05/2021NaT
6HeadsetNaNNaTNaTNaTNaT
7Test ProductNaNNaNNaNNaNNaT

This is how you can set the cell value of a dataframe using the iat property.

Set Value Using Loc

You can also set the value of a cell using the loc attribute of the dataframe. Loc allows you to access the cell of the dataframe using row and column labels.

It accepts two parameters.

  • column_label – String or Integer based label of the column
  • row_label – String or Integer based label of the row

You can use the below snippet to set the value of the cell at the location 7 and the column name Unit_Price to 1000.

Snippet

df.loc[7, 'Unit_Price'] = 1000

df

Now, when you print the dataframe, you’ll see the value of the Unit_Price column at the row index position 7 is set to 1000.

Product_NameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_DateRemarks
0Keyboard500.0005511/5/2021NaT
1Mouse200.000564/23/2021NaT
2Monitor5000.235101008/21/2021NaT
3CPU10000.55020Not Available09/18/2021No stock available. Will be available in 5 days
4CPU10000.55020Not Available09/18/2021NaT
5Speakers250.5008NaT01/05/2021NaT
6HeadsetNaNNaTNaTNaTNaT
7Test Product1000.000NaNNaNNaNNaT

This is how you can set the cell value of the dataframe using df.loc[].

Set Value Using iLOC

You can also set the value of a cell using the iloc attribute of the dataframe. Loc allows you to access the cell of the dataframe using row and column indexes (Integer) rather than using the labels.

It accepts two parameters.

  • column_index – Integer based label of the column
  • row_index – Integer based label of the row

You can get the index of the rows or columns by using the get_loc() method available in the df.index attribute. For example, to get the column location, you can use df.columns.get_loc('Column_Name').

Use the below snippet to set the cell value at the row position 0 and the column Remarks to the value Test Remarks.

Snippet

df.iloc[0, df.columns.get_loc('Remarks')] = 'Test Remarks'


df

Where,

  • 0 – Row index of the cell for which the value needs to be set
  • df.columns.get_loc('Remarks') – To identify the index of the columns remarks.

Now, when you print the dataframe, you’ll see the cell at the row index 0 and column Remarks available at index 6 will be set to Test Remarks.

Dataframe Will Look Like

Product_NameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_DateRemarks
0Keyboard500.0005511/5/2021Test Remarks
1Mouse200.000564/23/2021NaT
2Monitor5000.235101008/21/2021NaT
3CPU10000.55020Not Available09/18/2021No stock available. Will be available in 5 days
4CPU10000.55020Not Available09/18/2021NaT
5Speakers250.5008NaT01/05/2021NaT
6HeadsetNaNNaTNaTNaTNaT
7Test Product1000.000NaNNaNNaNNaT

This is how you can set the cell value of the dataframe using the iLoc attribute of the dataframe.

Set Value For an Entire Column In A Dataframe

In this section, you’ll learn how to set value for an entire column in a dataframe.

You can set value for an entire column in a dataframe using df = df.assign(column_name=’value’).

assign method assigns the new columns to the dataframe. It returns a new dataframe object with all the existing columns and a new column assigned. If there is a column already available with the same name, then it’ll be reassigned.

Use the below snippet to assign the value Test Remarks to the column Remarks using the assign() method.

Snippet

df = df.assign(Remarks='Test Remarks')

df

Now, when you print the dataframe, you’ll see the entire cells of the columns Remarks will have the value Test Remarks.

DataFrame Will Look Like

Product_NameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_DateRemarks
0Keyboard500.0005511/5/2021Test Remarks
1Mouse200.000564/23/2021Test Remarks
2Monitor5000.235101008/21/2021Test Remarks
3CPU10000.55020Not Available09/18/2021Test Remarks
4CPU10000.55020Not Available09/18/2021Test Remarks
5Speakers250.5008NaT01/05/2021Test Remarks
6HeadsetNaNNaTNaTNaTTest Remarks
7Test Product1000.000NaNNaNNaNTest Remarks

This is how you can set value for an entire column in the pandas dataframe.

Set Value Of One Cell Based On Value Of Another Cell

In this section, you’ll learn how to set the value of one cell based on Value of another cell.

You can use the dataframe loc attribute to set the value of one cell based on the value of another cell.

Use the below snippet to the value of the column Available_Quantity to 0 wherever the column Available_Quantity has the value ‘Not Available’.

Snippet

df.loc[df['Available_Quantity'] == 'Not Available', 'Available_Quantity'] = 0

df

Now all the cells in the column Available_Quantity with the value ‘Not Available’ will be set to 0

Dataframe Will Look Like

Product_NameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_DateRemarks
0Keyboard500.0005511/5/2021Test Remarks
1Mouse200.000564/23/2021Test Remarks
2Monitor5000.235101008/21/2021Test Remarks
3CPU10000.55020009/18/2021Test Remarks
4CPU10000.55020009/18/2021Test Remarks
5Speakers250.5008NaT01/05/2021Test Remarks
6HeadsetNaNNaTNaTNaTTest Remarks
7Test Product1000.000NaNNaNNaNTest Remarks

You can also use the lambda functions to set the cell value based on another cell.

Use the below snippet to set the Available_Quantity column based on the No_Of_Units column.

Snippet

df['Available_Quantity'] = df['No_Of_Units'].apply(lambda x: 16 if x == 8 else x)

df

Where,

  • apply – Applies the lambda function to the each row
  • lambda x: 16 if x == 8 else x – Lambda function to set the Available_Quantity to 16 wherever the No_Of_Units is 8.

Dataframe Will Look Like

Product_NameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_DateRemarks
0Keyboard500.0005511/5/2021Test Remarks
1Mouse200.000554/23/2021Test Remarks
2Monitor5000.235101008/21/2021Test Remarks
3CPU10000.550202009/18/2021Test Remarks
4CPU10000.550202009/18/2021Test Remarks
5Speakers250.50081601/05/2021Test Remarks
6HeadsetNaNNaTNaTNaTTest Remarks
7Test Product1000.000NaNNaNNaNTest Remarks

This is how you can set the value of a cell in a dataframe based on the value of another cell.

Set Cell Value Using Set_Value Method

In this section, you’ll learn how to set the cell value of the dataframe using the Set_Value() method.

This method is deprecated in the pandas version 0.21.0.

If you’re using the pandas version before this, you can use the below snippet to set the cell value using the set_Value() method.

Snippet

df.set_value(7, 'Unit_Price',1000)

df

If you’re using the latest version, you’ll face the below error. Otherwise, the value will be set appropriately.

Output

    ---------------------------------------------------------------------------

    AttributeError                            Traceback (most recent call last)

    <ipython-input-18-6b944ce7df22> in <module>
    ----> 1 df.set_value(7, 'Unit_Price',1000)
          2 
          3 df


    C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\generic.py in __getattr__(self, name)
       5463             if self._info_axis._can_hold_identifiers_and_holds_name(name):
       5464                 return self[name]
    -> 5465             return object.__getattribute__(self, name)
       5466 
       5467     def __setattr__(self, name: str, value) -> None:


    AttributeError: 'DataFrame' object has no attribute 'set_value'

Conclusion

To summarize, you’ve learned how to set the value of a cell in the pandas dataframe. You’ve used the attributes at, iat, loc, iloc to set the cell value. Along with this, you’ve also learned how to set value for an entire column in a dataframe and set the value of one cell based on the value of another cell.

If you have any questions, comment below.

You May Also Like

Leave a Comment