GitHub Repository: ine-rmotr-curriculum/FreeCodeCamp-Pandas-Real-Life-Example
Path: blob/master/Lecture_1.ipynb
¹⁴⁵ views

Kernel: Python 3

rmotr

Bike store sales

In this class we'll be analyzing sales made on bike stores.

Follow this data in a Google Spreadsheet

purple-divider

Hands on!

In [1]:

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

%matplotlib inline

green-divider

Loading our data:

In [2]:

!head data/sales_data.csv

Out[2]:

Date,Day,Month,Year,Customer_Age,Age_Group,Customer_Gender,Country,State,Product_Category,Sub_Category,Product,Order_Quantity,Unit_Cost,Unit_Price,Profit,Cost,Revenue
2013-11-26,26,November,2013,19,Youth (<25),M,Canada,British Columbia,Accessories,Bike Racks,Hitch Rack - 4-Bike,8,45,120,590,360,950
2015-11-26,26,November,2015,19,Youth (<25),M,Canada,British Columbia,Accessories,Bike Racks,Hitch Rack - 4-Bike,8,45,120,590,360,950
2014-03-23,23,March,2014,49,Adults (35-64),M,Australia,New South Wales,Accessories,Bike Racks,Hitch Rack - 4-Bike,23,45,120,1366,1035,2401
2016-03-23,23,March,2016,49,Adults (35-64),M,Australia,New South Wales,Accessories,Bike Racks,Hitch Rack - 4-Bike,20,45,120,1188,900,2088
2014-05-15,15,May,2014,47,Adults (35-64),F,Australia,New South Wales,Accessories,Bike Racks,Hitch Rack - 4-Bike,4,45,120,238,180,418
2016-05-15,15,May,2016,47,Adults (35-64),F,Australia,New South Wales,Accessories,Bike Racks,Hitch Rack - 4-Bike,5,45,120,297,225,522
2014-05-22,22,May,2014,47,Adults (35-64),F,Australia,Victoria,Accessories,Bike Racks,Hitch Rack - 4-Bike,4,45,120,199,180,379
2016-05-22,22,May,2016,47,Adults (35-64),F,Australia,Victoria,Accessories,Bike Racks,Hitch Rack - 4-Bike,2,45,120,100,90,190
2014-02-22,22,February,2014,35,Adults (35-64),M,Australia,Victoria,Accessories,Bike Racks,Hitch Rack - 4-Bike,22,45,120,1096,990,2086

In [3]:

sales = pd.read_csv(
    'data/sales_data.csv',
    parse_dates=['Date'])

green-divider

The data at a glance:

In [4]:

sales.head()

Out[4]:

In [5]:

sales.shape

Out[5]:

(113036, 18)

In [6]:

sales.info()

Out[6]:

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 113036 entries, 0 to 113035
Data columns (total 18 columns):
 #   Column            Non-Null Count   Dtype         
---  ------            --------------   -----         
 Date              113036 non-null  datetime64[ns]
 Day               113036 non-null  int64         
 Month             113036 non-null  object        
 Year              113036 non-null  int64         
 Customer_Age      113036 non-null  int64         
 Age_Group         113036 non-null  object        
 Customer_Gender   113036 non-null  object        
 Country           113036 non-null  object        
 State             113036 non-null  object        
 Product_Category  113036 non-null  object        
Sub_Category      113036 non-null  object        
Product           113036 non-null  object        
Order_Quantity    113036 non-null  int64         
Unit_Cost         113036 non-null  int64         
Unit_Price        113036 non-null  int64         
Profit            113036 non-null  int64         
Cost              113036 non-null  int64         
Revenue           113036 non-null  int64         
dtypes: datetime64[ns](1), int64(9), object(8)
memory usage: 15.5+ MB

In [7]:

sales.describe()

Out[7]:

green-divider

Numerical analysis and visualization

We'll analyze the Unit_Cost column:

In [8]:

sales['Unit_Cost'].describe()

Out[8]:

count    113036.000000
mean        267.296366
std         549.835483
min           1.000000
25%           2.000000
50%           9.000000
75%          42.000000
max        2171.000000
Name: Unit_Cost, dtype: float64

In [9]:

sales['Unit_Cost'].mean()

Out[9]:

267.296365759581

In [10]:

sales['Unit_Cost'].median()

Out[10]:

9.0

In [11]:

sales['Unit_Cost'].plot(kind='box', vert=False, figsize=(14,6))

Out[11]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f8af096b700>

In [12]:

sales['Unit_Cost'].plot(kind='density', figsize=(14,6)) # kde

Out[12]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f8af0bf0f70>

In [13]:

ax = sales['Unit_Cost'].plot(kind='density', figsize=(14,6)) # kde
ax.axvline(sales['Unit_Cost'].mean(), color='red')
ax.axvline(sales['Unit_Cost'].median(), color='green')

Out[13]:

<matplotlib.lines.Line2D at 0x7f8b17499250>

In [14]:

ax = sales['Unit_Cost'].plot(kind='hist', figsize=(14,6))
ax.set_ylabel('Number of Sales')
ax.set_xlabel('dollars')

Out[14]:

Text(0.5, 0, 'dollars')

green-divider

Categorical analysis and visualization

We'll analyze the Age_Group column:

In [16]:

sales.head()

Out[16]:

In [15]:

sales['Age_Group'].value_counts()

Out[15]:

Adults (35-64)          55824
Young Adults (25-34)    38654
Youth (<25)             17828
Seniors (64+)             730
Name: Age_Group, dtype: int64

In [17]:

sales['Age_Group'].value_counts().plot(kind='pie', figsize=(6,6))

Out[17]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f8ade8bc2e0>

In [18]:

ax = sales['Age_Group'].value_counts().plot(kind='bar', figsize=(14,6))
ax.set_ylabel('Number of Sales')

Out[18]:

Text(0, 0.5, 'Number of Sales')

green-divider

Relationship between the columns?

Can we find any significant relationship?

In [19]:

corr = sales.corr()

corr

Out[19]:

In [20]:

fig = plt.figure(figsize=(8,8))
plt.matshow(corr, cmap='RdBu', fignum=fig.number)
plt.xticks(range(len(corr.columns)), corr.columns, rotation='vertical');
plt.yticks(range(len(corr.columns)), corr.columns);

Out[20]:

In [21]:

sales.plot(kind='scatter', x='Customer_Age', y='Revenue', figsize=(6,6))

Out[21]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f8ade7c34f0>

In [22]:

sales.plot(kind='scatter', x='Revenue', y='Profit', figsize=(6,6))

Out[22]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f8ade7a70a0>

In [23]:

ax = sales[['Profit', 'Age_Group']].boxplot(by='Age_Group', figsize=(10,6))
ax.set_ylabel('Profit')

Out[23]:

Text(0, 0.5, 'Profit')

In [24]:

boxplot_cols = ['Year', 'Customer_Age', 'Order_Quantity', 'Unit_Cost', 'Unit_Price', 'Profit']

sales[boxplot_cols].plot(kind='box', subplots=True, layout=(2,3), figsize=(14,8))

Out[24]:

Year                 AxesSubplot(0.125,0.536818;0.227941x0.343182)
Customer_Age      AxesSubplot(0.398529,0.536818;0.227941x0.343182)
Order_Quantity    AxesSubplot(0.672059,0.536818;0.227941x0.343182)
Unit_Cost               AxesSubplot(0.125,0.125;0.227941x0.343182)
Unit_Price           AxesSubplot(0.398529,0.125;0.227941x0.343182)
Profit               AxesSubplot(0.672059,0.125;0.227941x0.343182)
dtype: object

green-divider

Column wrangling

We can also create new columns or modify existing ones.

Add and calculate a new `Revenue_per_Age` column

In [25]:

sales['Revenue_per_Age'] = sales['Revenue'] / sales['Customer_Age']

sales['Revenue_per_Age'].head()

Out[25]:

  50.000000
  50.000000
  49.000000
  42.612245
   8.893617
Name: Revenue_per_Age, dtype: float64

In [26]:

sales['Revenue_per_Age'].plot(kind='density', figsize=(14,6))

Out[26]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f8ade499d90>

In [27]:

sales['Revenue_per_Age'].plot(kind='hist', figsize=(14,6))

Out[27]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f8ade3f9100>

Add and calculate a new `Calculated_Cost` column

Use this formula

Calculated\_Cost = Order\_Quantity * Unit\_Cost

In [28]:

sales['Calculated_Cost'] = sales['Order_Quantity'] * sales['Unit_Cost']

sales['Calculated_Cost'].head()

Out[28]:

   360
   360
  1035
   900
   180
Name: Calculated_Cost, dtype: int64

In [29]:

(sales['Calculated_Cost'] != sales['Cost']).sum()

Out[29]:

0

We can see the relationship between Cost and Profit using a scatter plot:

In [30]:

sales.plot(kind='scatter', x='Calculated_Cost', y='Profit', figsize=(6,6))

Out[30]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f8ade3df790>

Add and calculate a new `Calculated_Revenue` column

Use this formula

Calculated\_Revenue = Cost + Profit

In [31]:

sales['Calculated_Revenue'] = sales['Cost'] + sales['Profit']

sales['Calculated_Revenue'].head()

Out[31]:

   950
   950
  2401
  2088
   418
Name: Calculated_Revenue, dtype: int64

In [32]:

(sales['Calculated_Revenue'] != sales['Revenue']).sum()

Out[32]:

0

In [33]:

sales.head()

Out[33]:

In [34]:

sales['Revenue'].plot(kind='hist', bins=100, figsize=(14,6))

Out[34]:

<matplotlib.axes._subplots.AxesSubplot at 0x7f8ade39afa0>

Modify all `Unit_Price` values adding 3% tax to them

In [35]:

sales['Unit_Price'].head()

Out[35]:

  120
  120
  120
  120
  120
Name: Unit_Price, dtype: int64

In [36]:

#sales['Unit_Price'] = sales['Unit_Price'] * 1.03

sales['Unit_Price'] *= 1.03

In [37]:

sales['Unit_Price'].head()

Out[37]:

  123.6
  123.6
  123.6
  123.6
  123.6
Name: Unit_Price, dtype: float64

green-divider

Selection & Indexing:

### Get all the sales made in the state of Kentucky

In [38]:

sales.loc[sales['State'] == 'Kentucky']

Out[38]:

Get the mean revenue of the `Adults (35-64)` sales group

In [39]:

sales.loc[sales['Age_Group'] == 'Adults (35-64)', 'Revenue'].mean()

Out[39]:

762.8287654055604

How many records belong to Age Group `Youth (<25)` or `Adults (35-64)`?

In [43]:

sales.loc[(sales['Age_Group'] == 'Youth (<25)') | (sales['Age_Group'] == 'Adults (35-64)')].shape[0]

Out[43]:

73652

Get the mean revenue of the sales group `Adults (35-64)` in `United States`

In [44]:

sales.loc[(sales['Age_Group'] == 'Adults (35-64)') & (sales['Country'] == 'United States'), 'Revenue'].mean()

Out[44]:

726.7260473588342

### Increase the revenue by 10% to every sale made in France

In [45]:

sales.loc[sales['Country'] == 'France', 'Revenue'].head()

Out[45]:

   787
   787
  2957
  2851
   626
Name: Revenue, dtype: int64

In [46]:

#sales.loc[sales['Country'] == 'France', 'Revenue'] = sales.loc[sales['Country'] == 'France', 'Revenue'] * 1.1

sales.loc[sales['Country'] == 'France', 'Revenue'] *= 1.1

In [47]:

sales.loc[sales['Country'] == 'France', 'Revenue'].head()

Out[47]:

   865.7
   865.7
  3252.7
  3136.1
   688.6
Name: Revenue, dtype: float64

purple-divider

Bike store sales

Hands on!

Loading our data:

The data at a glance:

Numerical analysis and visualization

Categorical analysis and visualization

Relationship between the columns?

Column wrangling

Add and calculate a new `Revenue_per_Age` column

Add and calculate a new `Calculated_Cost` column

Add and calculate a new `Calculated_Revenue` column

Modify all `Unit_Price` values adding 3% tax to them

Selection & Indexing:

Get the mean revenue of the `Adults (35-64)` sales group

How many records belong to Age Group `Youth (<25)` or `Adults (35-64)`?

Get the mean revenue of the sales group `Adults (35-64)` in `United States`

Product

Resources

Company

Bike store sales

Hands on!

Loading our data:

The data at a glance:

Numerical analysis and visualization

Categorical analysis and visualization

Relationship between the columns?

Column wrangling

Add and calculate a new Revenue_per_Age column

Add and calculate a new Calculated_Cost column

Add and calculate a new Calculated_Revenue column

Modify all Unit_Price values adding 3% tax to them

Selection & Indexing:

Get the mean revenue of the Adults (35-64) sales group

How many records belong to Age Group Youth (<25) or Adults (35-64)?

Get the mean revenue of the sales group Adults (35-64) in United States

Add and calculate a new `Revenue_per_Age` column

Add and calculate a new `Calculated_Cost` column

Add and calculate a new `Calculated_Revenue` column

Modify all `Unit_Price` values adding 3% tax to them

Get the mean revenue of the `Adults (35-64)` sales group

How many records belong to Age Group `Youth (<25)` or `Adults (35-64)`?

Get the mean revenue of the sales group `Adults (35-64)` in `United States`