<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <h1 id="tocheading">Table of Contents</h1>
<div id="toc"></div>
Step1:  Getting and Knowing your Data
Task
Step2:  Task
Step3:  Task
Step4:  Groupby
Task
Step5:  Task
Step6:  Task
Step7:  Task
Step8:  Task
Step9:  Task
Step10:  Task
Step11:  Task
Step12:  Concatenate, Merge & Join
Task
Step13:  Task
Step14:  Task
Step15:  Apply (interspersed)
Task
Step16:  Consider the following DataFrames for the next exercises
Step17:  Task
Step18:  Task
Step19:  Let's consider another data set to do some more Merge, Join & Concatenate exerciseses
Step20:  Task
Step21:  Task
Step22:  Task
Step23:  Task
Step24:  Task
Step25:  Transform
The transform method returns an object that is indexed the same (same size) as the one being grouped.
Task
Step26:  Task
Step27:  Task
Step28:  Task
Step29:  Task
Step30:  Pivot
Task
Step31:  Combine DataFrames
Task
Step32:  GroupBy Titanic data
Load the dataset in titanic.xls. It contains data on all the passengers that travelled on the Titanic.
Task
Step33:  Let's plot the number of survivors grouped by sex and passenger class.
Step34:  Task
Step35:  Task
<ASSISTANT_TASK:>
Python Code:
%%javascript
$.getScript('misc/kmahelona_ipython_notebook_toc.js')
Explanation: <h1 id="tocheading">Table of Contents</h1>
<div id="toc"></div>
End of explanation
fn = r"data/drinks.csv" 
# Answer:
df = pd.read_csv(fn, sep=",")
Explanation: Getting and Knowing your Data
Task: load the following file as a data frame
End of explanation
# Answer:
df.head(10)
Explanation: Task: See the first 10 entries
End of explanation
# Answer
df.sort_values("total_litres_of_pure_alcohol", ascending=False).head()
Explanation: Task: Which country has the highest alcohol consumption (total litres of pure alcohol)?
End of explanation
# Answer
# df.groupby("continent").beer_servings.mean()
# or 
df.groupby("continent")["beer_servings"].mean()
Explanation: Groupby
Task: Which continent drinks most beer on average?
End of explanation
# Answer:
df["continent"].unique()
# or 
# set(df["continent"].tolist())
Explanation: Task: List all unique continents.
End of explanation
# Answer:
df[df["continent"].isnull()]
Explanation: Task: Which countries have missing values in the continent column?
End of explanation
# Answer:
df.loc[df["continent"].isnull(), "continent"] = "NAs"
Explanation: Task: Set "the" missing continent with a name of your choice.
End of explanation
# Answer:
# df.groupby('continent').wine_servings.describe()
# or
df.groupby('continent')["wine_servings"].describe()
Explanation: Task: For each continent print "the" statistics (summary stats using "df.describe()") for wine consumption.
End of explanation
# Answer:
df.groupby('continent').median()
Explanation: Task: Print the median alcoohol consumption per continent for every column
End of explanation
# Answer:
df.groupby('continent').spirit_servings.agg(['mean', 'min', 'max'])
Explanation: Task: Print the mean, min and max values for spirit consumption.
End of explanation
# Answer:
df.groupby("continent").boxplot(figsize=(12, 9), rot=90)
Explanation: Task: GroupBy Continent and create a Boxplot. (Hint: using e.g. figsize=(12, 9), rot=90 might help with legibility.)
End of explanation
# Answer:
cars1 = pd.read_csv("data/cars1.csv")
cars2 = pd.read_csv("data/cars2.csv")
Explanation: Concatenate, Merge & Join
Task: Import the first dataset cars1 and cars2. Assign each to a to a variable called cars1 and cars2.
End of explanation
# Answer:
cars1 = cars1.loc[:, "mpg":"car"]
Explanation: Task: It seems our first dataset has some unnamed blank columns, fix cars1.
End of explanation
# Answer:
cars = cars1.append(cars2)
# or
cars_ = pd.concat([cars1, cars2], axis=0)
# check for equality
cars_.equals(cars)
Explanation: Task: Join cars1 and cars2 into a single DataFrame called cars
End of explanation
# Answer
def grep_firt_word(string_):
    return string_.split()[0]
    
cars["vendor"] = cars["car"].apply(grep_firt_word)
# or
cars["vendor"] = cars["car"].apply(lambda s: s.split()[0])
Explanation: Apply (interspersed)
Task: Create function that returns the first word of the string in the "car" column, the manufacturer name. Use the "apply" method to create a new column in the DataFrame.
End of explanation
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3'],
                     'C': ['C0', 'C1', 'C2', 'C3'],
                     'D': ['D0', 'D1', 'D2', 'D3']},
                     index=[0, 1, 2, 3])
df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],
                     'B': ['B4', 'B5', 'B6', 'B7'],
                     'C': ['C4', 'C5', 'C6', 'C7'],
                     'D': ['D4', 'D5', 'D6', 'D7']},
                      index=[4, 5, 6, 7])
df3 = pd.DataFrame({'A': ['A8', 'A9', 'A10', 'A11'],
                     'B': ['B8', 'B9', 'B10', 'B11'],
                     'C': ['C8', 'C9', 'C10', 'C11'],
                     'D': ['D8', 'D9', 'D10', 'D11']},
                     index=[8, 9, 10, 11])
Explanation: Consider the following DataFrames for the next exercises
End of explanation
# Answer:
pd.concat([df1, df2, df3], axis=0)
# or
# df1.append(df2).append(df3)
Explanation: Task: Concatenate the three DataFrames along the rows.
End of explanation
# Answer
print sum(pd.concat([df1, df2, df3], axis=1).isnull().sum())
# or
shape1 = df1.shape
shape2 = df2.shape
shape3 = df3.shape
num_true_values = shape1[0]*shape1[1] + shape2[0]*shape2[1] + shape3[0]*shape3[1]
total_num_values = (shape1[0] + shape2[0] + shape3[0]) * (shape1[1] + shape2[1] + shape3[1])
print total_num_values - num_true_values
assert sum(pd.concat([df1, df2, df3], axis=1).isnull().sum()) == total_num_values - num_true_values
Explanation: Task: How many missing values (NaNs) are produced if you concatenate along the other axis (appending the columns)?
End of explanation
raw_data_1 = {
        'subject_id': ['1', '2', '3', '4', '5'],
        'first_name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung'], 
        'last_name': ['Anderson', 'Ackerman', 'Ali', 'Aoni', 'Atiches']}
raw_data_2 = {
        'subject_id': ['4', '5', '6', '7', '8', '9', '10'],
        'first_name': ['Alice', 'Ayoung', 'Bran', 'Bryce', 'Betty', 'Jane', np.nan], 
        'last_name': ['Aoni', 'Atiches', 'Balwner', 'Brice', 'Btisan', np.nan, 'Doe']}
raw_data_3 = {
        'subject_id': ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],
        'test_id': [51, 15, 15, 61, 16, 14, 15, 1, 61, 16]}
