<jupyter_start><jupyter_text>_Lambda School Data Science_ Join and Reshape datasetsObjectives- concatenate data with pandas- merge data with pandas-  understand tidy data formatting-  melt and pivot data with pandasLinks- [Pandas Cheat Sheet](https://github.com/pandas-dev/pandas/blob/master/doc/cheatsheet/Pandas_Cheat_Sheet.pdf)- [Tidy Data](https://en.wikipedia.org/wiki/Tidy_data)  - Combine Data Sets: Standard Joins  - Tidy Data  - Reshaping Data- Python Data Science Handbook  - [Chapter 3.6](https://jakevdp.github.io/PythonDataScienceHandbook/03.06-concat-and-append.html), Combining Datasets: Concat and Append  - [Chapter 3.7](https://jakevdp.github.io/PythonDataScienceHandbook/03.07-merge-and-join.html), Combining Datasets: Merge and Join  - [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)<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
%cd instacart_2017_05_01
!ls -lh *.csv<jupyter_output>-rw-r--r-- 1 502 staff 2.6K May  2  2017 aisles.csv
-rw-r--r-- 1 502 staff  270 May  2  2017 departments.csv
-rw-r--r-- 1 502 staff 551M May  2  2017 order_products__prior.csv
-rw-r--r-- 1 502 staff  24M May  2  2017 order_products__train.csv
-rw-r--r-- 1 502 staff 104M May  2  2017 orders.csv
-rw-r--r-- 1 502 staff 2.1M May  2  2017 products.csv<jupyter_text>Assignment Join Data PracticeThese are the top 10 most frequently ordered products. How many times was each ordered? 1. Banana2. Bag of Organic Bananas3. Organic Strawberries4. Organic Baby Spinach 5. Organic Hass Avocado6. Organic Avocado7. Large Lemon 8. Strawberries9. Limes 10. Organic Whole MilkFirst, write down which columns you need and which dataframes have them.Next, merge these into a single dataframe.Then, use pandas functions from the previous lesson to get the counts of the top 10 most frequently ordered products.<jupyter_code>import pandas as pd

aisles = pd.read_csv('aisles.csv')
print(aisles.shape)
aisles.head() # Don't need these.
departments = pd.read_csv('departments.csv')
print(departments.shape)
departments.head() # Don't need these.
order_products__prior = pd.read_csv('order_products__prior.csv')
print(order_products__prior.shape)
order_products__prior.head() # May need these.
order_products__train = pd.read_csv('order_products__train.csv')
print(order_products__train.shape)
order_products__train.head() # Same columns as order_products__prior.
orders = pd.read_csv('orders.csv')
print(orders.shape)
orders.head() # Might need some of these. (Edit: Turns out I won't.)
products = pd.read_csv('products.csv')
print(products.shape)
products.head() # Will definitely need some of these.<jupyter_output>(49688, 4)<jupyter_text>The goal is to discover how many times certain products were ordered. To do this, I'll basically need to pull each row from "order_products" that contains one of the products in question, and then count up the number of such rows. That means I'll need:- the product name from "products"- the product ID associated with the name from "products"- the order ID column from "order_products"<jupyter_code># First, to concatenate the order_products dataframes.

order_products = pd.concat([order_products__prior, order_products__train])
print(order_products.shape)
assert order_products.shape[0] == (order_products__prior.shape[0] + order_products__train.shape[0]) # Success!
# Now to drop the columns from "products" that I don't need.

products2 = products.drop(['aisle_id', 'department_id'], axis=1)
products2.head()
# I also only want to keep two columns from "order_products."

order_products2 = order_products.drop(['add_to_cart_order', 'reordered'], axis=1)
order_products2.head()
# Now to merge!

merged = pd.merge(products2, order_products2, how='inner', on='product_id')
merged.head()
# Finally, I just have to count the number of times each value in "product name" appears, for the top 10 values only.

merged['product_name'].value_counts()[:10]<jupyter_output><empty_output><jupyter_text>Reshape Data Section- 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># Below I've replicated the lesson code; scroll down to "New Code" for my own 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
table1 = table1.reset_index()
table1
tidy = table1.melt(id_vars='index', value_vars=['treatmenta', 'treatmentb'])
tidy
tidy = tidy.rename(columns={'index': 'name', 'variable': 'trt', 'value': 'result'})
tidy
tidy['trt'] = tidy['trt'].str.replace('treatment', '')
tidy
wide = tidy.pivot_table(index='name', columns='trt', values='result')
wide<jupyter_output><empty_output><jupyter_text>New code<jupyter_code>table2
# Let's reset the index first.

table2 = table2.reset_index()
table2<jupyter_output><empty_output><jupyter_text>So basically, here's how the "melt" command works (I think):1. You select one column to keep, and then have only two other columns: variable and value.2. The other column names become the values in the 'variable' column.3. The original values under those columns become the values of the 'value' column.As such, to get this in "tidy" format, I only want to keep the current 'index' column.<jupyter_code>tidy2 = pd.melt(table2, id_vars=['index'])
tidy2
# Now I just have to rename and rearrange things so it looks exactly like the original "tidy" table.

tidy2 = tidy2[['variable', 'index', 'value']] # Got help from Stack Overflow.
tidy2
# Next, rename things just like in the lecture.
tidy2 = tidy2.rename(columns={'variable': 'name', 'index': 'trt', 'value': 'result'})
tidy2['trt'] = tidy2['trt'].str.replace('treatment', '')
tidy2
# Finally I have to rearrange the rows by treatment.

tidy2 = tidy2.sort_values(by='trt')
tidy2
# I'll reset the index so it looks better.

tidy2 = tidy2.reset_index(drop=True) # Dropping so I don't add a new column.
tidy2 # Perfect!<jupyter_output><empty_output><jupyter_text>Now to make the pivot table. From what I understand, here's how that works:1. You select a column(s) (or row(s) I guess) as your index.2. You select another column(s) as your new column labels.3. You select another column(s) as your new values.I need "treatmenta" and "treatmentb" to be my index, "<NAME>" etc to be my column names, and the numbers to be my values.<jupyter_code>wide2 = tidy2.pivot_table(index='trt', columns='name', values='result')
wide2
# Now I just have to rename and rearrange some things and I'll be done!

wide2 = wide2.rename(index={'a': 'treatmenta', 'b': 'treatmentb'})
wide2 = wide2[['<NAME>', '<NAME>', '<NAME>']]
wide2

# Just about right; the only thing left to do is to get rid of the "name trt" part. I'll do the other stuff first
# and come back to this when I have time.
flights = sns.load_dataset('flights')<jupyter_output><empty_output><jupyter_text>To remind myself of the assignment: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>print(flights.shape)
flights.head()
flights_table = flights.pivot_table(index='year', columns='month', values='passengers')
flights_table # Success!<jupyter_output><empty_output><jupyter_text>Join Data Stretch ChallengeThe [Instacart blog post](https://tech.instacart.com/3-million-instacart-orders-open-sourced-d40d29ead6f2) has a visualization of "**Popular products** purchased earliest in the day (green) and latest in the day (red)." The post says,> "We can also see the time of day that users purchase specific products.> Healthier snacks and staples tend to be purchased earlier in the day, whereas ice cream (especially Half Baked and The Tonight Dough) are far more popular when customers are ordering in the evening.> **In fact, of the top 25 latest ordered products, the first 24 are ice cream! The last one, of course, is a frozen pizza.**"Your challenge is to reproduce the list of the top 25 latest ordered popular products.We'll define "popular products" as products with more than 2,900 orders.<jupyter_code># First, I need to cut out the products that have less than 2900 orders. I can re-use my earlier code.

df = merged['product_name'].value_counts().reset_index() # I need the product names to be their own column.
df = df.rename(columns={'index': 'product_name', 'product_name': 'frequency'}) # Renaming for my own benefit.
df.head()
popular_condition = (df['frequency'] > 2900)
popular_products = df.loc[popular_condition, ['product_name', 'frequency']]
popular_products # Success!
# Now to merge this with the products dataframe.

pop_products2 = pd.merge(popular_products, products, how='inner', on='product_name')
pop_products2.head()
# Let's make sure it worked the way I wanted it to.

check = pop_products2.sort_values(by=['frequency'], ascending=True)
check.head() # It worked!
# Alright, now that I have access to the product IDs, I need to merge with order_products.
# First though, I'll get rid of the unnecessary columns.

pop_products3 = pop_products2.drop(['aisle_id', 'department_id'], axis=1)
temp = order_products.drop(['add_to_cart_order', 'reordered'], axis=1)

pop_products4 = pd.merge(pop_products3, temp, how='inner', on='product_id')
check2 = pop_products4.sort_values(by=['frequency'], ascending=True)
check2.head()
# And now for the final merge, with the orders dataframe--again, after dropping unnecessary columns.

temp2 = orders.drop(['user_id', 'eval_set', 'order_number', 'order_dow', 'days_since_prior_order'], axis=1)

pop_products_final = pd.merge(pop_products4, temp2, how='inner', on='order_id')
check3 = pop_products_final.sort_values(by=['frequency'], ascending=True)
check3.head()
# Alright, at this point I only need two columns.

final = pop_products_final.drop(['frequency', 'product_id', 'order_id'], axis=1)

# Now I need to somehow determine the average order hour of each individual product.
final.groupby('product_name').mean().sort_values('order_hour_of_day', ascending=False)[:25]

# This code was posted by <NAME> in the Slack channel. It successfully gives me the right answer,
# but I admit I didn't come up with it myself. I did look up the "groupby" function so I understand
# how it works...that's a really powerful function I'll have to remember.<jupyter_output><empty_output><jupyter_text>Reshape Data Stretch Challenge_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># I don't have time to do this right now, but I hope to come back to it later.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10957559898601685, 'fraction_numerical': 0.023714122168615587, 'mean_word_length': 5.386422976501305, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 0, '>': 53, 'https://': 13, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '597079', 'license': 'MIT', 'n_tokens_mistral': 4066, 'n_tokens_neox': 3757, 'n_words': 1355, 'path': 'module1-join-and-reshape-data/LS_DS_121_Join_and_Reshape_Data_Assignment.ipynb', 'repo_name': 'davidanagy/DS-Unit-1-Sprint-2-Data-Wrangling-and-Storytelling', 'size': '12263'}