<jupyter_start><jupyter_text>The pd.concat() and df.append(), Part 1<jupyter_code>week1
week2
# ignore_index as True when getting a new index
pd.concat(objs=[week1, week2], ignore_index=True)
week1.append(other=week2, ignore_index=True)
# df.append() can append multiple times
week1.append(other=[week1, week1, week2, week2], ignore_index=True)<jupyter_output><empty_output><jupyter_text>The pd.concat() and df.append(), Part 2<jupyter_code>sales = pd.concat(objs=[week1, week2], ignore_index=False, keys=["Week 1", "Week 2"])
sales.head()
# access the multiindex using the tuple
sales.loc[("Week 1")]
sales.loc[("Week 2", 7)]
# access the multiIndex values by a tuple and columns as a list or a tuple
sales.loc[("Week 1", 77), ("Food ID", "Customer ID")]
sales.loc[("Week 1", 77), ["Food ID", "Customer ID"]]<jupyter_output><empty_output><jupyter_text>Inner Joins, Part 1<jupyter_code>week1 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Sales.csv")
week2 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 2 Sales.csv")
customers = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Customers.csv")
foods = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Foods.csv")
week1.head()
week2.head()
week1.merge(right=week2, how="inner", on="Customer ID")
# prove
week2[week2["Customer ID"] == 537]
week1.merge(right=week2, how="inner", on="Customer ID").head(4)
# we got customer ID as 155 having two purchases
week1[week1["Customer ID"] == 155]
week2[week2["Customer ID"] == 155]
# suffixes后缀 
week2.merge(right=week1, how="inner", on="Customer ID", suffixes=[" - Week 1", " - Week 2"])<jupyter_output><empty_output><jupyter_text>Inner Joins, Part 2<jupyter_code>week1 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Sales.csv")
week2 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 2 Sales.csv")
customers = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Customers.csv")
foods = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Foods.csv")
week1.merge(right=week2, how="inner", on=["Customer ID", "Food ID"])
week1.merge(right=week2, how="inner", on=["Food ID", "Customer ID"])
# by default, if you omit the on = None then every columns are treated as PK
# Since every columns are same so only matching columns are there
week1.merge(right=week2, how="inner")
# customer 21 comes twice in the week 1
week1[week1["Customer ID"] == 21]
week2[week2["Customer ID"] == 21]
# customer 578 comes twice in the week 2
week1[week1["Customer ID"] == 578]
week2[week2["Customer ID"] == 578]<jupyter_output><empty_output><jupyter_text>Full Outer Joins<jupyter_code>week1 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Sales.csv")
week2 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 2 Sales.csv")
customers = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Customers.csv")
foods = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Foods.csv")
week1
week2
# 250 * 2 - 454 = 56 rows can be matched in Week 1 and Week 2 Tables
merged = week1.merge(right=week2, how="outer", on=["Customer ID"], suffixes=[" Week 1", " Week 2"], 
                     indicator=True)
merged
merged["_merge"].value_counts()
# full outer join where id is Null
merged[merged["_merge"] != "both"]
merged[merged["_merge"].isin(values=["left_only", "right_only"])]<jupyter_output><empty_output><jupyter_text>Left Joins<jupyter_code>week1 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Sales.csv")
week2 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 2 Sales.csv")
customers = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Customers.csv")
foods = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Foods.csv")
week1.head()
foods.head()
week1.merge(right=foods, how="left", on=["Food ID"], indicator=True, sort=True)<jupyter_output><empty_output><jupyter_text>The left_on and right_on Parameters<jupyter_code>week1 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Sales.csv")
week2 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 2 Sales.csv")
customers = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Customers.csv")
foods = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Foods.csv")
week2.head()
customers
# ID is from the week2 table and Customer ID is from the right table
week2.merge(right=customers, how="left", left_on="Customer ID", right_on="ID", sort=True, 
            indicator=True)
# Drop the ID column
week2.merge(right=customers, how="left", left_on="Customer ID", right_on="ID", sort=True, 
            indicator=True).drop(labels=["ID"], axis=1)<jupyter_output><empty_output><jupyter_text>Merging by Index with left_index and right_index Parameters<jupyter_code>week1 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Sales.csv")
week2 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 2 Sales.csv")
customers = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Customers.csv",
                       index_col="ID")
foods = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Foods.csv", 
                   index_col="Food ID")
customers.head()
foods.head()
week1.head()
# week1 merge customers into sales
sales = week1.merge(right=customers, how="left", left_on="Customer ID", right_index=True, 
                    sort=False, indicator=True).drop(labels="_merge", axis="columns")
sales.head()
# sales merge foods into sales
sales = sales.merge(right=foods, how="left", left_on="Food ID", right_index=True, sort=True, 
                    indicator=True)
sales.head()
# merge week1 and week2 using indexes
week1.merge(right=week2, how="left", left_index=True, right_index=True, 
            suffixes=["- Week 1", " - Week 2"], sort=True, indicator=True)<jupyter_output><empty_output><jupyter_text>The join() Method<jupyter_code>week1 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Sales.csv")
week2 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 2 Sales.csv")
customers = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Customers.csv")
foods = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Foods.csv")
satisfaction = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Satisfaction.csv")
week1.head()
satisfaction.head()
# use indexes to merge
week1.merge(right=satisfaction, how="left", left_index=True, right_index=True)
week1.join(other=satisfaction, how="left")<jupyter_output><empty_output><jupyter_text>The pd.merge() Method<jupyter_code>week1 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Sales.csv")
week2 = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 2 Sales.csv")
customers = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Customers.csv")
foods = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Foods.csv")
satisfaction = pd.read_csv("/Volumes/GoogleDrive/My Drive/Udemy/Pandas/Data Source/pandas/Restaurant - Week 1 Satisfaction.csv")
week1.head()
customers.head()
week1.merge(right=customers, how="left", left_on="Customer ID", right_on="ID", 
            sort=True).drop(labels="ID", axis=1)
pd.merge(left=week1, right=customers, how="left", left_on="Customer ID", right_on="ID",
         sort=True, indicator=True).drop(labels=["ID"], axis=1)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1483947681331748, 'fraction_numerical': 0.019381688466111772, 'mean_word_length': 5.53027950310559, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '292448', 'license': 'MIT', 'n_tokens_mistral': 3180, 'n_tokens_neox': 2961, 'n_words': 578, 'path': 'Section 10.ipynb', 'repo_name': 'perryisbusy/My-Pandas-Playground', 'size': '8410'}