<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Table of Contents
<p><div class="lev1"><a href="#Task-1.-Compiling-Ebola-Data"><span class="toc-item-num">Task 1.&nbsp;&nbsp;</span>Compiling Ebola Data</a></div>
 <div class="lev1"><a href="#Task-2.-RNA-Sequences"><span class="toc-item-num">Task 2.&nbsp;&nbsp;</span>RNA Sequences</a></div>
 <div class="lev1"><a href="#Task-3.-Class-War-in-Titanic"><span class="toc-item-num">Task 3.&nbsp;&nbsp;</span>Class War in Titanic</a></div></p>
Step1:  Task 1. Compiling Ebola Data
The DATA_FOLDER/ebola folder contains summarized reports of Ebola cases from three countries (Guinea, Liberia and Sierra Leone) during the recent outbreak of the disease in West Africa. For each country, there are daily reports that contain various information about the outbreak in several cities in each country.
Use pandas to import these data files into a single Dataframe.
Using this DataFrame, calculate for each country, the daily average per month of new cases and deaths.
Make sure you handle all the different expressions for new cases and deaths that are used in the reports.
First, we define some helpful functions that will help us during the parsing of the data.
- get_files
Step2:  sum_row
Step3:  Now, we define for each country a function, which, for a given file, returns a dictionnary with the country, date, upper and lower bounds for the new cases, and upper and lower bounds for the new deaths.
As we don't know if the new cases / deaths for the 'probable' and 'suspected' cases is reliable, we decided to create an upper bound with the sum of the 'confirmed', 'probable' and 'suspected' new cases / deaths, and a lower bound with only the 'confirmed' new cases / deaths.
The structure of these functions are the same for each country, only the name of the descrption of the data changes.
Step4:  As the files for the Sierra Leone does not contain data for the new deaths, we first extract the total deaths for each day, and we will process them later to get the new deaths.
Step5:  We now transform the data for the Sierra Leone 
Step6:  We can now insert the data in a dataframe. For Liberia, December's data is in a completely different format so we dropped it
Step7:  Finally, to have some final general idea for the data, we average the bounds.
Step8:  Task 2. RNA Sequences
In the DATA_FOLDER/microbiome subdirectory, there are 9 spreadsheets of microbiome data that was acquired from high-throughput RNA sequencing procedures, along with a 10<sup>th</sup> file that describes the content of each. 
Use pandas to import the first 9 spreadsheets into a single DataFrame.
Then, add the metadata information from the 10<sup>th</sup> spreadsheet as columns in the combined DataFrame.
Make sure that the final DataFrame has a unique index and all the NaN values have been replaced by the tag unknown.
We load the first spreadsheet from the file's Sheet 1. Then we add a new column that is the same for all the data in this import, which corresponds to the barcode of the code.
Then we rename the columns for more clarity.
Step9:  Now we repeat this operation for every other spreadsheet except the metadata. At each iteration we simply concatenate the data at the end of the previous data, this accumulating all the files' data into a single dataframe. We don't care about any index right now since we will use a random one later.
Step10:  Finally, we do a merge with the metadata. We join on the BARCODE column. This column will be the index of the metadata when we import it in this case. Finally we set the index for the three columns BARCODE, GROUP and SAMPLE which are all the columns of the metada and are unique.
The only NaN value we found was the NA value on the metadata, which may indicate that there is no sample for the first group. We replaced it anyway by unknown.
Step11:  Task 3. Class War in Titanic
Use pandas to import the data file Data/titanic.xls. It contains data on all the passengers that travelled on the Titanic.
Step12:  For each of the following questions state clearly your assumptions and discuss your findings
Step13:  Next we can list the data types of each field.
Step14:  When it comes to the object fields, we can be a bit more precise. name, sec, ticket, cabin, embarked, boat and home.dex are all strings.
Next, we call the describe method to list some statistics on the data. We thus obtain the range of all of the numeric fields of our data.
Step15:  Moreover, we can also note some ranges of other fields. For example, sex has only two possible values female and male. embarked can only be S, C and Q.
For a better visual result, we decided to replace the travel classes, ports to more readable values. As we make  them categorical types, the performance stays the same.
Step16:  Then we make categorical data as actually categorical.
Step17:  2. We plot the histogram of the travel class.
Step18:  Next we plot the histogram of the three embark ports.
Step19:  Next we plot the histogram of the sex.
Step20:  Next, we cut the ages data into decades and plot the histogram of the devades.
Step21:  3. We plot the cabin floor data as a pie chart.
Step22:  4. Here, we plot the proportion of people that survived in the first class.
Step23:  Next, we plot the proportion of people that survived in the second class.
Step24:  Finally, we plot the proportion of people that survived in the third class.
Step25:  As we can see, the lower the class, the higher the probability of death.
5. Here we add new columns that will help us to calculate proportions of survived people in the last part.
Step26:  Here we set these new columns to appropriate values. We essentialy separate the survived columns for easier summing later on. Finnaly we slice the data to take only the columns of interest.
Step27:  We group the data by the sec and class of the passangers and we sum it. Then we have the sum of alive and dead people groupped as we wish and we can easily calculate the proportion of them that survived, which we plot as a histogram.
Step28:  We can see that there is a huge difference of survival between the classes and sexes 
Step29:  Next, we set the correct category to people below or above the median age. The people that have the median age are grouped with the people below it. Next we set this column as a categorical column.
Step30:  Next, we take the columns that are of interest to us and group by age category, sec and travel class. Then we sum over these groups, obtaining the people that lived and those that died which which we can compute the proportion and display it as a dataframe.
<ASSISTANT_TASK:>
Python Code:
DATA_FOLDER = 'Data' # Use the data folder provided in Tutorial 02 - Intro to Pandas.
%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import datetime
from dateutil.parser import parse
from os import listdir
from os.path import isfile, join
sns.set_context('notebook')
Explanation: Table of Contents
<p><div class="lev1"><a href="#Task-1.-Compiling-Ebola-Data"><span class="toc-item-num">Task 1.&nbsp;&nbsp;</span>Compiling Ebola Data</a></div>
 <div class="lev1"><a href="#Task-2.-RNA-Sequences"><span class="toc-item-num">Task 2.&nbsp;&nbsp;</span>RNA Sequences</a></div>
 <div class="lev1"><a href="#Task-3.-Class-War-in-Titanic"><span class="toc-item-num">Task 3.&nbsp;&nbsp;</span>Class War in Titanic</a></div></p>
