<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: numpy>=1.9.3 in /usr/local/lib/python3.6/dist-packages (from seaborn) (1.14.6)
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: pandas>=0.15.2 in /usr/local/lib/python3.6/dist-packages (from seaborn) (0.22.0)
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: 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
from matplotlib import pyplot as plt

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.index.name = 'name'
tidy1 = table1.reset_index().melt(id_vars='name', var_name='trt', value_name='result')
tidy1['trt'] = tidy1['trt'].str.replace('treatment','')
tidy1<jupyter_output><empty_output><jupyter_text>Table 2 --> Tidy<jupyter_code>tidy2 = table2.T.reset_index().melt(id_vars='name', var_name='trt', value_name='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>tidy1.pivot_table(index='name', columns='trt', values='result')<jupyter_output><empty_output><jupyter_text>Tidy --> Table 2<jupyter_code>tidy2.pivot_table(index='name', columns='trt', values='result')
sns.catplot(x='trt', y='result', col='name', kind='bar', data=tidy1, height=2);<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')<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']
order_products_cols = ['order_id', 'product_id']
products_cols = ['product_id', 'product_name']
orders_cols = ['order_id', 'order_hour_of_day']

merged = orders[orders_cols].merge(order_products[order_products_cols]).merge(products[products_cols])

subset = merged[merged.product_name.isin(product_names)]
subset.head()<jupyter_output><empty_output><jupyter_text>4 ways to reshape and plot  1. value_counts<jupyter_code>froyo = subset[subset['product_name'] == product_names[0]]
creamer = subset[subset['product_name'] == product_names[1]]
creamer['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').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')
 .rename(columns={
     'order_hour_of_day':'Hour of Day Ordered',
     'product_name': 'Product',
     'value': 'Percent of Orders by Product'
 }))
sns.relplot(x='Hour of Day Ordered',
            y='Percent of Orders by Product',
            hue='Product',
            data=melted,
            kind='line')<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_pivot = flights.pivot_table(index='year',
                  columns='month',
                  values='passengers')
flights_pivot<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)<jupyter_code>#merge all dfs:

merged = orders.merge(order_products).merge(products)
#drop some columns we dont need to make the df more memory-efficient:

merged.drop(columns=['eval_set', 'order_dow', 'days_since_prior_order', 'reordered', 'aisle_id', 'department_id'], inplace=True)

#Replicate more of Instacart's visualization showing "Hour of Day Ordered" vs "Percent of Orders by Product"

#subset of popular items:

product_order_counts = merged.product_name.value_counts()

popular = product_order_counts[product_order_counts >= 2900]
#branch merged df to one that only includes popular items:

merged_popular = merged[merged['product_name'].isin(popular.index)]
#group by product name and take the mean hour of day for each product and sort

grouped = (merged_popular
           .groupby('product_name')
           .order_hour_of_day.mean()
           .sort_values())

earliest_25 = grouped.head(25)
latest_25 = grouped.tail(25)

#make subsets of the merged df with the 25 earliest and latest:

early_merged = merged_popular[merged_popular['product_name'].isin(earliest_25.index)]
late_merged = merged_popular[merged_popular['product_name'].isin(latest_25.index)]
#make crosstabs to plot:
  
early_ct = pd.crosstab(early_merged['order_hour_of_day'],
           early_merged['product_name'],
           normalize='columns');

late_ct = pd.crosstab(late_merged['order_hour_of_day'],
           late_merged['product_name'],
           normalize='columns');
#Plot the data reproducing the visual from instacart page:

fig = plt.figure(figsize=(18,11))
ax = plt.axes()
plt.plot(early_ct, color='g')
legend1 = plt.legend(labels=earliest_25.index, loc=2)
plt.plot(late_ct, color='r')
legend2 = plt.legend(labels=latest_25.index, loc=1)
ax.add_artist(legend1)
ax.add_artist(legend2)
ax.set_xticks([0,2,4,6,8,10,12,14,16,18,20,22,24])
plt.title('Popular products purchased earliest in the day (green) and latest in the day (red)', fontsize=16)
plt.ylabel('Percent of Orders by Product',fontsize=12)
plt.xlabel('Hour of Day Ordered',fontsize=12)
plt.show()
#Get the most recent order for each user in Instacart's dataset. This is a useful 
#baseline when predicting a user's next order

merged = merged.sort_values(by=['user_id', 'order_number'])

#group by user_id and max order number:
user_grouped = merged.groupby('user_id')
#get the max order number for each user_id
user_grouped_max = merged.groupby('user_id')['order_number'].max()
user_grouped_max.head()

#add max_order_number column ot merged df for each user_id

max_df = pd.DataFrame(user_grouped_max)
max_df.rename(columns={'order_number': 'max_order_number'}, inplace=True)
max_df = max_df.reset_index()

max_merged = merged.merge(max_df)

#make dataframe of the most recent order only: where the order number = the max_order number for that user_id

most_recent = max_merged[(max_merged['order_number'] == max_merged['max_order_number'])]
most_recent.head()
#drop a few more columns we dont need:

most_recent.drop(columns=['order_id', 'order_number', 'order_hour_of_day', 'add_to_cart_order'], inplace=True)
#consolidate multiple rows from the same order number into one, resulting in dataframe w 2 columns:
#user id, and list of products from the most recent order:

#first grab the Series:
most_recent_orders = most_recent.groupby('user_id')['product_name'].unique()
most_recent_orders.head()
#Convert to DataFrame:
most_recent_orders = pd.DataFrame(most_recent_orders).rename(columns={'product_name': 'List of items in most recent order'})
#Display the first 30 users:

pd.options.display.max_colwidth = 100

most_recent_orders.head(30)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11893238434163701, 'fraction_numerical': 0.02782918149466192, 'mean_word_length': 5.395539371870733, 'pattern_counts': {'":': 0, '<': 82, '<?xml version=': 0, '>': 103, 'https://': 14, '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': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '148081', 'license': 'MIT', 'n_tokens_mistral': 4984, 'n_tokens_neox': 4620, 'n_words': 1180, 'path': 'module3-reshape-data/LS_DS_123_Reshape_data.ipynb', 'repo_name': 'Nolanole/DS-Unit-1-Sprint-2-Data-Wrangling', 'size': '14102'}