Accessing Data from Dataframe with at and iat

Pandas Set Values is important when writing back to your CSV. Usually you’re doing to be reading Pandas tables. But what if you’re treating a CSV like a basic database and you need to update a cell value? If you need to set/get a single DataFrame values, .at[] and .iat[] is the way to do it.

Pandas .at[] and .iat[] is similar to .loc[]. Make sure to use square brackets instead of normal function parenthesis.

Vocabulary words we must know:

• .at[] = Using the index labels as the identifier of the intersection you’d like to update
• .iat[] = Using the index position as the identifier of the intersection you’d like to update.
• Intersection = The row/column combination that identifies a single cell in your DataFrame.
• Index Label = The name of your single index. For rows this is often the row number, but this can be any other string or timestamp as well.
• Index Position = The integer that represents the spot # your row/column sits in. You can always think of this as row/column number. Remember that python starts at index=0. So row 1 will be the 2nd row in your DataFrame.

1. pd.DataFrame.at['your_row_label', 'your_column_label']

2. pd.DataFrame.at[your_row_position, your_column_position]

Pseudo code: For a given row/column combination, get or set a value.

Pandas Set Values

.at[] and .iat[] Parameters

.at[]and.iat[] have similar but different parameters. We mostly use .at[] because it reads a bit easier.

• .at[]: Will take a row/column intersection of index labels. The key word is labels. These are the values that actually appear on your Frame. Most often this will be your row numbers and column names. In the picture above, “Sally” and “Test2” are our row labels.
• .iat[]: Will take a row/column intersection of index positions via integers. Make sure to remember that python indexes start at 0. So your 2nd row is actually in position 1. In the picture above, row 1 and column 1 would refer to the cell that contains ’60’.

Let’s run through a few examples.

---

In [1]:

import pandas as pd

Pandas Set Values

We will run through 4 examples:

1. Get – Referencing a cell via .at[]
2. Set – Setting a cell via .at[]
3. Get – Referencing a cell via .iat[]
4. Set – Setting a cell via .iat[]

First, let’s create our DataFrameIn [9]:

df = pd.DataFrame([('Foreign Cinema', 'Restaurant', 289.0),
                   ('Liho Liho', 'Restaurant', 224.0),
                   ('500 Club', 'bar', 80.5),
                   ('The Square', 'bar', 25.30)],
           columns=('name', 'type', 'AvgBill')
                 )
df

Out[9]:

	Name	Type	AvgBill
0	Foreign Cinema	Restaurant	289.0
1	Liho Liho	Restaurant	224.0
2	500 Club	bar	80.5
3	The Square	bar	25.3

1. Get – Referencing a cell via .at[]

In order to reference a cell (to see what it is) using .at[] you’ll need to know the row and column labels. The labels are what the index of the row and column actually say (vs what position they are in).

Here I’m calling the cell in row index label ‘2’ and column index label ‘AvgBill’In [11]:

df.at[2, 'AvgBill']

Out[11]:

80.5

2. Set – Setting a cell via .at[]

In order to set (update) a cell using .at[] you’ll need to know the row and column labels again. Then you’ll need to apply the assign operator (“=”) to update the value to something else.

Here I’m calling the cell in row index label ‘2’ and column index label ‘AvgBill’ and updating it to 101. This will update in place.In [12]:

df.at[2, 'AvgBill'] = 101
df

Out[12]:

	Name	Type	AvgBill
0	Foreign Cinema	Restaurant	289.0
1	Liho Liho	Restaurant	224.0
2	500 Club	bar	101.0
3	The Square	bar	25.3

3. Get – Referencing a cell via .iat[]

In order to reference a cell (to see what it is) using .iat[] you’ll need to know the row and column index positions. The positions are integers and represent where the row/column sits within your DataFrame/Series.

Each index spot has a label and a position. Sometimes they are the same, but sometimes they aren’t.In [13]:

df.iat[1, 2]

Out[13]:

224.0

4. Set – Setting a cell via .iat[]

Finally, in order to set a value with .iat[] you’ll need to use the assign operator (“=”) again with the index positions.

Here I’m going to update the 2nd row (row index position 1) at the “AvgBill” column (column index position 2). This will update in place.In [15]:

df.iat[1, 2] = 333
df

Out[15]:

	Name	Type	AvgBill
0	Foreign Cinema	Restaurant	289.0
1	Liho Liho	Restaurant	333.0
2	500 Club	bar	101.0
3	The Square	bar	25.3