Posts Tagged ‘select all rows from a dataframe’

Pandas for SQL Lovers – SELECT * FROM table

pandaSQL5Earlier in the series, I covered how to load data from a csv file into a pandas DataFrame. In this post I’ll focus on the building block for all other DataFrame queries: SELECT * FROM DataFrame

Also in this series:

So you have your data loaded into a Pandas DataFrame and now you want to run a query against the DataFrame.

There are different methods we can use depending on what we want to do. Let’s start with loc and iloc

  • loc allows you to specify which columns you want by providing the column name
  • iloc allows you to specify which columns you want be providing the column position

Imagine you have a DataFrame called airports with the following values:

index airport_code city state capacity
0 SEA Seattle WA 45000000
1 BOS Boston MA 36000000
2 HOU Houston TX 55000000
3 YOW Ottawa ON 25000000
4 YYZ Toronto ON 65000000

The first query you need is the same one you learn when you first learn SQL

SELECT * FROM airports

All the commands below will return the same result

Just want all the rows and columns? If you are using a Jupyter notebook just type the DataFrame name


To display the values as output from any Python code


loc, and iloc accept parameters to specify which rows and columns to return. A value of : indicates all rows, or all columns.


If you do not specify a value for the columns parameter, loc and iloc return all columns.


If you know how many rows are in the DataFrame, you could specify you want rows 0 through to the last row in the DataFrame.


If you don’t know the number of rows in the DataFrame or the number of rows might change, use len to return the number of rows and make your code more robust.



Every command above returns the exact same output:


As is so often the case when writing code, there are lots of different ways to accomplish the same task. Getting familiar with these different techniques will give you a good base to build on as me to more advanced queries.

Looking for similar topics and technical posts? See the full list