<jupyter_start><jupyter_text>1) Intro to Merging, Joining and Concatenating DataFrames<jupyter_code>import pandas as pd
week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv')
foods = pd.read_csv('Data/Restaurant - Foods.csv')<jupyter_output><empty_output><jupyter_text>-------  2) The `pd.concat` Method, Part 1<jupyter_code>week1.head(3)
week2.head(3)<jupyter_output><empty_output><jupyter_text>Preserving Original Index after concatWe can easily combine week1 and week2 as columns are the same.There are a total of 500 rows. But if we look a the index, we can see 249. This is because pandas glue one dataframe on top of each other without modifying the index. Note that in Pandas there can be same index, no need to be unique. If we need to preserve original index we need to set `ignore_index=False`, This will retain the original index after concatenation.<jupyter_code>pd.concat(objs=[week1, week2])
pd.concat(objs=[week1, week2], ignore_index=False)<jupyter_output><empty_output><jupyter_text>Otherwise, we can put `ignore_index=True`We can see that the brand new indexes are created after concat.<jupyter_code>pd.concat(objs=[week1, week2], ignore_index=True)<jupyter_output><empty_output><jupyter_text>Alternative method is `append()`<jupyter_code>week1.append(other=week2)
week1.append(other=week2, ignore_index=False)

week1.append(other=week2, ignore_index=True)<jupyter_output><empty_output><jupyter_text>-------  3) The `pd.concat()` Method, Part 2<jupyter_code>week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv')
foods = pd.read_csv('Data/Restaurant - Foods.csv')<jupyter_output><empty_output><jupyter_text>What if we want the best of both world?+ Create Brand New indexes+ also Retain the Original Indexes The answer is `Multi Index`  `Keys` parameter+ represents unique key identifier attached to each dataframeNow we have unique identifier for each row, also retaining the original index.<jupyter_code>sales = pd.concat(objs=[week1, week2], keys=['Week1', 'Week2'])
sales.head(3)<jupyter_output><empty_output><jupyter_text>Now we can use our good old trusty `.loc` to extract information.<jupyter_code>sales.loc[('Week1',)]

sales.loc[('Week1', 0)]
sales.loc['Week2', 50]

sales.loc[('Week1', 240), 'Customer ID']
sales.loc[('Week2', 183), ['Customer ID', 'Food ID']]<jupyter_output><empty_output><jupyter_text>-----  4) Inner Joins, Part 1<jupyter_code>week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv')
foods = pd.read_csv('Data/Restaurant - Foods.csv')
week1.head(2)
week2.head(2)<jupyter_output><empty_output><jupyter_text>`on` needs to be on same name key<jupyter_code>week1.merge(week2, how='inner', on='Customer ID').head()<jupyter_output><empty_output><jupyter_text>We can see that Food ID is suffixed with x and y indicating Week1 and week2 tables.There are also Customers who appear more than twice. We can dig into this.Example: for customer ID 155, he came to in week1 two times and ordered different food items.**There will be no de-dup.****Pandas will just combine every possible combinations.**<jupyter_code>week1[week1['Customer ID'] == 155]
week2[week2['Customer ID'] == 155]<jupyter_output><empty_output><jupyter_text>We can customize `suffixes` too<jupyter_code>week1.merge(week2, how='inner', on='Customer ID', suffixes=['Week - 1', 'Week - 2'])<jupyter_output><empty_output><jupyter_text>------  5) Inner Joins, Part 2<jupyter_code>week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv')
foods = pd.read_csv('Data/Restaurant - Foods.csv')
week1.head(2)
week2.head(2)<jupyter_output><empty_output><jupyter_text>Who are the customers who ordered the same food Item in both week1 and week2?<jupyter_code>week1.merge(week2, how='inner', on=['Customer ID', 'Food ID'])
week1[week1['Customer ID'] == 21]
week2[week2['Customer ID'] == 21]<jupyter_output><empty_output><jupyter_text>------  6) Outer Joins<jupyter_code>week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv')
foods = pd.read_csv('Data/Restaurant - Foods.csv')
week1.head(2)
week2.head(2)
week1.merge(week2, how='outer', on='Customer ID', suffixes=[' - Week1', ' - Week2'])
len(week1.merge(week2, how='outer', on='Customer ID', suffixes=[' - Week1', ' - Week2']))<jupyter_output><empty_output><jupyter_text>`indicator` : tells visually where data are pulled from+ we can see which table, data were pulled from<jupyter_code>merged = week1.merge(week2, how='outer', on='Customer ID', suffixes=[' - Week1', ' - Week2'], 
                   indicator=True)

merged
merged['_merge'].value_counts()<jupyter_output><empty_output><jupyter_text>We can see 197 unique customers in Week2, 195 unique customers in Week 1 and 62 common customers in both Week1 and Week2.<jupyter_code>197 + 195 + 62<jupyter_output><empty_output><jupyter_text>Let's say we want to find out customers who visited to Restrurant in either Week1 and Week2, not in both weeks.<jupyter_code>mask = merged['_merge'] != 'both'
merged[mask]

