[SOLVED] CS1656 Lab4-Data Analysis with Pandas

So far we have encountered basic data manipulation with pandas Dataframes including row and column selection, boolean indexing, working with missing values, groupby and aggregate functions such as mean().But there are many other powerful data manipulation and analysis techniques available in pandas. In this lab, we will learn some more advanced ways for data anylsis in Python using Dataframes.

Begin by importing pandas package.

In [1]: import pandas as pd

Next load the dataset that we will be playing arround with.

In [2]: df = pd.read_csv(‘http://data.cs1656.org/coffee-chain.csv’) df.head()

Out[2]:     Area Code Market        Market Size        Product Product Line \ 0                985 South Small Market Colombian          Beans 1 985 South Small Market Chamomile            Leaves

2 985 South Small Market Chamomile Leaves 3 985 South Small Market Decaf Irish Cream Beans

4                          985 South Small Market                                         Lemon                 Leaves

Product Type                  State                         Type Inventory Budget COGS Budget Margin \

Budget Profit Budget Sales COGS Margin Marketing Profit Sales \

Total Expenses

• 25
• 43
• 45
• 25
• 58

Let’s get the subset of the dataframe we need.

In [3]: df_small = df[[‘Area Code’,’Market’, ‘Market Size’, ‘Product’, ‘Product Line’, ‘Product df_small.head()

Out[3]:     Area Code Market        Market Size        Product Product Line \ 0                985 South Small Market Colombian          Beans 1 985 South Small Market Chamomile            Leaves

2          985 South Small Market Chamomile         Leaves 3               985 South Small Market Decaf Irish Cream         Beans

4                          985 South Small Market                                         Lemon                 Leaves

Product Type                  State                      Type Profit Total Expenses

1.3       Slicing & Indexing

What we saw above was slicing. Slicing uses the [] operator selects a set of rows and/or columns from a DataFrame. Slicing rows

To slice out a set of rows, you use the following syntax: data[start:stop]. When slicing in pandas the start bound is included in the output.

In [4]: df_small[0:3]

Out[4]:                Area Code Market           Market Size                     Product Product Line Product Type \

Slicing vs Copying

We might have thought that we were creating a fresh copy of df_small when we did slicing. However the statement y = x doesn’t create a copy of our DataFrame. It creates a new variable y that refers to the same object x refers to. This means that there is only one object (the DataFrame), and both x and y refer to it. To create a fresh copy of the DataFrame you can use the syntax y=x.copy(). We will see the effect of slicing but not copying in later steps.

** Indexing **

We can select specific ranges of our data in both the row and column directions using either label or integer-based indexing.

• loc: indexing via labels or integers or mixed
• iloc: indexing via integers only

To select a subset of rows AND columns from our DataFrame, we can use the iloc method. For example,

In [5]: df_small.loc[0:3, ‘Market’: ‘Product’]

Out[5]: Market Market Size Product 0 South Small Market Colombian

• South Small Market Chamomile
• South Small Market Chamomile
• South Small Market Decaf Irish Cream In [6]: df_small.iloc[0:4, 1:4]

Out[6]: Market Market Size Product 0 South Small Market Colombian

• South Small Market Chamomile
• South Small Market Chamomile 3 South Small Market Decaf Irish Cream

Notice that indexing in loc is inclusive whereas indexing in iloc is exlusive of the end index

1.4      Statistical Technqiues

1.4.1      Cross-tabulation

Cross tabultaion computes a frequency table of two or more factors. Let’s start by making a crosstab with two variables first.

In [7]: df_crosstab = pd.crosstab(df_small[“Market”],df_small[“Market Size”],margins=True) df_crosstab

Out[7]: Market Size Major Market Small Market All

Market

Central 696 648 1344 East 552 336 888 South 168 504 672 West 288 1056 1344

All                                              1704                            2544 4248

Let’c check the type of the cross-tab

In [8]: type(df_crosstab)

Out[8]: pandas.core.frame.DataFrame

Now let’s check the value counts of one of our cross-tab’s dimensions and see if the totals match?

In [9]: pd.value_counts(df_small[‘Market Size’])

Out[9]: Small Market      2544 Major Market             1704

Name: Market Size, dtype: int64

Now let’s make a cross-tab with three variables.

In [10]: pd.crosstab(df[“Product Type”], [df[“Market”],df[“Market Size”]],margins=True)

Out[10]: Market                                     Central                                                          East                                                        South \

Market Size Major Market Small Market Major Market Small Market Major Market Product Type

1.4.2       Binning Data

We can bin our data into categorirs by specifying bin widths. Let’s define equal width bins as shown below. The bins array specifies 4 bins from -800 to -400, -400 to 0, 0 to 400, 400 to 800. We will also specify a group names to assign as labels to each of our bins later.

In [11]: bins = [-800,-400, 0, 400, 800] group_names = [‘Low’, ‘Okay’, ‘Good’, ‘Great’]

Now lets bin the data into the categories and add it as a column to the dataframe

In [12]: df_small[‘Categories’] = pd.cut(df_small[‘Profit’], bins=bins, labels=group_names) df_small.head(20)

Out[12]:      Area Code Market Market Size             Product Product Line \ 0                985 South Small Market               Colombian          Beans 1 985 South Small Market Chamomile          Leaves

2       985 South Small Market Chamomile         Leaves 3               985 South Small Market Decaf Irish Cream      Beans 4 985 South Small Market Lemon  Leaves

• 985 South Small Market Decaf Irish Cream Beans
• 985 South Small Market Lemon Leaves 7               985 South Small Market Chamomile          Leaves
• 985 South Small Market Caffe Mocha Beans
• 985 South Small Market Caffe Latte Beans 10 985 South Small Market Caffe Latte Beans 11 985 South Small Market Decaf Irish Cream Beans 12 985 South Small Market Decaf Espresso Beans 13 985 South Small Market Lemon Leaves

14 985 South Small Market Decaf Espresso Beans 15 985 South Small Market Lemon Leaves 16 985 South Small Market Caffe Mocha Beans

• 985 South Small Market Caffe Latte Beans
• 985 South Small Market Caffe Mocha Beans
• 985 South Small Market Decaf Espresso Beans

Product Type                  State                         Type Profit Total Expenses Categories

To find out the value counts for each bin of category, we can use value_counts like we did earlier.

In [13]: pd.value_counts(df_small[‘Categories’])

Out[13]: Good                 3648

Okay               544

Great                40

Low                    16

Name: Categories, dtype: int64

1.4.3      Quantiles

Pandas allows an easy way of computing percentiles or quartiles. Let’s first specify the quantiles we want to calculate,

In [14]: quants = [0.0, 0.05, 0.25, 0.5, 0.75, 0.95, 1.0]

To compute the quantiles of Profit and Total Expenses,

In [15]: q = df_small[[‘Profit’,’Total Expenses’]].quantile(quants) q

Out[15]:                           Profit Total Expenses

1.4.4        Groupby & Apply

Groupby allows grouping or clustering the dataframe by a particular categorical attribute. Apply can be used to apply a function to a group or the entire dataframe. Let’s first define the function that we want to apply,

In [16]: def get_stats(group): return {‘min’: group.min(), ‘max’: group.max(), ‘count’: group.count(), ‘mean’: gro

This can be applied to a Dataframe or a grouping of the dataframe as shown below

In [17]: df_group = df_small[‘Profit’].groupby(df_small[‘Categories’]).apply(get_stats) df_group

sum                271829.000000

Great count 40.000000 max 778.000000 mean 517.650000 min 402.000000 sum 20706.000000

Name: Profit, dtype: float64

The width format of the output above can be fixed by using the unstack() function as shown below.

In [18]: df_group.unstack()

Out[18]:                                       count          max                   mean         min                 sum

Categories

Low 16.0 -404.0 -510.562500 -638.0 -8169.0 Okay      544.0     0.0 -45.630515 -392.0 -24823.0 Good       3648.0 397.0 74.514529         1.0 271829.0

Great                                40.0 778.0 517.650000 402.0                 20706.0

1.4.5      Sorting

Pandas allows nested sorting over mutliple columns of the Dataframe easily as shown below.

In [19]: data_sorted = df_small.sort_values([‘Total Expenses’, ‘Profit’], ascending=False) data_sorted[[‘Total Expenses’,’Profit’]].head(20)

Out[19]:                           Total Expenses Profit

1.5     Tasks

For your tasks, use the data file http://data.cs1656.org/bank-data.csv.

Task 1 Compute the mean income of males versus females.

Task 2 Create a cross-tab of save_acct and mortgage.

Task 3 Convert the frequencies in cross-tab to percentages. (Hint: use apply and indexing)