download notebook
view notebook w/ solutions

Reading and writing data with Pandas

files needed = (gdp_components.csv, gdp_parts.csv, debt.xlsx)

We have seen some of the basic things we can do with Pandas. In doing so, we created some simple DataFrames from dicts. That was simple, but it is almost never how we create DataFrames in the wild.

Most data live in files, often as comma-separated values or as MS Excel workbooks, either on our computers or in the cloud. In this notebook, we will review ways to get data into (and out of) Pandas. Our topics are

  • The file system
  • Reading CSV files
  • Reading Excel files

Reading from your computer

Let's start by getting files from our own computers. We start by loading Pandas. We are also loading the os package. os means 'operating system' and it contains functions that help us navigate the file structure of our computers. 

import pandas as pd     # load the pandas package and call it pd
import os               # The package name is already short enough. No need to rename it.

If you have not already, move the gdp_components.csv file to your U:\ drive and put it in the same folder that holds this notebook. We expect this file to contain U.S. GDP and its major components. Your directory structure might look something like

U:\
|
+-- Data_Class
|   +-- pandas_io_finished.ipynb
|   +-- gdp_components.csv
|   +-- gdp_parts.csv
|   +-- debt.xlsx

I am running the pandas_io_finished.ipynb notebook inside the Data_Class folder and my data file is in the same folder.

Let's get this data file into a pandas DataFrame. We use pd.read_csv().

# read_csv is a part of Pandas, so we need the pd. 
gdp = pd.read_csv('gdp_components.csv')       

print(type(gdp))

This looks successful. .read_csv() takes a string with the file name and creates a DataFrame. Let's take a look at the data. 

print(gdp)

If we print a large DataFrame, print() gives us a truncated view—the middle rows are missing. We can use the .head() and .tail() methods of DataFrame to peek at just the first or last few rows. 

# Show the first 4 rows.
print(gdp.head(4))

If you do not pass .head() or .tail() an argument, it defaults to 5 rows. 

print(gdp.tail(2))

The index isn't very sensible. This is time series data (the unit of observation is a year), so the date seems like a good index. How do we set the index?

# We could use 'inplace = True' if we didn't need a copy.
gdp_new_index = gdp.set_index('DATE')   

print(gdp_new_index.head())

gdp_new_index

We can also set the index as we read in the file. Let's take a look at the read_csv() function.

pd.read_csv?

I'm seeing a lot of good stuff here. index_col, usecols, header, sep,... some stuff I don't know about, too. When reading in messy files, these extra arguments may come in handy.

Let's give index_col a try. 

# Treat the CSV like a DataFrame. Count cols staring with 0
gdp_2 = pd.read_csv('gdp_components.csv', index_col = 0)    

gdp_2.head()

Navigating your file structure

We dumped our file into our current working directory so we could just ask for the file name gdp_components.csv in read_csv(). What is our current working directory (cwd)?

path_to_cwd = os.getcwd()           # getcwd() is part of the os package we imported earlier
print(path_to_cwd)

When we gave read_csv() the string 'gpd_components.csv', it looked in our cwd for the file. Let's try something more complicated. Go into your Data_Class folder and create a new folder called Data_Files. Move the file 'gdp_parts.csv' into the Data_Files folder.

My folder structure looks like this. Data_Class is the folder I keep all my files in for this class.

U:\
|
+-- ado
+-- Anaconda
+-- Data_Class
|   +-- pandas_2_io.ipynb
|   +-- gdp_components.csv
|   +-- Data_Files
|   |   +-- gdp_parts.csv
|
+-- Desktop
+-- Documents
+-- python
+-- R

# This looks for gdp_components_moved.csv in the current working directory.
gdp_moved = pd.read_csv('gdp_parts.csv')

Of course this doesn't work. The file is not in our cwd. It's good see what that kind of error message looks like. We need to pass csv_read() the path to the file. The path is the hierarchy of folders that contains the file. In my case, the path is

U:\Data_Class\Data_Files

