SPREADSHEETS WITH PYTHON
This section covers using Python to read and write csv files. A very useful tool for manipulating data in a spreadsheet.
Limitations - saving your spreadsheet as a csv file does limit the features you can use in the spreadsheet, however you can still link the csv file to other spreadsheet documents that do retain all functionality.
Limitations - saving your spreadsheet as a csv file does limit the features you can use in the spreadsheet, however you can still link the csv file to other spreadsheet documents that do retain all functionality.
WHAT IS A CSV FILE
CSV stands for Comma Separated Value. This type of file can be used for many applications including databases and spreadsheets. A comma separates each element within the document and it is the comma that allows programs such as MS Excel to put the data in to individual cells, rows and columns. You can view csv files in most text editors and you will see a comma separating each value, as can be seen in the example screenshot from MS Word.
|
READING THE CSV FILE WITH PYTHON
The code below shows two methods of reading the csv file through Python. The first method is useful if you want to keep it simple and just fetch the details from the csv file. The second method is more useful if you want to add more functionality to your program as we will see later in this section. If you want a csv file to test your code please feel free to download the 'activities.csv' file as seen in the examples.
NOTE: Save your csv file in the same location that your Python file is saved, otherwise you need to pass in the file path and file name.
This video runs you through accessing the spreadsheet from Python, printing the content and searching for a value within the document.
This video runs you through accessing the spreadsheet from Python, printing the content and searching for a value within the document.
|
USING THE OPEN METHOD - EXAMPLE CODE
import csv file = open('activities.csv')#Replace the file name with your file for line in file: print (line)
USING THE WITH OPEN METHOD - EXAMPLE CODE
import csv with open ('activities.csv', 'r') as file: sports = csv.reader(file) for row in sports: print (row[3])
NOT WORKING ? CHANGING THE DELIMITER
If the code does not work, first check that the csv file is saved in the same location as the Python file. Otherwise you may need to change the delimiter. the delimiter is what separates the values and not all CSV files use a comma to separate the values. Open the CSV file in a word editing document and chack what separates the values. Then add the code to tell python what the delimiter is, as can be seen below.
with open ('activities.csv', 'r') as file: sports = csv.reader(file, delimiter = ' ')
SEARCHING THE CSV FILE
This section looks at taking a user input and searching the csv to find the value then returning the row of details where the value is found.
import csv level = input("What level do you want to view? ").title() with open ('activities.csv', 'r') as file: sports = csv.reader(file, delimiter = ' ') for row in sports: if row[2] == (level): print (row)
CREATE A PYTHON LIST FROM THE CSV DATA
|
Opening the csv file and creating multiple lists in python to read and store the data. A great method to use especially if you already have the data in a spreadsheet but want o create lists to hold the data in Pyhton.
This could also be quiet useful with the more recent CIE GCSE Computer Science coding tasks.
This could also be quiet useful with the more recent CIE GCSE Computer Science coding tasks.
import csv activity_ID = [] activity_type = [] activity_level = [] activity_description = [] with open ('activities.csv','r') as file: sports = csv.reader(file) for row in sports: activity_ID.append(row[0]) activity_type.append(row[1]) activity_level.append(row[2]) activity_description.append(row[3]) print (activity_ID) print (activity_type ) print (activity_level) print (activity_description)
WRITTING TO A CSV FILE
|
This video talks you through writing to a csv file and taking a user input and writing the input to the csv file. The method below writes the column heading and then goes into a loop to enter the column content, asking the user each time to enter the details that will be appended to each column.
import csv with open ('activityV3.csv','w+') as file: myFile = csv.writer(file) myFile.writerow(["Id","Activity","Description","Level"]) noOfActivities = int(input("Please enter how many activities you want: ")) for i in range (noOfActivities): activityID = input("Activity " + str(i + 1) + " : Please enter the activity ID: ") activity = input("Activity " + str(i + 1) + " : Please enter the type of activity: ") desc = input("Activity " + str(i + 1) + " : Please enter the description of the activity: ") level = input("Activity " + str(i + 1) + " : Please enter the level of the activity: ") myFile.writerow([activityID, activity, desc,level])
APPENDING A CSV FILE
A quick append method to add more data to the end of a CSV file
EDITING A CSV FILE
|
This methods demonstrates using a list to edit a specific part of the CSV file. The video and code show a user-friendly way of showing the user the content of the file, asking the user what changes they would like to make and then writing the file.
#EDITING A CSV FILE #Task 1: Import CSV and create an empty list import csv myList = [] #Task 2: Open the file and populate the empty list with open ('activityv4.csv','r') as file: myFile =csv.reader(file) for row in myFile: myList.append(row) #Task 3: Show the user the content of the file and print row number to make selection easy print ("Please see details of the csv file below:") for i in range (len(myList)): print ("Row " + str(i) + ": " + str(myList[i])) #Task 4: Allow the user to select which row to edit editRow = int(input("\nWhich row would you like to change? Enter 1 - " + str(len(myList)-1) + " :")) print ("Please enter the new details for each of the following :") #Task 5: Allow the user to make add the changes and append changes to the list for i in range (len(myList[0])): newDetails = input("Enter new data for " + str(myList[0][i]) + " :") myList[editRow][i] = newDetails #Task 6: Show the user the new list and confirm changes print ("\nPlease see the details of the new file below:") for i in range (len(myList)): print ("Row " + str(i) + " :" + str(myList[i])) #Task 7: If changes are needed write the new file changeCSV = input ("\nWould you like to make the changes to the csv file ? Y/N").lower() if changeCSV == ("y"): with open ('activityv4.csv','w+') as file: myFile = csv.writer(file) for i in range (len(myList)): myFile.writerow(myList[i])