End of explanation
def get_files(country):
    path = DATA_FOLDER + "/ebola/" + country + "_data/"
    return [f for f in listdir(path) if isfile(join(path, f))]
Explanation: Task 1. Compiling Ebola Data
The DATA_FOLDER/ebola folder contains summarized reports of Ebola cases from three countries (Guinea, Liberia and Sierra Leone) during the recent outbreak of the disease in West Africa. For each country, there are daily reports that contain various information about the outbreak in several cities in each country.
Use pandas to import these data files into a single Dataframe.
Using this DataFrame, calculate for each country, the daily average per month of new cases and deaths.
Make sure you handle all the different expressions for new cases and deaths that are used in the reports.
First, we define some helpful functions that will help us during the parsing of the data.
- get_files: returns all the .csv files for a given country
End of explanation
def sum_row(row, total_col):
    return float(row[total_col].values[0])
def sum_rows(rows, total_col):
    tot = 0
    for row in rows:
        tot += sum_row(row, total_col)
    return tot 
Explanation: sum_row: for a given row, returns the total value for the new cases / deaths. We first defined this function as the sum of all new cases / deaths in all provinces, but we discovered some strange data for some provinces, so we decided to only take into account the 'total' column
sum_rows: sum all the rows given in argument
End of explanation
def get_row_guinea(file):
    country = 'guinea'
    date = file[:10]
    raw = pd.read_csv(DATA_FOLDER + "/ebola/" + country + "_data/" + file)
    total_col = "Totals"
    
    new_cases_lower = sum_row(raw[raw.Description == "New cases of confirmed"], total_col)
    new_cases_upper = sum_row(raw[raw.Description == "Total new cases registered so far"], total_col)
        
    new_deaths_lower = sum_row(raw[(raw.Description == "New deaths registered today (confirmed)") | (raw.Description == "New deaths registered")], total_col)
    new_deaths_upper = sum_row(raw[(raw.Description == "New deaths registered today") | (raw.Description == "New deaths registered")], total_col)
    
    return {'Country' : country, 'Date' : parse(date), 'NewCasesLower' : new_cases_lower, 'NewCasesUpper' : new_cases_upper, 'NewDeathsLower' : new_deaths_lower, 'NewDeathsUpper' : new_deaths_upper}