mask = merged['_merge'].isin(['left_only', 'right_only'])
merged[mask]<jupyter_output><empty_output><jupyter_text>---  7) Left Joins / Right Joins<jupyter_code>week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv')
foods = pd.read_csv('Data/Restaurant - Foods.csv')
week1.head(3)
foods.head(3)<jupyter_output><empty_output><jupyter_text>Which foods does customer order?<jupyter_code>week1.merge(foods, how='left', on='Food ID')<jupyter_output><empty_output><jupyter_text>`sort` parameter<jupyter_code>week1.merge(foods, how='left', on='Food ID', sort=True)
week1.merge(week2, how='right', on='Customer ID', suffixes=[' - Week1', ' - Week2'], indicator=True)<jupyter_output><empty_output><jupyter_text>-------  8) The `left_on` and `right_on` Parameters+ usually use them when we have different Keys in left and right tables.<jupyter_code>week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv')
foods = pd.read_csv('Data/Restaurant - Foods.csv')
week2.head(3)
customers.head(3)<jupyter_output><empty_output><jupyter_text>If we have Keys with different name in tables<jupyter_code>week2.merge(customers, how='left', left_on='Customer ID', right_on='ID').head(2)<jupyter_output><empty_output><jupyter_text>After merging, we can see that both 'Customer ID' and 'ID' are presented. **As those information are duplicated, we can drop one of them.**<jupyter_code>week2.merge(customers, how='left', left_on='Customer ID', right_on='ID').drop('ID', axis=1)

week2 = week2.merge(customers, how='left', left_on='Customer ID', right_on='ID', sort=True).drop('ID', axis=1)
week2<jupyter_output><empty_output><jupyter_text>-------  9) Merging by Indexes with the `left_index` and `right_index` Parameters+ Sometimes we want to use column indexes to merge. Then we can use those parameters.<jupyter_code>week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv', index_col=['ID'])
foods = pd.read_csv('Data/Restaurant - Foods.csv', index_col=['Food ID'])
customers.head(3)
foods.head(3)
week1.head(3)<jupyter_output><empty_output><jupyter_text>We want to merge Week1 and Customers Table. However in Customer table, there is no Customer ID column. Instead it is index.+ We can use `right_index=True` to tell pandas that we want to use right index for merging+ one advantage is no additional column is created (example: ID column that we needed to drop previously.)<jupyter_code>sales = week1.merge(customers, how='left', left_on='Customer ID', right_index=True)
sales.head(3)
# merge with Foods df
sales = sales.merge(foods, how='left', left_on='Food ID', right_index=True)
sales.head(3)<jupyter_output><empty_output><jupyter_text>We want to see the sequences of Customers purchasing<jupyter_code>week1.head(3)
week2.head(3)
week1.merge(week2, how='left', left_index=True, right_index=True, suffixes=[' - Week 1', ' - Week 2'])

# We can see which customers made first purchases in week 1 and week 2, second purchase, third purchase, etc.<jupyter_output><empty_output><jupyter_text>-----  10) The `.join()` Method<jupyter_code>week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv')
foods = pd.read_csv('Data/Restaurant - Foods.csv')
satisfication = pd.read_csv('Data/Restaurant - Week 1 Satisfaction.csv')
week1.head(3)
satisfication.head(3)
week1.merge(satisfication, how='left', left_index=True, right_index=True).head(3)<jupyter_output><empty_output><jupyter_text>We can get the same result by using join.+ join will merge using indexes of both tables.+ However, merge method is more versitile.<jupyter_code>week1.join(satisfication).head(3)<jupyter_output><empty_output><jupyter_text>-----  11) The `pd.merge()` Method<jupyter_code>week1 = pd.read_csv('Data/Restaurant - Week 1 Sales.csv')
week2 = pd.read_csv('Data/Restaurant - Week 2 Sales.csv')
customers = pd.read_csv('Data/Restaurant - Customers.csv')
foods = pd.read_csv('Data/Restaurant - Foods.csv')
satisfication = pd.read_csv('Data/Restaurant - Week 1 Satisfaction.csv')<jupyter_output><empty_output><jupyter_text>It is basically the same like directly using DataFrame. The only difference is to tell which is `left` and `right` dataframes.<jupyter_code>pd.merge(left=week1, right=customers, how='left', left_on='Customer ID', right_on = 'ID')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1397195803510148, 'fraction_numerical': 0.02235513285616237, 'mean_word_length': 6.402031930333817, 'pattern_counts': {'":': 0, '<': 125, '<?xml version=': 0, '>': 125, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '31', 'dir': 'jupyter-structured-clean-dedup', 'id': '337964', 'license': 'MIT', 'n_tokens_mistral': 3691, 'n_tokens_neox': 3458, 'n_words': 883, 'path': 'Pandas - Data Analysis with Pandas and Python - BP/09_Merging_Joining_Concatenating_DataFrames.ipynb', 'repo_name': 'ptyadana/probability-and-statistics-for-business-and-data-science', 'size': '10199'}