data1 = pd.DataFrame(raw_data_1, columns = ['subject_id', 'first_name', 'last_name'])
data2 = pd.DataFrame(raw_data_2, columns = ['subject_id', 'first_name', 'last_name'])
data3 = pd.DataFrame(raw_data_3, columns = ['subject_id','test_id'])
Explanation: Let's consider another data set to do some more Merge, Join & Concatenate exerciseses
End of explanation
# Answer:
all_data = pd.concat([data1, data2], ignore_index=True)
assert len(set(all_data.index)) == all_data.shape[0]
# otherwise you might get unexpected behaviour:
all_data = pd.concat([data1, data2], ignore_index=False)
all_data.loc[0, :]
Explanation: Task: Join the two dataframes, data1 and data2, along rows and assign all_data. Make sure that the row index is unique.
End of explanation
# Answer:
all_data_col = pd.concat([data1, data2], axis = 1)
all_data_col
Explanation: Task: Join the two dataframes, data1 and data2, along columns and assing to all_data_col.
End of explanation
# Answer:
pd.merge(all_data, data3)
Explanation: Task: Merge all_data and data3 along the subject_id value.
End of explanation
# Answer:
dfm = pd.merge(all_data, data3, how="outer")
cond = dfm["test_id"].notnull() & (dfm["first_name"].isnull() | dfm["last_name"].isnull())
sum(cond)
Explanation: Task: How many test_ids have missing values in the first or last name column?
End of explanation
# Answer:
pd.merge(data1, data2, how='inner')
Explanation: Task: Merge only the data that has the same 'subject_id' in both data1 and data2.
End of explanation
df = pd.DataFrame([[1, 1], [1, -1], [2, 1], [2, 2]], columns=["groups", "vals"])
df
# Answer
def replace_within_group(group):
    mask = group < 0
    # Select those values where it is < 0, and replace
    # them with the mean of the values which are not < 0.
    group[mask] = group[~mask].mean() # "~" is the "invert" or "complement" operation
    return group
df.groupby(['groups'])['vals'].transform(replace_within_group)
Explanation: Transform
The transform method returns an object that is indexed the same (same size) as the one being grouped.
Task: Given a DataFrame with a column of group IDs, 'groups', and a column of corresponding integer values, 'vals', replace any negative values in 'vals' with the group mean.
End of explanation
df = pd.DataFrame({'a':[1,2,3,4,5,6],
                   'b':[1,2,3,4,5,6],
                   'c':['q', 'q', 'q', 'q', 'w', 'w'],  
                   'd':['z','z','z','o','o','o']})
# Answer:
df['e'] = df['a'] + df['b']
df['f'] = (df.groupby(['c', 'd'])['e'].transform('sum'))
Explanation: Task: Use groupby in conjunction with transform across multiple columns: We want to group by one to n columns and apply a function on these groups across two columns.
    1. Calculate the sum of a and b and assign it to a column named e.
    2. Group by 'c' and d, and calculate the sum of e
End of explanation
index = pd.date_range('10/1/1999', periods=1100)
ser = pd.Series(np.random.normal(0.5, 2, 1100), index=index)
ser = ser.rolling(window=100,min_periods=100).mean().dropna()
# Answer:
key = lambda x: x.year
zscore = lambda x: (x - x.mean()) / x.std()
transformed = ser.groupby(key).transform(zscore)
Explanation: Task: Normalize (standardize) the data by calculating the z-score. Group the data by year and calculate the z-score per group. z = (value - mean) / standard_deviation
<div style="font-size: 150%;">  
$$z=\frac{x-\mu}{\sigma}$$
</div>
End of explanation
# Answer:
grouped = transformed.groupby(key)
grouped.mean()
grouped.std()
Explanation: Task: We would expect the result to now have mean 0 and standard deviation 1 within each group, which we can easily check. Calculate the mean and standard deviation within each group.
End of explanation
# Answer:
compare = pd.DataFrame({'Original': ser, 'Transformed': transformed}, columns=["Transformed", "Original"])
compare.plot(figsize=(12,9), color=["blue", "red"])
Explanation: Task: Visually compare the original and transformed data sets.
End of explanation
df = pd.DataFrame({"Person": ["a", "a", "a", "b", "c", "c"], "Code": ["D99", "E32", "A41", "D99", "D99", "A41"]}, columns=["Person", "Code"])
df
# Answer:
df["Values"] = pd.Series([True]*df.shape[0])
dfp = df.pivot(index="Person", columns="Code", values="Values")
dfp = dfp.fillna(value=False)
dfp = dfp.reset_index()
dfp
Explanation: Pivot
Task: Let's reshape this small example DataFrame of "extended" ICU codes. Each person has different code-associations. Only positive associations are listed. Transform (reshape) the DataFrame to a wide format (one column per code) that lists positive and negative (missing) associations as Booleans.
End of explanation
# Answer
metadata = pd.read_excel(r"data/microbiome/metadata.xls", sheetname='Sheet1')
chunks = []
for i in range(9):
    df_temp = pd.read_excel(r"data/microbiome/MID{0}.xls".format(i+1), 'Sheet 1', header=None, names=['Taxon', 'Count'])
    df_temp = df_temp.set_index("Taxon")
    df_temp.columns = ['Count']
    df_temp.index.name = 'Taxon'
    for colname in metadata.columns:
        df_temp[colname] = metadata.loc[i, colname]
    chunks.append(df_temp)
