Pandas read_csv to DataFrames: Python Pandas Tutorial

 How to import csv files using pandas with examples

In this tutorial, we’ll show how to use read_csv pandas to import data into Python, with practical examples.

csv (comma-separated values) files are popular to store and transfer data. And pandas is the most popular Python package for data analysis/manipulation. These make pandas read_csv a critical first step to start many data science projects with Python.

You’ll learn from basics to advanced of pandas read_csv, how to:

• import csv files to pandas DataFrame.
• specify data types (low_memory/dtype/converters).
• use a subset of columns/rows.
• assign column names with no header.
• And More!

This pandas tutorial includes all common cases when loading data using pandas read_csv.

Practice along and keep this as a cheat sheet as well!

Before reading the files

If you have no experience with Python, please take our FREE Python crash course: breaking into data science.

Learn Python for data science: FREE online course – Just into Data

A FREE Python online course, beginner-friendly tutorial. Start your successful data science career journey: learn Python for data science, machine learning.

To make it easy to show, we created 5 small csv files. You can download them to your computer from Github pandas-read-csv-practice to practice.

Note: You can open these csv files and view them through Jupyter Notebook. Just launch a Jupyter Notebook, then look for them within the directory.
Or for Windows users, you can right-click the file, select “Edit” and view it in Notepad. For Mac users, you can right-click and open with TextEdit.
The comma (,) is used to separate columns, while a new line is used to separate rows.

To start practicing, you can either:

• open the read_csv_code.ipynb file from Jupyter Notebook.
  or
• create a new notebook within the same folder as these csv files.

Note: To avoid specifying the whole path/directory for the folder, Python needs both the csv files and the notebook to be in the same directory. This way, Python can locate the files by its name in the current working directory.
Otherwise, you will get FileNotFoundError.

To be able to use the pandas read_csv function, we also need to import the pandas and NumPy packages into Python. Both libraries should have been installed on your computer, if you installed Anaconda Distribution.

Note: pd is the common alias name for pandas, and np is the common alias name for NumPy.

Pandas Data Structures: Series? DataFrame?

pandas is the most popular Python data analysis/manipulation package, which is built upon NumPy.

pandas has two main data structures: Series and DataFrame.

• Series: a 1-dimensional labeled array that can hold any data type such as integers, strings, floating points, Python objects. 
  It has row/axis labels as the index.
• DataFrame: a 2-dimensional labeled data structure with columns of potentially different types.
  It also contains row labels as the index.

DataFrame can be considered as a collection of Series; it has a structure like a spreadsheet.

We’ll be using the read_csv function to load csv files into Python as pandas DataFrames.

Great!

With all this basic knowledge, we can start practicing pandas read_csv!

pandas read_csv Basics

There is a long list of input parameters for the read_csv function. We’ll only be showing the popular ones in this tutorial.

The most basic syntax of read_csv is below.

With only the file specified, the read_csv assumes:

• the delimiter is commas (,) in the file.
  We can change it by using the sep parameter if it’s not a comma. For example, df = pd.read_csv(‘test1.csv’, sep= ‘;’)
• the first row of the file is the headers/column names.
• read all the data.
• the quote character is double (“).
• an error will occur if there are bad lines.
  Bad lines happen when there are too many delimiters in the row.

Most structured datasets that are saved to text-based files can be opened using this method. They often have clear formats and don’t need any further specification of read_csv.

The test1.csv file is nice and clean, so the default setting is appropriate to load the file.

Below you can see the original file (left) and the pandas DataFrame df (right).

Note: you can use the type function to find out that df is a pandas.core.frame.DataFrame.

As you can see, the first row in the csv file was taken as the header, and the first three lines are straightforward.

But how can we have commas (,) and double quotes (“) in the 3rd and 4th row?

Shouldn’t they be special characters?

Take a closer look at the original file of test1.csv, and you will notice the tricks:

• to have the delimiter comma (,) in the data, we need to put quotes around them.
• to have the quote (“) in the data, we need to type two double quotes (“”) to help read_csv understand that we want to read it literally.

Fix error_bad_lines of more commas

The most common error is when there are extra delimiter characters in a row.

This usually happens when the comma in the data wasn’t quoted, which often appears for variables of addresses or company names.

Let’s see an example.

Below is the original data for test2.csv. We can see that among the row for CityD, there’s an extra comma (,) within the address (58 Fourth Street, Apt 500). But the entry isn’t quoted.

If we read by using the default settings, read_csv will be confused by this extra delimiter and give an error.

As the error message says, Python expected 3 fields in line 5, but saw 4. This is due to the extra comma.

There are two main methods to fix this error:

