<jupyter_start><jupyter_text>_Lambda School Data Science_ Reshape dataObjectives-  understand tidy data formatting-  melt and pivot data with pandasLinks- [Tidy Data](https://en.wikipedia.org/wiki/Tidy_data)- [Pandas Cheat Sheet](https://github.com/pandas-dev/pandas/blob/master/doc/cheatsheet/Pandas_Cheat_Sheet.pdf)  - Tidy Data  - Reshaping Data- Python Data Science Handbook  - [Chapter 3.8](https://jakevdp.github.io/PythonDataScienceHandbook/03.08-aggregation-and-grouping.html), Aggregation and Grouping  - [Chapter 3.9](https://jakevdp.github.io/PythonDataScienceHandbook/03.09-pivot-tables.html), Pivot Tables  Reference- pandas documentation: [Reshaping and Pivot Tables](https://pandas.pydata.org/pandas-docs/stable/reshaping.html)- Modern Pandas, Part 5: [Tidy Data](https://tomaugspurger.github.io/modern-5-tidy.html)  Why reshape data? Some libraries prefer data in different formatsFor example, the Seaborn data visualization library prefers data in "Tidy" format often (but not always).> "[Seaborn will be most powerful when your datasets have a particular organization.](https://seaborn.pydata.org/introduction.htmlorganizing-datasets) This format ia alternately called “long-form” or “tidy” data and is described in detail by <NAME>. The rules can be simply stated:> - Each variable is a column- Each observation is a row> A helpful mindset for determining whether your data are tidy is to think backwards from the plot you want to draw. From this perspective, a “variable” is something that will be assigned a role in the plot." Data science is often about putting square pegs in round holesHere's an inspiring [video clip from _Apollo 13_](https://www.youtube.com/watch?v=ry55--J4_VQ): “Invent a way to put a square peg in a round hole.” It's a good metaphor for data wrangling!  Upgrade SeabornRun the cell below which upgrades Seaborn and automatically restarts your Google Colab Runtime.<jupyter_code>!pip install seaborn --upgrade
import os
os.kill(os.getpid(), 9)<jupyter_output>Requirement already up-to-date: seaborn in /usr/local/lib/python3.6/dist-packages (0.9.0)
Requirement already satisfied, skipping upgrade: matplotlib>=1.4.3 in /usr/local/lib/python3.6/dist-packages (from seaborn) (3.0.3)
Requirement already satisfied, skipping upgrade: numpy>=1.9.3 in /usr/local/lib/python3.6/dist-packages (from seaborn) (1.14.6)
Requirement already satisfied, skipping upgrade: scipy>=0.14.0 in /usr/local/lib/python3.6/dist-packages (from seaborn) (1.1.0)
Requirement already satisfied, skipping upgrade: pandas>=0.15.2 in /usr/local/lib/python3.6/dist-packages (from seaborn) (0.22.0)
Requirement already satisfied, skipping upgrade: kiwisolver>=1.0.1 in /usr/local/lib/python3.6/dist-packages (from matplotlib>=1.4.3->seaborn) (1.0.1)
Requirement already satisfied, skipping upgrade: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /usr/local/lib/python3.6/dist-packages (from matplotlib>=1.4.3->seaborn) (2.3.1)
Requirement already satisfied, skipping upgrade: cycler>=0.10 in /u[...]<jupyter_text><NAME>'s ExamplesFrom his paper, [Tidy Data](http://vita.had.co.nz/papers/tidy-data.html)<jupyter_code>%matplotlib inline
import pandas as pd
import numpy as np
import seaborn as sns

table1 = pd.DataFrame(
    [[np.nan, 2],
     [16,    11], 
     [3,      1]],
    index=['<NAME>', '<NAME>', '<NAME>'], 
    columns=['treatmenta', 'treatmentb'])

table2 = table1.T<jupyter_output><empty_output><jupyter_text>"Table 1 provides some data about an imaginary experiment in a format commonly seen in the wild. The table has two columns and three rows, and both rows and columns are labelled."<jupyter_code>table1<jupyter_output><empty_output><jupyter_text>"There are many ways to structure the same underlying data. Table 2 shows the same data as Table 1, but the rows and columns have been transposed. The data is the same, but the layout is different."<jupyter_code>table2<jupyter_output><empty_output><jupyter_text>"Table 3 reorganises Table 1 to make the values, variables and obserations more clear.Table 3 is the tidy version of Table 1. Each row represents an observation, the result of one treatment on one person, and each column is a variable."| name         | trt | result ||--------------|-----|--------|| <NAME>   | a   | -      || <NAME>     | a   | 16     || <NAME> | a   | 3      || <NAME>   | b   | 2      || <NAME>     | b   | 11     || <NAME> | b   | 1      |  Table 1 --> TidyWe can use the pandas `melt` function to reshape Table 1 into Tidy format.<jupyter_code>table1.columns
table1.index.tolist()
tidy = table1.reset_index().melt(id_vars='index')  

tidy = tidy.rename(columns={
    'index':'name',
    'variable':'trt',
    'value': 'result'
})

tidy['trt'] = tidy['trt'].str.replace('treatment', '')

tidy
tidy['trt'].replace(['a','b'],[0,1])
(tidy['trt'] == 'b').astype(int)
tidy['trt'].apply(lambda x: ord(x) - ord('a'))<jupyter_output><empty_output><jupyter_text>Table 2 --> Tidy<jupyter_code># Original Form
table2
# Tidy Form
tidy2 = table2.reset_index().melt(id_vars='index')

tidy2 = tidy2.rename(columns={
    'index':'trt',
    'variable':'name',
    'value': 'result'
})

tidy2['trt'] = tidy2['trt'].str.replace('treatment', '')

tidy2<jupyter_output><empty_output><jupyter_text>Tidy --> Table 1The `pivot_table` function is the inverse of `melt`.<jupyter_code>tidy.pivot_table(index='name', columns = 'trt', values = 'result')<jupyter_output><empty_output><jupyter_text>Tidy --> Table 2<jupyter_code># Use pivot_table to revert tidied table to original form
tidy2.pivot_table(index='trt',columns='name',values='result')<jupyter_output><empty_output><jupyter_text>Seaborn Example<jupyter_code>tidy
sns.catplot(x='trt', y='result', col='name',
           kind='bar', data=tidy, height=5);<jupyter_output><empty_output><jupyter_text>Load Instacart dataLet's return to the dataset of [3 Million Instacart Orders](https://tech.instacart.com/3-million-instacart-orders-open-sourced-d40d29ead6f2) If necessary, uncomment and run the cells below to re-download and extract the data<jupyter_code>!wget https://s3.amazonaws.com/instacart-datasets/instacart_online_grocery_shopping_2017_05_01.tar.gz
 !tar --gunzip --extract --verbose --file=instacart_online_grocery_shopping_2017_05_01.tar.gz<jupyter_output>instacart_2017_05_01/
instacart_2017_05_01/._aisles.csv
instacart_2017_05_01/aisles.csv
instacart_2017_05_01/._departments.csv
instacart_2017_05_01/departments.csv
instacart_2017_05_01/._order_products__prior.csv
instacart_2017_05_01/order_products__prior.csv
instacart_2017_05_01/._order_products__train.csv
instacart_2017_05_01/order_products__train.csv
instacart_2017_05_01/._orders.csv
instacart_2017_05_01/orders.csv
instacart_2017_05_01/._products.csv
instacart_2017_05_01/products.csv<jupyter_text>Run these cells to load the data<jupyter_code>%cd instacart_2017_05_01
products = pd.read_csv('products.csv')

order_products = pd.concat([pd.read_csv('order_products__prior.csv'), 
                            pd.read_csv('order_products__train.csv')])

orders = pd.read_csv('orders.csv')

department = pd.read_csv('departments.csv')<jupyter_output><empty_output><jupyter_text>Goal: Reproduce part of this exampleInstead of a plot with 50 products, we'll just do two — the first products from each list- Half And Half Ultra Pasteurized- Half Baked Frozen Yogurt<jupyter_code>from IPython.display import display, Image
url = 'https://cdn-images-1.medium.com/max/1600/1*wKfV6OV-_1Ipwrl7AjjSuw.png'
example = Image(url=url, width=600)

display(example)<jupyter_output><empty_output><jupyter_text>So, given a `product_name` we need to calculate its `order_hour_of_day` pattern.  Subset and Merge<jupyter_code>product_names = ['Half Baked Frozen Yogurt', 'Half And Half Ultra Pasteurized']
products.columns.tolist()
orders.columns.tolist()
order_products.columns.tolist()
merged = (products[['product_id', 'product_name']]
          .merge(order_products[['order_id','product_id']])
          .merge(orders[['order_id','order_hour_of_day']]))
products.shape, order_products.shape, orders.shape, merged.shape
merged.head()
# What condition will filter 'merged' to just the 2 products that we care about

condition = ((merged['product_name']=='Half Baked Frozen Yogurt') |
             (merged['product_name']=='Half and Half Ultra Pasteurized'))

# ... to this:

product_names = ['Half Baked Frozen Yogurt', 'Half And Half Ultra Pasteurized']

condition = merged['product_name'].isin(product_names)

subset = merged[condition]
merged.shape, subset.shape
subset.sample(5)<jupyter_output><empty_output><jupyter_text>4 ways to reshape and plot  1. value_counts<jupyter_code>froyo = subset[subset['product_name']=='Half Baked Frozen Yogurt']
cream = subset[subset['product_name']=='Half And Half Ultra Pasteurized']
cream
(cream['order_hour_of_day']
 .value_counts(normalize=True)
 .sort_index()
 .plot());
 
(froyo['order_hour_of_day']
 .value_counts(normalize=True)
 .sort_index()
 .plot());<jupyter_output><empty_output><jupyter_text>2. crosstab<jupyter_code>pd.crosstab(subset['order_hour_of_day'],
           subset['product_name'],
           normalize='columns')
(pd.crosstab(subset['order_hour_of_day'],
           subset['product_name'],
           normalize='columns') * 100).plot();<jupyter_output><empty_output><jupyter_text>3. pivot_table<jupyter_code>subset.pivot_table(index='order_hour_of_day',
                  columns='product_name',
                  values='order_id',
                  aggfunc=len).plot();<jupyter_output><empty_output><jupyter_text>4. melt<jupyter_code>table = pd.crosstab(subset['order_hour_of_day'],
                   subset['product_name'],
                   normalize=True)

melted = (table.reset_index().melt(id_vars='order_hour_of_day'))
melted<jupyter_output><empty_output><jupyter_text>ASSIGNMENT- Replicate the lesson code- Complete the code cells we skipped near the beginning of the notebook  - Table 2 --> Tidy  - Tidy --> Table 2 - Load seaborn's `flights` dataset by running the cell below. Then create a pivot table showing the number of passengers by month and year. Use year for the index and month for the columns. You've done it right if you get 112 passengers for January 1949 and 432 passengers for December 1960.<jupyter_code>flights = sns.load_dataset('flights')
flights.head()
# Pivot table showing number of passengers by month and year
flights.pivot_table(index ='year', columns = 'month', values='passengers')
flight_pivot = flights.pivot_table(index ='year', columns = 'month', values='passengers' )
# Classic heatmap use case!
# We can see the increase in flights during the summer months and the increase in flights year over year

import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(20,10))

