Python CSV Quick & Simple Guide | Read, Write & Manipulate (Updated 2023)

Introduction

CSV is a typical file format that is often used in the industry to store data because it stores data in a typical text file and maintains a tabular format. Thus, it is necessary to have a good understanding of the CSV format to easily handle the data you are going to deal with on a day-to-day basis. In this beginner-friendly article, I’ll introduce you to the CSV format and show you how to handle CSV format in python.

Learning Objectives

• In this tutorial, you will learn about the CSV file format and its basic operations using Python.
• We will create a Pandas DataFrame by importing a local csv file and importing csv file from a URL.
• Lastly, we will see how to handle CSV format under different circumstances.

This article was published as a part of the Data Science Blogathon.

Table of Contents

1. What Is a CSV?
2. Basic Operations With CSV Files
3. Why Do We Use CSV Files?
4. Pandas DataFrame
5. Different Parameters in CSV
6. Conclusion

What Is a CSV?

CSV (Comma Separated Values) may be a simple file format accustomed to storing tabular data, like a spreadsheet or database. CSV file stores tabular data (numbers and text) in plain text. Each line of the file could be a data record. Each record consists of 1 or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.

Basic Operations With CSV Files

In Basic operations, we are going to understand the subsequently three things:

1. Working with CSV files
2. Opening a CSV file
3. Saving a CSV file

How to Work With CSV Files?

Working with CSV files isn’t a tedious task, but it’s pretty straightforward. However, counting on your workflow, there can become caveats that you might want to observe out for.

How to Open a CSV File?

If you’ve got a CSV file, you’ll open it in Excel without much trouble. Just open Excel, open and find the CSV file to figure with (or right-click on the CSV file and choose Open in Excel). After you open the file, you’ll notice that the info is simply plain text put into different cells.

How to Save a CSV File?

If you wish to save lots of your current workbook into a CSV file, you have got to use the subsequent commands:

File -> Save As… and choose CSV file. 

More often than not, you’ll get this warning:

Let’s understand what this error is saying to us.

Here Excel is trying to mention that your CSV Files don’t save any reasonable formatting in the least.

For Example, Column widths, font styles, colors, etc., won’t be saved.

Just your plain old data are saved in an exceedingly comma-separated file.

Note that even after you put it aside, Excel will still show the formats you just had, so don’t be fooled by this and think that after you open the workbook again, your formats will still be there. They won’t be.

Even after you open up a CSV get in Excel, if you apply even any minor formatting, like adjusting the column widths to work out the info, Excel will still warn you that you can’t save the formats that you just added. You’ll get a warning like this one:

                                                                 Image Source: Google Images

So, the aim is to note that your formats can never be saved in CSV Files.

Why Do We Use CSV Files?

CSV files are the simplest way to speak data between different applications. Say you had a database application and wanted to export the info to a file. If you wish to export it to an Excel file, the database application will support exporting to XLS* files.

However, since the CSV file format is extremely straightforward and lightweight (much more than XLS* files), it’s easier for varied applications to support it. In its basic usage, you have a line of text, with every column of data going in alternative ways by a comma. That’s it. And since of this simplicity, it’s simple for developers to make Export / Import practicality with CSV files to transfer knowledge between applications instead of a lot of sophisticated file formats.

For Example,

Let’s take the tabular data in the given form below:

If we convert this data into a CSV Format, then it looks like this:

Now, we are completed with all the basics of CSV files. So, In the later part of the article, we will be discussing working with CSV files in a detailed manner.

Pandas DataFrame

One of the easiest ways to read a csv file in Python is using the Pandas library. It has a very useful function, read_csv(), which allows us to read a CSV file and create a Pandas DataFrame. A Pandas DataFrame is nothing but a two-dimensional data structure storing data like a table with rows and columns. So here I’ll show you how to work with CSV files using Python Pandas.

Importing Pandas

Firstly, we import the necessary the Pandas Library of Python.

import pandas as pd

Now we will see how to load and read the dataset easily.

Read-csv Function

• It is an important pandas function to read CSV files and do operations on them.
• This function helps us load the file from your local machine or any URL.

Opening a Local CSV File

If the file is present in the same location as in our Python File, then give the file name only to load that file; otherwise, you have to give the complete filepath to the file. Following is the syntax to read a csv file and create a pandas dataframe from it.

df = pd.read_csv('aug_train.csv')
df

Output:

Opening a CSV File From a URL

If the file is not present directly in our local machine, but we have to fetch the data from a given URL, then we take the help of the requests module to load that data.

Python Code:

Output:

Different Parameters in CSV

Sep Parameter

Suppose we have a dataset in which entities in a particular row are not separated by a comma but by some other delimiter or separator. In that case, we have to use the sep parameter to specify that specific separator or delimiter. For example, a tab or a semi-colon could also be used as separtors.

Say, we have a tsv file, i.e., entities are tab-separated, and if we try to load this data directly, then loads of all the entities are combined.

import pandas as pd
pd.read_csv('movie_titles_metadata.tsv')

Output:

To solve the above problem for the CSV file, we have to overwrite the sep parameter to ‘t‘ instead of ‘,‘ which is a default separator.

import pandas as pd
pd.read_csv('movie_titles_metadata.tsv',sep='t')

Output:

