<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)  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: 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: matplotlib>=1.4.3 in /usr/local/lib/python3.6/dist-packages (from seaborn) (2.1.2)
Requirement already satisfied, skipping upgrade: python-dateutil>=2 in /usr/local/lib/python3.6/dist-packages (from pandas>=0.15.2->seaborn) (2.5.3)
Requirement already satisfied, skipping upgrade: pytz>=2011k in /usr/local/lib/python3.6/dist-packages (from pandas>=0.15.2->seaborn) (2018.9)
Requirement already satisfied, skipping upgrade: six>=1.10 in /usr/local/lib/python3.6/dist-packages[...]<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>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'] = tidy.trt.replace('a',0).replace('b',1)

tidy.trt.map({'a':0,'b':1})
tidy.head()<jupyter_output><empty_output><jupyter_text>Table 2 --> Tidy<jupyter_code>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>tidy2.pivot_table(index='name',columns='trt', values='result')<jupyter_output><empty_output><jupyter_text>Seaborn uses tidy data> "[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."<jupyter_code>import seaborn as sns
sns.catplot(x='trt',y='result',col='name',kind='bar',data=tidy,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><empty_output><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>products.columns.tolist()
orders.columns.tolist()
order_products.columns.tolist()
a=products[['product_id','product_name']]
b=order_products[['order_id','product_id']]
c=orders[['order_id','order_hour_of_day']]

merged1=pd.merge(a,b)
merged2=pd.merge(merged1,c)
merged2.shape
merged2.head()
product_names = ['Half Baked Frozen Yogurt', 'Half And Half Ultra Pasteurized']
#condition = (merged2.product_name=='Half Baked Frozen Yogurt') |(merged2.product_name=='Half And Half Ultra Pasteurized')
#equal to below
condition = merged2.product_name.isin(product_names)
subset=merged2[condition]
subset.sample(n=10)
subset.product_name.value_counts()<jupyter_output><empty_output><jupyter_text>4 ways to reshape and plot  1. value_counts<jupyter_code>cream=subset[subset.product_name=='Half And Half Ultra Pasteurized']
froyo=subset[subset.product_name=='Half Baked Frozen Yogurt']
cream.order_hour_of_day.value_counts().sort_index().plot();
froyo.order_hour_of_day.value_counts().sort_index().plot();
cream.order_hour_of_day.value_counts(normalize=True).sort_index()<jupyter_output><empty_output><jupyter_text>2. crosstab<jupyter_code>#normalize=True is for making percentages, if passed ‘all’ or True, will normalize over all values, If passed ‘index’ will normalize over each row, If passed ‘columns’ will normalize over each column
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='columns')
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');
#assignment

flights = sns.load_dataset('flights')
flights.head()
flight_table = flights.pivot_table(index='year',columns='month', values='passengers')
ax = sns.heatmap(flight_table)
#stretch

aisles = pd.read_csv('aisles.csv')
departments = pd.read_csv('departments.csv')
aisles.columns, orders.columns, order_products.columns, products.columns, departments.columns
df = pd.merge(order_products[['order_id','product_id','reordered']],products[['product_id','product_name','aisle_id','department_id']])
df=pd.merge(df,aisles[['aisle_id','aisle']])
df=pd.merge(df,departments[['department_id','department']])
df.head()
orders_and_reorders= df.groupby('aisle').order_id.count()
reorders= df.groupby('aisle').reordered.sum()
orders_and_reorders = pd.DataFrame(orders_and_reorders)
orders_and_reorders=orders_and_reorders.rename(columns={'order_id':'orders'})
orders_and_reorders['reorders']=reorders
orders_and_reorders['percent reordered'] = orders_and_reorders['reorders']/orders_and_reorders['orders']

orders_and_reorders.head(200)
output=pd.merge(orders_and_reorders,df[['aisle','department']],left_index=True,right_on='aisle')

output.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14035989717223651, 'fraction_numerical': 0.02025706940874036, 'mean_word_length': 6.604378420641126, 'pattern_counts': {'":': 0, '<': 79, '<?xml version=': 0, '>': 97, 'https://': 10, 'lorem ipsum': 0, 'www.': 0, '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': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '142111', 'license': 'MIT', 'n_tokens_mistral': 3516, 'n_tokens_neox': 3325, 'n_words': 690, 'path': 'module3-reshape-data/LS_DS_123_Reshape_data.ipynb', 'repo_name': 'donw385/DS-Unit-1-Sprint-2-Data-Wrangling', 'size': '9777'}