#Teaching pandas to MBA students

### Import standard libraries
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

### Setup
sns.set(rc={'figure.figsize':(12,6)})

### Read source file
df = pd.read_excel("Online Retail.xlsx")

### Change column types
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
df['Quantity'] = df['Quantity'].astype('int')
df['UnitPrice'] = df['UnitPrice'].astype('float')
df['CustomerID'] = df['CustomerID'].astype('str')

### Filter on 1 year of data
df = df.loc[lambda x: x['InvoiceDate'] < '2011-12-01']

### Look at first rows
df.head()

### We can see more rows
df.head(10)

### ✅ 1.1. Look at *last* rows
### Hint: use `tail()`
df.tail()

### ✅ 1.2. How many rows and columns do we have ?
### Hint: use `shape`
df.shape

### ✅ 1.3. Show some useful info about our dataframe
### Hint: use `info()`
df.info()

### ✅ 1.4. Show summary statistics for numeric columns
### Hint: use `describe()`
df.describe()

### ✅ 1.5. Check for missing (null) data
### Hint: use `isnull()` followed by `sum()`
df.isnull().sum()

### Select a single column
df['InvoiceDate']

### ✅ 2.1. Select column `Description`
df['Description']

### More verbose way with `.loc[]`
df.loc[:, 'CustomerID']

### ✅ 2.2. Select column `Description` with `.loc[]`
df.loc[:, 'Description']

### Select multiple columns
df.loc[:, ['CustomerID','Country']]

### ✅ 2.3. Select columns `StockCode` and `Description`
df.loc[:, ['StockCode', 'Description']]

### Now let's select also subset of rows
df.loc[:5, ['CustomerID','Country']]

### ✅ 2.4. Select 10 first rows for columns `StockCode` and `Description`
df.loc[:10, ['StockCode', 'Description']]

### Filtering is an essential aspect
df.loc[df['StockCode'] == '85123A']

### How many rows did we get back ?
df.loc[df['StockCode'] == '85123A'].count()

### ✅ 2.5. Get rows where `StockCode` equals `84029G`
df.loc[df['StockCode'] == '84029G']

### ✅ 2.6. And count them
df.loc[df['StockCode'] == '84029G'].count()

### ✅ 2.7. What is the average unit price ?
### Hint: use mean()
df['UnitPrice'].mean()

### ✅ 2.8. What are the first and last dates in our table ?
### Hint: use min() and max()
print(df['InvoiceDate'].min())
print(df['InvoiceDate'].max())

### Day of the order, without time
df['InvoiceDay'] = df['InvoiceDate'].dt.date
df['InvoiceDay'] = pd.to_datetime(df['InvoiceDay'])

### Week of the order
df['Week'] = df['InvoiceDate'].dt.isocalendar().week

### ✅ 3.1. Add a column named `WeekDay` that gives the day of week
### Hint: use `dt.weekday()`
df['WeekDay'] = df['InvoiceDate'].dt.weekday

### ✅ 3.2. Add a column named `Revenue` that multiplies quantity by unit price
df['Revenue'] = df['UnitPrice'] * df['Quantity']

### ✅ 3.3. Check that it looks OK with `head()`
df.head()

### First try to group by date
df_date = df.groupby('InvoiceDay').sum(numeric_only=True)
df_date.head()

### Let's try again with more appropriate aggregates
df_date = (
    df
    .groupby('InvoiceDay')
    .agg({'InvoiceNo': 'nunique', 'CustomerID': 'nunique', 'Quantity': 'sum', 'Revenue': 'sum'})
    .reset_index()
)
df_date.head()

### ✅ 4.1. Add back the UnitPrice
df_date['UnitPrice'] = df_date['Revenue'] / df_date['Quantity']
df_date.head()

### ✅ 4.2. Starting with `df`, get the revenue per country
df.groupby('Country').agg({'Revenue': 'sum'})

### ✅ 5.1. What is the day with the greatest revenue ?
### Hint: use sort_values()
df_date.sort_values('Revenue').tail(1)

### ✅ 5.2. And the day with the smallest revenue ?
df_date.sort_values('Revenue').head(1)

### ✅ 5.3. How many days have a revenue greather than 75k$ ?
df_date.loc[df_date['Revenue'] > 75000]

### Let's plot the revenue by day
df_date.plot.line(x='InvoiceDay', y='Revenue')

### And plot the distribution with a histogram
df_date.plot.hist(x='InvoiceDay', y='Revenue', bins=20)

### Add the mean
fig, ax = plt.subplots()
df_date.plot.hist(x='InvoiceDay', y='Revenue', bins=20, ax=ax)
ax.axvline(df_date['Revenue'].mean(), color='red')

### ✅ 5.4. Group data by week in a dataframe named `df_week`
### Don't forget to recompute the UnitPrice
df_week = (
    df
    .groupby('Week')
    .agg({'InvoiceNo': 'nunique', 'CustomerID': 'nunique', 'Quantity': 'sum', 'Revenue': 'sum'})
    .reset_index()
)
df_week['UnitPrice'] = df_week['Revenue'] / df_week['Quantity']
df_week.head()

### ✅ 5.5. What is the average revenue by week ?
df_week['Revenue'].mean()

### ✅ 5.6. Plot the weekly Revenue evolution
df_week.plot.line(x='Week', y='Revenue')

### ✅ 5.7. Now plot the weekly number of orders, along with the number of customers
### and the average for each metric
### Hint: use `ax.axhline()`
fig, ax = plt.subplots()
df_week.plot.line(x='Week', y='InvoiceNo', ax=ax)
df_week.plot.line(x='Week', y='CustomerID', ax=ax)
ax.axhline(df_week['InvoiceNo'].mean())
ax.axhline(df_week['CustomerID'].mean(), color='orange')

### ✅ 5.8. Add the cumulated revenue in a column named RevenueCtd
### Hint: use `cumsum()`
df_week['RevenueCtd'] = df_week['Revenue'].cumsum()
df_week.head()

### ✅ 5.9. Plot the weekly cumulated revenue in an area plot
### Hint: use `plot.area()`
df_week.plot.area(x='Week', y='RevenueCtd')

### ✅ 5.10. Create a column named `Hour` with the hour of the day
df['Hour'] = df['InvoiceDate'].dt.hour

### ✅ 5.11. Group Revenue by day of week *and* hour of day
df_week_hour = (
    df
    .groupby(['WeekDay', 'Hour'])
    .agg({'Revenue': 'sum'})
)
df_week_hour.head()

### Let's pivot the dataframe
df_week_hour = (
    df_week_hour
    .reset_index()
    .pivot(index='Hour', columns='WeekDay', values='Revenue')
)
df_week_hour

### ✅ 5.12. Plot a heatmap
### Hint: use `sns.heatmap()`
sns.heatmap(df_week_hour)

### ✅ 5.13. Let's group by customers now: number of Orders, Revenue and first date of Order
### Hint: use `min()` to get the first date 
df_clients = (
    df
    .groupby('CustomerID')
    .agg({'InvoiceNo': 'nunique', 'Revenue': 'sum', 'InvoiceDay': 'min'})
)
df_clients.head()

### ✅ 5.14. Who are the top 5 biggest spenders ?
df_clients.sort_values('Revenue', ascending=False).head()

### ✅ 5.15. Plot the Revenue distribution of clients in a histogram
df_clients.plot.hist(y='Revenue')

### ✅ 5.16. Look at the quartiles of Revenue and save it to `df_clients_quantiles`
### Hint: use `quantiles([0, .25, .5, .75, .9, .95])`
df_clients_quantiles = df_clients['Revenue'].quantile([0, .25, .5, .75, .9, .95])
df_clients_quantiles

### ✅ 5.17. Now filter client Revenue to be >0 and less than the 95% percentile
### And plot the histogram again
(
    df_clients
    .loc[df_clients['Revenue'] > 0]
    .loc[df_clients['Revenue'] <= df_clients_quantiles[0.95]]
    .plot.hist(y='Revenue')
)

### ✅ 5.18. Add a `WeekDay` column to the df_date dataframe
df_date['WeekDay'] = df_date['InvoiceDay'].dt.dayofweek

### Plot revenue by WeekDay with a boxplot
sns.boxplot(data=df_date, x='WeekDay', y='Revenue')

### ✅ 5.19. Try with a `sns.boxenplot()`
sns.boxenplot(data=df_date, x='WeekDay', y='Revenue')

### ✅ 5.20. And with a `sns.violinplot()`
sns.violinplot(data=df_date, x='WeekDay', y='Revenue')

### ✅ 6.1. Fit a linear regression on weekly revenue with `sns.regplot()`
sns.regplot(x=list(range(df_week.shape[0])),
            y=df_week['Revenue']
)

### ✅ 6.2. Fit a linear regression on weekly *cumulated* revenue
sns.regplot(x=list(range(df_week.shape[0])),
            y=df_week['RevenueCtd']
)