Get values, rows and columns in pandas dataframe (2024)

Last Updated on July 14, 2022 by Jay

This article is part of the Transition from Excel to Python series. We have walked through the data i/o (reading and saving files) part. Let’s move on to something more interesting. In Excel, we can see the rows, columns, and cells. We can reference the values by using a “=” sign or within a formula. In Python, the data is stored in computer memory (i.e., not directly visible to the users), luckily the pandas library provides easy ways to get values, rows, and columns.

Let’s first prepare a dataframe, so we have something to work with. We’ll use this example file from before, and we can open the Excel file on the side for reference.

import pandas as pddf = pd.read_excel('users.xlsx')>>> df User Name Country City Gender Age0 Forrest Gump USA New York M 501 Mary Jane CANADA Tornoto F 302 Harry Porter UK London M 203 Jean Grey CHINA Shanghai F 30

Get values, rows and columns in pandas dataframe (1)

Some observations about this small table/dataframe:

There are five columns with names: “User Name”, “Country”, “City”, “Gender”, “Age”
There are 4 rows (excluding the header row)

df.index returns the list of the index, in our case, it’s just integers 0, 1, 2, 3.

df.columns gives the list of the column (header) names.

pandas get columns

There are several ways to get columns in pandas. Each method has its pros and cons, so I would use them differently based on the situation.

The dot notation

We can type df.Country to get the “Country” column. This is a quick and easy way to get columns. However, if the column name contains space, such as “User Name”. This method will not work.

>>> df.Country0 USA1 CANADA2 UK3 CHINAName: Country, dtype: object>>> df.Age0 501 302 203 30Name: Age, dtype: int64>>> df.User NameSyntaxError: invalid syntax

Square brackets notation

This is my personal favorite. It requires a dataframe name and a column name, which goes like this: dataframe[column name]. The column name inside the square brackets is a string, so we have to use quotation around it. Although it requires more typing than the dot notation, this method will always work in any cases. Because we wrap around the string (column name) with a quote, names with spaces are also allowed here.

>>> df['User Name']0 Forrest Gump1 Mary Jane2 Harry Porter3 Jean GreyName: User Name, dtype: object>>> df['City']0 New York1 Tornoto2 London3 ShanghaiName: City, dtype: object

Get multiple columns

The square bracket notation makes getting multiple columns easy. The syntax is similar, but instead, we pass a list of strings into the square brackets. Pay attention to the double square brackets:

dataframe[ [column name 1, column name 2, column name 3, ... ] ]

>>> df[['User Name', 'Age', 'Gender']] User Name Age Gender0 Forrest Gump 50 M1 Mary Jane 30 F2 Harry Porter 20 M3 Jean Grey 30 F

pandas get rows

We can use .loc[] to get rows. Note the square brackets here instead of the parenthesis (). The syntax is like this: df.loc[row, column]. column is optional, and if left blank, we can get the entire row. Because Python uses a zero-based index, df.loc[0] returns the first row of the dataframe.

Get one row

>>> df.loc[0]User Name Forrest GumpCountry USACity New YorkGender MAge 50Name: 0, dtype: object>>> df.loc[2]User Name Harry PorterCountry UKCity LondonGender MAge 20Name: 2, dtype: object

Get multiple rows

We’ll have to use indexing/slicing to get multiple rows. In pandas, this is done similar to how to index/slice a Python list.

To get the first three rows, we can do the following:

>>> df.loc[0:2] User Name Country City Gender Age0 Forrest Gump USA New York M 501 Mary Jane CANADA Tornoto F 302 Harry Porter UK London M 20

pandas get cell values

To get individual cell values, we need to use the intersection of rows and columns. Think about how we reference cells within Excel, like a cell “C10”, or a range “C10:E20”. The follow two approaches both follow this row & column idea.

Square brackets notation

Using the square brackets notation, the syntax is like this: dataframe[column name][row index]. This is sometimes called chained indexing. An easier way to remember this notation is: dataframe[column name] gives a column, then adding another [row index] will give the specific item from that column.

Let’s say we want to get the City for Mary Jane (on row 2).

>>> df['City'][1]'Tornoto'

To get the 2nd and the 4th row, and only the User Name, Gender and Age columns, we can pass the rows and columns as two lists like the below.

>>> df[['User Name', 'Age', 'Gender']].loc[[1,3]] User Name Age Gender1 Mary Jane 30 F3 Jean Grey 30 F

Remember, df[['User Name', 'Age', 'Gender']] returns a new dataframe with only three columns. Then .loc[ [ 1,3 ] ] returns the 1st and 4th rows of that dataframe.

.loc[] method

As previously mentioned, the syntax for .loc is df.loc[row, column]. Need a reminder on what are the possible values for rows (index) and columns?

>>> df.indexRangeIndex(start=0, stop=4, step=1)>>> df.columnsIndex(['User Name', 'Country', 'City', 'Gender', 'Age'], dtype='object')

Let’s try to get the country name for Harry Porter, who’s on row 3.

>>> df.loc[2,'Country']'UK'

To get the 2nd and the 4th row, and only the User Name, Gender and Age columns, we can pass the rows and columns as two lists into the “row” and “column” positional arguments.

>>> df.loc[[1,3],['User Name', 'Age', 'Gender']] User Name Age Gender1 Mary Jane 30 F3 Jean Grey 30 F