• the best way is to correct the error within the original csv file.
• when not possible, we can also skip the bad lines by changing the error_bad_lines parameter setting to be False.

This will load the data into Python while skipping the bad lines, but with warnings.

b'Skipping line 5: expected 3 fields, saw 4\n'

We can also suppress this warning by setting warn_bad_lines=False, but we’d like to see the warnings most of the time.

As you can see, the line with CityD was skipped here.

Next, let’s see another common issue with csv files.

Specify Data Types: Numeric or String

As you know, the csv files are plain-text files.

While it is important to specify the data types such as numeric or string in Python. We need to rely on pandas read_csv to determine the data types.

By default, if everything in a column is number, read_csv will detect that it is a numerical column; if there are any non-numbers in the column, read_csv will set the column to be an object type.

It is more apparent with an example.

The test3.csv has three columns as below. The address column has both numbers and strings.

If we use the default settings of read_csv to load its data.

read_csv specifies the data types of:

• id as int64 (integer) since it contains only numbers.
• address as object, since it contains some text, even though most of the lines are numbers.
• city as object since it’s all text.

This is great since these are the data types we want these columns to have.

The problem of Mixed Data Types

But when the file has many rows, we might get a mixed data type column. In this situation, there will be problems.

To show this, let’s create a large csv file with 2 columns and 1,000,010 rows:

• col1 has the letter ‘a’ in every row.
• col2 has 1,000,000 rows with the number 123 and the last 10 rows with the string ‘Hello’.

If we use read_csv with the default settings, pandas will get confused and gives a warning saying it is mixed data types.

/Users/justin/py_envs/DL/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (1) have mixed types.Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)

So even though Python loaded the data, it has a strange data type. Python considers the first 1,000,000 rows with 123 as integer/numeric, and the last 10 rows with ‘Hello’ as strings.

We can perform numeric operations on the first 20 rows of 123.

But we can’t perform any numerical or string operations on the entire column.

The first “+100” operation returns an error “TypeError: can only concatenate str (not “int”) to str”. While the second str.len() function returns NaN for all the 123 rows.

And we can take out two random rows to check their data types. We can see that the row 0 with 123 is an integer, while the row 1,000,009 with ‘Hello’ is a string.

This column has mixed data types. This is NOT what we want!

How do we fix them?

There are 3 main ways. Let’s look at them one-by-one.

Method #1: set low_memory = False

By default, low_memory is set to True. That means when the file is larger, read_csv loads the file in chunks.

If an entire chunk has all numeric values, read_csv will save it as numeric.
If it encounters some non-numeric values in a chunk, then it will save the values in that chunk as non-numeric (object).
This is why we had the mixed data type in our large file example.

When we set low_memory = False, read_csv reads in all rows together and decides how to store the values based on all the rows. If there are any non-numeric values, then it will store as an object.

Let’s try it out.

After it’s loaded, we can test the data types by taking out one row with 123 and one row with ‘Hello’ from the DataFrame df again.

It will tell us that Python is now treating them both as str (strings).

And we can perform string operations on the entire column. Try it out!

But using low_memory = False is not memory efficient, which leads to better method #2.

Method #2: set dtype (data types)

It is more efficient to tell Python the data types (dtype) when loading the data, especially when the dataset is larger.

Let’s try setting the column to be the string data type as below.

We’ll see that the string operation str.len() returns the result with no issue.

What if we know that this column should be numeric, and those text entries are typos?

Note that we cannot use a numeric type in this situation, because there are texts ‘Hello’ in the column.

But we can use method #3 to convert the values in the column.

Method #3: set converters (functions)

We can define a converter function to convert the values in specific columns.

In the example below, we kept the values that are numeric and changed the text values to all NaNs for col2.

Then we use this converter_func as a parameter in read_csv.

Numeric and strings are the most common data types, and we now know how to deal with them!

What about another common data type: date and time?

Specify Data Types: Datetime

When we have date/time columns, we cannot use the dtype parameter to specify the data type.

We’ll need to use the parse_dates, date_parser, dayfirst, keep_date_col  parameters within the read_csv function.

We won’t go over it here, but please take a look at the official document for more details.

And if you do use date_parser, it is good to get familiar with the Python DateTime formats.

Further Reading: How to Manipulate Date And Time in Python Like a Boss

So far we’ve been loading the entire file, what if we only want a subset?

Use certain Columns (usecols)

When we only want to analyze certain columns from the file, it saves memory to only read in those columns.

We can use the usecols parameter.

For example, only the col2 is loaded with the specification below.

Since we are looking at columns, let’s also see how to name them better.

Set Column Names (names/prefix/no header)

When we don’t have a header row in the csv file, we can specify columns names.

For example, test5.csv is created without a header row.

