Reading & Writing CSV Files in Python

Python CSV Read And Write

This is a detailed tutorial to read and write CSV files in Python. Learn to parse CSV files with inbuilt Python Library and also with the Pandas library.

Understanding the CSV Files

CSV is one of the most popular formats around the globe to keep, exchange and process the information at various levels. CSV stands for Comma Separated Values. It is basically a text file that stores tabular data. Like every other textual file, the data characters written in these files are also readable ASCII or Unicode characters. Each line of this file represents a single table row and to separate the column values, commas are used.

In CSV, the comma is the separation symbol while there are several other similar formats that use different separation symbols. One such example is TSV (TAB Separated Values) that makes use of tabular space (\t) to separate columns. The separation symbols are known as delimiters. Colon (:) and Semi-Colon (;) are two other popular delimiters.

A CSV file may look like the following.

The first row of the CSV file is usually used for the column names but it is not at all necessary. You should also understand the importance of delimiter symbols. These symbols should not actually match with any character that is the actual data contain in the row and column values. If it matches it, it may actually break the file.

Now, what if you wanted to save a column value that contains a comma (‘) itself?

In such a case, other symbols like quotes are used to keep the CSV file valid. As you can see in the above format, the second row and second column value contain a comma, therefore it is enclosed within double-quotes. Now, if the value will be containing a double quote itself, then it either has to enclosed by some other character or one or more double quotes has to be appended to keep the column valid. Find more about CSV Files here.

Therefore in the case of large CSV files with lots of such data, manual management is difficult. So, let’s learn how you can use Python to handle the writing and reading operation on a CSV file.

Using Python’s Built-in Library For CSV File Parsing

Python comes with a pre-installed library to parse CSV files. This library can be used to read as well as to write the CSV files. This library has been extensively designed not just work for one single format but for various other CSV formats as well. Even it is capable of being used for parsing Excel Generated files. Therefore, this library contains all the powerful tools to read and create CSV files of all types.

Reading Files With CSV Library

This library provides an object named reader that can be used to read files from an existing valid .csv file. Before we can read the file using the reader object, we’ll first need to open the file. We can use another built-in Python function open() for the purpose. Using this function we can read any file with any extension, provided contents are textual.

Once, we open the file, we’ve to pass it as the first argument to the reader() object. This object also takes a second parameter. It is the delimiter. You’ve to specify the symbol with you’re separating the values in the file. As we’re working with CSV files, we’ll specify the delimiter here to be a comma.

For demonstration, we’ve taken a sample CSV file employees.csv, the contents of which are written below.

The Python code to read the above file using Python’s inbuilt CSV library is also written below.

The output of the above code is shown in the screenshot given below.

Reading CSV File Using Python Inbuilt Module Example

Let’s understand the code. First of all, we’re importing the CSV module. Then using the Python open() function, we’re loading the file employees.csv into the file variable. Then we’re parsing the file variable as a CSV file using the reader object of CSV module.  Then, we’re just iterating csvFile variable using Python For Loop.

With every iteration, the variable row will be a Python List that will contain all the column values for that current row. You can fetch the value of any particular column using indexes on the list variable. For example, row[0] for first, row[1] for second column value for the current row and so on.

Reading CSV File Into Python Dictionary

The code in the above section will read the CSV file and loads it into the csvFile variable. reader Object is the data type of this variable. Iterating and fetching the different column and row values using the above method is good when you do not have to further perform complex operations on the data. But it is not the recommended way in most of the cases.

There’s a more suitable way and that is reading the CSV files and loading them directly into a Python Dictionary object. This makes the row and column values more readable. You can access any cell of the CSV file tabular data by just knowing the column name for a particular iteration. If you will use the reader object method then you have to know the column index for a particular iteration.

Instead of the reader object, here we’ll use the DictReader object of the CSV module. The code demonstrating the same is written below.

The following screenshot displays the output of the above code.

Python Reading CSV Files Into Dictionary Example

Note. Also notice once more difference between the two methods. When you iterate reader object, the first row is going to be the column names row while iterating the DictReader object, the first row is the row of values itself. It automatically assumes the first row of the CSV files as column names for creating the dictionary and in the DictReader object, therefore, the first-row id row values rather than the column names.

Now, when you know the difference between the two methods and working, you can use any of these as per your requirement and use-case.

Optional Parameters For Complex CSV File Readings

You can parse different types and styles of CSV and other textual files by providing the required extra information as additional parameters to the reader object. These parameters are listed below in the tabular form with the description of each of them.

