# Pandas¶

Pandas is a Python library for working with data. Pandas is a large library with many useful features. This lab is adapted from a similar lab from last quarter (CMSC 12100), with some additional exercises useful for PA #4.

## Getting started¶

To get started, if you are using your VM and have not already done so, open up a terminal window and please run:

```
sudo pip3 install pandas
```

to install the Pandas library on your VM. Instead, if you are on a CSIL computer, you should upgrade its pandas installation by running:

```
pip3 install --user --upgrade pandas
```

Once Pandas is installed, you should navigate (`cd`

) to your
`cs122-win-17-username`

directory. Run `git pull upstream master`

to collect the lab materials and `git pull`

to sync with your
personal repository.

## Reading the data¶

We will be using one of the sample data sets from an assignment from last quarter. Please look at the Data and MORG Files sections for get a quick overview of this data.

**Task 1**: Use `pd.read_csv`

to read the sample data into a pandas
dataframe and save the result in a variable named `morg_df`

. Use
`h_id`

, which uniquely identifies each row and as the row index.

## Basics¶

Read 10 Minutes to pandas for an overview of how to select items from a dataframe using simple and boolean indexing, do simple element-wise arithmetic, and deal with missing data.

**Task 2**: Extract the `"age"`

column from `morg_df`

.

**Task 3**: Extract the row that corresponds to `h_id`

1_2_2 from `morg_df`

.

**Task 4**: Use slicing to extract the first four rows of `morg_df`

.

**Task 5**: Use boolean indexing to extract all rows that correspond to a person who works 35 or more hours per week.

## Extracting strings¶

Read Working with Text Data for a description on the functions pandas provides to manipulate text data.

**Task 6**: Each `h_id`

values has three numbers separated by
underscores. Create a dataframe with two columns, with the first and
last of these numbers.

**Task 7**: Now use the pandas `strip()`

function to create a series
in which you have the substring left in `h_id`

if you remove the
first and last number.

## Concatenating objects¶

Read Concatenating objects on how to combine dataframes and series.

**Task 8**: Use the pandas `concat`

function to create a new
dataframe of three columns created by concatenating the dataframe and the series created in Task 6 and 7 above.

[The remaining tasks are from the original lab, and are optional. You should complete them if you feel rusty with pandas.]

## Categorical values¶

Categoricals are a pandas data type that is used to represent
variables which can take on only a limited, and usually fixed, number
of possible values. We saw a way to convert strings to categorical
values using `.astype`

in lecture. This method is less useful for
the Current Population Survey Data, because the categorical values
are encoded as small integers. We could work with these small integers
but it is much better to work with meaningful values.

The Pandas library function `pd.Categorical.from_codes`

allows you
to convert codes into meaningful categorical values. For example, we
could convert the `ethnicity_code`

(after having replaced the
`NaN`

values with zeros using the `fillna`

method) from an integer
encoding into more meaningful values using the following:

```
# convert NaN to 0
morg_df["ethnicity_code"] = morg_df["ethnicity_code"].fillna(0)
# convert codes to categorical values
ethnic_categories = ['Non-Hispanic', 'Mexican', 'PuertoRican',
'Cuban', 'Dominican',
'Salvadoran', 'CentralAmericanExcludingSalvadoran',
'SouthAmerican', 'OtherSpanish']
morg_df["ethnicity_code"] = pd.Categorical.from_codes(morg_df["ethnicity_code"], ethnic_categories)
```

Having done so, we would probably want to also change the name of the column:

```
morg_df.rename(columns={"ethnicity_code":"ethnicity"}, inplace=True)
```

to reflect the fact that it no longer contains integer codes.

**Task 9**: Convert the status code column, which has values that
range from 1 through 7 to categorical values and rename it “status”.

**Task 10**: Now that you have converted the status column into categorical values, use boolean indexing to extract the rows that correspond to the people who are not working.

**Task 11**: Use boolean indexing to extract the rows that correspond to people who worked at least 35 hours per week and have a status of “Working”.

## Converting floats to bins¶

On occasion, it is useful to group floating point values, such as ages or salaries, into bins. Pandas provides a function cut that serves this purpose.

Let’s look at some code for adding an “age bin” column to the Morg dataframe. But first, let’s look at the range of ages seen in our data:

```
In [19]: morg_df["age"].min()
Out[19]: 16
In [20]: morg_df["age"].max()
Out[20]: 85
```

We’d like to group the data into 10 bins. For this lab, we’ll use
`range(16, 89, 8)`

for this purpose. This approach is not ideal,
since the last bin includes four years that do not correspond to the
ages of any of the people in our dataset. For PA #6, we recommend
using Numpy’s linspace function, which allows you to get a list of
values equally spaced between a lower bound and an upper bound.

The bins need to be labelled. For simplicity, we’ll label the bins with integers. Keep in mind that we need \(N+1\) boundaries for \(N\) bins (and \(N\) labels).

Once we have defined the bin boundaries and the labels, we can use
`cut`

to compute a new column where the ith entry correponds to the
appropriate bin for the age of the person in the ith row of `morg_df`

:

```
boundaries = range(16, 89, 8)
morg_df["age_bin"] = pd.cut(morg_df["age"],
bins=boundaries,
labels=range(len(boundaries)-1),
include_lowest=True, right=False)
```

We use `include_lowest=True`

because we have at least one person who
is 16 and we want to include his age in a bin. We use
`right=False`

, because we do not want to include 89 in our last bin.

**Task 12**: Add a column to `morg_df`

for hours worked per week that assigns a person’s hours worked per week to one of ten buckets, evenly spaced from the fewest number of hours worked (0) to the most (99) inclusive.

## Counting¶

Counting the number of occurrences of different values is a very
common operation. We saw two ways to do this task in class: using
`value_counts`

and `groupby`

plus `size`

:

```
In [59]: morg_df["age_bin"].value_counts()
Out[59]:
3 41893
4 40857
0 39945
2 37416
1 37368
5 32701
6 23293
7 16891
8 13656
dtype: int64
In [60]: morg_df.groupby("age_bin").size()
Out[60]:
age_bin
0 39945
1 37368
2 37416
3 41893
4 40857
5 32701
6 23293
7 16891
8 13656
dtype: int64
```

**Task 13**: Use these methods to count the number of people in each of your hours-worked-per-week bins.

Note that you can get the values sorted by bin number, for either approach,
using the `sort_index`

method:

```
In [63]: morg_df["age_bin"].value_counts().sort_index()
Out[63]:
0 39945
1 37368
2 37416
3 41893
4 40857
5 32701
6 23293
7 16891
8 13656
dtype: int64
In [62]: morg_df.groupby("age_bin").size().sort_index()
Out[62]:
age_bin
0 39945
1 37368
2 37416
3 41893
4 40857
5 32701
6 23293
7 16891
8 13656
dtype: int64
```