<jupyter_start><jupyter_text>Lambda School Data Science*Unit 1, Sprint 1, Module 3*--- 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>##### YOUR CODE HERE #####
#explore each csv  by sampling item name
import pandas as pd
pd.options.display.max_rows=999
#pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
df = pd.read_csv("aisles.csv")
print(df.shape)
print(df)


import pandas as pd
pd.options.display.max_rows=999
#pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
df = pd.read_csv("departments.csv")
print(df.shape)
print(df)
import pandas as pd
pd.options.display.max_rows=999
#pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
df = pd.read_csv("order_products__prior.csv")
print(df.shape)
print(df)
import pandas as pd
pd.options.display.max_rows=999
#pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
df = pd.read_csv("order_products__train.csv")
print(df.shape)
print(df)
import pandas as pd
pd.options.display.max_rows=999
#pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
df = pd.read_csv("orders.csv")
print(df.shape)
print(df)
import pandas as pd
pd.options.display.max_rows=999
#pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
df = pd.read_csv("products.csv")
print(df.shape)
print(df)

#departments(21 rows), aisles(134 rows), products (49688 rows) files have string data like banana, etc. 
#logically, you can see less depts vs aisles vs products, etc. So our strings are in products csv. There are 49000 product id's, which is our items?
#search products.csv file for our string matches:
#Bag of Organic Bananas
#Organic Strawberries
#Organic Baby Spinach
#Organic Hass Avocado
#Organic Avocado
#Large Lemon
#Strawberries
#Limes
#Organic Whole Milk
import numpy as np
df=pd.read_csv('products.csv')
print(np.where((df['product_name']=='Banana')))
print(np.where((df['product_name']=='Bag of Organic Bananas')))
print(np.where((df['product_name']=='Organic Strawberries')))
print(np.where((df['product_name']=='Organic Baby Spinach')))
print(np.where((df['product_name']=='Organic Hass Avocado')))
print(np.where((df['product_name']=='Organic Avocado')))
print(np.where((df['product_name']=='Large Lemon')))
print(np.where((df['product_name']=='Strawberries')))
print(np.where((df['product_name']=='Limes')))
print(np.where((df['product_name']=='Organic Whole Milk')))
#Index of items found, print row to confirm our item
print(df.iloc[[24851]])
print(df.iloc[[13175]])
print(df.iloc[[21136]])
print(df.iloc[[21902]])
print(df.iloc[[47208]])
print(df.iloc[[47765]])
print(df.iloc[[47625]])
print(df.iloc[[16796]])
print(df.iloc[[26208]])
print(df.iloc[[27844]])
#now use product_id to find how many times was each ordered.
df=pd.read_csv('order_products__prior.csv')

df['product_id'].value_counts().head(10)
#amazingly the above list automatically listed the values I was searching for and specificed earlier,
#as the top values, and not a long list of values that I thought I'd have to search through.
#this must be why python is a (multi-paradigm) functional language! I love it! A lot more than java.<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>#replicate the lesson code
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline


#concatenate
#create df1
df1 = pd.DataFrame([['a',1],['b',2]], columns=['letter','number'])
df1.head()
#create df2
df2 = pd.DataFrame([['c',3],['d',4]],columns=['letter','number'])
df2.head()
#now concat
df3=pd.concat([df1,df2], axis=0)
df3
df4=pd.concat([df1,df2], axis=1)
df4.columns=['a','b','c','d']
df4
#concat is simplest, just sticks two df's together. .merge() is the advanced function
#stocknames
stockname = pd.DataFrame({'Symbol': ['AMZN', 'MSFT', 'FB', 'AAPL', 'GOOGL'], 'Name': ['Amazon', 'Microsoft', 'Facebook', 'Apple', 'Google']})
stockname
# stock prices.
openprice = pd.DataFrame({'Symbol': ['AAPL', 'MSFT', 'GOOGL', 'FB', 'AMZN'], 'OpenPrice': [217.51, 96.54, 501.3, 51.45, 1703.34]})
openprice
#merge the dataframes
named_stocks=pd.merge(openprice, stockname)
named_stocks
#common key = common column? eyve good job at the lecture! 
# Create a 3rd dataset of weekly highs
wkhigh = pd.DataFrame({'Symbol': ['FB', 'AMZN', 'AAPL', 'MSFT', 'NFLX'], '52wkHigh': [60.79, 2050.49, 233.47, 110.11, 303.22]})
wkhigh
#now merge the above 2 datasets
full_stocks=pd.merge(named_stocks, wkhigh, on='Symbol', how='inner')
full_stocks=pd.merge(named_stocks, wkhigh, on='Symbol', how='left')
full_stocks=pd.merge(named_stocks, wkhigh, on='Symbol', how='right')
full_stocks=pd.merge(named_stocks, wkhigh, on='Symbol', how='outer')
full_stocks
#on=, is the common column, how=, is which side is main to contrast
# This is code to display a `.png` inside of a jupyter notebook.
from IPython.display import display, Image
url = 'https://shanelynnwebsite-mid9n9g1q9y8tt.netdna-ssl.com/wp-content/uploads/2017/03/join-types-merge-names.jpg'
venn_diagram = Image(url=url, width=600)
display(venn_diagram)
#reshape: melt and pivot table
full_stocks.shape
#let's create a simple table for Tidy Data
#start with wide data
myindex=['<NAME>', '<NAME>', '<NAME>']
mycolumns=['treatmenta', 'treatmentb']

table1 = pd.DataFrame([[np.nan, 2],[16,11],[3,1]],columns=mycolumns,index=myindex)
table1
#transpose
table2 = table1.T # .T means transpose! It moves the rows to columns thus the name wide table
table2
#tidy
#get the columns as a list
list(table1.columns)
table1.columns.tolist()<jupyter_output><empty_output><jupyter_text><jupyter_code>#get the index values as a list
table1.index.tolist()
#for table1 convert index list into a column using reset_index()
table1 = table1.reset_index()
table1
#convert the table from wide to tidy using melt()
tidy=table1.melt(id_vars='index',value_vars=['treatmenta','treatmentb'])
tidy
#to clean things up, rename columns
tidy = table1.melt(id_vars='index', value_vars=['treatmenta', 'treatmentb'])
tidy
tidy = tidy.rename(columns={
    'index': 'name', 
    'variable': 'trt', 
    'value': 'result'
})
# shorten the `trt` values
tidy.trt = tidy.trt.str.replace('treatment', '')
tidy
wide = tidy.pivot_table(index='name', columns='trt', values='result')
wide

#plot using seaborn
sns.catplot(x='trt', y='result', col='name', 
            kind='bar', data=tidy, height=2);
#more complex examples
#concatenating time-series from Chicago
# Here's some data about Chicago bikesharing.
source_path='https://raw.githubusercontent.com/austinlasseter/pandas_visualization/master/data/Divvy_Trips_dataset/'
q1_path=source_path + 'Divvy_Trips_2015-Q1.csv'
q2_path=source_path + 'Divvy_Trips_2015-Q2.csv'
q3_path=source_path + 'Divvy_Trips_2015-Q3.csv'
q4_path=source_path + 'Divvy_Trips_2015-Q4.csv'
#1st quarter
q1 = pd.read_csv(q1_path)
print(q1.shape)
q1.head()
#2nd quarter?
q2 = pd.read_csv(q2_path)
print(q2.shape)
q2.head()
#do they have exactly the same column names? Otherwise .concat() won't work.
print(q1.columns)
print(q2.columns)
#check if they're really equal
def diff_check(list1,list2):
  diff=list(set(list1)-set(list2))
  print('difference is:',diff)

diff_check(q1.columns, q2.columns)
#empty value means there's no difference, the columns are exactly equal
#now that we know they're equal, .concat() them
q1_q2=pd.concat([q1,q2], axis=0)
(q1_q2)
#now add q3 and 4
q3=pd.read_csv(q3_path)
q4=pd.read_csv(q4_path)
full_year=pd.concat([q1,q2,q3,q4],axis=0)
full_year.shape
#merging datasets
source1='https://raw.githubusercontent.com/austinlasseter/dash-virginia-counties/master/resources/acs2017_county_data.csv'
census=pd.read_csv(source1)
census.sample(5)
census.columns
commute=census[['CountyId', 'State','County','MeanCommute']]
commute.sample(3)
commute['MeanCommute'].mean()
#let's add some data from USDA
source2='https://github.com/austinlasseter/dash-virginia-counties/blob/master/resources/ruralurbancodes2013.xls?raw=true'
usda=pd.read_excel(source2)
usda
#what's a RUCC code?
usda.groupby('RUCC_2013')[['Description']].min()


usda=usda[['FIPS','RUCC_2013']]
usda.head(3)
commute.head(2)
commute.rename(columns={'CountyId':'FIPS'})
#merge  with census data
metro_commute=pd.merge(commute,usda,how='left',left_on='CountyId',right_on='FIPS')
metro_commute.sample(3)
#is there any difference in commutes by rural-urban designation?
metro_commute[metro_commute['RUCC_2013']==1]['MeanCommute'].mean()
#what about rural
metro_commute[metro_commute['RUCC_2013']==5]['MeanCommute'].mean()
#contrast means all in one blow
drivetimes=metro_commute.groupby('')
##### YOUR CODE HERE #####<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>##### YOUR CODE HERE #####<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>##### YOUR CODE HERE #####<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13791526973483695, 'fraction_numerical': 0.03573605608046327, 'mean_word_length': 6.121540965816603, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 32, 'https://': 17, 'lorem ipsum': 0, 'www.': 1, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '419691', 'license': 'MIT', 'n_tokens_mistral': 4944, 'n_tokens_neox': 4507, 'n_words': 1242, 'path': 'Mike Smith LS_DS_113_Join_and_Reshape_Data_Assignment.ipynb', 'repo_name': 'gomlfx/CodeSearchNet', 'size': '13136'}