Parameter Description
delimiter You can specify a symbol in this parameter that is being used in the file to separate the different columns and values. The default value of this parameter is a comma. (,) Example. delimiter = ';'
quotechar As discussed at the very start of this article that some of the CSV files with complex data may contain characters in the data that are also used as file delimiters. So, in such cases enclosing symbols like double quotes are used to specify the column value starting and ending. That enclosing symbol can be specified in the quotechar parameter. The default value of this parameter is a double quote. (") Example. quotechar = '|'
escapecar Escape characters are used when quotes are not used to escape the delimiter character. There is no escape character parameter value set by default.

Consider the following file, employees.txt. It contains values that are separated by semi-colons as commas are used to separate the first and last name itself. Also, for the last column salary_code the delimiter character (;) is used as the value. Therefore, the enclosing character (|) is also used.

So, in order to read this file, you’ve to specify the delimiter parameter as ';' and quotechar as  '|'.

The following code illustrated the concept of reading the above file by providing extra parameter values.

The output screenshot shows that the above code reads the file with custom delimiters and enclosing symbols perfectly.

Reading Custom Delimiter And Enclosing Quotes CSV Files

Writing Files With CSV Library

So, till now, we’ve learned how to read CSV files and now let’s begin with the writing part of the story. Before we can start writing, we’ve to open a file on which we can write the CSV data. Again, using the Python inbuilt function open(), this can be done. You must set the mode of this function as 'w' to enable file writing.

Have a look at the code and then we’ll understand how it works.

The above code will create a file named employees_created.csv, the data of which is given below.

As the DictReader object is used for reading, the DictWriter object is used for writing the CSV files. You require to give two parameters to this function, one is the file to which you want to write and the other is the fieldnames which is basically a list object of column names.

In case you have comma or quotes in your values, this function will automatically do the necessary addition of escaping but you can also provide you custom escaping character using the quotechar parameter.

Using Pandas Library For CSV File Parsing

When you want to parse CSV files in Python for advanced purposes like Machine Learning, you should not simply go with the basic reading and writing approach as discussed in the above section. Rather, you should use a library like Pandas for doing a lot more with the CSV data! Especially if you are doing data analysis and have lots of data, this is the recommended option.

Pandas is basically an open-source library that can be used for data analyses. It also provides easy to use and high-perform data structures for better analysis of the data. It is much more than just working with CSV files. Find more about it here. We need not go dig deeper into Pandas to use its CSV reading and writing feature.

Make sure you have the pandas library installed. If not, you can quickly install it using PIP. Just run the following command to install it.

Pandas Reading CSV Files

Reading CSVs with Pandas is actually quite easy and you can do it in just 2 or 3 short lines of code. Consider the same employees.csv file that we’ve used in the previous examples.

The following code will read this file and after loading it into a pandas DataFrame, it will print it on the output screen, as shown in the screenshot after it.

Reading CSV Files With Pandas Python Example

The read_csv() object of the pandas library does a lot of stuff itself. It identifies the first line of the file as the column names and it automatically assigns indexes to each and every row as you can see from 0 to 9 in the above screenshot. These are really useful when you quickly wanted to fetch the value for a particular column and row of the entire tabular data of the CSV file.

If you do not want the indices, you’ve to specify any other column as an index column. You can do this by specifying a column in the index_col parameter of the read_csv() object.

Reading CSV File Specifying Index Column Pandas Python

Pandas also ensure the data types of the column values. If there are numerical values, pandas is enough intelligent to differentiate it as numerical values from the other string-based values. Let check. The value of the column emp_no for any row should be an integer while for first_name it should be a string type object.

The above code basically checks the data type of first three columns of the first row of the CSV file, employees.csv.

Pandas CSV File Processing

You can see it has detected the data type as an integer for the column emp_no and string for the column first_name. But it is still considering the birth_date column value as a string, rather it should also identify it as the date data type. You can make sure the correct data type for this column by adding an additional parameter  parse_dates. You have to provide a list of columns that should be of the date data type. The following code and output illustrate the same.

Pandas CSV Handling Data Types

Another case could be the first line of the CSV file may not contain the column names. So, Pandas also provide the feature that you can specify the column names in the code for any CSV file that you want to load and read.

Read CSV Files Python Pandas Column Names Defined

You can also specify a parameter named header, it can take numerical values starting from 0 and so on. The first row is numbered 0 and in the growing order the other rows. So, with this parameter, you can specify from which row it should start reading the CSV file.

As you can see in the following screenshot, as the header parameter is set to 5 and there is no column row in the CSV file, so it starts reading the CSV file from line number 7. (Its index also starts from 0 and one is the header row).

Read Specific CSV File Lines

Pandas Writing CSV Files

As Pandas read the CSV file into a Pandas DataFrame, therefore in order to write files using Pandas, you’ve to provide it the data in the form of Pandas DataFrame format. The following Python code first read the file employees.csv and then after converting it into Pandas DataFrame, it writes the same into a new CSV file, employees_new_column_names.csv.

Writing CSV Files Python Pandas

I hope you found this guide useful. If so, do share it with others who are willing to learn Python. If you have any questions related to this article, feel free to ask us in the comments section.

And do not forget to subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *