# Import pandas so that we can use it. The accepted short name for pandas is pd.  
import pandas as pd

# 1. Load the dataframe.
# 2. First four rows.
ind = pd.read_excel('industrial_production.xlsx', sheet_name='Quarterly')
ind.head(4)

# 2. Last four rows.

ind.tail(4)

ind.head(4)
ind.tail(4)

# 3. Set the index to 'DATE'.

ind = ind.set_index('DATE')

# 4. Change the column names (method 1). 

# This is the slick way. 
# Grab the column names from the index. 
# Make a list of the new column names.
# Zip them together (check the documentation for zip) and then create a dict. 
# The columns have to be in the correct order with respect to new_names.

old_names = ind.columns.to_list()
new_names = ['consumer', 'consumer durables', 'crude oil', 'mining', 'elec and gas', 'cars', 'manuf', 'ice cream', 'steel']
names = dict(zip(old_names, new_names))
ind = ind.rename(columns=names)

ind.head(2)

# 4. Change the column names (method 2). 

# If you know for sure that that the columns are in the correct order, you can also do this.
# In method 1, you can look at `names` and make sure the new and old names line up before changing the columns names. 

# new_names = ['consumer', 'consumer durables', 'crude oil', 'mining', 'elec and gas', 'cars', 'manuf', 'ice cream', 'steel']
# ind.columns = new_names

# 4. Change the column names (method 3).

# The most tedious, but robust way. No matter what order the columns are in, this will replace the names properly. 

# names = {'IPB51000SQ':'consumer', 'IPB51100SQ':'consumer durables', 'IPG211111CSQ':'crude oil', 
#         'IPG21SQ':'mining', 'IPG2211A2SQ':'elec and gas', 'IPG3361T3SQ':'cars', 'IPGMFSQ':'manuf', 
#         'IPN31152NQ':'ice cream', 'IPN3311A2RNQ':'steel'}
# ind = ind.rename(columns=names)

# 5. dtypes

# Each column is made up of floats. (float64 is the same as float)
ind.dtypes

consumer             float64
consumer durables    float64
crude oil            float64
mining               float64
elec and gas         float64
cars                 float64
manuf                float64
ice cream            float64
steel                float64
dtype: object

# 6. shape

# This gives us the number of rows and columns in a tuple.
ind.shape

(404, 9)

#7. (column) index 

# An index object. The column names. 
ind.columns

Index(['consumer', 'consumer durables', 'crude oil', 'mining', 'elec and gas',
       'cars', 'manuf', 'ice cream', 'steel'],
      dtype='object')

# 8. (row) index 

# Another index object. A different type (a DatetimeIndex, to hold dates). 
ind.index

DatetimeIndex(['1919-01-01', '1919-04-01', '1919-07-01', '1919-10-01',
               '1920-01-01', '1920-04-01', '1920-07-01', '1920-10-01',
               '1921-01-01', '1921-04-01',
               ...
               '2017-07-01', '2017-10-01', '2018-01-01', '2018-04-01',
               '2018-07-01', '2018-10-01', '2019-01-01', '2019-04-01',
               '2019-07-01', '2019-10-01'],
              dtype='datetime64[ns]', name='DATE', length=404, freq=None)

# 9. sample()

# A random sample of rows.
ind.sample(5)

# 10. describe()

# Summary statistics. If a column is not made up of numbers, it will not appear here. 
ind.describe()

# 2. Reading from the internet.

url = 'http://dx.doi.org/10.1787/888932937035'
pisa = pd.read_excel(url,
                     skiprows=18,             # skip the first 18 rows
                     skipfooter=7,            # skip the last 7
                     usecols=[0,1,9,13],      # select columns of interest
                     )

# Rather than use the 'usecols' argument, you could have loaded all the columns and dropped the ones you do not want.
# Notice that the first row is a bunch of text. That will cause some problems later...

pisa.head()

# 3. Drop NaNs.

pisa2 = pisa.dropna()
pisa2.shape

(66, 4)

# 4. Set the index.

pisa2 = pisa2.set_index('Unnamed: 0')
pisa2.head(2)

# 5. US relative to the average. 
# The US is pretty average...

print(pisa2.loc['United States']/pisa2.loc['OECD average'])

Mathematics       0.974335
Reading            1.00225
Science           0.992517
dtype: object

# 6. Convert types/compute correlations
# Strings! 

pisa2.dtypes

Mathematics       object
Reading           object
Science           object
dtype: object

# New column names
pisa2.columns = ['math', 'read', 'sci']  

# There are several ways to convert strings to numeric values. This is one of them.
pisa2=pisa2[['math', 'read', 'sci']].apply(pd.to_numeric)  
pisa2.dtypes

math    float64
read    float64
sci     float64
dtype: object

# Now we are in good shape. What does .corr() do?
# What is the return type of .corr()?

pisa_corrs = pisa2.corr()
print(pisa_corrs)
print(type(pisa_corrs))

# Now save the DataFrame of results to a file. 
pisa_corrs.to_excel('pisa_corrs.xlsx')

          math      read       sci
math  1.000000  0.959806  0.972131
read  0.959806  1.000000  0.978559
sci   0.972131  0.978559  1.000000
<class 'pandas.core.frame.DataFrame'>