This is the 13th day of my participation in the August More text Challenge. For details, see: August More Text Challenge

If there are no common columns in the two tables to be joined, you need to specify a common column for concatenation.

Commodity Information Sheet

User shopping list

1 Specifies a common column name as the connect key

In this case, use left_on and right_on respectively to specify the left table as the key for the join and the right table as the key for the join

Import pandas as pd goods_df = pd.read_excel(r 'c :\Users\viruser.v-desktop\ desktop\ product info table. XLSX ') print(goods_df)Copy the code

result:

Category Number Name 0 fruit 0 Apple 1 Fruit 1 Orange 2 groceries 2 Toothbrush 3 Groceries 3 Refrigerator 4 Groceries 4 TV 5 Food 0 Apple 6 Groceries 1 Orange 7 Appliances 3 refrigerator 8 Appliances 4 TV 9 Large items 3 refrigerator 10 Large 4 TV 11 large 5 tea table 12 daily necessities 7 baby handwardersCopy the code
User_df = pd.read_excel(r 'c :\Users\viruser.v-desktop\ desktop\ user shopping table.xlsx ') print(user_df)Copy the code

result:

User name commodity Code Goods 0 Xiaoming 0 Apple 1 Xiaoming 2 Toothbrush 2 Xiaoming 4 TV 3 Xiaogang 7 hand warming baby 4 Xiaogang 5 Tea table 5 Xiaogang 4 TV 6 Xiaogang 3 refrigeratorCopy the code
New_df = pd.merge(goods_df, user_df, left_on=" id ", right_on= "product code") print(new_df)Copy the code

result:

Category Code Name Username Commodity Code Goods 0 Fruit 0 Apple Xiaoming 0 Apple 1 Food 0 Apple Xiaoming 0 Apple 2 Daily necessities 2 Toothbrush Xiaoming 2 Toothbrush 3 Daily necessities 3 Refrigerator Xiaogang 3 Refrigerator 4 Home Appliances 3 Refrigerator Xiaogang 3 Refrigerator 5 Large items 3 refrigerator xiaogang 3 refrigerator 6 daily necessities 4 TV Xiaoming 4 TV 7 daily necessities 4 TV Xiaogang 4 TV 8 home appliances 4 TV Xiaoming 4 TV 9 home appliances 4 TV Xiaogang 4 TV 10 large items 4 TV Xiaoming 4 TV 11 Large 4 TV xiaogang 4 TV 12 large 5 tea table Xiaogang 5 tea table 13 daily necessities 7 baby xiaogang 7 hand warming babyCopy the code

2 Specify the index as the join key

Import pandas as pd goods_df = pd.read_excel(r 'c :\Users\viruser.v-desktop\ desktop\ XLSX ') print(goods_df.columns)Copy the code

result:

Index([' category ', 'number ',' name '], dtype='object')Copy the code
User_df = pd.read_excel(r 'c :\Users\viruser.v-desktop\ desktop\ users.xlsx ') print(user_df.columns)Copy the code

result:

Index([' username ', 'product code ',' product '], dtype='object')Copy the code

You can also set left_index and right_index to True to specify that the two tables use the index as the join key

Import pandas as pd goods_df = pd.read_excel(r 'c :\Users\viruser.v-desktop\ desktop\ product info table. XLSX ') print(goods_df)Copy the code
new_df = pd.merge(goods_df, user_df, left_index=True, right_index=True)
print(new_df)
Copy the code

result:

Category Code Name User Name Commodity Code Goods 0 Fruit 0 Apple Xiaoming 0 Apple 1 Fruit 1 Orange Xiaoming 2 Toothbrush 2 Daily necessities 2 Toothbrush Xiaoming 4 TV 3 Daily necessities 3 Refrigerator Xiaogang 7 Hand warming baby 4 Daily necessities 4 TV Xiaogang 5 Coffee table 5 Food 0 Apple Xiaogang 4 TV 6 Food 1 Orange Xiaogang 3 RefrigeratorCopy the code

3 Common column names and indexes are mixed

Import pandas as pd goods_df = pd.read_excel(r 'c :\Users\viruser.v-desktop\ desktop\ product information table. XLSX ') user_df = Read_excel (r 'c :\Users\viruser.v-desktop\ desktop\ user shopping table. XLSX ') new_df = pd.merge(goods_df, user_df, left_index=True, Right_on =' product code ') print(new_df)Copy the code

result:

Category Code Name User Name Commodity Code Goods 0 Fruit 0 Apple Xiaoming 0 Apple 1 Fruit 1 Orange Xiaoming 2 Toothbrush 2 Daily necessities 2 Toothbrush Xiaoming 4 TV 3 Daily necessities 3 Refrigerator Xiaogang 7 Hand warming baby 4 Daily necessities 4 TV Xiaogang 5 Coffee table 5 Food 0 Apple Xiaogang 4 TV 6 Food 1 Orange Xiaogang 3 RefrigeratorCopy the code