10/6/2016
DataMungingInPythonUsingPandas
Timefliesby!IseeJenika(mydaughter)[Link]
stillslipsandtripsbutisnowindependenttoexploretheworldandfigureoutnewstuffonherown.
IhopeIwouldhavebeenabletoinspiresimilarconfidencewithuseofPythonfordataanalysisin
thefollowersofthisseries.
Forthose,whohavebeenfollowing,hereareapairofshoesforyoutostartrunning!
By end of this tutorial, you will also have all the tools necessary to perform any data analysis by
yourselfusingPython.
RecapGettingthebasicsright
In the previous posts in this series, we had downloaded and setup a Python installation, got
introduced to several useful libraries and data structures and finally started with an exploratory
analysisinPython(usingPandas).
In this tutorial, we will continue our journey from where we left it in our last tutorial we have a
reasonable idea about the characteristics of the dataset we are working on. If you have not gone
throughthepreviousarticleintheseries,kindlydosobeforeproceedingfurther.
[Link]
1/7
10/6/2016
DataMungingInPythonUsingPandas
Datamungingrecapoftheneed
Whileourexplorationofthedata,wefoundafewproblemsinthedataset,whichneedtobesolved
[Link]
aretheproblems,wearealreadyawareof:
1.About31%(277outof891)[Link]
hencewouldwanttoestimatethisinsomemanner.
[Link],wesawthatFareseemedtocontainextremevaluesateitherenda
[Link]$512sounds
likeaveryhighfareforbookingaticket
Inadditiontotheseproblemswithnumericalfields,weshouldalsolookatthenonnumericalfields
[Link],TicketandCabintosee,iftheycontainanyusefulinformation.
Checkmissingvaluesinthedataset
[Link]
[Link],letuscheckthenumberofnulls/NaNsinthedataset
sum(df['Cabin'].isnull())
Thiscommandshouldtellusthenumberofmissingvaluesasisnull()returns1,ifthevalueisnull.
[Link],wellneedtodropthisvariable.
Next,[Link]
containanyinformation,sowilldropTicketaswell.
[Link]
2/7
10/6/2016
DataMungingInPythonUsingPandas
df=[Link](['Ticket','Cabin'],axis=1)
HowtofillmissingvaluesinAge?
There are numerous ways to fill the missing values of Age the simplest being replacement by
mean,whichcanbedonebyfollowingcode:
meanAge=[Link]([Link])
[Link]=[Link](meanAge)
Theotherextremecouldbetobuildasupervisedlearningmodeltopredictageonthebasisofother
variablesandthenuseagealongwithothervariablestopredictsurvival.
Since, the purpose of this tutorial is to bring out the steps in data munging, Ill rather take an
approach, which lies some where in between these 2 extremes. The key hypothesis is that the
salutationsinName,GenderandPclasscombinedcanprovideuswithinformationrequiredtofillin
themissingvaluestoalargeextent.
Herearethestepsrequiredtoworkonthishypothesis:
Step1:ExtractingsalutationsfromName
Letusdefineafunction,whichextractsthesalutationfromaNamewritteninthisformat:
Family_Name,[Link]
defname_extract(word):
[Link](',')[1].split('.')[0].strip()
[Link]
3/7
10/6/2016
DataMungingInPythonUsingPandas
This function takes a Name, splits it by a comma (,), then splits it by a dot(.) and removes the
[Link],[Link],[Link]
wouldbeMiss
Next,weapplythisfunctiontotheentirecolumnusingapply()functionandconverttheoutcometoa
newDataFramedf2:
df2=[Link]({'Salutation':df['Name'].apply(name_extract)})
Once we have the Salutations, let us look at their distribution. We use the good old groupby after
mergingtheDataFramedf2withDataFramedf:
df=[Link](df,df2,left_index=True,right_index=True)#mergesonindex
temp1=[Link]('Salutation').[Link]()
printtemp1
Followingistheoutput:
Salutation
Capt1
Col2
Don1
Dr7
Jonkheer1
Lady1
[Link]
4/7
10/6/2016
DataMungingInPythonUsingPandas
Major2
Master40
Miss182
Mlle2
Mme1
Mr517
Mrs125
Ms1
Rev6
Sir1
theCountess1
dtype:int64
As you can see, there are 4 main Salutations Mr, Mrs, Miss and Master all other are less in
[Link],[Link]
ordertodoso,wetakethesameapproach,aswedidtoextractSalutationdefineafunction,apply
ittoanewcolumn,storetheoutcomeinaDataFrameandthenmergeitwitholdDataFrame:
defgroup_salutation(old_salutation):
ifold_salutation=='Mr':
return('Mr')
else:
ifold_salutation=='Mrs':
return('Mrs')
else:
ifold_salutation=='Master':
return('Master')
else:
ifold_salutation=='Miss':
[Link]
5/7
10/6/2016
DataMungingInPythonUsingPandas
return('Miss')
else:
return('Others')
df3=[Link]({'New_Salutation':df['Salutation'].apply(group_salutation)})
df=[Link](df,df3,left_index=True,right_index=True)
temp1=[Link]('New_Salutation').count()
temp1
[Link](column='Age',by='New_Salutation')
FollowingistheoutcomeforDistributionofNew_SalutationandvariationofAgeacrossthem:
[Link]
6/7
10/6/2016
DataMungingInPythonUsingPandas
Step2:Creatingasimplegrid(ClassxGender)xSalutation
SimilarlyplottingthedistributionofagebySex&Classshowsasloping:
So,wecreateaPivottable,[Link],
wedefineafunction,whichreturnsthevaluesofthesecellsandapplyittofillthemissingvaluesof
age:
[Link]
7/7