<jupyter_start><jupyter_text>(pd)= PandasIn addition to what's in Anaconda, this lecture will need the followinglibraries:<jupyter_code>!pip install --upgrade pandas-datareader<jupyter_output>Requirement already up-to-date: pandas-datareader in /home/choldgraf/anaconda/envs/dev/lib/python3.8/site-packages (0.8.1)
Requirement already satisfied, skipping upgrade: lxml in /home/choldgraf/anaconda/envs/dev/lib/python3.8/site-packages (from pandas-datareader) (4.5.0)
Requirement already satisfied, skipping upgrade: pandas>=0.21 in /home/choldgraf/anaconda/envs/dev/lib/python3.8/site-packages (from pandas-datareader) (1.0.1)
Requirement already satisfied, skipping upgrade: requests>=2.3.0 in /home/choldgraf/anaconda/envs/dev/lib/python3.8/site-packages (from pandas-datareader) (2.23.0)
Requirement already satisfied, skipping upgrade: pytz>=2017.2 in /home/choldgraf/anaconda/envs/dev/lib/python3.8/site-packages (from pandas>=0.21->pandas-datareader) (2019.3)
Requirement already satisfied, skipping upgrade: numpy>=1.13.3 in /home/choldgraf/anaconda/envs/dev/lib/python3.8/site-packages (from pandas>=0.21->pandas-datareader) (1.18.1)
Requirement already satisfied, skipping upgrade: p[...]<jupyter_text>Overview[Pandas](http://pandas.pydata.org/) is a package of fast, efficient dataanalysis tools for Python.Its popularity has surged in recent years, coincident with the rise offields such as data science and machine learning.Here\'s a popularity comparison over time against STATA, SAS, and[dplyr](https://dplyr.tidyverse.org/) courtesy of Stack Overflow Trends```{figure} /_static/lecture_specific/pandas/pandas_vs_rest.png:scale: 23%```Just as [NumPy](http://www.numpy.org/) provides the basic array datatype plus core array operations, pandas1.  defines fundamental structures for working with data and2.  endows them with methods that facilitate operations such as  -   reading in data  -   adjusting indices  -   working with dates and time series  -   sorting, grouping, re-ordering and general data munging[^1]  -   dealing with missing values, etc., etc.More sophisticated statistical functionality is left to other packages,such as [statsmodels](http://www.statsmodels.org/) and[scikit-learn](http://scikit-learn.org/), which are built on top ofpandas.This lecture will provide a basic introduction to pandas.Throughout the lecture, we will assume that the following imports havetaken place<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
import requests<jupyter_output><empty_output><jupyter_text>SeriesTwo important data types defined by pandas are `Series` and `DataFrame`.You can think of a `Series` as a \"column\" of data, such as acollection of observations on a single variable.A `DataFrame` is an object for storing related columns of data.Let\'s start with `Series`<jupyter_code>s = pd.Series(np.random.randn(4), name='daily returns')
s<jupyter_output><empty_output><jupyter_text>Here you can imagine the indices `0, 1, 2, 3` as indexing four listedcompanies, and the values being daily returns on their shares.Pandas `Series` are built on top of NumPy arrays and support manysimilar operations<jupyter_code>s * 100
np.abs(s)<jupyter_output><empty_output><jupyter_text>But `Series` provide more than NumPy arrays.Not only do they have some additional (statistically oriented) methods<jupyter_code>s.describe()<jupyter_output><empty_output><jupyter_text>But their indices are more flexible<jupyter_code>s.index = ['AMZN', 'AAPL', 'MSFT', 'GOOG']
s<jupyter_output><empty_output><jupyter_text>Viewed in this way, `Series` are like fast, efficient Pythondictionaries (with the restriction that the items in the dictionary allhave the same type---in this case, floats).In fact, you can use much of the same syntax as Python dictionaries<jupyter_code>s['AMZN']
s['AMZN'] = 0
s
'AAPL' in s<jupyter_output><empty_output><jupyter_text>DataFramesWhile a `Series` is a single column of data, a `DataFrame` is severalcolumns, one for each variable.In essence, a `DataFrame` in pandas is analogous to a (highly optimized)Excel spreadsheet.Thus, it is a powerful tool for representing and analyzing data that arenaturally organized into rows and columns, often with descriptiveindexes for individual rows and individual columns.```{only} htmlLet\'s look at an example that reads data from the CSV file`pandas/data/test_pwt.csv` that can be downloaded[here](https://lectures.quantecon.org/_downloads/pandas/data/test_pwt.csv).``````{only} latexLet\'s look at an example that reads data from the CSV file`pandas/data/test_pwt.csv` and can be downloaded[here](https://lectures.quantecon.org/_downloads/pandas/data/test_pwt.csv).```Here\'s the content of `test_pwt.csv````{code-block} none"country","country isocode","year","POP","XRAT","tcgdp","cc","cg""Argentina","ARG","2000","37335.653","0.9995","295072.21869","75.716805379","5.5788042896""Australia","AUS","2000","19053.186","1.72483","541804.6521","67.759025993","6.7200975332""India","IND","2000","1006300.297","44.9416","1728144.3748","64.575551328","14.072205773""Israel","ISR","2000","6114.57","4.07733","129253.89423","64.436450847","10.266688415""Malawi","MWI","2000","11801.505","59.543808333","5026.2217836","74.707624181","11.658954494""South Africa","ZAF","2000","45064.098","6.93983","227242.36949","72.718710427","5.7265463933""United States","USA","2000","282171.957","1","9898700","72.347054303","6.0324539789""Uruguay","URY","2000","3219.793","12.099591667","25255.961693","78.978740282","5.108067988"```Supposing you have this data saved as `test_pwt.csv` in the presentworking directory (type `%pwd` in Jupyter to see what this is), it canbe read in as follows:<jupyter_code>df = pd.read_csv('https://raw.githubusercontent.com/QuantEcon/lecture-source-py/master/source/_static/lecture_specific/pandas/data/test_pwt.csv')
type(df)
df<jupyter_output><empty_output><jupyter_text>We can select particular rows using standard Python array slicingnotation<jupyter_code>df[2:5]<jupyter_output><empty_output><jupyter_text>To select columns, we can pass a list containing the names of thedesired columns represented as strings<jupyter_code>df[['country', 'tcgdp']]<jupyter_output><empty_output><jupyter_text>To select both rows and columns using integers, the `iloc` attributeshould be used with the format `.iloc[rows, columns]`<jupyter_code>df.iloc[2:5, 0:4]<jupyter_output><empty_output><jupyter_text>To select rows and columns using a mixture of integers and labels, the`loc` attribute can be used in a similar way<jupyter_code>df.loc[df.index[2:5], ['country', 'tcgdp']]<jupyter_output><empty_output><jupyter_text>Let\'s imagine that we\'re only interested in population (`POP`) andtotal GDP (`tcgdp`).One way to strip the data frame `df` down to only these variables is tooverwrite the dataframe using the selection method described above<jupyter_code>df = df[['country', 'POP', 'tcgdp']]
df<jupyter_output><empty_output><jupyter_text>Here the index `0, 1,..., 7` is redundant because we can use the countrynames as an index.To do this, we set the index to be the `country` variable in thedataframe<jupyter_code>df = df.set_index('country')
df<jupyter_output><empty_output><jupyter_text>Let\'s give the columns slightly better names<jupyter_code>df.columns = 'population', 'total GDP'
df<jupyter_output><empty_output><jupyter_text>Population is in thousands, let\'s revert to single units<jupyter_code>df['population'] = df['population'] * 1e3
df<jupyter_output><empty_output><jupyter_text>Next, we\'re going to add a column showing real GDP per capita,multiplying by 1,000,000 as we go because total GDP is in millions<jupyter_code>df['GDP percap'] = df['total GDP'] * 1e6 / df['population']
df<jupyter_output><empty_output><jupyter_text>One of the nice things about pandas `DataFrame` and `Series` objects isthat they have methods for plotting and visualization that work throughMatplotlib.For example, we can easily generate a bar plot of GDP per capita<jupyter_code>ax = df['GDP percap'].plot(kind='bar')
ax.set_xlabel('country', fontsize=12)
ax.set_ylabel('GDP per capita', fontsize=12)
plt.show()<jupyter_output><empty_output><jupyter_text>At the moment the data frame is ordered alphabetically on thecountries---let\'s change it to GDP per capita<jupyter_code>df = df.sort_values(by='GDP percap', ascending=False)
df<jupyter_output><empty_output><jupyter_text>Plotting as before now yields<jupyter_code>ax = df['GDP percap'].plot(kind='bar')
ax.set_xlabel('country', fontsize=12)
ax.set_ylabel('GDP per capita', fontsize=12)
plt.show()<jupyter_output><empty_output><jupyter_text>On-Line Data SourcesPython makes it straightforward to query online databasesprogrammatically.An important database for economists is[FRED](https://research.stlouisfed.org/fred2/) --- a vast collectionof time series data maintained by the St. Louis Fed.For example, suppose that we are interested in the [unemploymentrate](https://research.stlouisfed.org/fred2/series/UNRATE).Via FRED, the entire series for the US civilian unemployment rate can bedownloaded directly by entering this URL into your browser (note thatthis requires an internet connection)```{code-block} nonehttps://research.stlouisfed.org/fred2/series/UNRATE/downloaddata/UNRATE.csv```(Equivalently, click here:https://research.stlouisfed.org/fred2/series/UNRATE/downloaddata/UNRATE.csv)This request returns a CSV file, which will be handled by your defaultapplication for this class of files.Alternatively, we can access the CSV file from within a Python program.This can be done with a variety of methods.We start with a relatively low-level method and then return to pandas. Accessing Data with requestsOne option is to use[requests](https://requests.readthedocs.io/en/master/), a standardPython library for requesting data over the Internet.To begin, try the following code on your computer<jupyter_code>r = requests.get('http://research.stlouisfed.org/fred2/series/UNRATE/downloaddata/UNRATE.csv')<jupyter_output><empty_output><jupyter_text>If there\'s no error message, then the call has succeeded.If you do get an error, then there are two likely causes1.  You are not connected to the Internet --- hopefully, this isn\'t    the case.2.  Your machine is accessing the Internet through a proxy server, and    Python isn\'t aware of this.In the second case, you can either-   switch to another machine-   solve your proxy problem by reading [the    documentation](https://requests.readthedocs.io/en/master/)Assuming that all is working, you can now proceed to use the `source`object returned by the call`requests.get('http://research.stlouisfed.org/fred2/series/UNRATE/downloaddata/UNRATE.csv')`<jupyter_code>url = 'http://research.stlouisfed.org/fred2/series/UNRATE/downloaddata/UNRATE.csv'
source = requests.get(url).content.decode().split("\n")
source[0]
source[1]
source[2]<jupyter_output><empty_output><jupyter_text>We could now write some additional code to parse this text and store itas an array.But this is unnecessary --- pandas\' `read_csv` function can handlethe task for us.We use `parse_dates=True` so that pandas recognizes our dates column,allowing for simple date filtering<jupyter_code>data = pd.read_csv(url, index_col=0, parse_dates=True)<jupyter_output><empty_output><jupyter_text>The data has been read into a pandas DataFrame called `data` that we cannow manipulate in the usual way<jupyter_code>type(data)
data.head()  # A useful method to get a quick look at a data frame
pd.set_option('precision', 1)
data.describe()  # Your output might differ slightly<jupyter_output><empty_output><jupyter_text>We can also plot the unemployment rate from 2006 to 2012 as follows<jupyter_code>ax = data['2006':'2012'].plot(title='US Unemployment Rate', legend=False)
ax.set_xlabel('year', fontsize=12)
ax.set_ylabel('%', fontsize=12)
plt.show()<jupyter_output><empty_output><jupyter_text>Note that pandas offers many other file type alternatives.Pandas has [a widevariety](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html)of top-level methods that we can use to read, excel, json, parquet orplug straight into a database server. Using pandas_datareader to Access DataThe maker of pandas has also authored a library called`pandas_datareader` that gives programmatic access to manydata sources straight from the Jupyter notebook.While some sources require an access key, many of the most important(e.g., FRED, [OECD](https://data.oecd.org/),[EUROSTAT](https://ec.europa.eu/eurostat/data/database) and the WorldBank) are free to use.For now let\'s work through one example of downloading and plotting data--- this time from the World Bank.The World Bank [collects and organizesdata](http://data.worldbank.org/indicator) on a huge range ofindicators.For example,[here\'s](http://data.worldbank.org/indicator/GC.DOD.TOTL.GD.ZS/countries)some data on government debt as a ratio to GDP.The next code example fetches the data for you and plots time series forthe US and Australia<jupyter_code>from pandas_datareader import wb

govt_debt = wb.download(indicator='GC.DOD.TOTL.GD.ZS', country=['US', 'AU'], start=2005, end=2016).stack().unstack(0)
ind = govt_debt.index.droplevel(-1)
govt_debt.index = ind
ax = govt_debt.plot(lw=2)
ax.set_xlabel('year', fontsize=12)
plt.title("Government Debt to GDP (%)")
plt.show()<jupyter_output>/home/choldgraf/anaconda/envs/dev/lib/python3.8/site-packages/pandas_datareader/compat/__init__.py:7: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.
  from pandas.util.testing import assert_frame_equal<jupyter_text>The[documentation](https://pandas-datareader.readthedocs.io/en/latest/index.html)provides more details on how to access various data sources. Exercises(pd_ex1)= Exercise 1With these imports:<jupyter_code>import datetime as dt 
from pandas_datareader import data<jupyter_output><empty_output><jupyter_text>Write a program to calculate the percentage price change over 2019 forthe following shares:<jupyter_code>ticker_list = {'INTC': 'Intel',
               'MSFT': 'Microsoft',
               'IBM': 'IBM',
               'BHP': 'BHP',
               'TM': 'Toyota',
               'AAPL': 'Apple',
               'AMZN': 'Amazon',
               'BA': 'Boeing',
               'QCOM': 'Qualcomm',
               'KO': 'Coca-Cola',
               'GOOG': 'Google',
               'SNE': 'Sony',
               'PTR': 'PetroChina'}<jupyter_output><empty_output><jupyter_text>Here\'s the first part of the program<jupyter_code>def read_data(ticker_list,
          start=dt.datetime(2019, 1, 2),
          end=dt.datetime(2019, 12, 31)): 
    """
    This function reads in closing price data from Yahoo 
    for each tick in the ticker_list.
    """
    ticker = pd.DataFrame()

    for tick in ticker_list:
        prices = data.DataReader(tick, 'yahoo', start, end)
        closing_prices = prices['Close']
        ticker[tick] = closing_prices

    return ticker

ticker = read_data(ticker_list)<jupyter_output><empty_output><jupyter_text>Complete the program to plot the result as a bar graph like this one:```{glue:} pandas_share_prices```(pd_ex2)= Exercise 2Using the method `read_data` introduced in{ref}`Exercise 1 `, write a program toobtain year-on-year percentage change for the following indices:<jupyter_code>indices_list = {'^GSPC': 'S&P 500',
               '^IXIC': 'NASDAQ',
               '^DJI': 'Dow Jones',
               '^N225': 'Nikkei'}<jupyter_output><empty_output><jupyter_text>Complete the program to show summary statistics and plot the result as atime series graph like this one:```{glue:} pandas_indices_pctchange``` Solutions Exercise 1There are a few ways to approach this problem using Pandas to calculatethe percentage change.First, you can extract the data and perform the calculation such as:<jupyter_code>p1 = ticker.iloc[0]    #Get the first set of prices as a Series
p2 = ticker.iloc[-1]   #Get the last set of prices as a Series
price_change = (p2 - p1) / p1 * 100
price_change<jupyter_output><empty_output><jupyter_text>Alternatively you can use an inbuilt method `pct_change` and configureit to perform the correct calculation using `periods` argument.<jupyter_code>change = ticker.pct_change(periods=len(ticker)-1, axis='rows')*100
price_change = change.iloc[-1]
price_change<jupyter_output><empty_output><jupyter_text>Then to plot the chart<jupyter_code>price_change.sort_values(inplace=True)
price_change = price_change.rename(index=ticker_list)
fig, ax = plt.subplots()
ax.set_xlabel('stock', fontsize=12)
ax.set_ylabel('percentage change in price', fontsize=12)
price_change.plot(kind='bar', ax=ax)
plt.show()
from myst_nb import glue
glue("pandas_share_prices", fig, display=False)<jupyter_output><empty_output><jupyter_text>Exercise 2Following the work you did in {ref}`Exercise 1 `, you can query the data using `read_data` by updating thestart and end dates accordingly.<jupyter_code>indices_data = read_data(
        indices_list,
        start=dt.datetime(1928, 1, 2),
        end=dt.datetime(2020, 12, 31)
)<jupyter_output><empty_output><jupyter_text>Then, extract the first and last set of prices per year as DataFramesand calculate the yearly returns such as:<jupyter_code>yearly_returns = pd.DataFrame()

for index, name in indices_list.items():
    p1 = indices_data.groupby(indices_data.index.year)[index].first()  # Get the first set of returns as a DataFrame
    p2 = indices_data.groupby(indices_data.index.year)[index].last()   # Get the last set of returns as a DataFrame
    returns = (p2 - p1) / p1
    yearly_returns[name] = returns

yearly_returns<jupyter_output><empty_output><jupyter_text>Next, you can obtain summary statistics by using the method `describe`.<jupyter_code>yearly_returns.describe()<jupyter_output><empty_output><jupyter_text>Then, to plot the chart<jupyter_code>fig, axes = plt.subplots(2, 2)

for iter_, ax in enumerate(axes.flatten()):            # Flatten 2-D array to 1-D array
    index_name = yearly_returns.columns[iter_]         # Get index name per iteration
    ax.plot(yearly_returns[index_name])                # Plot pct change of yearly returns per index
    ax.set_ylabel("percent change", fontsize = 12)
    ax.set_title(index_name)

plt.tight_layout() 
from myst_nb import glue
glue("pandas_indices_pctchange", fig, display=False)<jupyter_output><empty_output>