Working With CSV In Python

CSV in Python

In my previous article we learned about File Handling in Python, so based on that we will now learn about CSV. So, in this article the topics that we will cover are,

• What is CSV?
• Why it is used?
• How to create CSV file?
• How to read file in CSV?
• How to write into a CSV file?
• How to read and write using dictionaries?

Introduction to CSV

• CSV stands for Comma Separated Values.
• CSVs are used to store tabular data in spreadsheet or databases.
• Each line of the file is called a record.
• Each record is separated by a delimiter, usually it is comma (,) but you can change it according to your need.

Now you must be wondering that how we can create these CSV files and how these looks like.

Creating a CSV File

Method 1

1. First, open excel
2. Enter some data you want
3. Then save it as ‘filename.csv’, so you have to use .csv extension.

Method 2

Open your notepad

Enter comma separated values in the format you want. Like:

 

Then save this file as ‘filename.csv’. In this case, I have named my file as ‘sample.csv’.

This will convert the sheet to a spreadsheet and it will be visible to you as: 

Now let’s see how CSV file looks like, it will look same for method 1 and 2: 

‘with’ Keyword/Statement

Before getting started with all the operations in CSV, lets get familiar with ‘with’ keyword, because we would be using it a lot.

• ‘with’ keyword/statement is used just for a cleaner approach and also it simplifies read/write process of files in minimal line of code.
• SYNTAX:

with open(‘filename’,’access_mode’) as file_variable

This is same as: file_variable= open(‘filename’,’access_mode’)

• ‘with’ is basically used for resource management and exception handling.

Reading a CSV file

The very first step that you need to do is- import csv

This statement will import the csv module in python. Now we simply have to read a file, like we did in file handling, using access mode as ‘r’ i.e. read mode.

import csv

with open('sample.csv','r') as fileVariable:
    variableToRead = csv.reader(fileVariable)
    print(variableToRead)

So, in above example,

Line1: We imported the csv module, it implements classes to read and write tabular data in CSV format.

Line3: Using ‘with’ keyword, we opened our file ‘sample.csv’ with open() method and it is opened in read mode i.e. ’r’ and this is all stored in variable as ‘fileVariable’

Line 4: We create a variable to store the read data i.e. ‘variableToRead’ and using csv module’s reader() method, with this we are reading content inside our file ‘sample.csv’. This reader() method is taking an argument which is csvfile, since it is stored in our variable- ‘fileVariable’, that’s why we passed it in the argument. We are creating csv reader object here.

Line 5: Using print(), we simply are trying to display the content of the read file i.e. ‘sample.csv’

OUTPUT

Now, as you can see, the above code block will just print the object and its location, but we want to see the data, so in that case we have to loop through the file line by line to display data. Like-

import csv

with open('sample.csv','r') as fileVariable:
    variableToRead= csv.reader(fileVariable)
    for i in variableToRead:
        print(i)

OUTPUT

Example 1

What if you want to print just the second indexed values in this data file, i.e just the roll numbers and no other values should be printed.

It is very simple, you just have to give the index which you want to display, in this case roll number have index 2, so will use it.

import csv

with open('sample.csv','r') as fileVariable:
    variableToRead= csv.reader(fileVariable)
    for i in variableToRead:
        print(i[2])

OUTPUT

Example 2

What if you don’t want to print the headers of each data. Meaning,

here we don’t want to print ‘name’, ‘emailID’ and ‘rollNo’ as headers, we just want to print the data.

import csv

with open('sample.csv','r') as fileVariable:
    variableToRead= csv.reader(fileVariable)
    next(variableToRead)
    for i in variableToRead:
        print(i)

next() method here is helping us to skip the headers.

OUTPUT

Writing into a file

Writing is also a very  simple task, you just have to use function- writer() inside csv module.

If the file is not existing then also if you perform write operation, it will create a file. Let’s see how:

import csv

header_names = ['Name', 'Class', 'Marks']
data_values = [['Nick','10', '9.0'],
         ['Sam', '11', '9.1'],
         ['Abby',  '10', '9.3'],
         ['Sunny', '12', '9.5'],
         ['Penny', '12', '7.8'],
         ['Zubin','10', '9.1']]

with open('file1.csv', 'w') as fileVariable:
    csvwriter = csv.writer(fileVariable)
    csvwriter.writerow(header_names)
    csvwriter.writerows(data_values)

Line1: We are importing the csv module.

Line3: We are creating a list of headers

Line4: We are inserting a list of values w.r.t every header that is why we created multiple lists within a list.

Line11: Using ‘with’ keyword, we opened our file ‘test.csv’ with open() method and it is opened in write mode i.e. ’w’ and this is all stored in variable as ‘fileVariable’

Line12: We are creating csv writer object here, using writer() method from the csv module. With this we are writing content inside our file ‘test.csv’. This writer () method is taking an argument which is csvfile, since it is stored in our variable- ‘fileVariable’, that’s why we passed it in the argument.

