Exporting data to spreadsheet using python
One of the easiest way of viewing a large amount of data is by using a spreadsheet. In my previous article about gathering data from the web, I briefly brought up the fact that the information which we’ve gathered could later be processed into many different forms. In this article, I’ll talk about how we could export such data into a nice and tidy spreadsheet file.
There are several ways to export data into a spreadsheet, one of the simplest one is to write them into a .csv file, where each data is separated by a comma(,) character, but it would be really messy when the data itself contains commas. The method that we are going to look into today is the .xlsx file format. Luckily, there is an easy to use library in python that could be installed using these commands in command prompt:
pip install xlsxwriterFor an easier explanation, I would be showing a code snippet with a sample data of student scores in the form of python dictionary, which are similar to the json format, which is widely used among many programming languages.
import xlsxwriterstudent_scores = [
{ "name":"Andy", "score":7.6, "grade":"B" },
{ "name":"Bob", "score":8.2, "grade":"A" },
{ "name":"Charles", "score":6.9, "grade":"C" },
{ "name":"David", "score":7.4, "grade":"B" },
{ "name":"Edward", "score":6.5, "grade":"C" }
]wb = xlsxwriter.Workbook("Student_Scores.xlsx")
ws = wb.add_worksheet()
bold = wb.add_format({'bold': True})# Header
row = 0
col = 0
for key in student_scores[0].keys():
ws.write(row,col,key.capitalize(),bold)
col+=1# Student data
row = 1
for stdata in student_scores:
col = 0
for value in stdata.values():
ws.write(row,col,value)
col+=1
row+=1wb.close()
In this code, wb is a variable that references the .xlsx file that it would export into, if it didn’t already exist, it would generate a new file based on the given name. ws references a worksheet in wb that is generated by the function add_worksheet(). The row variable is used to keep track of the current row that is being written in the worksheet (like a cursor). The col variable keeps track of the current column number. The write function in ws is used to write out data into the spreadsheet, it has many overload functions, but the one I used in this code takes 3 parameters: row, column, value, and format (optional).
The code shown above first writes out the header by iterating through the keys of the first item in the student_scores list and applying bold to the formatting. Afterwards, it will iterate through each items in the student_scores list and write out all their values into the worksheet while also incrementing the rows and columns. A new file named ‘Student_Scores.xlsx’ would be generated within the same folder and just like that, we’ve successfully exported our data.