df = pd.concat(chunks)
df = df.reset_index()
df.head()
Explanation: Combine DataFrames
Task: In the data/microbiome subdirectory, there are 9 spreadsheets of microbiome data that was acquired from high-throughput RNA sequencing procedures, along with a 10th file that describes the content of each. Write code that imports each of the data spreadsheets and combines them into a single DataFrame, adding the identifying information from the metadata spreadsheet as columns in the combined DataFrame.
End of explanation
# Answers
titanic = pd.read_excel("data/titanic.xls", "titanic")
# 1.
# explicit version
# titanic.groupby('sex')['survived'].agg("mean")
# or short version
titanic.groupby('sex')['survived'].mean()
# 2.
titanic.groupby(['pclass','sex'])['survived'].mean()
# 3.
titanic['agecat'] = pd.cut(titanic.age, [0, 13, 20, 64, 100], labels=['child', 'adolescent', 'adult', 'senior'])
titanic.groupby(['agecat', 'pclass','sex'])['survived'].mean()
titanic.groupby(['agecat', 'pclass','sex'])['survived'].agg(["mean", "count"])
Explanation: GroupBy Titanic data
Load the dataset in titanic.xls. It contains data on all the passengers that travelled on the Titanic.
Task:
Women and children first?
Use the groupby method to calculate the proportion of passengers that survived by sex.
Calculate the same proportion, but by class and sex.
Create age categories: children (under 14 years), adolescents (14-20), adult (21-64), and senior(65+), and calculate survival proportions by age category, class and sex. Additionally, count the number of passengers per group.
End of explanation
titanic.groupby(['sex','pclass'])["survived"].sum().plot(kind='barh')
Explanation: Let's plot the number of survivors grouped by sex and passenger class.
End of explanation
# Answer
# 1.
titanic.survived = titanic.survived.astype(bool)
# 2.
death_counts = pd.crosstab([titanic.pclass, titanic.sex], titanic.survived)
# 3.
death_counts.plot(kind='bar', stacked=True)
Explanation: Task: Let's also look at the deaths (and not only at the survivors) within the groups and create a stacked Barplot of survivers vs. deaths grouped by sex and passenger-class (as before).
1. Convert the "survived" column to boolean values
2. Compute the cross tabulation (a.k.a. contingency table) of passenger-class and sex vs. survived. Assign the result to the variable name "death_counts" --> checkout pd.crosstab()
3. Create a stacked barplot of the computed death_counts
End of explanation
# Answer
# 1.
sum_death_counts = death_counts.sum(axis=1).astype(float)
# 2. 
adjusted_death_counts = death_counts.div(sum_death_counts, axis=0)
# 3.
adjusted_death_counts.plot(kind='barh', stacked=True)
# or in short:
# death_counts.div(death_counts.sum(1).astype(float), axis=0).plot(kind='barh', stacked=True)
Explanation: Task: Another way of comparing the groups is to look at the survival rate, by adjusting for the number of people in each group.
Create a stacked, horizontal Barplot of the adjusted death counts
1. Sum the death_counts per passenger-class and sex, and convert to data type float (for Python 2.x division purposes).
2. Compute the adjusted survival rate by dividing the death_counts by the result from 1. 
3. Plot a stacked, horizontal Barplot from the result of 3.
End of explanation
<END_TASK>