Get Last value of a Column in Pandas DataFrame

This article will discuss different ways to get the last value a column in a Pandas Dataframe. In all of the solutions, we will first select the Column by either column name or index position; then, we will see different techniques to get and set the last value of that Column.

Table of Contents

• Get the last value of a column based on the column name
  • Using iat[]
  • Using iloc[]
• Get the last value of a column based on column index position.

A DataFrame is a data structure offered by the Pandas module in Python. It stores the data in tabular format, i.e., in rows and columns. Let’s create a DataFrame from a list of tuples,

import pandas as pd

# List of Tuples
students = [('Mark',    24, 'Berlin',    'Germany',        89000),
            ('Rita',    20, 'Seoul',     'South Korea',    93000),
            ('Vicki',   21, 'Amsterdam', 'Netherlands',    95670),
            ('Justin',  22, 'Singapore', 'Singapore',      78900),
            ('John',    36, 'Paris',     'France',         98711),
            ('Michal',  37, 'London',    'United Kingdom', 90000)]

# Create a DataFrame object
df = pd.DataFrame( students,
                   columns=['Name', 'Age', 'City', 'Country', 'Budget'],
                   index=['a', 'b', 'c', 'd', 'e', 'f'])

print(df)

Output

     Name  Age       City         Country  Budget
a    Mark   24     Berlin         Germany   89000
b    Rita   20      Seoul     South Korea   93000
c   Vicki   21  Amsterdam     Netherlands   95670
d  Justin   22  Singapore       Singapore   78900
e    John   36      Paris          France   98711
f  Michal   37     London  United Kingdom   90000

Now let’s see how to get the last value of a specific column of this DataFrame,

Get the last value of a column based on the column name

Suppose we know the name of the column and want to fetch the last value of that Column. We can do that using two techniques. Let’s see them one by one,

Get the last value of a column using iat[]

First of all, select the Column of the DataFrame as a Series object, using the column name. Then call the iat[-1] attribute on that Series object to get the last value of that Column. For example,

# Get last value of column 'City'
last_value = df['City'].iat[-1]

print(last_value)

Output:

London

Here we fetched the last value of the column ‘City’ from the DataFrame. As Series supports the negative indexing, therefore the iat[-1] returns the reference of the last value of the Series. We can use this to change the last value of the Column too. For example,

# Change the Last value of column 'City'
df['City'].iat[-1] = 'Liverpool'

# Display the DataFrame
print(df)

Output:

     Name  Age       City         Country  Budget
a    Mark   24     Berlin         Germany   89000
b    Rita   20      Seoul     South Korea   93000
c   Vicki   21  Amsterdam     Netherlands   95670
d  Justin   22  Singapore       Singapore   78900
e    John   36      Paris          France   98711
f  Michal   37  Liverpool  United Kingdom   90000

Here, we changed the last value of column ‘City’ to Liverpool.

Get the last value of a column using iloc[]

In Pandas, the DataFrame provides a property iloc[]. In the iloc[row_number, column_number], we need to pass the row and column index positions, and it fetches the cell value based on that. But we have the column name instead of the column index position. So we need to get the column index from the column name using the get_loc() function and then use the iloc[] property with row value -1 to get the last value of the Column (because of negative indexing -1 denotes the last entry in the Series). For example,

import pandas as pd

# List of Tuples
students = [('Mark',    24, 'Berlin',    'Germany',        89000),
            ('Rita',    20, 'Seoul',     'South Korea',    93000),
            ('Vicki',   21, 'Amsterdam', 'Netherlands',    95670),
            ('Justin',  22, 'Singapore', 'Singapore',      78900),
            ('John',    36, 'Paris',     'France',         98711),
            ('Michal',  37, 'London',    'United Kingdom', 90000)]

# Create a DataFrame object
df = pd.DataFrame( students,
                   columns=['Name', 'Age', 'City', 'Country', 'Budget'],
                   index=['a', 'b', 'c', 'd', 'e', 'f'])

print(df)

# Get last value of column 'City'
last_value = df.iloc[-1, df.columns.get_loc('City')]

print(last_value)

Output:

     Name  Age       City         Country  Budget
a    Mark   24     Berlin         Germany   89000
b    Rita   20      Seoul     South Korea   93000
c   Vicki   21  Amsterdam     Netherlands   95670
d  Justin   22  Singapore       Singapore   78900
e    John   36      Paris          France   98711
f  Michal   37     London  United Kingdom   90000


London

Here we fetched the last value of the column ‘City’ from the DataFrame.

Using the get_loc() function, we last fetched the column number from column name and then passed that to iloc[] property of the DataFrame with row value -1. The iloc[], returned the reference of the last value of the Column. We can use this to change the last value of the Column too. For example,

# Change the Last value of column 'City'
df.iloc[-1, df.columns.get_loc('City')] = 'Birmingham'

# Display the DataFrame
print(df)

Output:

     Name  Age        City         Country  Budget
a    Mark   24      Berlin         Germany   89000
b    Rita   20       Seoul     South Korea   93000
c   Vicki   21   Amsterdam     Netherlands   95670
d  Justin   22   Singapore       Singapore   78900
e    John   36       Paris          France   98711
f  Michal   37  Birmingham  United Kingdom   90000

Here, we changed the last value of column ‘City’ to Birmingham.

Get the last value of a column based on column index position

In Pandas, the DataFrame provides a property iloc[]. In the iloc[row_number, column_number], we need to pass the row and column index positions, and it fetches the cell value based on that. As we already have the column index position, we can directly use that and pass -1 as the row number (negative indexing) to get the last value of the Column. For example,

import pandas as pd

# List of Tuples
students = [('Mark',    24, 'Berlin',    'Germany',        89000),
            ('Rita',    20, 'Seoul',     'South Korea',    93000),
            ('Vicki',   21, 'Amsterdam', 'Netherlands',    95670),
            ('Justin',  22, 'Singapore', 'Singapore',      78900),
            ('John',    36, 'Paris',     'France',         98711),
            ('Michal',  37, 'London',    'United Kingdom', 90000)]

# Create a DataFrame object
df = pd.DataFrame( students,
                   columns=['Name', 'Age', 'City', 'Country', 'Budget'],
                   index=['a', 'b', 'c', 'd', 'e', 'f'])

print(df)

column_index = 2
# Get last value of column index 2
last_value = df.iloc[-1, column_index]

print(last_value)

Output:

     Name  Age       City         Country  Budget
a    Mark   24     Berlin         Germany   89000
b    Rita   20      Seoul     South Korea   93000
c   Vicki   21  Amsterdam     Netherlands   95670
d  Justin   22  Singapore       Singapore   78900
e    John   36      Paris          France   98711
f  Michal   37     London  United Kingdom   90000


London

Here we fetched the last value of the column ‘City’ from the DataFrame. The iloc[] returns the reference of the last value of the Column. We can use this to change the last value of the Column too. For example,

column_index = 2
# Change the Last value of column index 2
df.iloc[-1, column_index] = 'Sheffield'

# Display the DataFrame
print(df)

Output:

     Name  Age       City         Country  Budget
a    Mark   24     Berlin         Germany   89000
b    Rita   20      Seoul     South Korea   93000
c   Vicki   21  Amsterdam     Netherlands   95670
d  Justin   22  Singapore       Singapore   78900
e    John   36      Paris          France   98711
f  Michal   37  Sheffield  United Kingdom   90000

Here, we changed the last value of column ‘City’ to Sheffield.

Summary

We learned about different ways to get and set a column’s last value, either by column name or index position.