When working on real world data tasks, you'll quickly realize that a large portion of your time is spent manipulating raw data into a form that you can actually work with, a process often called data munging or data wrangling. Different programming langauges have different methods and packages to handle this task, with varying degrees of ease, and luckily for us, Python has an excellent one called Pandas which we will be using in this exercise.
In [1]:
import numpy as np
import pandas as pd
The Data Frame is perhaps the most important object in Pandas and Data Science in Python, providing a plethora of functions for common data tasks. Using only Pandas, do the following exercises.
1 - Download the free1.csv from the R Data Repository and save it to the same directory as this notebook. Then import into your environment as a Data Frame. Now read free2.csv directly into a Data Frame from the URL.
In [2]:
# Import from file
free1 = pd.read_csv('free1.csv')
# Import from URL
free2 = pd.read_csv('https://vincentarelbundock.github.io/Rdatasets/csv/Zelig/free2.csv')
In [3]:
print(free1.head())
print(free2.head())
2 - Combine your free1 Data Frame with free2 into a single Data Frame, named free_data, and print the first few rows to verify that it worked correctly. From here on out, this combined Data Frame is what we will be working with.
In [4]:
free_data = pd.concat([free1, free2], ignore_index=True)
3 - Print the last 10 rows.
In [5]:
free_data.tail(10)
Out[5]:
4 - Rename the first column (currently unamed), to id. Print the column names to verify that it worked correctly.
In [6]:
new_col = np.array(free_data.columns)
new_col[0] = 'id'
free_data.columns = new_col
print(free_data.columns)
# solution: free_date.rename(columns={'Unnamed: 0': 'id}, inplace=True)
5 - What are the number of rows and columns of the Data Frame?
In [7]:
free_data.shape
Out[7]:
6 - What are the data types of each column? Can quantities like the mean be calculated for each columm? If not, which one(s) and why?
In [8]:
free_data.dtypes
Out[8]:
7 - Print out the first 5 rows of the country column.
In [9]:
free_data['country'].head()
Out[9]:
8 - How many unique values are in the country column?
In [10]:
len(free_data['country'].unique())
Out[10]:
9 - Print out the number of occurences of each unique value in the country column.
In [11]:
free_data.groupby('country')['id'].count()
# solution: free_data.country.value_count()
Out[11]:
10 - Summarize the dataframe.
In [12]:
free_data.describe()
Out[12]:
11 - Were all columns included in the summary? If not, print the summary again, forcing this column to appear in the result.
In [13]:
#free_data['country'] = free_data['country'].astype('category')
In [14]:
free_data.describe(include='all')
Out[14]:
12 - Print rows 100 to 110 of the free1 Data Frame.
In [15]:
free1.iloc[99:110]
Out[15]:
13 - Print rows 100 to 110 of only the first 3 columns in free1 using only indices.
In [16]:
free1.iloc[99:110, 0:3]
Out[16]:
14 - Create and print a list containing the mean and the value counts of each column in the data frame except the country column.
In [17]:
# create a list containing all columns
col = list(free_data.columns)
# exclude the country column
col = col[0:4] + col[5:]
#calculate mean and count and put them in a list
[list(free_data[col].mean()), list(free_data[col].count())]
# solution:
# results = []
# for col in free_data.drop('country', axis=1).columns:
# results.append((free_data[col].mean(), free_data[col].value_counts()))
Out[17]:
15 - Create a Data Frame, called demographics, using only the columns sex, age, and educ from the free1 Data Frame. Also create a Data Frame called scores, using only the columns v1, v2, v3, v4, v5, v6 from the free1 Data Frame
In [18]:
demographics = free1[['sex', 'age', 'educ']]
print(demographics.head())
scores = free1[['v1', 'v2', 'v3', 'v4', 'v5', 'v6']]
print(scores.head())
16 - Loop through each row in scores and grab the largest value, in the v_ columns, found in each row and store your results in two lists containing the value and column name it came from. For example, row 0 is
{'v1': 4, 'v2': 3, 'v3': 3, 'v4': 5, 'v5': 3, 'v6': 4}
the values
('v4', 5)
should be added to your two lists.
We can do this in two ways: vectorized or using a for loop.
In the vectorized version we calculate max and argmax along the columns axis and put the values obtained in two lists:
In [19]:
value = list(scores.max(axis=1))
col_name = list(scores.apply(np.argmax, axis=1))
# print the first ten values for checking
print(value[0:10])
print(col_name[0:10])
In the for loop version we iterate through each row and append the max and argmax for every row in two lists:
In [20]:
value = []
col_name = []
for index, row in scores.iterrows():
value.append(np.max(row))
col_name.append(np.argmax(row))
# print the first ten values for checking
print(value[0:10])
print(col_name[0:10])
17 - Create a new Data Frame with columns named cat and score from your results in part (16), for the column with the largest score and the actual score respectively.
In [21]:
cat_scores = pd.DataFrame({'cat':col_name,'score':value})
cat_scores.head()
Out[21]:
18 - Using the Data Frame created in part (17), print the frequency of each column being the max score.
In [22]:
cat_scores.groupby('cat').count() / cat_scores.shape[0]
Out[22]:
Most of the time, we'll want to rearrange the data a bit, include only certain values in our analysis, or put the data into useful groups. Pandas provides syntax and many functions to do this.
Using only Pandas, do the following exercises.
1 - Using the free1.csv downloaded above, import it as a Data Frame named free_data, rename the first column to id, and print the first few rows.
In [23]:
free_data = pd.read_csv('free1.csv')
col = list(free_data.columns)
col[0] = 'id'
free_data.columns = col
free_data.head()
Out[23]:
2 - Sort free_data by country, educ, and then by age in decending order, modifying the original Data Frame.
In [24]:
free_data.sort_values(['country', 'educ', 'age'], ascending=[True, True, False], inplace=True)
free_data.head()
Out[24]:
3 - Create a new Data Frame called uni containing only rows from free_data which indicate that the person attended university or graduate school. Print the value counts for each country.
This is the dictionary for the educ column:
In [25]:
# I guess graduate school means high school, not certain though...
uni = free_data[free_data['educ'] >= 5]
uni.groupby('country')['id'].count()
Out[25]:
4 - Create a list of three Data Frames for those who are less than 25 years old, between 25 and 50 years old, and older than 50.
In [26]:
ages_df_list = [free_data[free_data['age'] < 25],
free_data[(free_data['age'] >= 25) & (free_data['age'] <= 50)],
free_data[free_data['age'] > 50]]
ages_df_list[1]
Out[26]:
5 - Using a for loop, create a list of 3 Data Frames each containing only one of the 3 countries.
In [27]:
countries_df_list = []
for country in free_data['country'].unique():
countries_df_list.append(free_data[free_data['country'] == country])
countries_df_list[2]
Out[27]:
6 - Create a list of age categories, labled 0, 1, and 2 for each row for the three groups made in part (4). Attach this list to the free_data dataframe as a column named age_cat.
In [28]:
# create the list of categories
age_cat = []
for i in range(3):
# concatenate a list of i's of the same length of the i-th dataframe created in part 4
age_cat = age_cat + [i] * ages_df_list[i].shape[0]
# sort values by age
free_data.sort_values('age', ascending=True, inplace=True)
# add the list as a new column
free_data.loc[pd.notnull(free_data['age']), 'age_cat'] = age_cat
free_data.head()
Out[28]:
7 - Print the mean for all columns for each age_cat using groupby.
In [29]:
free_data.groupby('age_cat').mean()
Out[29]:
8 - Print the mean education for each age_cat using groupby.
In [30]:
free_data.groupby('age_cat')['educ'].mean()
Out[30]:
9 - Print summary statistics for each column for those with an education greater than or equal to 5, grouped by age_cat.
In [31]:
free_data[free_data['educ'] >= 5].groupby('age_cat').describe()
Out[31]:
10 - Which of the vignette has the largest mean score for each education level? What about the median?
In this survey six vignettes were showed to different people and they were asked to determine the degree of freedom of the situation depicted.
These are the possible votes:
and these are the vignettes:
Kay does not like many of the government's policies. She frequently publishes her opinion in newspapers, criticizing decisions by officials and calling for change. She sees little reason these actions could lead to government reprisal.
Michael disagrees with many of the government's policies. Though he knows criticism is frowned upon, he doesn't believe the government would punish someone for expressing critical views. He makes his opinion known on most issues without regard to who is listening.
Bob has political views at odds with the government. He has heard of people occasionally being arrested for speaking out against the government, and government leaders sometimes make political speeches condemning those who criticize. He sometimes writes letters to newspapers about politics, but he is careful not to use his real name.
Connie does not like the government's stance on many issues. She has a friend who was arrested for being too openly critical of governmental leaders, and so she avoids voicing her opinions in public places.
Vito disagrees with many of the government's policies, and is very careful about whom he says this to, reserving his real opinions for family and close friends only. He knows several men who have been taken away by government officials for saying negative things in public.
Sonny lives in fear of being harassed for his political views. Everyone he knows who has spoken out against the government has been arrested or taken away. He never says a word about anything the government does, not even when he is at home alone with his family.
It seems (reasonably enough) that the sixth vignette has the highest mean and median score among the situations proposed in all but two cases:
In [34]:
print(free_data.groupby('educ')['v1', 'v2', 'v3', 'v4', 'v5', 'v6'].mean().apply(np.argmax, axis=1))
print(free_data.groupby('educ')['v1', 'v2', 'v3', 'v4', 'v5', 'v6'].median().apply(np.argmax, axis=1))
12 - Which country would you say has the most freedom of speech? Be sure to justify your answer quantitatively.
I would say Oceana has the most freedom of speech, because it has the higher mean for the vignettes, expecially vignettes 4 and 5.
It should be noted also that the vignette 5 has a surprisingly low mean in the other two countries, more so if compared to vignette 4 or 3.
In [33]:
free_data.groupby('country')['v1', 'v2', 'v3', 'v4', 'v5', 'v6', 'y'].describe()
Out[33]:
13 - Is there a difference of opinion between men and women regarding freedom of speech? If any, does this difference manifest itself accross the different countries? Accross education levels? Be sure to justify your answers quantiatively.
We can say that women think of themselves as slightly more free than men:
In [34]:
print(free_data.groupby('sex')['y'].mean()) # men thing of themselves as slightly more free
and that this difference is greater in Eastasia and almost none in Eurasia:
In [35]:
print(free_data.groupby(['sex', 'country'])['y'].mean().unstack())
The difference is also present across education levels: curiously uneducated women and women with secondary school degree or higher feel freer than men but women with an intermediate education feel less free than men. We can also observe a tendency, aside from uneducated people, for women to feel freer the more they are educated when compared to men.
Lastly, it's pretty remarkable that the most educated men does not feel free at all, but this can be due to the small sample size.
In [36]:
print(free_data.groupby(['sex', 'educ'])['y'].mean().unstack())
Apply()Much of the power of Data Sciences comes from the ability to join together datasets from very different sources. One could be interested in seeing if there is a relationship between housing prices and prevalence of infectious disease in a given ZIP code for example. This task is often referred to as a merge or join.
Every Pandas Data Frame has an index. Indices in Pandas are a bit of a complex topic, but for the time being consider them to be a unique identifier for each row in a Data Frame. When performing joins and manipulating Data Frames, it is important to remember that your task may require the creation or change of the Data Frame's index. For more extensive reading on this topic, consult the Pandas Documentation.
And lastly, if you are coming from a programming background like C/C++ or Java, you are likely very accustomed to operating on arrays and lists using for loops. Often this is how you will want to work with Data Frames in Python, but Pandas also provides functionality for functional like programming by utilizing the Apply() function. This is similar to the apply family of functions in R and the Map() and related functions in Lisp. Making use of Apply() in Python can make your code more concise, readable, and faster when performing operations on an entire Data Frame.
Using on Pandas, perform the following exercises.
1 - Using the free1.csv downloaded above, import it as a Data Frame named free_data and rename the first column to id.
In [37]:
free_data = pd.read_csv('free1.csv')
col = list(free_data.columns)
col[0] = 'id'
free_data.columns = col
free_data.head()
Out[37]:
2 - Create a dataframe named free_sub, consisting of the id, country, and y columns from free_data.
In [38]:
free_sub = free_data[['id', 'country', 'y']]
free_sub.head()
Out[38]:
3 - Create a new Data Frame called ed_level, consisting of the id and three categories of education levels, labeled high, med, and low, for ranges of your choosing. Do this using a for loop.
In [39]:
# create a dataframe with one column
ed_level = pd.DataFrame(free_data['id'])
# add a blank column
ed_level['educ_cat'] = None
# dictionary of labels
educ_cat = {1:'low', 2:'low', 3:'med', 4:'med', 5:'high', 6:'high', 7:'high'}
# for every row in the dataframe if the corresponding educ column in the free data is not missing
# use the dictionary to set the corresponding label
for i in range(0, len(ed_level)):
if pd.notnull(free_data.loc[i, 'educ']):
ed_level.loc[i, 'educ_cat'] = educ_cat[free_data.loc[i, 'educ']]
ed_level.head(5)
Out[39]:
4 - Merge free_sub and ed_level together. Which column should the merge be performed on? Do this using both the concat() and merge() functions.
The merge should be performed on the id column, which is used implicitly by the merge function.
The concat function returns a DataFrame that duplicates all the rows (and indexes) setting to NaN the missing values for each of the original datasets.
The merge function instead performs an inner join returning the rows from the first dataset enriched with the values of the second.
In [40]:
# Solution: the id is set as an index for both columns
pd.concat([free_sub, ed_level])
Out[40]:
In [41]:
pd.merge(free_sub, ed_level)
Out[41]:
5 - Use the append() function to join together free_sub and ed_level. Are the results the same as in part (4)? If not, how could you reproduce the result append() by using concat() or merge()?
In [42]:
free_sub.append(ed_level)
Out[42]:
The append function produces the same results of concat: it returns a DataFrame with duplicated rows and indices setting to NaN the missing values:
6 - Use numpy to generate two lists 100 random floats labeled y1 and y2. Now create a sequence of integers on the range 0-100 labeled x1 and a sequence of integers on the range 50-150 labeled x2. Create two DataFrames, dat1 and dat2 consisting of x1 and y1, and x2 and y2 respectively, but having labels x, y1, and x, y2. Use merge() to join these two Data Frames together, on x, using both an inner and outer join. What is the difference between the two joins?
In [39]:
np.random.seed(0)
# random floats
y1 = np.random.ranf(100)
y2 = np.random.ranf(100)
# random integers
x1 = np.arange(0, 100)
x2 = np.arange(50, 150)
# create the dataframes
dat1 = pd.DataFrame({'x':x1, 'y1':y1})
dat2 = pd.DataFrame({'x':x2, 'y2':y2})
The inner join mantains only the rows of the first dataset which have a counterpart in the second dataset, eventually duplicating them if there is more than one match. This leads, in general, to fewer rows returned:
In [40]:
pd.merge(dat1, dat2)
Out[40]:
The outer join instead returns all the rows from both the datasets with the eventual matches between them (again duplicating the rows if there are multiple matches):
In [41]:
pd.merge(dat1, dat2, how='outer')
Out[41]:
7 - Create a Data Frame, called scores consising of only the y and v_ columns from free_data.
In [46]:
scores = free_data[['y', 'v1', 'v2', 'v3', 'v4', 'v5', 'v6']]
8 - Using a for loop(s), compute the sum and mean for each column in scores.
In [47]:
# initialize an array of zeros to store the results
sums = np.zeros(7)
# calculate the sum with a for loop
for i in range(len(scores)):
sums += scores.iloc[i]
# calculate the mean out the loop, dividing by the total number of scores
means = sums / len(scores)
print(sums)
print(means)
9 - Using the apply() function, compute the sum and mean for each column in scores.
In [48]:
scores.apply(lambda x: [np.sum(x), np.mean(x)])
Out[48]:
10 - Using the apply() function, label each column in scores as either high, med, or low by first computing the mean for each column and assigning the categories at values of your choosing. Do this by writing a single function you can call with apply().
In [49]:
scores.apply(lambda x: 'low' if np.mean(x) <= 3 else 'med' if np.mean(x) <= 4 else 'high')
Out[49]:
In many situations you may not know the relationship between two variables but you do know that there ought to be one. Take for example the daily price of beef and grain. It is reasonable to assume that there exists some, perhaps even a causal, relationship between these two, but due to the complexity of the phenomenon, and the vast number of underlying latent variables involved (fuel price, politics, famine, etc...), you likely have little hope to uncover such a relationship in a reasonable amount of time. However, you do know that these two variables are related in time and may exibit some pattern that repeats itself in time. Identifying these types of patterns is called Time Series Analysis and sequencing your data such that each data point is represented as a unique point in time is called a Time Series. The canonical example of a Time Series is, of course, stock market data which is what we will be using for this exercise
Do the following exercises.
1 - Create a start and end datetime object, starting at a date of your choosing and ending today.
In [50]:
start = pd.datetime(2016, 1, 1)
end = pd.datetime.today()
print(start, end)
2 - For three stocks of your choosing, put their symbols into a list and use pandas to retrieve their data from google for the time frame you created in part (1). Print the results.
In [51]:
import pandas_datareader.data as web
It turns out that the DataReader method return a Panel object if called passing a list of stocks, so we can transform it in a DataFrame and unstack it to obtain the derired result:
In [52]:
stock_code = ['GOOG', 'FB', 'MSFT']
stock_panel = web.DataReader(stock_code, start=start, end=end, data_source='google')
stock_prices = stock_panel.to_frame().unstack(level=1)
stock_prices
Out[52]:
3 - Create a Data Frame called stock_open for the open prices of the stocks you retrieved in part (2). Print the first few rows.
In [53]:
stock_open = stock_prices['Open']
stock_open.head(10)
Out[53]:
4 - Compute the total, average, and maximum price for each stock weekly.
If by total we mean sum (it doesn't make too much sense to me though...):
In [54]:
stock_open.resample('W').aggregate([sum,np.mean, max])
Out[54]:
Instead, if we mean last opening price of the week:
In [55]:
# calculate last value for each week
last = stock_open.resample('W').last()
# add a level to the column index for merging this data with mean and max dataframe created below
last.columns = pd.MultiIndex.from_product([stock_open.columns, ['last']])
# merge mean and max dataframe and last dataframe using index, then stack and unstack to have the columns in the right place
pd.merge(stock_open.resample('W').aggregate([np.mean, max]),
last,
left_index=True,
right_index=True).stack().unstack()
Out[55]:
5 - For each stock, return the weeks for which the opening stock price was greater than the yearly daily average.
In [56]:
# create a dataframe containing the opening price for each week minus the mean over the year
# I'm appending start year resampling and en of year resampling in order to have all the weeks in the years considered
stock_difference = stock_open.resample('W').first() - stock_open.resample('AS').mean().append(stock_open.resample('A').mean()).resample('W').ffill()
stock_difference.head()
Out[56]:
In [57]:
# print values for every stock:
for stock in stock_code:
print('Weeks with opening price above yearly average for {}:\n{}\n'.format(stock,
# format the index where stock difference is greater than 0 and join the array using newline
'\n'.join(stock_difference[stock_difference[stock] > 0].index.strftime('Week %W of %Y'))))