sns.heatmap(data=flight_pivot, cmap="YlGnBu");<jupyter_output><empty_output><jupyter_text>STRETCH OPTIONS_Try whatever sounds most interesting to you!_- Replicate more of Instacart's visualization showing "Hour of Day Ordered" vs "Percent of Orders by Product"- Replicate parts of the other visualization from [Instacart's blog post](https://tech.instacart.com/3-million-instacart-orders-open-sourced-d40d29ead6f2), showing "Number of Purchases" vs "Percent Reorder Purchases"- Get the most recent order for each user in Instacart's dataset. This is a useful baseline when [predicting a user's next order](https://www.kaggle.com/c/instacart-market-basket-analysis)- Replicate parts of the blog post linked at the top of this notebook: [Modern Pandas, Part 5: Tidy Data](https://tomaugspurger.github.io/modern-5-tidy.html)  Stretch Goal For my first stretch goal, I will attempt to reproduce the other visualization on the Instacart blog post. ![alt text](https://cdn-images-1.medium.com/max/1200/1*jwDcKJTXV8D1DK0KOlUJAQ.png) To obtain the data for 'Percent Reorder Purchases' and 'Number of Purchases' we will need the following features:- **product_name**- **reordered**The data is plotted by department, so we will also need to grab the **department** feature.<jupyter_code># Checking the headers of all csv_files for the features noted above
products.columns.tolist()
order_products.columns.tolist()
department.columns.tolist()<jupyter_output><empty_output><jupyter_text>Now that we have the dataframes we need, lets create a new dataframe by merging on the features they share in common.<jupyter_code>merged = (products[['product_id', 'product_name','department_id']]
          .merge(order_products[['product_id','reordered']])
         .merge(department[['department_id','department']]))
# Check - Everything looks good!
merged.head()<jupyter_output><empty_output><jupyter_text>We will use the groupby function to aggregate the mean and total count of the reorder column for each department in our database.<jupyter_code>reordered = (merged
            .groupby('department')
            .reordered.agg(['mean','count'])
            .rename(columns={'mean':'Percent Reorder Purchases', 'count':'Number of Purchases'})
            .reset_index())
# Check - it looks good!
reordered.head()<jupyter_output><empty_output><jupyter_text>Final PlotThe plot from the website (pictured above) is not ideal for two reasons:- The text-markers make it difficult to see the departments that are aggregated on the left.- The log scale can be misleading for the not-so mathematically minded people that may find this information useful.I have replaced the text-markers with circles that are color coordinated and which reveal the associated department when the cursor is hovered over the marker. Now each department marker is easier to discern for the plots end user. With the logarithm scale taken out, it is visibly apparent how much produce is purchased over all other departments.<jupyter_code>import altair as alt

alt.Chart(reordered,height=400,width=700).mark_circle(size=100).encode(
    x='Number of Purchases',
    y='Percent Reorder Purchases',
    tooltip='department',
    color='department',
    
    
).interactive()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11894714255060439, 'fraction_numerical': 0.024962449037980117, 'mean_word_length': 5.14323374340949, 'pattern_counts': {'":': 0, '<': 98, '<?xml version=': 0, '>': 118, 'https://': 15, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '370090', 'license': 'MIT', 'n_tokens_mistral': 4831, 'n_tokens_neox': 4488, 'n_words': 1226, 'path': 'Connor_Heraty_LS_DS3_123_Reshape_data_LIVE_LESSON.ipynb', 'repo_name': 'connorpheraty/DS-Unit-1-Sprint-2-Data-Wrangling', 'size': '14033'}