<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Intorduction to Pandas - Pan(el)-da(ta)-s
Laszlo Tetenyi
Step1:  Survey of Consumer Finances (SCF) 2013
Load and explore data from the SCF website - note that this data cannot be loaded by 2007- Excel due to its size
Step2:  Lets have a quick look at the table
Step3:  We can select particular rows of the table using standard Python array slicing notation
Step4:  table is a DataFrame - a multi-dimensional equivalent of Series (another Pandas object), as it has multiple columns, so you can think of it as a matrix, where columns can be accessed by their 'names'. In fact, many operations can be performed on them (coming from numpy)
Step5:  But they know more than that - they have several built-in statistics 
Step6:  As an example try to access normalized income and net-worth variables.
Step7:  There are way too many variables in there - try to search for the proper column names
Step8:  Get the "mean" (non -weighted) income and minimal net worth
Step9:  That is, there is one person with a net worth of -227 million \$ ! Suppose we do not want our analysis to depend on these extremely low values and we would like to trim our dataframe. As a first step, create a new dataframe that only contains the variables of interest (and the id of each observation)
Step10:  Rename the columns
Step11:  Try to get a general picture of what would be a "good" trimming value of net worth by plotting an estimated kernel density
Step12:  Let's see how many observations we eliminate if we exclude everyone below -1 million \$
Step13:  But how many households are in this category?
Step14:  Pivoting
Note that the data has a nice panel structure which we so far did not exploit - each household has multiple observations. Lets use pivoting so that our dataframe reflects that. First get the index of our dataframe
Step15:  We simply have the index of each observation. As a first step, replace these indeces by the household identifier and group income levels in each observation
Step16:  If instead we are interested in both income and net worth grouped by observations then
Step17:  Use stacking to transform the data into a panel structure we are familiar with (and unstacking to go back to cross-section)
Step18:  Using the panel structure it is even easier to see the number of households that had fewer
than -1 million \$ net worth
Step19:  Pandas even have their own data-structure for panel data - for it, we need to create dataframes as inputs
Step20:  but this is not as useful - unfortunately the panel part of the package has been neglected. Very few functions are available. Now as a last exercise, save our dataFrame to a csv file
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import requests, zipfile, io # So that we can download and unzip files
Explanation: Intorduction to Pandas - Pan(el)-da(ta)-s
Laszlo Tetenyi
End of explanation
r = requests.get('http://www.federalreserve.gov/econresdata/scf/files/scfp2013excel.zip')
z = zipfile.ZipFile(io.BytesIO(r.content))
f = z.open('SCFP2013.xlsx')
table = pd.read_excel(f, sheetname='SCFP2013')
Explanation: Survey of Consumer Finances (SCF) 2013
Load and explore data from the SCF website - note that this data cannot be loaded by 2007- Excel due to its size
End of explanation
table.head()
Explanation: Lets have a quick look at the table
End of explanation
table[0:5]
Explanation: We can select particular rows of the table using standard Python array slicing notation
End of explanation
table.max().max()
Explanation: table is a DataFrame - a multi-dimensional equivalent of Series (another Pandas object), as it has multiple columns, so you can think of it as a matrix, where columns can be accessed by their 'names'. In fact, many operations can be performed on them (coming from numpy):
End of explanation
table.describe()
Explanation: But they know more than that - they have several built-in statistics :
End of explanation
table.dtypes[0:5]
table.dtypes.shape
Explanation: As an example try to access normalized income and net-worth variables.
End of explanation
[col for col in table.columns if 'NETWOR'  in col]
[col for col in table.columns if 'INC'  in col]
income = table['NORMINC']
net_worth = table['NETWORTH']
Explanation: There are way too many variables in there - try to search for the proper column names
End of explanation
income.mean()
net_worth.min()
Explanation: Get the "mean" (non -weighted) income and minimal net worth
End of explanation
keep = ['YY1','Y1', 'NORMINC', 'NETWORTH']
data = table[keep]
data.head()
Explanation: That is, there is one person with a net worth of -227 million \$ ! Suppose we do not want our analysis to depend on these extremely low values and we would like to trim our dataframe. As a first step, create a new dataframe that only contains the variables of interest (and the id of each observation)
End of explanation
data.columns ='Household', 'Observation' , 'Income', 'Net Worth'
data.head()
Explanation: Rename the columns:
End of explanation
data['Net Worth'].plot(kind='density')
plt.show()
Explanation: Try to get a general picture of what would be a "good" trimming value of net worth by plotting an estimated kernel density
End of explanation
data_trimmed = data[data['Net Worth'] > -1000000]
data.shape[0] - data_trimmed.shape[0] 
Explanation: Let's see how many observations we eliminate if we exclude everyone below -1 million \$
End of explanation
data[data['Net Worth'] < -1000000]
data_trimmed['Net Worth'].plot(kind='density')
plt.show()
Explanation: But how many households are in this category?
End of explanation
data.index
Explanation: Pivoting
Note that the data has a nice panel structure which we so far did not exploit - each household has multiple observations. Lets use pivoting so that our dataframe reflects that. First get the index of our dataframe:
End of explanation
new_observations = data.loc[:,'Observation'] - 10 * data.loc[:,'Household']
data.loc[:,'Observation'] = new_observations
data[0:10]
# Normally, you should not do this - instead use assign
# Reload the data
data = table[keep]
data.columns ='Household', 'Observation' , 'Income', 'Net Worth'
data = data.assign(Observations = (data['Observation'] - 10.0 * data['Household']).astype(int))
del data['Observation'] # delete the old column
data = data.rename(columns = {'Observations':'Observation'}) # rename the column
data = data[['Household', 'Observation' , 'Income', 'Net Worth']] # reinsert the column
data.head()
p = data.pivot(index = 'Household' , columns = 'Observation' , values = 'Income' )
p.head()
Explanation: We simply have the index of each observation. As a first step, replace these indeces by the household identifier and group income levels in each observation:
End of explanation
p = data.pivot(index='Household', columns='Observation')
p.head()
Explanation: If instead we are interested in both income and net worth grouped by observations then:
End of explanation
panel_data = p.stack()
panel_data.head()
Explanation: Use stacking to transform the data into a panel structure we are familiar with (and unstacking to go back to cross-section):
End of explanation
panel_data[panel_data['Net Worth'] < -1000000]
Explanation: Using the panel structure it is even easier to see the number of households that had fewer
than -1 million \$ net worth
End of explanation
p = data.pivot(index='Observation', columns='Household')
pdata = {'Observation 1' : pd.DataFrame(p.ix[1,:]),
        'Observation 2' : pd.DataFrame(p.ix[2,:]),
        'Observation 3' : pd.DataFrame(p.ix[3,:]),
        'Observation 4' : pd.DataFrame(p.ix[4,:]),
        'Observation 5' : pd.DataFrame(p.ix[5,:])}
pdata = pd.Panel(pdata)
pdata
Explanation: Pandas even have their own data-structure for panel data - for it, we need to create dataframes as inputs
End of explanation
data.to_excel('SCF_2013_inc_netw.xlsx')
Explanation: but this is not as useful - unfortunately the panel part of the package has been neglected. Very few functions are available. Now as a last exercise, save our dataFrame to a csv file
End of explanation
<END_TASK>