Python: Working with CSV Files

Since you are on my site, I am going to go out on a limb here and say you are interested in working with Data in one form or another. I mean, if you are looking to learn how to make a video game with Python, you are in the wrong place. I am not going to be much help to you at all.

But, for those of us who do want to work with data, the last thing we want to do have to manually input all the data. We need methods for reading from and writing to files and databases. We will start by learning to read and write a CSV  (comma separated values) file.

If you would like to play along, you can download the CSV file I will be working with here: empList

Unzip the file and place it somewhere you can find it.

The file contains 7 records, with 3 columns each.

csv

If you want to make your life a little easier, when you open your Jupyter Notebook, type pwd

This will give you your present working directory. If you place your csv file in this directory, all you have to do is call it by name.

csv1

I am not going to do that however. I want to show you how to work with directories.

Get Full Directory Path

For Windows Users, if you hold down the Shift key while right clicking on your file, you will see an option that says: Copy as Path

csv2

CSV Module

To read or write to a CSV file, you need to import the CSV module.

In the next line I use the open() method from the CSV module to open my file.

f = open(paste your file path you copied here, ‘rb’)‘rb’ is required for reading CSV files in Python 2.7

Note the double \\ . I had to add the second \ to my copied file path. If you don’t Python will view the single \ as an escape character and your file will not open.

csv3.jpg

Read the file

Now that the file has been opened, let’s read it. We start by passing the file through the csv.reader() method

Now we can work with it. Below I use a for loop to iterate each row in the file.

csv4.jpg

You can pick and choose columns like you would reading from a list (using indexes).

Notice I had to import the file again – once CSV iterates through a file, you need to re-import it use it again.

csv5.jpg

You can dump the file into variables though. You can work on those variables repeatedly. In the example below I dump each column into a list.

csv56.jpg

Write a CSV file

Writing a csv file is pretty simple as well. However, when opening the file, use ‘wb’ as the second argument, not ‘rb’.

In this example, I am creating a new file ‘empList1.csv‘ and placing the values found in my Name list in the file.

writing in CSV requires you to use the csv.writer() command to make a writable object and use writerow() just like you did readrow()

also, make sure to close() the file or you will not be able to access it later.

csv57.jpg

There is a problem here though, look at the output. Python separated each letter in the names into their own columns.

csv58.jpg

The CSV file looks like this in Excel

csv59.jpg

Not exactly what I wanted.

The fix is square brackets [] inside the writerow().

csv60

Now our file is how we want it.

csv61


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python

 

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s