Read and write data to CSV file
Subject: Python Programming
As you program you will later realise that you may need to store or access data for example a product or student information for later use. This is when it becomes necessary to have a separate file to hold such information.
Again Python is populate use for data analysis and many data specialist and programmers now use it to handle a variety of data.
CSV format
CSV (Comma-Separated Values) is one of the most popular formats for storing and exchanging tabular data.
The structure of a CSV file just as its name is just a plain text file that use a comma to separate each specific data value, it looks like this:
<![CDATA[
column1, column2, column3...
row1data1, row1data2, row1data3 ....
row2data1, row2data2, row2data3....
row3data1, row3data2, row3data3....
row4data1, row4data2, row4data3....
]]>
Each data is separated by comma (,) while the first row typical has the column name that let us now the data about the data under them.
CSV files are very easy to work with using Python, therefore lots of large volume dataset comes in CSV formats.
Any language that supports text file input and string manipulation (like Python) can work with CSV files directly. You can visit. https://www.kaggle.com/datasets for free large and useful dataset for machine learning and data analysis project.
For this course we will be creating a simple CSV file to hold students information.
Example 1
<![CDATA[
name,age,course,level
Ben Onuorah, 25, Computer Science, 300
Biodun Oje, 23, Economics, 400
Hammed Bala, 32, History, 100
]]>
Read from CSV file
The next example is a Python code to open, read, loop through the record and print them.
Example 2
In the example create a file name students.csv in the same location where your python program file is.
<![CDATA[
#import the csv module to work with CSV files
import csv
#create variables to hold filename
filename="students.csv"
#open the file using the open function in read mode.
with open( filename, mode='r') as csvfile:
# creating a csv reader object
csvreader = csv.reader(csvfile)
# extracting each data row one by one and print
row=0
for col in csvreader:
print(f'{col[0]}, {col[1]}, {col[2]},{col[3]}')
row += 1
]]>
Example 3
You can also read a CSV file into a dictionary, making it more friendly to access our data. Using csv.DictReader function.Â
<![CDATA[
import csv
filename="students.csv"
with open( filename, mode='r') as csvfile:
csvreader = csv.DictReader(csvfile)
row=0
for col in csvreader:
#print the column heading
if row==0:
print (f'{ ", ".join(col)}')
row +=1
# print each record
print(f'{col["name"]}, {col["age"]}, {col["course"]}, {col["level"]}')
row += 1
]]>
Printing the column heading may look like lots of work, but may come handy if we need to programmatically add new record (append) to an existing record in the file (Example 5) so it will be helpful to first know the heading columns name and and how many they are:
Writing to CSV file
To write data to a CSV file, you can use the writer function.
To write to a CSV file, we need to open the CSV file in "write" mode. i.e mode='w'
Warning: when you write to a CSV file the new entry will clear all (overwrite) the existing record. So if it's an empty CVS file you may actually find the write more helpful in populating the initial record, and in doing such, you will have to define the column heading and it's rows or record
Example 4
<![CDATA[
import csv
filename="students.csv"
with open( filename, mode='w') as csvfile:
csvwriter = csv.writer(csvfile)
#Write to the CSV file
#column heading
csvwriter.writerow(['name', 'age', 'course', 'level','id'])
csvwriter.writerow(['Olumide Kayode', '36', 'Biology', '100','1'])
print ("Write completed")
]]>
Note: I introduced a new column 'id' in this new record we have now. This is very important practice in data management, ensuring that each record has a unique id e.g "student id" can make it easy for us to identify and managing the information, as we will see in Example 6.
Appending record to CSV file
This is very handy if you want to add new record to the CSV file without overwriting the entire file content like "write" will do.
To append a record to a CSV file, we need to open the CSV file in "append" mode. i.e mode='a'
Let us assume that a new student got admitted we can ask the user to input the data and we use the append to add it to existing record.
Example 5
<![CDATA[
import csv
filename="students.csv"
#ask user to input data
input_name=input ("Enter student name ")
input_age=input ("Enter age ")
input_course=input ("Enter course of study ")
input_level=input ("Enter level of study ")
input_id=input("Enter stident id ")
#store data in a list
newstudent = [input_name, input_age, input_course, input_level, input_id]
#open file in append mode
with open( filename, mode='a') as csvfile:
csvwriter = csv.writer(csvfile)
#Append to the CSV file
csvwriter.writerow(newstudent)
print ("Student record stored")
]]>
Removing a record from CSV file
Removing or deleting a row from a CSV file is a bit tricky because on like a relational database management system with the SQL DELETE statement you can easily do this. But our CSV does not have a function to delete so we have to:
1. Read all the record that did not match the one we want to delete into a variable (rows_not_del) i.e the all the record except the one we don't want using it's ID)
2. Write this new record to the file, therefore overwriting all exist content of the file
Example 6
<![CDATA[
import csv
filename="students.csv"
del_id=input("Enter the id to delete ")
with open(filename, "r") as oldfile:
reader = csv.reader(oldfile)
rows_not_del = [row for row in reader if row[4] != del_id]
with open(filename, "w", newline="") as newfile:
csvwriter = csv.writer(newfile)
for row in rows_not_del:
csvwriter.writerow(row)
print ("Record deleted")
]]>
By:
Benjamin Onuorah
Login to comment or ask question on this topic
Previous Topic
