CSV Files

Working with CSV files

CSV Files

Following on from the work on plain text files, it is also important to understand how Python can be used with CSV files.

A file with the CSV file extension is a Comma Separated Values file. All CSV files are plain text, can contain numbers and letters only, and structure the data contained within them in a tabular, or table, form.

Files of this format are generally used to exchange data, usually when there's a large amount, between different applications. Database programs, analytical software, and other applications that store massive amounts of information (like contacts and customer data), will usually support the CSV format.

A Comma Separated Values file might sometimes be referred to as a Character Separated Values or Comma Delimited file but regardless of how someone says it, they're talking about the same CSV format.

The image below shows a typical CSV file, in this case showing American states with their representative bird. Note the use of column Headings.

CSV
Introduction

Before we can begin working in Python, we need to make sure we have a file to work with. Download the file below and save it in a folder.

Next, create the program below and run it - you will need to make sure your Python program is saved to the SAME folder as the CSV file!

import csv
f=open("coffee.csv")
for row in csv.reader(f):
    print(row)
['Coffee', 'Water', 'Milk', 'Icecream']
['Espresso', 'No', 'No', 'No']
['Long Black', 'Yes', 'No', 'No']
['Flat White', 'No', 'Yes', 'No']
['Cappuccino', 'No', 'Yes - Frothy', 'No']
['Affogato', 'No', 'No', 'Yes']

Each row is read as a list of strings representing the fields in the row.

Step 1 - Opening a CSV File

We're going to do some processing of real-world data now, using freely available airline data sets from the OpenFlights project.

Airports

We can see from the data file that "Name" is the second field in each row of data. This means in the list of fields it will have index 1 (index 0 is the first field.)

Here's some code that prints the name of every airport:

import csv
f = open("airports.csv", encoding='utf-8')
for row in csv.reader(f):
    print(row[1])

I bet the output is still running!! Remember we are now working with large data files - 12000 rows!

Let's expand out code so that it only outputs airports from specific countries.

import csv
f = open("airports.csv", encoding='utf-8')
if row[3] == "Australia" or row[3] == "Russia":
        print(row[1])

You can use this code to work with basic data output of specific rows and fields.

Step 2 — Big Data

One of the most common tasks when working with CSV files is searching for information and outputting related data. In this section we are going to carry out a simple search.

Use the code below to search for the product number 2999.

Once you have tested it works, play around with the number of entries in the CSV file and search different fields to return different values.

import csv
import sys

#input number you want to search
number = input('Enter number to find\n')

#read csv, and split on "," the line
csv_file = csv.reader(open('products.csv'), delimiter=",")


#loop through csv list
for row in csv_file:
    #if current rows 2nd value is equal to input, print that row
    if number == row[1]:
         print (row[2])
Enter number to find
2999
76
Step 3 — Searching

It is essential to be able to write information back to a CSV file. The example code below gives you the outline of how this can be achieved for a single line and multiple lines of data. This code can be adapted to fit your needs.

Single line of data:

import csv

f = open("sample.csv", "w")
writer = csv.writer(f)
writer.writerow(["a", "1"])
f.close()
a,1

Multiple lines of data:

import csv

f = open("sample.csv", "w")
writer = csv.writer(f)
writer.writerows([["a", "1"], ["b", "2"]])
f.close()
a,1
b,2
Step 4 — Writing
If you need to get in touch with Mr McG then this is the way….

What's your name?
image/svg+xml
Thank you [name], that's great.

Can I ask where you are from?
image/svg+xml
Hey [name], can I ask where you are from?
image/svg+xml
Hey [name], which class are you in?
image/svg+xml
OK [name], thanks for that.

I will need your email address so I can get back to you!!
image/svg+xml
Thanks [name], nearly there.

What can I help you with?
image/svg+xml

Thanks [name] from [whichclass][location], Mr McG will get your message…"
[message]"…and will get back to you ASAP via [email]. Catch you soon.

image/svg+xml
Thank you [name], Mr McG got your message.
Thanks [name], Mr McG will get back to you as soon as he can!!

Have a great day!!

K McGuinness - 2018

No personal data will be shared with any third party. The only data processed via this website is anonymous browsing data to best manage your user experience.