If we use the default setting to read_csv, Python will take the first row as the column names.

To fix this, we can use the names parameter to set the column names as my_col1, mycol2, mycol3.

What if we have many columns, and we want to assign them names with the same format/prefix to them?

We can set header = None, and the prefix parameter. read_csv will assign column names with the prefix.

After looking at columns, let’s see how we can deal with rows with more flexibility.

Specify Rows/Random Sampling (nrows/skiprows)

When we have a large dataset, it might not all fit into memory. Rather than importing the entire dataset, we can take a subset/sample from it.

Method #1: use nrows

We can use the nrows parameter to set the top number of rows to read from the file.

For example, we can set nrows = 10 to read the first 10 rows.

Method #2: use skiprows

Using the skiprows parameter gives us more flexibility for the rows to skip.

But we do need to provide more information. We have to input:

• an integer showing the number of rows to skip at the start of the file.
• or a list of row numbers (starting at 0 index position) to skip.
• or a callable function that returns either True (skip) or False (otherwise) for each row number.

Let’s start with a simple example.

We can set skiprows=100,000 to skip the first 100,000 rows.

If we check the shape of the DataFrame, it will return (900010, 2), which is 100,000 rows less than the original file.

When we set skiprows as a list of numbers, read_csv will skip the row numbers in the list.

For example, to achieve random sampling, we can first create a random list skip_list of size 200,000 from a range(1,000,010). Then we can use skiprows to skip the 200,000 random rows specified by the skip_list.

In this case, we know the total number of rows in the file is 1,000,010, so we can draw random row numbers from range(1,000,010).

What if we don’t know that?

We can create a function that randomly samples some of the rows.

For example, the code below uses a lambda function to sample roughly 10% of the rows randomly. The lambda function goes through each row index, and there’s a 10% chance that a particular row is included in the new dataset.
Note that we also skipped the first row (x == 0) containing the header since we are using names to specify the column names.

pandas read_csv in chunks (chunksize) with summary statistics

When we have a really large dataset, another good practice is to use chunksize.

As mentioned earlier as well, pandas read_csv reads files in chunks by default. But it keeps all chunks in memory.

While with the chunksize setting, Python reads in chunks without keeping them in memory until it’s called. This is more efficient and makes it easier to spot any errors when loading the data.

Let’s see how it works.

For example, we use the chunksize setting to create a TextFileReader object reader below.
Note that reader is not a pandas DataFrame anymore. It is a pandas TextFileReader. The data is not in memory until we call it.

We can use the get_chunk method to fetch chunks from the file.

You may try out the below code to see what it returns.

A more popular way of using chunk is to loop through it and use aggregating functions of pandas groupby to get summary statistics.

For example, we can iterate through reader to process the file by chunks, grouping by col2, and counting the number of values within each group/chunk.

Related article: How to GroupBy with Python Pandas Like a Boss
If you are not familiar with pandas GroupBy, take a look at this complete tutorial with examples.

Based on the output above, we can do another GroupBy to return the total count for each group in col2.

Load zip File (compression)

read_csv also supports reading compressed files. This is very useful since we often store the csv file compressed to save storage space.

For example, we saved a zip version of test4.csv within the folder. And we can use the compression parameter setting to read it directly.

Set Missing Values Strings (na_values)

By default the following values are interpreted as NaN/missing value: ‘’, ‘#N/A’, ‘#N/A N/A’, ‘#NA’, ‘-1.#IND’, ‘-1.#QNAN’, ‘-NaN’, ‘-nan’, ‘1.#IND’, ‘1.#QNAN’, ‘<NA>’, ‘N/A’, ‘NA’, ‘NULL’, ‘NaN’, ‘n/a’, ‘nan’, ‘null’.

We can also specify extra strings within the file to be recognized as NA/NaN by using the na_values parameter.

pandas read_csv Cheat Sheet

The code below summarized everything covered in this tutorial. You’ve learned a lot of pandas read_csv parameters!

That’s it for this pandas tutorial for pandas read_csv. You’ve mastered this first step of your data science projects!

Leave a comment for any questions you may have or anything else.

Related “Break into Data Science” resources:

Python crash course: Break into Data Science – FREE

Learn Python for data science: FREE online course – Just into Data

A FREE Python online course, beginner-friendly tutorial. Start your successful data science career journey: learn Python for data science, machine learning.

How to GroupBy with Python Pandas Like a Boss

Read this pandas tutorial to learn Group by in pandas. It is an essential operation on datasets (DataFrame) when doing data manipulation or analysis.

How to Learn Data Science Online: ALL You Need to Know

Check out this for a detailed review of resources online, including courses, books, free tutorials, portfolios building, and more.