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.
You can set value of cell in pandas dataframe using df.at[7, ‘column_name’] = ‘value’ statement.
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_Name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | Remarks | |
---|---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 | NaT |
1 | Mouse | 200.000 | 5 | 6 | 4/23/2021 | NaT |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 | NaT |
3 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | NaT |
4 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | NaT |
5 | Speakers | 250.500 | 8 | NaT | 01/05/2021 | NaT |
6 | Headset | NaN | NaT | NaT | NaT | NaT |
7 | Test Product | NaN | NaN | NaN | NaN | NaT |
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.
Table of Contents
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_Name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | Remarks | |
---|---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 | NaT |
1 | Mouse | 200.000 | 5 | 6 | 4/23/2021 | NaT |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 | NaT |
3 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | NaT |
4 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | NaT |
5 | Speakers | 250.500 | 8 | NaT | 01/05/2021 | NaT |
6 | Headset | NaN | NaT | NaT | NaT | NaT |
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 columnscolumn_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_Name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | Remarks | |
---|---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 | NaT |
1 | Mouse | 200.000 | 5 | 6 | 4/23/2021 | NaT |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 | NaT |
3 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | NaT |
4 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | NaT |
5 | Speakers | 250.500 | 8 | NaT | 01/05/2021 | NaT |
6 | Headset | NaN | NaT | NaT | NaT | NaT |
7 | Test Product | NaN | NaN | NaN | NaN | NaT |
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_index
– Integer based label of the columnscolumn_index
– Integer 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 setdf.columns.get_loc('Remarks')
– To identify the index of the columnsremarks
.
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_Name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | Remarks | |
---|---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 | NaT |
1 | Mouse | 200.000 | 5 | 6 | 4/23/2021 | NaT |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 | NaT |
3 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | No stock available. Will be available in 5 days |
4 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | NaT |
5 | Speakers | 250.500 | 8 | NaT | 01/05/2021 | NaT |
6 | Headset | NaN | NaT | NaT | NaT | NaT |
7 | Test Product | NaN | NaN | NaN | NaN | NaT |
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 columnrow_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_Name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | Remarks | |
---|---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 | NaT |
1 | Mouse | 200.000 | 5 | 6 | 4/23/2021 | NaT |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 | NaT |
3 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | No stock available. Will be available in 5 days |
4 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | NaT |
5 | Speakers | 250.500 | 8 | NaT | 01/05/2021 | NaT |
6 | Headset | NaN | NaT | NaT | NaT | NaT |
7 | Test Product | 1000.000 | NaN | NaN | NaN | NaT |
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 columnrow_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 setdf.columns.get_loc('Remarks')
– To identify the index of the columnsremarks
.
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_Name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | Remarks | |
---|---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 | Test Remarks |
1 | Mouse | 200.000 | 5 | 6 | 4/23/2021 | NaT |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 | NaT |
3 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | No stock available. Will be available in 5 days |
4 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | NaT |
5 | Speakers | 250.500 | 8 | NaT | 01/05/2021 | NaT |
6 | Headset | NaN | NaT | NaT | NaT | NaT |
7 | Test Product | 1000.000 | NaN | NaN | NaN | NaT |
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_Name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | Remarks | |
---|---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 | Test Remarks |
1 | Mouse | 200.000 | 5 | 6 | 4/23/2021 | Test Remarks |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 | Test Remarks |
3 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | Test Remarks |
4 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 | Test Remarks |
5 | Speakers | 250.500 | 8 | NaT | 01/05/2021 | Test Remarks |
6 | Headset | NaN | NaT | NaT | NaT | Test Remarks |
7 | Test Product | 1000.000 | NaN | NaN | NaN | Test 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_Name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | Remarks | |
---|---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 | Test Remarks |
1 | Mouse | 200.000 | 5 | 6 | 4/23/2021 | Test Remarks |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 | Test Remarks |
3 | CPU | 10000.550 | 20 | 0 | 09/18/2021 | Test Remarks |
4 | CPU | 10000.550 | 20 | 0 | 09/18/2021 | Test Remarks |
5 | Speakers | 250.500 | 8 | NaT | 01/05/2021 | Test Remarks |
6 | Headset | NaN | NaT | NaT | NaT | Test Remarks |
7 | Test Product | 1000.000 | NaN | NaN | NaN | Test 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 rowlambda 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_Name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | Remarks | |
---|---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 | Test Remarks |
1 | Mouse | 200.000 | 5 | 5 | 4/23/2021 | Test Remarks |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 | Test Remarks |
3 | CPU | 10000.550 | 20 | 20 | 09/18/2021 | Test Remarks |
4 | CPU | 10000.550 | 20 | 20 | 09/18/2021 | Test Remarks |
5 | Speakers | 250.500 | 8 | 16 | 01/05/2021 | Test Remarks |
6 | Headset | NaN | NaT | NaT | NaT | Test Remarks |
7 | Test Product | 1000.000 | NaN | NaN | NaN | Test 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.