Reading Tabular Data into DataFrames

Overview

Teaching: 10 min
Exercises: 10 min

Questions

• How can I read tabular data?

Objectives

• Import the Pandas library.

• Use Pandas to load a simple CSV data set.

• Get some basic information about a Pandas DataFrame.

Use the Pandas library to do statistics on tabular data.

• Pandas is a widely-used Python library for statistics, particularly on tabular data.
• Borrows many features from R’s dataframes.
  • A 2-dimenstional table whose columns have names and potentially have different data types.
• Load it with import pandas.
• Read a Comma Separate Values (CSV) data file with pandas.read_csv.
  • Argument is the name of the file to be read.
  • Assign result to a variable to store the data that was read.

import pandas

data = pandas.read_csv('data/gapminder_gdp_oceania.csv')
print(data)

       country  gdpPercap_1952       ...        gdpPercap_2002  gdpPercap_2007
0    Australia     10039.59564       ...           30687.75473     34435.36744
1  New Zealand     10556.57566       ...           23189.80135     25185.00911

[2 rows x 13 columns]

• The columns in a dataframe are the observed variables, and the rows are the observations.
• Pandas uses ellipsis ... to show missing columns when output is too wide to fit the screen.

File Not Found

Our lessons store their data files in a data sub-directory, which is why the path to the file is data/gapminder_gdp_oceania.csv. If you forget to include data/, or if you include it but your copy of the file is somewhere else, you will get a runtime error that ends with a line like this:

OSError: File b'gapminder_gdp_oceania.csv' does not exist

Use index_col to specify that a column’s values should be used as row headings.

• Row headings are numbers (0 and 1 in this case).
• Really want to index by country.
• Pass the name of the column to read_csv as its index_col parameter to do this.

data = pandas.read_csv('data/gapminder_gdp_oceania.csv', index_col='country')
print(data)

             gdpPercap_1952       ...        gdpPercap_2007
country                           ...                      
Australia       10039.59564       ...           34435.36744
New Zealand     10556.57566       ...           25185.00911

[2 rows x 12 columns]

Use DataFrame.info to find out more about a dataframe.

data.info()

<class 'pandas.core.frame.DataFrame'>
Index: 2 entries, Australia to New Zealand
Data columns (total 12 columns):
gdpPercap_1952    2 non-null float64
gdpPercap_1957    2 non-null float64
gdpPercap_1962    2 non-null float64
gdpPercap_1967    2 non-null float64
gdpPercap_1972    2 non-null float64
gdpPercap_1977    2 non-null float64
gdpPercap_1982    2 non-null float64
gdpPercap_1987    2 non-null float64
gdpPercap_1992    2 non-null float64
gdpPercap_1997    2 non-null float64
gdpPercap_2002    2 non-null float64
gdpPercap_2007    2 non-null float64
dtypes: float64(12)
memory usage: 208.0+ bytes

• This is a DataFrame
• Two rows named 'Australia' and 'New Zealand'
• Twelve columns, each of which has two actual 64-bit floating point values.
  • We will talk later about null values, which are used to represent missing observations.
• Uses 208 bytes of memory.

The DataFrame.columns variable stores information about the dataframe’s columns.

• Note that this is data, not a method.
  • Like math.pi.
  • So do not use () to try to call it.
• Called a member variable, or just member.

print(data.columns)

Index(['gdpPercap_1952', 'gdpPercap_1957', 'gdpPercap_1962', 'gdpPercap_1967',
       'gdpPercap_1972', 'gdpPercap_1977', 'gdpPercap_1982', 'gdpPercap_1987',
       'gdpPercap_1992', 'gdpPercap_1997', 'gdpPercap_2002', 'gdpPercap_2007'],
      dtype='object')

Use DataFrame.T to transpose a dataframe.

• Sometimes want to treat columns as rows and vice versa.
• Transpose (written .T) doesn’t copy the data, just changes the program’s view of it.
• Like columns, it is a member variable.

print(data.T)

country           Australia  New Zealand
gdpPercap_1952  10039.59564  10556.57566
gdpPercap_1957  10949.64959  12247.39532
gdpPercap_1962  12217.22686  13175.67800
gdpPercap_1967  14526.12465  14463.91893
gdpPercap_1972  16788.62948  16046.03728
gdpPercap_1977  18334.19751  16233.71770
gdpPercap_1982  19477.00928  17632.41040
gdpPercap_1987  21888.88903  19007.19129
gdpPercap_1992  23424.76683  18363.32494
gdpPercap_1997  26997.93657  21050.41377
gdpPercap_2002  30687.75473  23189.80135
gdpPercap_2007  34435.36744  25185.00911

Use DataFrame.describe to get summary statistics about data.

DataFrame.describe() gets the summary statistics of only the columns that have numerical data. All other columns are ignored, unless you use the argument include='all'.

print(data.describe())

       gdpPercap_1952       ...        gdpPercap_2007
count        2.000000       ...              2.000000
mean     10298.085650       ...          29810.188275
std        365.560078       ...           6540.991104
min      10039.595640       ...          25185.009110
25%      10168.840645       ...          27497.598692
50%      10298.085650       ...          29810.188275
75%      10427.330655       ...          32122.777857
max      10556.575660       ...          34435.367440

[8 rows x 12 columns]

• Not particularly useful with just two records, but very helpful when there are thousands.

Reading Other Data

Read the data in gapminder_gdp_americas.csv (which should be in the same directory as gapminder_gdp_oceania.csv) into a variable called americas and display its summary statistics.

