Opening Excel Spreadsheets (Python)

Microsoft Excel is great at spreadsheets but what if you want to use a different tool to analyse the data an Excel spreadsheet contains? In steps Python 3 and pandas, and as I’m currently looking at using pandas I’ve decided to make a few notes.

1599px-Giant_Panda_EatingNo, not this type of panda (kindly used under Creative Commons license from https://commons.wikimedia.org/wiki/File:Giant_Panda_Eating.jpg) but a Python module called pandas.

pandas is an open source data analysis library for Python and has a website at https://pandas.pydata.org/

For my example I have created a very basic spreadsheet (xlsx format) with some fictional comic book sales in:

geektechstuff_fictional_comic_sales.png

To import Excel (xlsx) data into Python requires only a few lines of code:

import pandas as panda_data

# read data from Excel spreadsheet
comic_data = panda_data.read_excel(‘comics_geektechstuff.xlsx’,sheet_name=’Sheet1′,skiprows=1)
 
 

geektechstuff_python_pandas_1.png

Looking at the line to read the spreadsheet:

comic_data = panda_data.read_excel(‘comics_geektechstuff.xlsx’,sheet_name=’Sheet1′,skiprows=1)

comic_data is the variable where Python will store the data.

panda_data.read_excel opens the xlsx file

‘comics_geektechstuff.xlsx’ is the xlsx file name that I want to open

sheet_name=’Sheet1′ is the name of the sheet that I want to look at

skiprows=1 is used to skip the first row of data as this is an unneeded header (number of comics in my xlsx file)

print(comic_data.head()) can then be used to output the first five rows of data.

print(comic_data) could be used to output all the data.

One thought on “Opening Excel Spreadsheets (Python)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.