def get_row_liberia(file):
    country = 'liberia'
    date = file[:10]
    raw = pd.read_csv(DATA_FOLDER + "/ebola/" + country + "_data/" + file).fillna(0)
    total_col = "National"
    
    new_cases_lower = sum_row(raw[raw.Variable == "New case/s (confirmed)"], total_col)
    list_cases_upper = (["New Case/s (Suspected)", 
                        "New Case/s (Probable)",
                        "New case/s (confirmed)"])
    new_cases_upper = sum_rows([raw[raw.Variable == row] for row in list_cases_upper], total_col)
        
    new_deaths_lower = sum_row(raw[raw.Variable == "Newly reported deaths"], total_col)
    new_deaths_upper = new_deaths_lower
    
    return {'Country' : country, 'Date' : parse(date), 'NewCasesLower' : new_cases_lower, 'NewCasesUpper' : new_cases_upper, 'NewDeathsLower' : new_deaths_lower, 'NewDeathsUpper' : new_deaths_upper}
Explanation: Now, we define for each country a function, which, for a given file, returns a dictionnary with the country, date, upper and lower bounds for the new cases, and upper and lower bounds for the new deaths.
As we don't know if the new cases / deaths for the 'probable' and 'suspected' cases is reliable, we decided to create an upper bound with the sum of the 'confirmed', 'probable' and 'suspected' new cases / deaths, and a lower bound with only the 'confirmed' new cases / deaths.
The structure of these functions are the same for each country, only the name of the descrption of the data changes.
End of explanation
def get_row_sl(file):
    country = 'sl'
    date = file[:10]
    raw = pd.read_csv(DATA_FOLDER + "/ebola/" + country + "_data/" + file).fillna(0)
    total_col = "National"
        
    new_cases_lower = sum_row(raw[raw.variable == "new_confirmed"], total_col)
    list_cases_upper = (["new_suspected", 
                        "new_probable",
                        "new_confirmed"])
    new_cases_upper = sum_rows([raw[raw.variable == row] for row in list_cases_upper], total_col)
    
    list_death_upper = (["death_suspected", 
                        "death_probable",
                        "death_confirmed"])
    total_death_upper = sum_rows([raw[raw.variable == row] for row in list_death_upper], total_col)
    total_death_lower = sum_row(raw[raw.variable == "death_confirmed"], total_col)
    
    return {'Country' : country, 'Date' : parse(date), 'NewCasesLower' : new_cases_lower, 'NewCasesUpper' : new_cases_upper, 'TotalDeathLower' : total_death_lower, 'TotalDeathUpper' : total_death_upper}
rows_guinea = [get_row_guinea(file) for file in get_files("guinea")]
rows_liberia = [get_row_liberia(file) for file in get_files("liberia")]
Explanation: As the files for the Sierra Leone does not contain data for the new deaths, we first extract the total deaths for each day, and we will process them later to get the new deaths.
End of explanation
rows_sl_total_deaths = [get_row_sl(file) for file in get_files("sl")]
dic_sl_total_deaths = {}
for row in rows_sl_total_deaths:
    dic_sl_total_deaths[row['Date']] = row
    
