Phyton : A First Date with Data
An entry point to Data Analysis can be a (Regression Example). But first things first, data should be prepared before the analysis can be performed. This step is called preprocessing. Real-world (raw) data can be inconsistent or incomplete and can even contain errors. Through the following lines, we will try to walk through a simple data preprocessing task using a famous dataset.
A statistician with the name Francis Galton wanted to see if there was a connection between the heights of sons and their fathers. He measured the height of children and their parents across 205 families.
We will look closely at the data he used (which can be found here) , explore it and see what different information it contains and do some data preprocessing.
We will use Python in this post, here is the R version. So let´s dive in :).
First of all we will import the necessary packages:
# Pandas is an open source library for data manipulation and analysis
# NumPy is the fundamental package for scientific computing
import pandas as pd
import numpy as np
We can read the data into a Dataframe which is a data structure with columns of potentially different types. It basically looks like a spreadsheet or SQL table. And since we have a tab-separated data file (data separated with a tab rather than a comma in a “comma-separated values”), We need to specify it as follows:
data = pd.read_csv("galton-data.tab", sep="\t")
Now that we have our data in a Dataframe, we can use head() to see the first 5 rows.
data.head()
| family | father | mother | gender | height | kids | male | female | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 78.5 | 67.0 | M | 73.2 | 4 | 1.0 | 0.0 |
| 1 | 1 | 78.5 | 67.0 | F | 69.2 | 4 | 0.0 | 1.0 |
| 2 | 1 | 78.5 | 67.0 | F | 69.0 | 4 | 0.0 | 1.0 |
| 3 | 1 | 78.5 | 67.0 | F | 69.0 | 4 | 0.0 | 1.0 |
| 4 | 2 | 75.5 | 66.5 | M | 73.5 | 4 | 1.0 | 0.0 |
We can see that there are 8 columns. For each of the adult children of one family, we have the data about their height and gender as well as their parents height and the number of siblings.
Now, we will try to find more about our DataFrame by asking some basic questions:
# How many rows and columns are in the dataframe ?
data.shape
(898, 8)
# What are the available data concerning family number 7
data.loc[data["family"] == "7"]
| family | father | mother | gender | height | kids | male | female | |
|---|---|---|---|---|---|---|---|---|
| 22 | 7 | 74.0 | 68.0 | M | 76.5 | 6 | 1.0 | 0.0 |
| 23 | 7 | 74.0 | 68.0 | M | 74.0 | 6 | 1.0 | 0.0 |
| 24 | 7 | 74.0 | 68.0 | M | 73.0 | 6 | 1.0 | 0.0 |
| 25 | 7 | 74.0 | 68.0 | M | 73.0 | 6 | 1.0 | 0.0 |
| 26 | 7 | 74.0 | 68.0 | F | 70.5 | 6 | 0.0 | 1.0 |
| 27 | 7 | 74.0 | 68.0 | F | 64.0 | 6 | 0.0 | 1.0 |
# what is the maximum height of fathers
data["father"].max()
78.5
# what is the maximum height of sons
data.loc[data["male"] == 1,"height"].max()
79.0
# what is the maximum height of mothers
data["mother"].max()
70.5
# what is the maximum height of daughters
data.loc[data["female"] == 1,"height"].max()
70.5
# what is the mean height of fathers
data["father"].mean()
69.23285077950997
# what is the mean height of sons
data.loc[data["male"] == 1,"height"].mean()
69.22881720430114
# What is the number of observed families
data["family"].nunique()
197
From the study of Francis Galton we know that he gathered data across 205 families. This means that we have some missing data.
A part of data analysis is dealing with missing or incomplete data. We will try to take a look into the last rows of our dataframe just out of curiosity. For this we will use tail() and specify that we want the last 20 rows
data.tail(20)
| family | father | mother | gender | height | kids | male | female | |
|---|---|---|---|---|---|---|---|---|
| 878 | 199 | 64.0 | 64.0 | F | 65.0 | 7 | 0.0 | 1.0 |
| 879 | 199 | 64.0 | 64.0 | F | 64.0 | 7 | 0.0 | 1.0 |
| 880 | 199 | 64.0 | 64.0 | F | 64.0 | 7 | 0.0 | 1.0 |
| 881 | 199 | 64.0 | 64.0 | F | 60.0 | 7 | 0.0 | 1.0 |
| 882 | 200 | 64.0 | 63.0 | M | 64.5 | 1 | 1.0 | 0.0 |
| 883 | 201 | 64.0 | 60.0 | M | 66.0 | 2 | 1.0 | 0.0 |
| 884 | 201 | 64.0 | 60.0 | F | 60.0 | 2 | 0.0 | 1.0 |
| 885 | 203 | 62.0 | 66.0 | M | 64.0 | 3 | 1.0 | 0.0 |
| 886 | 203 | 62.0 | 66.0 | F | 62.0 | 3 | 0.0 | 1.0 |
| 887 | 203 | 62.0 | 66.0 | F | 61.0 | 3 | 0.0 | 1.0 |
| 888 | 204 | 62.5 | 63.0 | M | 66.5 | 2 | 1.0 | 0.0 |
| 889 | 204 | 62.5 | 63.0 | F | 57.0 | 2 | 0.0 | 1.0 |
| 890 | 136A | 68.5 | 65.0 | M | 72.0 | 8 | 1.0 | 0.0 |
| 891 | 136A | 68.5 | 65.0 | M | 70.5 | 8 | 1.0 | 0.0 |
| 892 | 136A | 68.5 | 65.0 | M | 68.7 | 8 | 1.0 | 0.0 |
| 893 | 136A | 68.5 | 65.0 | M | 68.5 | 8 | 1.0 | 0.0 |
| 894 | 136A | 68.5 | 65.0 | M | 67.7 | 8 | 1.0 | 0.0 |
| 895 | 136A | 68.5 | 65.0 | F | 64.0 | 8 | 0.0 | 1.0 |
| 896 | 136A | 68.5 | 65.0 | F | 63.5 | 8 | 0.0 | 1.0 |
| 897 | 136A | 68.5 | 65.0 | F | 63.0 | 8 | 0.0 | 1.0 |
With a quick look into the family column we find that the number 202 is missing from the count and, as a bonus, there appears to be a strange value “136A” in the end of our dataframe.
# What is the data type of each column? (numerical or character values?)
data.dtypes
family object
father float64
mother float64
gender object
height float64
kids int64
male float64
female float64
dtype: object
We notice that each of the height columns : “father”, “mother”, “height”(of the adult children) are float64, a.k.a. floating-point number. For the column: “kid” as it indicates the number of children it should be obviously an integer.
Let´s look now at the remaining two culumns with the data type: “object”.
First, the column “gender” is supposed to have one of two values: F for female, or M for male. let´s make sure that it is the case.
data["gender"].unique()
array(['M', 'F'], dtype=object)
Out of curiosity, we want to see the number of both of them.
data["gender"].value_counts()
M 465
F 433
Name: gender, dtype: int64
Data like gender, blood types, ratings are usually called a categorical data, because they can take on only a limited, and usually fixed, number of possible values. So, we can convert it from “object” data type to “category”.
data["gender"] = data["gender"].astype("category")
data["gender"].unique()
[M, F]
Categories (2, object): [M, F]
The last one is “family”. This is the column which can be considered as a family identification number. It should be an integer as well. But, since it has the “136A” value, it was given the type “object”.
We want to check this column for any missing values:
familynb = list(data.family.unique())
seq = map(str, range(1,206))
set(seq) - set(familynb)
{'111', '120', '13', '161', '189', '202', '205', '50', '84'}
Well, we can see that we have 9 values missing. If we include the “136A” family, we should have data for 197, which is the number we found earlier.
We will see if the “136A” is the same as the family number 136, to verify it was not a mistyping case.
data.loc[data["family"] == "136"]
| family | father | mother | gender | height | kids | male | female | |
|---|---|---|---|---|---|---|---|---|
| 588 | 136 | 68.0 | 64.0 | M | 71.0 | 10 | 1.0 | 0.0 |
| 589 | 136 | 68.0 | 64.0 | M | 68.0 | 10 | 1.0 | 0.0 |
| 590 | 136 | 68.0 | 64.0 | M | 68.0 | 10 | 1.0 | 0.0 |
| 591 | 136 | 68.0 | 64.0 | M | 67.0 | 10 | 1.0 | 0.0 |
| 592 | 136 | 68.0 | 64.0 | F | 65.0 | 10 | 0.0 | 1.0 |
| 593 | 136 | 68.0 | 64.0 | F | 64.0 | 10 | 0.0 | 1.0 |
| 594 | 136 | 68.0 | 64.0 | F | 63.0 | 10 | 0.0 | 1.0 |
| 595 | 136 | 68.0 | 64.0 | F | 63.0 | 10 | 0.0 | 1.0 |
| 596 | 136 | 68.0 | 64.0 | F | 62.0 | 10 | 0.0 | 1.0 |
| 597 | 136 | 68.0 | 64.0 | F | 61.0 | 10 | 0.0 | 1.0 |
data.loc[data["family"] == "136A"]
| family | father | mother | gender | height | kids | male | female | |
|---|---|---|---|---|---|---|---|---|
| 890 | 136A | 68.5 | 65.0 | M | 72.0 | 8 | 1.0 | 0.0 |
| 891 | 136A | 68.5 | 65.0 | M | 70.5 | 8 | 1.0 | 0.0 |
| 892 | 136A | 68.5 | 65.0 | M | 68.7 | 8 | 1.0 | 0.0 |
| 893 | 136A | 68.5 | 65.0 | M | 68.5 | 8 | 1.0 | 0.0 |
| 894 | 136A | 68.5 | 65.0 | M | 67.7 | 8 | 1.0 | 0.0 |
| 895 | 136A | 68.5 | 65.0 | F | 64.0 | 8 | 0.0 | 1.0 |
| 896 | 136A | 68.5 | 65.0 | F | 63.5 | 8 | 0.0 | 1.0 |
| 897 | 136A | 68.5 | 65.0 | F | 63.0 | 8 | 0.0 | 1.0 |
Well, we can clearly see that there are two different families. it might be a good idea to replce the “136A” with an acual number. We can choose the number “205” since it is next on the list.
data.loc[data["family"] == "136A","family"] = "205"
We can check the modification to be sure everything is in order
data.loc[data["family"] == "205"]
| family | father | mother | gender | height | kids | male | female | |
|---|---|---|---|---|---|---|---|---|
| 890 | 205 | 68.5 | 65.0 | M | 72.0 | 8 | 1.0 | 0.0 |
| 891 | 205 | 68.5 | 65.0 | M | 70.5 | 8 | 1.0 | 0.0 |
| 892 | 205 | 68.5 | 65.0 | M | 68.7 | 8 | 1.0 | 0.0 |
| 893 | 205 | 68.5 | 65.0 | M | 68.5 | 8 | 1.0 | 0.0 |
| 894 | 205 | 68.5 | 65.0 | M | 67.7 | 8 | 1.0 | 0.0 |
| 895 | 205 | 68.5 | 65.0 | F | 64.0 | 8 | 0.0 | 1.0 |
| 896 | 205 | 68.5 | 65.0 | F | 63.5 | 8 | 0.0 | 1.0 |
| 897 | 205 | 68.5 | 65.0 | F | 63.0 | 8 | 0.0 | 1.0 |
Now that we are sure every value in “family” is numeric, we can convert it to be so, as follows:
data["family"] = data["family"].astype("int")
# We can check data types again
data.dtypes
family int64
father float64
mother float64
gender category
height float64
kids int64
male float64
female float64
dtype: object
As a last step, we should make sure there is no missing data a.k.a. NaN value. this can be done using the following code:
data.isnull().values.any()
False