Solution

To read in a CSV, we use pandas.read_csv and pass the filename ‘data/gapminder_gdp_americas.csv’ to it. We also once again pass the column name ‘country’ to the parameter index_col in order to index by country:

americas = pandas.read_csv('data/gapminder_gdp_americas.csv', index_col='country')

Inspecting Data.

After reading the data for the Americas, use help(americas.head) and help(americas.tail) to find out what DataFrame.head and DataFrame.tail do.

1. What method call will display the first three rows of this data?
2. What method call will display the last three columns of this data? (Hint: you may need to change your view of the data.)

Solution

1. We can check out the first five rows of americas by executing americas.head() (allowing us to view the head of the DataFrame). We can specify the number of rows we wish to see by specifying the parameter n in our call to americas.head(). To view the first three rows, execute:

americas.head(n=3)

The output is then

         continent  gdpPercap_1952  gdpPercap_1957  gdpPercap_1962  \
country                                                               
Argentina  Americas     5911.315053     6856.856212     7133.166023   
Bolivia    Americas     2677.326347     2127.686326     2180.972546   
Brazil     Americas     2108.944355     2487.365989     3336.585802   

          gdpPercap_1967  gdpPercap_1972  gdpPercap_1977  gdpPercap_1982  \
country                                                                     
Argentina     8052.953021     9443.038526    10079.026740     8997.897412   
Bolivia       2586.886053     2980.331339     3548.097832     3156.510452   
Brazil        3429.864357     4985.711467     6660.118654     7030.835878   

          gdpPercap_1987  gdpPercap_1992  gdpPercap_1997  gdpPercap_2002  \
country                                                                     
Argentina     9139.671389     9308.418710    10967.281950     8797.640716   
Bolivia       2753.691490     2961.699694     3326.143191     3413.262690   
Brazil        7807.095818     6950.283021     7957.980824     8131.212843   

          gdpPercap_2007  
country                    
Argentina    12779.379640  
Bolivia       3822.137084  
Brazil        9065.800825 

1. To check out the last three rows of americas, we would use the command, americas.tail(n=3), analogous to head() used above. However, here we want to look at the last three columns so we need to change our view and then use tail(). To do so, we create a new new DataFrame in which rows and columns are switched

americas_flipped = americas.T

We can then view the last three columns of americas by viewing the last three rows of americas_flipped:

americas_flipped.tail(n=3)

The output is then

country        Argentina  Bolivia   Brazil   Canada    Chile Colombia  \
gdpPercap_1997   10967.3  3326.14  7957.98  28954.9  10118.1  6117.36   
gdpPercap_2002   8797.64  3413.26  8131.21    33329  10778.8  5755.26   
gdpPercap_2007   12779.4  3822.14   9065.8  36319.2  13171.6  7006.58   

country        Costa Rica     Cuba Dominican Republic  Ecuador    ...     \
gdpPercap_1997    6677.05  5431.99             3614.1  7429.46    ...      
gdpPercap_2002    7723.45  6340.65            4563.81  5773.04    ...      
gdpPercap_2007    9645.06   8948.1            6025.37  6873.26    ...      

country          Mexico Nicaragua   Panama Paraguay     Peru Puerto Rico  \
gdpPercap_1997   9767.3   2253.02  7113.69   4247.4  5838.35     16999.4   
gdpPercap_2002  10742.4   2474.55  7356.03  3783.67  5909.02     18855.6   
gdpPercap_2007  11977.6   2749.32  9809.19  4172.84  7408.91     19328.7   

country        Trinidad and Tobago United States  Uruguay Venezuela  
gdpPercap_1997             8792.57       35767.4  9230.24   10165.5  
gdpPercap_2002             11460.6       39097.1     7727   8605.05  
gdpPercap_2007             18008.5       42951.7  10611.5   11415.8  

Note: we could have done the above in a single line of code by ‘chaining’ the commands:

americas.T.tail(n=3)

Reading Files in Other Directories

The data for your current project is stored in a file called microbes.csv, which is located in a folder called field_data. You are doing analysis in a file called analysis.py in a sibling folder called thesis:

your_home_directory
+-- field_data/
|   +-- microbes.csv
+-- thesis/
    +-- analysis.py

What value(s) should you pass to read_csv to read microbes.csv in analysis.py?

Solution

We need to specify the path to the file of interest in the call to pandas.read_csv. We first need to ‘jump’ out of the folder thesis using ‘../’ and then into the folder field_data using ‘field_data/’. Then we can specify the filename `microbes.csv. The result is as follows:

data_microbes = pandas.read_csv('../field_data/microbes.csv')

Writing Data

As well as the read_csv function for reading data from a file, Pandas provides a to_csv function to write dataframes to files. Applying what you’ve learned about reading from files, write one of your dataframes to a file called processed.csv. You can use help to get information on how to use to_csv.

Solution

In order to write the DataFrame americas to a file called processed.csv, execute the following command:

americas.to_csv('processed.csv')

For help on to_csv, you could execute, for example,

help(americas.to_csv)

Note that help(to_csv) throws an error! This is a subtlety and is due to the fact that to_csv is NOT a function in and of itself and the actual call is americas.to_csv.

Key Points

• Use the Pandas library to do statistics on tabular data.

• Use index_col to specify that a column’s values should be used as row headings.

• Use DataFrame.info to find out more about a dataframe.

• The DataFrame.columns variable stores information about the dataframe’s columns.

• Use DataFrame.T to transpose a dataframe.

• Use DataFrame.describe to get summary statistics about data.