rows_sl = []
for date, entry in dic_sl_total_deaths.items():
    date_before = date - datetime.timedelta(days=1)
    if date_before in dic_sl_total_deaths:
        
        if entry['TotalDeathUpper'] != 0 and dic_sl_total_deaths[date_before]['TotalDeathUpper'] != 0 and entry['TotalDeathLower'] != 0 and dic_sl_total_deaths[date_before]['TotalDeathLower'] != 0: 
            copy = dict(entry)
            del copy['TotalDeathUpper']
            del copy['TotalDeathLower']
            
            copy['NewDeathsUpper'] = entry['TotalDeathUpper'] - dic_sl_total_deaths[date_before]['TotalDeathUpper']
            copy['NewDeathsLower'] = entry['TotalDeathLower'] - dic_sl_total_deaths[date_before]['TotalDeathLower']
            rows_sl.append(copy)
Explanation: We now transform the data for the Sierra Leone :
- we first create a new dictionary for which the keys are date, and the values are the previously extracted values from the .csv files.
- then for each value in this dictionary, we try to get the value of the day before, and perform the difference to get the new deaths of this day.
End of explanation
raw_dataframe = pd.DataFrame(columns=['Country', 'Date', 'NewCasesLower', 'NewCasesUpper', 'NewDeathsLower', 'NewDeathsUpper'])
for row in rows_sl, rows_guinea:
    raw_dataframe = raw_dataframe.append(row, ignore_index = True)
for row in rows_liberia:
    if row['Date'].month != 12: #December data is erroneous
        raw_dataframe = raw_dataframe.append(row, ignore_index = True)
        
raw_dataframe
dataframe = raw_dataframe.set_index(['Country', 'Date'])
dataframe_no_day = raw_dataframe
dataframe_no_day['Year'] = raw_dataframe['Date'].apply(lambda x: x.year)
dataframe_no_day['Month'] = raw_dataframe['Date'].apply(lambda x: x.month)
final_df = dataframe_no_day[['Country', 'Year', 'Month', 'NewCasesLower', 'NewCasesUpper', 'NewDeathsLower', 'NewDeathsUpper']].groupby(['Country', 'Year', 'Month']).mean()
final_df
Explanation: We can now insert the data in a dataframe. For Liberia, December's data is in a completely different format so we dropped it: for instance, for some days, the new cases are the new cases for the day and for some other they are the total cases for this country.
End of explanation
s1 = final_df[['NewCasesLower', 'NewCasesUpper']].mean(axis=1)
s2 = final_df[['NewDeathsLower', 'NewDeathsUpper']].mean(axis=1)
final = pd.concat([s1, s2], axis=1)
final.columns = ['NewCasesAverage', 'NewDeathsAverage']
final
Explanation: Finally, to have some final general idea for the data, we average the bounds.
End of explanation
mid = pd.read_excel(DATA_FOLDER + '/microbiome/MID1.xls', sheetname='Sheet 1', header=None)
mid.fillna('unknown', inplace=True)
mid['BARCODE'] = 'MID1'
mid.columns = ['Taxon', 'Count', 'BARCODE']
Explanation: Task 2. RNA Sequences
In the DATA_FOLDER/microbiome subdirectory, there are 9 spreadsheets of microbiome data that was acquired from high-throughput RNA sequencing procedures, along with a 10<sup>th</sup> file that describes the content of each. 
Use pandas to import the first 9 spreadsheets into a single DataFrame.
Then, add the metadata information from the 10<sup>th</sup> spreadsheet as columns in the combined DataFrame.
Make sure that the final DataFrame has a unique index and all the NaN values have been replaced by the tag unknown.
We load the first spreadsheet from the file's Sheet 1. Then we add a new column that is the same for all the data in this import, which corresponds to the barcode of the code.
Then we rename the columns for more clarity.
End of explanation
for i in range(2, 10):
    midi = pd.read_excel(DATA_FOLDER + '/microbiome/MID' + str(i) + '.xls', sheetname='Sheet 1', header=None)
    midi.fillna('unknown', inplace=True)
    midi['BARCODE'] = 'MID' + str(i)
    midi.columns = ['Taxon', 'Count', 'BARCODE']
    mid = pd.concat([mid, midi])
Explanation: Now we repeat this operation for every other spreadsheet except the metadata. At each iteration we simply concatenate the data at the end of the previous data, this accumulating all the files' data into a single dataframe. We don't care about any index right now since we will use a random one later.
End of explanation
metadata = pd.read_excel(DATA_FOLDER + '/microbiome/metadata.xls', sheetname='Sheet1', index_col=0)
metadata.fillna('unknown', inplace=True)
merged = pd.merge(mid, metadata, right_index=True, left_on='BARCODE')
merged = merged.set_index(keys=['BARCODE', 'Taxon'])
merged
Explanation: Finally, we do a merge with the metadata. We join on the BARCODE column. This column will be the index of the metadata when we import it in this case. Finally we set the index for the three columns BARCODE, GROUP and SAMPLE which are all the columns of the metada and are unique.
The only NaN value we found was the NA value on the metadata, which may indicate that there is no sample for the first group. We replaced it anyway by unknown.
End of explanation
from IPython.core.display import HTML
HTML(filename=DATA_FOLDER+'/titanic.html')
Explanation: Task 3. Class War in Titanic
Use pandas to import the data file Data/titanic.xls. It contains data on all the passengers that travelled on the Titanic.
End of explanation
titanic = pd.read_excel(DATA_FOLDER + '/titanic.xls', sheetname='titanic')
titanic
Explanation: For each of the following questions state clearly your assumptions and discuss your findings:
1. Describe the type and the value range of each attribute. Indicate and transform the attributes that can be Categorical. 
2. Plot histograms for the travel class, embarkation port, sex and age attributes. For the latter one, use discrete decade intervals. 
3. Calculate the proportion of passengers by cabin floor. Present your results in a pie chart.
4. For each travel class, calculate the proportion of the passengers that survived. Present your results in pie charts.
5. Calculate the proportion of the passengers that survived by travel class and sex. Present your results in a single histogram.
6. Create 2 equally populated age categories and calculate survival proportions by age category, travel class and sex. Present your results in a DataFrame with unique index.
1. We start by importing the data from the file.
End of explanation
titanic.dtypes
Explanation: Next we can list the data types of each field.
End of explanation
titanic.describe()
Explanation: When it comes to the object fields, we can be a bit more precise. name, sec, ticket, cabin, embarked, boat and home.dex are all strings.
Next, we call the describe method to list some statistics on the data. We thus obtain the range of all of the numeric fields of our data.
End of explanation
class_dic = {1 : 'First Class', 2 : 'Second Class', 3 : 'Third Class', np.nan : np.nan}
survived_dic = {0 : 'Deceased' , 1 : 'Survived', np.nan : np.nan}
emarked_dic = {'C' : 'Cherbourg', 'Q' : 'Queenstown', 'S' : 'Southampton', np.nan : np.nan}
titanic['pclass'] = titanic['pclass'].apply(lambda x: class_dic[x])
titanic['survived'] = titanic['survived'].apply(lambda x: survived_dic[x])
titanic['embarked'] = titanic['embarked'].apply(lambda x: emarked_dic[x])
Explanation: Moreover, we can also note some ranges of other fields. For example, sex has only two possible values female and male. embarked can only be S, C and Q.
For a better visual result, we decided to replace the travel classes, ports to more readable values. As we make  them categorical types, the performance stays the same.
End of explanation
titanic['pclass'] = titanic.pclass.astype('category')
titanic['survived'] = titanic.survived.astype('category')
titanic['sex'] = titanic.sex.astype('category')
titanic['embarked'] = titanic.embarked.astype('category')
titanic['cabin'] = titanic.cabin.astype('category')
titanic['boat'] = titanic.boat.astype('category')
Explanation: Then we make categorical data as actually categorical.
End of explanation
titanic.pclass.value_counts(sort=False).plot(kind='bar')
Explanation: 2. We plot the histogram of the travel class.
End of explanation
titanic.embarked.value_counts().plot(kind='bar')
Explanation: Next we plot the histogram of the three embark ports.
End of explanation
titanic.sex.value_counts().plot(kind='bar')
Explanation: Next we plot the histogram of the sex.
End of explanation
pd.cut(titanic.age, range(0,90,10)).value_counts(sort=False).plot(kind='bar')
Explanation: Next, we cut the ages data into decades and plot the histogram of the devades.
End of explanation
titanic.cabin.dropna().apply(lambda x : x[0]).value_counts(sort=False).plot(kind='pie')
Explanation: 3. We plot the cabin floor data as a pie chart.
End of explanation
titanic[titanic.pclass == "First Class"].survived.value_counts(sort=False).plot(kind='pie')
Explanation: 4. Here, we plot the proportion of people that survived in the first class.
End of explanation
titanic[titanic.pclass == "Second Class"].survived.value_counts(sort=False).plot(kind='pie')
Explanation: Next, we plot the proportion of people that survived in the second class.
End of explanation
titanic[titanic.pclass == "Third Class"].survived.value_counts(sort=False).plot(kind='pie')
Explanation: Finally, we plot the proportion of people that survived in the third class.
End of explanation
titanic.insert(0, 'alive', 0)
titanic.insert(0, 'dead', 0)
titanic.insert(0, 'ratio', 0)
Explanation: As we can see, the lower the class, the higher the probability of death.
5. Here we add new columns that will help us to calculate proportions of survived people in the last part.
End of explanation
titanic.loc[titanic['survived'] == "Survived", 'alive'] = 1
titanic.loc[titanic['survived'] == "Deceased", 'dead'] = 1
df = titanic[['pclass', 'sex', 'alive', 'dead', 'ratio']]
Explanation: Here we set these new columns to appropriate values. We essentialy separate the survived columns for easier summing later on. Finnaly we slice the data to take only the columns of interest.
End of explanation
aggregated = df.groupby(['sex', 'pclass']).sum()
(aggregated['alive'] / (aggregated['alive'] + aggregated['dead'])).plot(kind='bar')
Explanation: We group the data by the sec and class of the passangers and we sum it. Then we have the sum of alive and dead people groupped as we wish and we can easily calculate the proportion of them that survived, which we plot as a histogram.
End of explanation
titanic.dropna(axis=0, subset=['age'], inplace=True)
titanic.insert(0, 'age_category', 0)
median = titanic['age'].median()
Explanation: We can see that there is a huge difference of survival between the classes and sexes : for instance, the third class males have 7 less times chance of survival than the first class females.
6. Next we insert a new column that will be the age category of each person. Since we wan't to split the people in two equal groups based on age, we compute the median age of passangers. We also drop the passengers with an unknown age value, to avoid bad results for the median computation.
End of explanation
titanic.loc[titanic['age'] > median, 'age_category'] = "Age > " + str(median)
titanic.loc[titanic['age'] <= median, 'age_category'] = "Age <= " + str(median)
titanic['age_category'] = titanic.age_category.astype('category')
Explanation: Next, we set the correct category to people below or above the median age. The people that have the median age are grouped with the people below it. Next we set this column as a categorical column.
End of explanation
sub = titanic[['pclass', 'sex', 'age_category', 'alive', 'dead', 'ratio']]
subagg = sub.groupby(['age_category', 'sex', 'pclass']).sum()
subagg['ratio'] = (subagg['alive'] / (subagg['alive'] + subagg['dead']))
only_ratio = subagg[['ratio']]
only_ratio
Explanation: Next, we take the columns that are of interest to us and group by age category, sec and travel class. Then we sum over these groups, obtaining the people that lived and those that died which which we can compute the proportion and display it as a dataframe.
End of explanation
<END_TASK>