Lesson 4

Series and DataFrames

This lesson covers:

  • Constructing pandas Series and DataFrames

Data

September 2018 prices (adjusted closing prices) for the S&P 500 EFT (SPY), Apple (AAPL) and Google (GOOG) are listed below:

Date SPY Price AAPL Price GOOG Price
Sept4 289.81 228.36 1197.00
Sept5 289.03 226.87 1186.48
Sept6 288.16 223.10 1171.44
Sept7 287.60 221.30 1164.83
Sept10 288.10 218.33 1164.64
Sept11 289.05 223.85 1177.36
Sept12 289.12 221.07 1162.82
Sept13 290.83 226.41 1175.33
Sept14 290.88 223.84 1172.53
Sept17 289.34 217.88 1156.05
Sept18 290.91 218.24 1161.22
Sept19 291.44 216.64 1158.78

Prices in September 2018

Problem: Input a pandas Series

Create vectors for each of the days in the Table named sep_xx where xx is the numeric date. For example,

import pandas as pd

sep_04 = pd.Series([289.81,228.36,1197.00], index=["SPY","AAPL","GOOG"]);

Using the ticker names as the index of each series

In [ ]:
 

Problem: Create a Vector of Dates

Use the pandas function pd.to_datetime to convert a list of string dates to a pandas DateTimeIndex, which can be used to set dates in other arrays.

For example, the first two dates are

import pandas as pd

dates_2 = pd.to_datetime(["4-9-2018","5-9-2018"])
print(dates_2)

which produces

DatetimeIndex(["2018-04-09", "2018-05-09"], dtype="datetime64[ns]", freq=None)

Create a vector containing all of the dates in the table.

In [ ]:
 

Problem: Input a Series with Dates

Create vectors for each of the ticker symbols in Table named spy, aapl and goog, respectively. Use the variable dates that you created in the previous step as the index.

For example

goog = pd.Series([1197.00,1186.48,1171.44,...], index=dates)

Set the name of each series as the series" ticker.

In [ ]:
 

Problem: Create a DataFrame

Create a DataFrame named prices containing Table. Set the column names equal to the ticker and set the index to dates.

prices = pd.DataFrame([[289.81, 228.36, 1197.00], [289.03, 226.87, 1186.48]],
                      columns = ["SPY", "AAPL", "GOOG"],index=dates_2)
In [ ]:
 

Save the price data

This block saves prices to a HDF file for use in later lessons. The function used to save the data is covered in a later lesson.

This function uses some sophisticated features of Python. Do not worry if it is unclear at this point.

In [ ]:
# Setup: Save prices, goog and sep_04 into a single file for use in other lessons

# Only run if prices has been defined
if "prices" in globals():
    import pandas as pd
    dates = pd.Series(dates)
    variables = ["sep_04", "sep_05", "sep_06", "sep_07", "sep_10", "sep_11",
                 "sep_12", "sep_13", "sep_14", "sep_17", "sep_18", "sep_19",
                 "spy", "goog", "aapl", "prices", "dates"]
    with pd.HDFStore("data/dataframes.h5", mode="w") as h5:
        for var in variables:
            h5.put(var, globals()[var])

Exercises

Exercise: Creating DataFrames

Turn the table below into a DataFrame where the index is set as the index and the column names are used in the DataFrame.

index Firm Profit
A Alcoa 3,428
B Berkshire 67,421
C Coca Cola 197.4
D Dannon -342.1
In [ ]: