Using Python to Parse Spreadsheet Data

Large organizations and enterprises often store data in spreadsheets and require an interface for entering this data into their web apps. The general idea is to upload the file, read its contents, and store it either in files or databases that the web application uses. Organizations may also need to export data from a web app. For example, they might need to export the grades of all students in a class. Again, spreadsheets are the preferred medium.

In this post, we’ll discuss different ways to handle these files and parse them to get the required information using Python.

A Quick Spreadsheet Primer

Before parsing spreadsheets, you must understand how they’re structured. A spreadsheet file is a collection of sheets, and each sheet is a collection of data cells placed in a grid, similar to a table. In a sheet, a data cell is identified by two values: its row and column numbers.

For instance, in the screenshot above, the spreadsheet contains only one sheet, “Sheet1”. The cell “2A” corresponds to the second row and first column. The value of cell 2A is 1.

Although programs with a GUI assign letters to the names of columns, when we parse the data, we will start row and column numbers from 0. That means, cell 2A will correspond to (1, 0), 4B to (1,3), 3C to (2, 2), and so on.

Setting up the Python Environment

We’ll use Python 3 to read and write spreadsheets. To read and write XLSX files, you need to install the Pandas module. You can do so through one of the Python installers: pip or easy_install. Pandas uses the openpyxl module to read new spreadsheet (.xlsx) files, and xlrd modules to read legacy spreadsheets (.xls files). Both these openpyxl and xlrd are installed as dependencies when you install Pandas:

pip3 install pandas

To read and write CSV files, you need the csv module, which comes pre-installed with Python. You can also read CSV files through Pandas.

Reading Spreadsheets

If you have a file and you want to parse the data in it, you need to perform the following in this order:

1. import the pandas module
2. open the spreadsheet file (or workbook)
3. select a sheet
4. extract the values of particular data cells

Open a spreadsheet file

Let’s first open a file in Python. To follow along you can use the following sample spreadsheet, courtesy of Learning Container:

import pandas as pd
workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx')
workbook.head()

Pandas reads the spreadsheet as a table and stores it as a Pandas dataframe.

If your file has non-ASCII characters, you should open it in the unicode format as follows:

import sys
workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx', encoding=sys.getfilesystemencoding())

If your spreadsheet is very large, you can add an argument use_cols, which loads only certain columns to the dataframe. For instance, the following argument would read only the first five columns:

workbook = pd.read_excel('~/Desktop/import-export-data.xlsx', usecols = 'A:E')
workbook.head()

Additionally, you can use the nrows and skiprows arguments to read only a certain number of rows, or ignore a certain number of rows at the beginning, respectively.

Opening a specific sheet

You can select a certain sheet from your spreadsheet by using the sheet_name argument. By default, the read_excel() function parses the first sheet in the file. You can either provide the name of the sheet as a string, or the index of the sheet (starting from 0):

# Read the sheet with the name 'Sheet1'
worksheet = pd.read_excel('sample-xlsx-file-for-testing.xlsx', sheet_name = 'Sheet1')

# Read the 1st sheet in the file
worksheet = pd.read_excel('sample-xlsx-file-for-testing.xlsx', sheet_name = 0)

You can also select a number of sheets to be stored as a dict of Pandas dataframes by passing a list the sheet_name argument:

# Read the first two sheets and a sheet with the name 'Sheet 3'
worksheets = pd.read_excel('~/Desktop/import-export-data.xlsx', sheet_name = [0, 1, 'Sheet 3'])

Getting data from cells

Once you’ve selected the worksheet into a dataframe, you can extract the value of a particular data cell by querying into the Pandas dataframe:

import pandas as pd
workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx')

# Print the 1st value of the Product column
print(workbook['Product'].iloc[0])

=> Carretera

The .iloc() method helps you search for a value based on the index location. In the above code, .iloc() searches for the value at the 0th index location. Similarly, you can search for a value using a label through the .loc() method. For instance, if you pass the argument 0 to the .loc() method, it will search for the label 0 within the index:

print(workbook['Product'].loc[0])

=> Carretera

You can query your dataset once it’s loaded into a dataframe with the inbuilt functions in Pandas. Here’s an article on exploring the values of your Pandas dataframe.

Creating Spreadsheets

The workflow for creating worksheets is similar to that of the previous section.

1. import the pandas module
2. save data into a workbook
3. create a sheet within the workbook
4. add styling to the cells in the workbook

Create a new file

To create a new file, we first need a dataframe. Let’s recreate the demo sheet from the top of the article:

import pandas as pd

name = ['John', 'Mary', 'Sherlock']
age = [11, 12, 13]
df = pd.DataFrame({ 'Name': name, 'Age': age })
df.index.name = 'ID'

Then you can create a new spreadsheet file by calling the to_excel() function on the dataframe, specifying the name of the file it should save as:

df.to_excel('my_file.xlsx')

You can also open the same file using the function read_excel().

Adding sheets

You can save your dataframe as a certain sheet in the workbook using the sheet_name argument. The default value of this argument is Sheet1:

df.to_excel('my_file.xlsx', sheet_name = 'My Sheet')

More options while saving your spreadsheet

You can use the ExcelWriter class to get more options while saving to your spreadsheet. If you’d like to save multiple dataframes to the same file, you can use the following syntax:

import pandas as pd

workbook = pd.read_excel('my_file.xlsx')

# Creating a copy of workbook
workbook_2 = workbook.copy()

with pd.ExcelWriter('my_file_1.xlsx') as writer:
    workbook.to_excel(writer, sheet_name='Sheet1')
    workbook_2.to_excel(writer, sheet_name='Sheet2')

To append a dataframe to an existing spreadsheet, use the mode argument. Notice that append mode is only supported when you specify the engine as openpyxl:

with pd.ExcelWriter('my_file_1.xlsx', engine="openpyxl", mode='a') as writer:
    workbook_2.to_excel(writer, sheet_name='Sheet3'

Additionally, use the date_format and datetime_format to set the values for date and time values:

with pd.ExcelWriter('my_file.xlsx',
  date_format='YYYY-MM-DD',
    datetime_format='YYYY-MM-DD HH:MM:SS') as writer:
  workbook.to_excel(writer)

Reading Legacy (.xls) Spreadsheets

You can read legacy spreadsheets with the .xls extension using the same syntax in Pandas:

workbook = pd.read_excel('my_file_name.xls')

While you used the same read_excel() function, Pandas uses the xlrd engine to read it. You can read and write legacy spreadsheets using the same syntax that we discussed earlier in this tutorial.

A Quick Summary of CSV Files

CSV stands for “comma-separated values” (or sometimes character-separated if the delimiter used is some character other than a comma) and the name is pretty self explanatory. A typical CSV file looks like the following:

'ID', 'Name', 'Age'
'1', 'John', '11'
'2', 'Mary', '12'
'3', 'Sherlock', '13'

You may convert spreadsheets to CSV files to ease the parsing. CSV files can be parsed easily using the csv module in Python, in addition to Pandas:

workbook = pd.read_csv('my_file_name.csv')

Conclusion

As I mentioned earlier, creating and parsing spreadsheets is inevitable when you’re working with huge web applications. Thus, familiarity with parsing libraries can only help you when the need arises.

What scripting language do you use to handle spreadsheets? Does Python have any other library for this purpose that you prefer? Feel free to hit me up on Twitter.