Pandas DataFrames

Setup

Import Libraries

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd

from IPython.display import Image

Section 1: What is a DataFrame?

A DataFrame, simply put, is a Table of data. It is a structure that contains multiple rows, each row containing the same labelled collection of data types. A DataFrame is a table with named rows (called the “index”). For example, a DataFrame might look like this:

Because each row contains the same data, DataFrames can also be thought of as a collection of same-length columns!

Pandas is a Python package that has a DataFrame class. Using either the DataFrame class constructor or one of Pandas’ many read_() functions, you can make your own DataFrame from a variety of sources.

Different Ways of Making DataFrames Directly

From a List of Dicts

Dicts are named collections. If you have many of the same dicts in a list, the DataFrame constructor can convert it to a Dataframe:

Exercises

friends = [
    {'Name': "Nick", "Age": 31, "Height": 2.9, "Weight": 20},
    {'Name': "Jenn", "Age": 55, "Height": 1.2},
    {"Name": "Joe", "Height": 1.2, "Age": 25, },
]
pd.DataFrame(friends)

From a Dict of Lists

df = pd.DataFrame({
    'Name': ['Nick', 'Jenn', 'Joe'], 
    'Age': [31, 55, 25], 
    'Height': [2.9, 1.2, 1.2],
})
df

From a List of Lists

if you have a collection of same-length sequences, you essentially have a rectangular data structure already! All that’s needed is to add some column labels.

friends = [
    ['Nick', 31, 2.9],
    ['Jenn', 55, 1.2],
    ['Joe',  25, 1.2],
]
pd.DataFrame(friends, columns=["Name", "Age", "Height"])

From an empty DataFrame

If you prefer, you can also add columns one at a time, starting with an empty DataFrame:

df = pd.DataFrame()
df['Name'] = ['Nick', 'Jenn', 'Joe']
df['Age'] = [31, 55, 25]
df['Height'] = [2.9, 1.2, 1.2]
df

Exercise: Please use Pandas to recreate the table here as a Dataframe using one of the approaches detailed above:

df = pd.DataFrame()
df['Year'] = [2015, 2016, 2015, 2016]
df['Product'] = ['Apples', 'Apples', 'Bananas', 'Bananas']
df['Cost'] = [.35, .45, .75, 1.10]
df

Section 2: Reading Data from Files into a DataFrame

Understanding Different File Formats

Run the code below to download the Titanic passengers dataset, and transform it into different file formats

Note: Yep, that’s right, you can supply a web url and pandas reads it like a normal file!

Exercises

url = 'https://raw.githubusercontent.com/mwaskom/seaborn-data/master/titanic.csv'
df = pd.read_csv(url)
df[:5]

Now run the code below to save the file to a comma-seperated file using the DataFrame.to_csv() method.

df.to_csv("titanic2001.csv", index=False)

Exercise: Use a text editor to examine the file that was saved on the computer. How is the file structured?

Solution

Exercise: Now read the file back into Python using the pd.read_csv() function:

pd.read_csv('titanic2001.csv')

JSON

Exercise: Save the dataframe to a JSON file using the df.to_json() method. Make sure to use the correct extension in the filename.

df.to_json('titanic.ignore.json', )

Exercise: Read the JSON file into Pandas again, using the pd.read_json() method.

pd.read_json('titanic.ignore.json')

HTML

Exercise: Save the dataframe to a HTML file, using the df.to_html() method.

df.to_html('titanic.ignore.html')

Exercise: Read the HTML file into Pandas again, using the pd.read_html() function.

Note: Because HTML files can contain multiple dataframes, you’ll get a list of dataframes instead of just one. Just add [0] onto the end of the line to get the first dataframe, and it will look the same as before.

pd.read_html('titanic.ignore.html')[0]

Exercise: Try opening the file with a text editor, then with a web browser (e.g. Chrome, Firefox, etc). What does the file look like in each case? In text editor:

Solution

In the Editor:
In the Browser:

Exercise: Save the dataframe to an Excel file. DataFrame.to_excel(engine='openpyxl') Note: Because XLS and XLSX are proprietary formats, you may need to install a couple extra packages for this to work

df.to_excel('titanic.ignore.xlsx', engine='openpyxl')

Exercise: Read the Excel file into Pandas again, using the pd.read_excel(engine='openpyxl') function.

pd.read_excel('titanic.ignore.xlsx', engine='openpyxl')

Exercise: Open it in your spreadsheet program. What does it look like?

Solution