Note that there is a \ each time we list a new folder.

On Windows: When we specify a file path, we escape the \ by using a second backslash in front of it. 

'U:\\Data_Class\\Data_Files\\gdp_components_moved.csv'

On a Mac, you need to use the forward slash / and you do not need a backslash in front of it.

'/Users/username/Data_Class/gdp_components_moved.csv'

gdp_moved = pd.read_csv('U:\\Data_Class\\Data_Files\\gdp_parts.csv', index_col=0)
gdp_moved.head()

We can manipulate strings to get to this, too. This approach might be useful if you needed to read in many files from the same place. (Maybe using a for loop and a list of file names?) 

path_to_cwd = os.getcwd()
file_name = 'gdp_parts.csv'
path_to_data_file = path_to_cwd + '\\Data_Files\\' +  file_name  # Note the double \ characters
print(path_to_data_file)

gdp_moved = pd.read_csv(path_to_data_file, index_col=0)
gdp_moved.head()

Practice: Reading CSVs

Take a few minutes and try the following. Feel free to chat with those around if you get stuck. The TA and I are here, too.

  1. Try out the .to_csv() method of DataFrame. Save gdp_parts as 'gdp_parts_2.csv' in your cwd. [You can use ? if you need help.]

  2. Use to_csv() again to save gdp_parts to the Data_Files folder. Name it 'gdp_parts_3.csv'

Are your files in the correct places?

Isn't this supposed to be practice reading in CSV files? Right. Let's do some of that.

  1. Use gdp_parts_3.csv to create a DataFrame named gdp_growth. Set the index to the dates. Print out the first 10 years of data.

  2. Rename 'GDPA' to 'gdp' and rename 'GCEA' to 'gov'

Reading Excel spreadsheets

Reading spreadsheets isn't much different than reading csv files. But, since workbooks are more complicated than csv files, we have a few more options to consider. Many of the options we will explore using workbooks are also available when reading csv files.

If you haven't already, copy over 'debt.xlsx' to your cwd. Let's open it in Excel and have a look at it...

There's a lot going on here: missing data, some #N/A stuff, and several header rows. Let's get to work.

debt = pd.read_excel('debt.xlsx')
debt.head(15)

# Use the 'header' option to specify the row to use as the column names (zero based, as usual).

debt = pd.read_excel('debt.xlsx', header = 12)

print(debt)

That's looking good. Notice that Pandas added NaN for the missing data and for those #N\A entries. We will have to deal with those at some point. The header parameter is part of read_csv(), too.

We didn't specify which sheet in the workbook to load, so Pandas took the first one. We can ask for sheets by name. 

debt_q = pd.read_excel('debt.xlsx', header=12, sheet_name='quarterly')
print(debt_q)

We can ask for just a subset of the columns when reading in a file (csv or xlsx). Use the usecols argument. This takes either integers or Excel column letters. 

# Take the first and third columns of sheet 'quarterly'

interest_rates = pd.read_excel('debt.xlsx', header=12,  sheet_name='quarterly', usecols=[0,2])  
interest_rates.head()

Practice: Reading Excel

Take a few minutes and try the following. Feel free to chat with those around if you get stuck. I am here, too.

  1. Read in the quarterly data from 'debt.xlsx' and keep only the columns with the date, gdp, and GFDEBTN. Try to do it all using arguments to .read_excel(). Name your new DataFrame fed_debt.

Print out the first 3 rows of fed_debt.

  1. Oops, I wanted to set the observation_date to the index. Go back and add that to your solution to 1.

  2. What is 'GFDEBTN'? It is the federal debt, in millions. Rename this variable to 'DEBT'

  3. Create a variable name debt_ratio that is the debt-to-GDP ratio. Debt is in millions and gdp is in billions. Adjust accordingly.

There are a lot of missing debt values. Did Pandas throw an error? No. Pandas knows (in some cases) how to work around missing data.

  1. Summarize the debt_ratio variable. What is its max level? Its min?