Pandas - EDA Case Study - 7 Days of Pandas
Tags: python, beginners, datascience
Published: 2022-12-27 10:25:26

Welcome to the seventh (and final) article in the "7 Days of Pandas" series where we cover the `pandas` library in Python which is used for data manipulation.

In the [first article](https://dev.to/piyushraj/pandas-read-and-write-data-from-csv-files-7-days-of-pandas-1o4f) of the series, we looked at how to read and write CSV files with Pandas. In this tutorial, we will look at some of the most common operations that we perform on a dataframe in Pandas.  

In the [second article](https://dev.to/piyushraj/pandas-basic-data-manipulation-7-days-of-pandas-4c47), we looked at how to perform basic data manipulation.   

In the [third article](https://dev.to/piyushraj/pandas-basic-exploratory-data-analysis-7-days-of-pandas-3816), we looked at how to perform EDA (exploratory data analysis) with Pandas.  

In the [fourth article](https://dev.to/piyushraj/pandas-handling-missing-values-7-days-of-pandas-a16), we looked at how to handle missing values in a dataframe.

In the [fifth article](https://dev.to/piyushraj/pandas-aggregating-and-grouping-data-7-days-of-pandas-p60) we looked at how to aggregate and group data in Pandas.

In the [sixth article]() we looked at how to visualize the data in a pandas dataframe.

In this tutorial, we will look apply the methods learned so far in a case-study. We'll be working with a [demo assignment](https://www.kaggle.com/code/kashnitsky/a1-demo-pandas-and-uci-adult-dataset/notebook) on performing EDA from the open source [mlcourse.ai](https://mlcourse.ai/book/index.html) project.

## The Task

In this task you should use Pandas to answer a few questions about the [Adult dataset](https://archive.ics.uci.edu/ml/datasets/Adult).

Unique values of all features (for more information, please see the links above):
- `age`: continuous.
- `workclass`: Private, Self-emp-not-inc, Self-emp-inc, Federal-gov, Local-gov, State-gov, Without-pay, Never-worked.
- `fnlwgt`: continuous.
- `education`: Bachelors, Some-college, 11th, HS-grad, Prof-school, Assoc-acdm, Assoc-voc, 9th, 7th-8th, 12th, Masters, 1st-4th, 10th, Doctorate, 5th-6th, Preschool.
- `education-num`: continuous.
- `marital-status`: Married-civ-spouse, Divorced, Never-married, Separated, Widowed, Married-spouse-absent, Married-AF-spouse.
- `occupation`: Tech-support, Craft-repair, Other-service, Sales, Exec-managerial, Prof-specialty, Handlers-cleaners, Machine-op-inspct, Adm-clerical, Farming-fishing, Transport-moving, Priv-house-serv, Protective-serv, Armed-Forces.
- `relationship`: Wife, Own-child, Husband, Not-in-family, Other-relative, Unmarried.
- `race`: White, Asian-Pac-Islander, Amer-Indian-Eskimo, Other, Black.
- `sex`: Female, Male.
- `capital-gain`: continuous.
- `capital-loss`: continuous.
- `hours-per-week`: continuous.
- `native-country`: United-States, Cambodia, England, Puerto-Rico, Canada, Germany, Outlying-US(Guam-USVI-etc), India, Japan, Greece, South, China, Cuba, Iran, Honduras, Philippines, Italy, Poland, Jamaica, Vietnam, Mexico, Portugal, Ireland, France, Dominican-Republic, Laos, Ecuador, Taiwan, Haiti, Columbia, Hungary, Guatemala, Nicaragua, Scotland, Thailand, Yugoslavia, El-Salvador, Trinadad&Tobago, Peru, Hong, Holand-Netherlands.   
- `salary`: >50K,<=50K

Let's now read the data as a dataframe.


```python
import pandas as pd

# read data from csv file
df = pd.read_csv("adult.data.csv")
# display the first five rows
df.head()
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>age</th>
      <th>workclass</th>
      <th>fnlwgt</th>
      <th>education</th>
      <th>education-num</th>
      <th>marital-status</th>
      <th>occupation</th>
      <th>relationship</th>
      <th>race</th>
      <th>sex</th>
      <th>capital-gain</th>
      <th>capital-loss</th>
      <th>hours-per-week</th>
      <th>native-country</th>
      <th>salary</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>39</td>
      <td>State-gov</td>
      <td>77516</td>
      <td>Bachelors</td>
      <td>13</td>
      <td>Never-married</td>
      <td>Adm-clerical</td>
      <td>Not-in-family</td>
      <td>White</td>
      <td>Male</td>
      <td>2174</td>
      <td>0</td>
      <td>40</td>
      <td>United-States</td>
      <td>&lt;=50K</td>
    </tr>
    <tr>
      <th>1</th>
      <td>50</td>
      <td>Self-emp-not-inc</td>
      <td>83311</td>
      <td>Bachelors</td>
      <td>13</td>
      <td>Married-civ-spouse</td>
      <td>Exec-managerial</td>
      <td>Husband</td>
      <td>White</td>
      <td>Male</td>
      <td>0</td>
      <td>0</td>
      <td>13</td>
      <td>United-States</td>
      <td>&lt;=50K</td>
    </tr>
    <tr>
      <th>2</th>
      <td>38</td>
      <td>Private</td>
      <td>215646</td>
      <td>HS-grad</td>
      <td>9</td>
      <td>Divorced</td>
      <td>Handlers-cleaners</td>
      <td>Not-in-family</td>
      <td>White</td>
      <td>Male</td>
      <td>0</td>
      <td>0</td>
      <td>40</td>
      <td>United-States</td>
      <td>&lt;=50K</td>
    </tr>
    <tr>
      <th>3</th>
      <td>53</td>
      <td>Private</td>
      <td>234721</td>
      <td>11th</td>
      <td>7</td>
      <td>Married-civ-spouse</td>
      <td>Handlers-cleaners</td>
      <td>Husband</td>
      <td>Black</td>
      <td>Male</td>
      <td>0</td>
      <td>0</td>
      <td>40</td>
      <td>United-States</td>
      <td>&lt;=50K</td>
    </tr>
    <tr>
      <th>4</th>
      <td>28</td>
      <td>Private</td>
      <td>338409</td>
      <td>Bachelors</td>
      <td>13</td>
      <td>Married-civ-spouse</td>
      <td>Prof-specialty</td>
      <td>Wife</td>
      <td>Black</td>
      <td>Female</td>
      <td>0</td>
      <td>0</td>
      <td>40</td>
      <td>Cuba</td>
      <td>&lt;=50K</td>
    </tr>
  </tbody>
</table>
</div>



**1. How many men and women (sex feature) are represented in this dataset?**


```python
# we need to get the value counts in the "sex" column
df["sex"].value_counts()
```




    Male      21790
    Female    10771
    Name: sex, dtype: int64



**2. What is the average age (age feature) of women?**


```python
# filter for women and then get their average age
df[df["sex"]=="Female"]["age"].mean()
```




    36.85823043357163



**3. What is the percentage of German citizens (native-country feature)?**


```python
# find the number of German citizens and divide that by the total population
len(df[df["native-country"]=="Germany"])/len(df) * 100
```




    0.42074874850281013



Only 0.42%

**4-5. What are the mean and standard deviation of age for those who earn more than 50K per year (salary feature) and those who earn less than 50K per year?**


```python
# group on salary and then calculate the mean and std for the age
df.groupby(by="salary")["age"].agg(['mean', 'std'])
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>mean</th>
      <th>std</th>
    </tr>
    <tr>
      <th>salary</th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>&lt;=50K</th>
      <td>36.783738</td>
      <td>14.020088</td>
    </tr>
    <tr>
      <th>&gt;50K</th>
      <td>44.249841</td>
      <td>10.519028</td>
    </tr>
  </tbody>
</table>
</div>



**6. Is it true that people who earn more than 50K have at least high school education? (education – Bachelors, Prof-school, Assoc-acdm, Assoc-voc, Masters or Doctorate feature)**


```python
# filter the dataframe for >50k and see the distribution of education
df[df["salary"] == ">50K"]["education"].value_counts()
```




    Bachelors       2221
    HS-grad         1675
    Some-college    1387
    Masters          959
    Prof-school      423
    Assoc-voc        361
    Doctorate        306
    Assoc-acdm       265
    10th              62
    11th              60
    7th-8th           40
    12th              33
    9th               27
    5th-6th           16
    1st-4th            6
    Name: education, dtype: int64



No, we can see that there are individuals with less than high-school education in the >50K bucket.

**7. Display age statistics for each race (race feature) and each gender (sex feature). Use groupby() and describe(). Find the maximum age of men of Amer-Indian-Eskimo race.**


```python
# for each race
df.groupby(by="race")["age"].describe()
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
    </tr>
    <tr>
      <th>race</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Amer-Indian-Eskimo</th>
      <td>311.0</td>
      <td>37.173633</td>
      <td>12.447130</td>
      <td>17.0</td>
      <td>28.0</td>
      <td>35.0</td>
      <td>45.5</td>
      <td>82.0</td>
    </tr>
    <tr>
      <th>Asian-Pac-Islander</th>
      <td>1039.0</td>
      <td>37.746872</td>
      <td>12.825133</td>
      <td>17.0</td>
      <td>28.0</td>
      <td>36.0</td>
      <td>45.0</td>
      <td>90.0</td>
    </tr>
    <tr>
      <th>Black</th>
      <td>3124.0</td>
      <td>37.767926</td>
      <td>12.759290</td>
      <td>17.0</td>
      <td>28.0</td>
      <td>36.0</td>
      <td>46.0</td>
      <td>90.0</td>
    </tr>
    <tr>
      <th>Other</th>
      <td>271.0</td>
      <td>33.457565</td>
      <td>11.538865</td>
      <td>17.0</td>
      <td>25.0</td>
      <td>31.0</td>
      <td>41.0</td>
      <td>77.0</td>
    </tr>
    <tr>
      <th>White</th>
      <td>27816.0</td>
      <td>38.769881</td>
      <td>13.782306</td>
      <td>17.0</td>
      <td>28.0</td>
      <td>37.0</td>
      <td>48.0</td>
      <td>90.0</td>
    </tr>
  </tbody>
</table>
</div>




```python
# for each gender
df.groupby(by="sex")["age"].describe()
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
    </tr>
    <tr>
      <th>sex</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Female</th>
      <td>10771.0</td>
      <td>36.858230</td>
      <td>14.013697</td>
      <td>17.0</td>
      <td>25.0</td>
      <td>35.0</td>
      <td>46.0</td>
      <td>90.0</td>
    </tr>
    <tr>
      <th>Male</th>
      <td>21790.0</td>
      <td>39.433547</td>
      <td>13.370630</td>
      <td>17.0</td>
      <td>29.0</td>
      <td>38.0</td>
      <td>48.0</td>
      <td>90.0</td>
    </tr>
  </tbody>
</table>
</div>




```python
# for each race and gender
df.groupby(by=["race", "sex"])["age"].describe()
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th></th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
    </tr>
    <tr>
      <th>race</th>
      <th>sex</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th rowspan="2" valign="top">Amer-Indian-Eskimo</th>
      <th>Female</th>
      <td>119.0</td>
      <td>37.117647</td>
      <td>13.114991</td>
      <td>17.0</td>
      <td>27.0</td>
      <td>36.0</td>
      <td>46.00</td>
      <td>80.0</td>
    </tr>
    <tr>
      <th>Male</th>
      <td>192.0</td>
      <td>37.208333</td>
      <td>12.049563</td>
      <td>17.0</td>
      <td>28.0</td>
      <td>35.0</td>
      <td>45.00</td>
      <td>82.0</td>
    </tr>
    <tr>
      <th rowspan="2" valign="top">Asian-Pac-Islander</th>
      <th>Female</th>
      <td>346.0</td>
      <td>35.089595</td>
      <td>12.300845</td>
      <td>17.0</td>
      <td>25.0</td>
      <td>33.0</td>
      <td>43.75</td>
      <td>75.0</td>
    </tr>
    <tr>
      <th>Male</th>
      <td>693.0</td>
      <td>39.073593</td>
      <td>12.883944</td>
      <td>18.0</td>
      <td>29.0</td>
      <td>37.0</td>
      <td>46.00</td>
      <td>90.0</td>
    </tr>
    <tr>
      <th rowspan="2" valign="top">Black</th>
      <th>Female</th>
      <td>1555.0</td>
      <td>37.854019</td>
      <td>12.637197</td>
      <td>17.0</td>
      <td>28.0</td>
      <td>37.0</td>
      <td>46.00</td>
      <td>90.0</td>
    </tr>
    <tr>
      <th>Male</th>
      <td>1569.0</td>
      <td>37.682600</td>
      <td>12.882612</td>
      <td>17.0</td>
      <td>27.0</td>
      <td>36.0</td>
      <td>46.00</td>
      <td>90.0</td>
    </tr>
    <tr>
      <th rowspan="2" valign="top">Other</th>
      <th>Female</th>
      <td>109.0</td>
      <td>31.678899</td>
      <td>11.631599</td>
      <td>17.0</td>
      <td>23.0</td>
      <td>29.0</td>
      <td>39.00</td>
      <td>74.0</td>
    </tr>
    <tr>
      <th>Male</th>
      <td>162.0</td>
      <td>34.654321</td>
      <td>11.355531</td>
      <td>17.0</td>
      <td>26.0</td>
      <td>32.0</td>
      <td>42.00</td>
      <td>77.0</td>
    </tr>
    <tr>
      <th rowspan="2" valign="top">White</th>
      <th>Female</th>
      <td>8642.0</td>
      <td>36.811618</td>
      <td>14.329093</td>
      <td>17.0</td>
      <td>25.0</td>
      <td>35.0</td>
      <td>46.00</td>
      <td>90.0</td>
    </tr>
    <tr>
      <th>Male</th>
      <td>19174.0</td>
      <td>39.652498</td>
      <td>13.436029</td>
      <td>17.0</td>
      <td>29.0</td>
      <td>38.0</td>
      <td>49.00</td>
      <td>90.0</td>
    </tr>
  </tbody>
</table>
</div>



**8. Among whom is the proportion of those who earn a lot (>50K) greater: married or single men (marital-status feature)? Consider as married those who have a marital-status starting with Married (Married-civ-spouse, Married-spouse-absent or Married-AF-spouse), the rest are considered bachelors.**


```python
# add a new column "is-married"
df["is-married"] = df["marital-status"].str.startswith("Married")

# display the dataframe
df.head()
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>age</th>
      <th>workclass</th>
      <th>fnlwgt</th>
      <th>education</th>
      <th>education-num</th>
      <th>marital-status</th>
      <th>occupation</th>
      <th>relationship</th>
      <th>race</th>
      <th>sex</th>
      <th>capital-gain</th>
      <th>capital-loss</th>
      <th>hours-per-week</th>
      <th>native-country</th>
      <th>salary</th>
      <th>is-married</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>39</td>
      <td>State-gov</td>
      <td>77516</td>
      <td>Bachelors</td>
      <td>13</td>
      <td>Never-married</td>
      <td>Adm-clerical</td>
      <td>Not-in-family</td>
      <td>White</td>
      <td>Male</td>
      <td>2174</td>
      <td>0</td>
      <td>40</td>
      <td>United-States</td>
      <td>&lt;=50K</td>
      <td>False</td>
    </tr>
    <tr>
      <th>1</th>
      <td>50</td>
      <td>Self-emp-not-inc</td>
      <td>83311</td>
      <td>Bachelors</td>
      <td>13</td>
      <td>Married-civ-spouse</td>
      <td>Exec-managerial</td>
      <td>Husband</td>
      <td>White</td>
      <td>Male</td>
      <td>0</td>
      <td>0</td>
      <td>13</td>
      <td>United-States</td>
      <td>&lt;=50K</td>
      <td>True</td>
    </tr>
    <tr>
      <th>2</th>
      <td>38</td>
      <td>Private</td>
      <td>215646</td>
      <td>HS-grad</td>
      <td>9</td>
      <td>Divorced</td>
      <td>Handlers-cleaners</td>
      <td>Not-in-family</td>
      <td>White</td>
      <td>Male</td>
      <td>0</td>
      <td>0</td>
      <td>40</td>
      <td>United-States</td>
      <td>&lt;=50K</td>
      <td>False</td>
    </tr>
    <tr>
      <th>3</th>
      <td>53</td>
      <td>Private</td>
      <td>234721</td>
      <td>11th</td>
      <td>7</td>
      <td>Married-civ-spouse</td>
      <td>Handlers-cleaners</td>
      <td>Husband</td>
      <td>Black</td>
      <td>Male</td>
      <td>0</td>
      <td>0</td>
      <td>40</td>
      <td>United-States</td>
      <td>&lt;=50K</td>
      <td>True</td>
    </tr>
    <tr>
      <th>4</th>
      <td>28</td>
      <td>Private</td>
      <td>338409</td>
      <td>Bachelors</td>
      <td>13</td>
      <td>Married-civ-spouse</td>
      <td>Prof-specialty</td>
      <td>Wife</td>
      <td>Black</td>
      <td>Female</td>
      <td>0</td>
      <td>0</td>
      <td>40</td>
      <td>Cuba</td>
      <td>&lt;=50K</td>
      <td>True</td>
    </tr>
  </tbody>
</table>
</div>




```python
df.groupby(by=["is-married"])["salary"].value_counts(normalize=True)
```




    is-married  salary
    False       <=50K     0.935546
                >50K      0.064454
    True        <=50K     0.563080
                >50K      0.436920
    Name: salary, dtype: float64



We can see that amongst Married people, we have a higher proportion of people with salary >50K

**9. What is the maximum number of hours a person works per week (hours-per-week feature)? How many people work such a number of hours, and what is the percentage of those who earn a lot (>50K) among them?**


```python
# max number of hourse a person works per week
df["hours-per-week"].max()
```




    99




```python
# how many people work the above maximum number of hourse
len(df[df["hours-per-week"] == df["hours-per-week"].max()])
```




    85




```python
# percentage of people in the above population that earn more than 50K
df[df["hours-per-week"] == df["hours-per-week"].max()]['salary'].value_counts(normalize=True)
```




    <=50K    0.705882
    >50K     0.294118
    Name: salary, dtype: float64



Only 29%

**10. Count the average time of work (hours-per-week) for those who earn a little and a lot (salary) for each country (native-country). What will these be for Japan?**


```python
# group the data on native country and salary and find the average work time for each group
with pd.option_context('display.max_rows', None):
    print(df.groupby(by=["native-country", "salary"])["hours-per-week"].mean())
```

    native-country              salary
    ?                           <=50K     40.164760
                                >50K      45.547945
    Cambodia                    <=50K     41.416667
                                >50K      40.000000
    Canada                      <=50K     37.914634
                                >50K      45.641026
    China                       <=50K     37.381818
                                >50K      38.900000
    Columbia                    <=50K     38.684211
                                >50K      50.000000
    Cuba                        <=50K     37.985714
                                >50K      42.440000
    Dominican-Republic          <=50K     42.338235
                                >50K      47.000000
    Ecuador                     <=50K     38.041667
                                >50K      48.750000
    El-Salvador                 <=50K     36.030928
                                >50K      45.000000
    England                     <=50K     40.483333
                                >50K      44.533333
    France                      <=50K     41.058824
                                >50K      50.750000
    Germany                     <=50K     39.139785
                                >50K      44.977273
    Greece                      <=50K     41.809524
                                >50K      50.625000
    Guatemala                   <=50K     39.360656
                                >50K      36.666667
    Haiti                       <=50K     36.325000
                                >50K      42.750000
    Holand-Netherlands          <=50K     40.000000
    Honduras                    <=50K     34.333333
                                >50K      60.000000
    Hong                        <=50K     39.142857
                                >50K      45.000000
    Hungary                     <=50K     31.300000
                                >50K      50.000000
    India                       <=50K     38.233333
                                >50K      46.475000
    Iran                        <=50K     41.440000
                                >50K      47.500000
    Ireland                     <=50K     40.947368
                                >50K      48.000000
    Italy                       <=50K     39.625000
                                >50K      45.400000
    Jamaica                     <=50K     38.239437
                                >50K      41.100000
    Japan                       <=50K     41.000000
                                >50K      47.958333
    Laos                        <=50K     40.375000
                                >50K      40.000000
    Mexico                      <=50K     40.003279
                                >50K      46.575758
    Nicaragua                   <=50K     36.093750
                                >50K      37.500000
    Outlying-US(Guam-USVI-etc)  <=50K     41.857143
    Peru                        <=50K     35.068966
                                >50K      40.000000
    Philippines                 <=50K     38.065693
                                >50K      43.032787
    Poland                      <=50K     38.166667
                                >50K      39.000000
    Portugal                    <=50K     41.939394
                                >50K      41.500000
    Puerto-Rico                 <=50K     38.470588
                                >50K      39.416667
    Scotland                    <=50K     39.444444
                                >50K      46.666667
    South                       <=50K     40.156250
                                >50K      51.437500
    Taiwan                      <=50K     33.774194
                                >50K      46.800000
    Thailand                    <=50K     42.866667
                                >50K      58.333333
    Trinadad&Tobago             <=50K     37.058824
                                >50K      40.000000
    United-States               <=50K     38.799127
                                >50K      45.505369
    Vietnam                     <=50K     37.193548
                                >50K      39.200000
    Yugoslavia                  <=50K     41.600000
                                >50K      49.500000
    Name: hours-per-week, dtype: float64



```python
# for japan
df[df["native-country"]=="Japan"].groupby(by=["salary"])["hours-per-week"].mean()
```




    salary
    <=50K    41.000000
    >50K     47.958333
    Name: hours-per-week, dtype: float64