In the above example, we have observed that the first row is treated as the column’s name, and to solve this problem and make our custom name for the columns, we have to specify the list of words with names as the name of the list.

pd.read_csv('movie_titles_metadata.tsv',sep='t',names=['sno','name','release_year','rating','votes','genres'])

Output:

Index-col Parameter

The index-col parameter allows us to set which columns to use as the data frame’s index. The default value for this parameter is None, and pandas automatically will add a new column starting from 0 to describe the index column.

So, it allows us to use a column as the row labels for a given DataFrame. This function comes in handy quite often. Let’s say we have an ID column present with our dataset, which is not impacting our predictions, so we make that column our index for rows instead of the default.

pd.read_csv('aug_train.csv',index_col='enrollee_id')

Output:

Header Parameter

Header parameter allows us to specify which row will be used as column names for your data frame. It expects input as an int value or a list of int values.

The default value for this parameter is header=0, which implies that the first row of the CSV file will be considered as column names.

pd.read_csv('test.csv',header=1)

Output:

Use-cols Parameter

The use-cols parameter specifies which columns to import from the complete dataset to the data frame. It can take input from either a list of int values or directly from the column names.

This function comes in handy when we have to analyze data on just some columns and not on all the columns of our dataset.

So, this parameter returns a subset of the columns from your dataset.

pd.read_csv('aug_train.csv',usecols=['enrollee_id','gender','education_level'])

Output:

Squeeze Parameter

If true and only one column is passed, the squeeze parameter returns a pandas series instead of a DataFrame. A Pandas series is a single-dimensional data structure storing only a single column with a single data type.

pd.read_csv('aug_train.csv',usecols=['gender'],squeeze=True)

Output:

Skiprows Parameter

Skiprows parameter is used to skip past rows in the new data frame. This is useful as we can easily iterate through the csv data and read only the relevant rows.

pd.read_csv('aug_train.csv',skiprows=[0,1])

Output:

Nrows Parameter

The nrows function only reads the fixed number of rows from the file. Needs an int value.

This parameter comes in handy when we have a huge dataset, and we want to load our dataset in chunks instead of directly loading the complete dataset.

pd.read_csv('aug_train.csv',nrows=100)

Output:

Encoding Parameter

The encoding parameter helps determine which encoding you must use for UTF when reading or writing the files.

Sometimes, our files are not encoded in the default form, i.e., UTF-8. So, saving that with a text editor or adding the param “encoding=’utf-8′ doesn’t work. In both cases, it returns the error.

So, to resolve this issue, we call our read_csv function with encoding=’latin1′, encoding=’iso-8859-1′ or encoding=’cp1252′ (these are some of the various encodings found on Windows).

pd.read_csv('zomato.csv',encoding='latin-1')

Output:

Error-bad-lines Parameter

If we have a dataset in which some lines is having too many fields (For Example, a CSV line with too many commas), then by default, it raises and causes an exception, and no DataFrame will be returned.

So, to resolve these types of issues, we have to make this parameter False, then these “bad lines” will be dropped from the DataFrame that is returned. (Only valid with C parser)

pd.read_csv('BX-Books.csv', sep=';', encoding="latin-1",error_bad_lines=False)

Output:

Dtype Parameter

The dtype parameter stands for ‘data type’ for converting data or columns. For Example, {‘a’: np.float64, ‘b’: np.int32}

This function comes in handy when we need to convert our columns from float data type to int data type.

pd.read_csv('aug_train.csv',dtype={'target':int}).info()

Output:

Parse-dates Parameter

If we make this parameter True, then it tries to parse the index.

For Example, If [1, 2, 3] -> try parsing columns 1, 2, 3 each as a separate date column, and if we have to combine columns 1 and 3 and parse them as a single date column, then use [[1,3]].

pd.read_csv('IPL Matches 2008-2020.csv',parse_dates=['date']).info()

Output:

Convertors Parameter

This parameter helps us to convert values in the columns based on a custom function given by the user.

def rename(name):
    if name == "Royal Challengers Bangalore":
        return "RCB"
    else:
        return name

rename("Royal Challengers Bangalore")

Output:

‘RCB’

pd.read_csv('IPL Matches 2008-2020.csv',converters={'team1':rename})

Output:

Na-values Parameter

As we know, the missing values by default will be NaN. If we want other strings to be considered as NaN, then we have to use this parameter. It expects a list of strings as the input.

Sometimes in our dataset, another type of symbol is used to make them missing values, so at that time to understand those values as missing, we use this parameter.

pd.read_csv('aug_train.csv',na_values=['Male',])

Output:

This completes our discussion!

NOTE: In this article, we will only discuss those very useful parameters while working with CSV files daily. But if you are interested to learn more parameters, then refer to the official website of Pandas.

Conclusion

So in this beginner-friendly tutorial, we gained a good understanding of the CSV format and how to handle it in Python. We looked at how to create a Pandas DataFrame and read a CSV file using read_csv() function. We also looked at various parameters of read_csv() function like sep, header, nrows, etc. parameters to read the CSV file in Pandas in the most efficient manner to serve our purpose.

Key Takeaways

• CSV format is a simple file format that stores data in a tabular form, like a spreadsheet or database.
• CSV format is simple, straightforward, lightweight, and supported by various applications.

Frequently Asked Questions