Line13: writerow() method of writes a row of data into the specified file.

Line14: writerows() method is used to write multiple rows at a time or write rows list.

OUTPUT

It will create a new file i.e. ‘test.csv’ 

There will be no output on the console, but ‘test.csv’ file is created and it will look like: \

Now to see the output on the console we have to read the file as well.

import csv

header_names = ['Name', 'Class', 'Marks']
data_values = [['Nick','10', '9.0'],
         ['Sam', '11', '9.1'],
         ['Abby',  '10', '9.3'],
         ['Sunny', '12', '9.5'],
         ['Penny', '12', '7.8'],
         ['Zubin','10', '9.1']]

with open('test.csv', 'w') as fileVariable:
    csvwriter = csv.writer(fileVariable)
    csvwriter.writerow(header_names)
    csvwriter.writerows(data_values)

fileVariable.close()

with open('test.csv','r') as fileVariable:
    variableToRead= csv.reader(fileVariable)
    for i in variableToRead:
        print(i)

Console Output

File Output

Now you must be noticing that there is blank space between the data, when you see it in the file and blank lists printing in console, to avoid this you can write:

with open('test.csv', 'w', newline=' ') as fileVariable:

newline parameter will help skipping the blank space. Try it!

Console Output

File Output

Example 3

Read a file and write its data into another file. Also change the delimiter from ‘,’ to ‘*’

In this case we, we are reading ‘sample.csv’ file and writing its data in ‘test.csv’

sample.csv file looks like,

import csv

with open('sample.csv', 'r') as file1:
    with open('test.csv','w',newline='') as file2:
        readerObject= csv.reader(file1)
        writeObject= csv.writer(file2, delimiter='*')

        for i in readerObject:
            writeObject.writerow(i)

File Output

Again, to see, the Console Output, read the ‘test.csv’ file

import csv

with open('test.csv','r') as file:
    objectVariable = csv.reader(file)
    for i in objectVariable:
        print(i)

Console Output

Reading a file using Dictionary

Reading a csv file using dictionary is almost similar to what we did before except we will be using DictReader() method now for reading our csv file from csv module.

import csv

with open('sample.csv','r') as file:
    objectVariable = csv.DictReader(file)
    for i in objectVariable:
        print(i)

OUTPUT

Now you must be wondering, why use dictionary when we can do it normally with writer() method.

• As you can see in the above output, the displayed file is looking more sorted and clearer in the form of a dictionary.
• The first line become keys and rest of the data are the values, which makes it more readable now, in case we have huge data.
• Now talking about huge data!! It is very difficult to find index of a particular attribute if we want to access that, so in case of dictionaries you don’t have to go and check the index of the data in csv file and then mention it in the code. You can directly write the key to access that particular data. Let’s see an example.

Example 4

Let’s say you want to read all the ‘emailID’ in ‘sample.csv’ file and other attribute should not be displayed.

import csv

with open('sample.csv','r') as file:
    objectVariable = csv.DictReader(file)
    for i in objectVariable:
        print(i['emailID'])

OUTPUT

Writing into a file using Dictionary

Writing into a csv file using dictionary is almost similar to what we did before except we will be using DictWriter() method now for writing in our csv file from csv module.

But since we are using dictionaries here for writing in CSV, so we need to create dictionary and provide the field names also. Like-

import csv

my_dictionary =[{'name': 'Nick', 'emailID': '[email protected]', 'rollNo': '1'},
         {'name': 'Sam', 'emailID': '[email protected]', 'rollNo': '2'},
         {'name': 'Adil', 'emailID': '[email protected]', 'rollNo': '3'},
         {'name': 'Tom', 'emailID': '[email protected]', 'rollNo': '4'},
         {'name': 'Patt', 'emailID': '[email protected]', 'rollNo': '5'},
         {'name': 'Siri', 'emailID': '[email protected]', 'rollNo': '6'}]

my_attributes = ['name', 'emailID', 'rollNo']

with open('test.csv', 'w', newline='') as fileObject:
          writerObject = csv.DictWriter(fileObject, fieldnames = my_attributes)
          writerObject.writeheader()
          writerObject.writerows(my_dictionary)

OUTPUT

Initially we are creating our own dictionary i.e. ‘my_dictionary’, then we are providing the headers of the data in ‘my_attributes’, then we created a new file ‘test.csv’ by opening it in write mode i.e ‘w’. Then by creating writer Object we use DictWriter() method and passed our csv file i.e ‘test.csv’ and headers.

• Now using write object i.e. ‘writerObject’ we call writeheader() method which helps to display the headers or the fieldnames.
• For writing the data rows, we use writerows() method.

Summary

That’s all for now, we have covered all the major operations that are to be performed related to CSV. Now you are aware how to work with CSV in Python. To work with CSV, you need to clear your basic fiile handling and if you are not clear or comfortable working with Dictionaries then refer to my previous articles:

• File Handling in Python
• Dictionaries in Python

Hands-on is must, so practice hard! 

Thanks for reading.