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

Table of lateral joining together is in horizontal two tables on the basis of public listed spliced together, this article mainly rely on the merge approach to the type of connection table attention related operation is to connect the two tables are what type, the main three kinds of situations: one-on-one, many-to-one and many-to-many in order to facilitate discussion, default merge two tables have a public listed here

1 One-to-one splicing

One-to-one concatenation means that the common columns of the two tables to be concatenated are one-to-one

Transcript:

Student Information Sheet:

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

result:

Student Id name score ranking 0 0 Xiaoming 90 3 1 1 Xiaogang 89 4 2 2 Xiaohong 98 1 3 3 Xiaolan 63 7 4 4 Li Hua 95 2 5 5 Zhang SAN 23 9 6 6 Li Si 56 8 7 7 Wang Wu 85 5 8 8 Xiao Zhang 72 6Copy the code
Info_df = pd.read_excel(r 'c :\Users\viruser.v-desktop\ desktop\ student information table.xlsx ') print(info_df)Copy the code

result:

Class 0 Grade 3 Class 2 1 1 Grade 5 Class 2 2 Grade 6 Class 1 3 3 Grade 1 Class 5 4 Grade 3 Class 5 5 Grade 4 Class 3 6 6 Grade 3 Class 2 7 7 Grade 2 Class 1 8 8 Grade 5 Class 2Copy the code
new_df = pd.merge(score_df, info_df)
print(new_df)
Copy the code

result:

Student no name result rank grade class 0 xiaoming 90 3 class 2, grade 3 1 1 xiaogang 89 4 class 3, grade 5 2 xiahong 98 1 class 1, grade 63 3 xiaolan 63 7 class 5, grade 1 4 li hua 95 2 class 2, grade 3 5 zhang 3 23 9 Grade 4 Class 3 6 6 Li 4 56 8 Grade 3 Class 2 7 7 Wang 5 85 5 Grade 2 Class 1 8 8 Xiao Zhang 75 6 Grade 5 Class 2Copy the code

At this point, the method automatically looks for the common columns in both tables and treats the found common columns as join columns. In this example, the secondary number column is joined as a column linking the two tables, the grade table and the student information table.

Two many-to-one splices

A many-to-one join is when the common columns of the two tables to be joined are not one-to-one, and the common columns of one table have duplicate values and the common columns of the other table are unique.

Transcript:

Student Information Sheet:

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

result:

Student Id Name Score Examination Time 0 0 Xiaoming 90 Mid-term 11 Xiaogang 89 Final 22 Xiahong 98 Mid-term 3 3 Xiaolan 63 Final 4 4 Li Hua 95 Mid-term 5 5 Zhang SAN 23 Final 6 6 Li Si 56 Mid-term 7 7 Wang Wu 85 Final 8 8 Xiao Zhang 75 mid-term 90 Xiao Ming 90 final 10 1 Xiaogang 148 Mid-term 11 2 Xiao Hong 125 Final 12 3 Xiaolan 115 Mid-term 13 4 Li Hua 150 final 14 5 Zhang SAN 89 Mid-term 15 6 Li Si 45 Final 16 7 Wang Wu 135 Midterm 17 8 xiaozhang 126 FinalCopy the code
Info_df = pd.read_excel(r 'c :\Users\viruser.v-desktop\ desktop\ student information table.xlsx ') print(info_df)Copy the code

result:

Class 0 Grade 3 Class 2 1 1 Grade 5 Class 2 2 Grade 6 Class 1 3 3 Grade 1 Class 5 4 Grade 3 Class 5 5 Grade 4 Class 3 6 6 Grade 3 Class 2 7 7 Grade 2 Class 1 8 8 Grade 5 Class 2Copy the code
new_df = pd.merge(score_df, info_df)
print(new_df)
Copy the code

result:

Student No. Name Result Examination Time Grade Class 0 Xiaoming 90 Mid-term Class 2 grade 3 10 Xiaoming 90 Final Grade 2 Class 2 2 1 Xiaogang 89 Final Grade 3 Class 3 3 1 Xiaogang 148 Mid-term Class 3 Grade 5 4 2 Xiaohong 98 Mid-term 1 Grade 6 5 2 Xiaohong 125 Final Exam, Class 1, Grade 6 63 Xiaolan 63 Final Exam, Class 5, Grade 1 7 3 Xiaolan 115 Mid-term exam, Class 5, Grade 1 8 4 Lihua 95 Mid-term Exam, Class 2, Grade 3 9 4 Lihua 150 Final Exam, Class 2, Grade 3 10 5 Zhang SAN 23 Final exam Grade 4 Class 3 11 5 Zhang 3 89 Mid-term Grade 4 Class 3 12 6 Li 4 56 Mid-term Grade 3 Class 2 13 6 Li 4 45 Final Grade 3 Class 2 14 7 Wang 5 85 Final Grade 2 Class 1 15 7 Wang 5 135 Mid-term Grade 2 Class 1 16 8 Xiao Zhang 75 Mid-term 5 Class 2 17 8 xiaozhang 126 Final 5 Class 2Copy the code

3 Many-to-many splicing

Many-to-many join means that the common columns of the two tables to be joined are not one-to-one, and the common columns in both tables have duplicate values. Many-to-many join is equivalent to multiple many-to-one joins

Commodity Information Sheet

User shopping list

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 Code Product Name 0 fruit 0 Apple 1 Fruit 1 Orange 2 daily necessities 2 toothbrush 3 Daily necessities 3 refrigerator 4 Daily necessities 4 TV 5 Food 0 Apple 6 Food 1 Orange 7 Home appliances 3 refrigerator 8 home appliances 4 TV 9 Large items 3 refrigerator 10 large pieces 4 TV setsCopy 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 No. 0 Xiaoming 0 1 Xiaoming 2 2 Xiaoming 4 3 Xiaogang 7 4 Xiaogang 5 5 Xiaogang 4 6 Xiaogang 3Copy the code
new_df = pd.merge(goods_df, user_df)
print(new_df)
Copy the code

result:

Category Item No. Commodity Name User Name 0 Fruit 0 Apple Xiaoming 1 Food 0 Apple Xiaoming 2 Daily necessities 2 Toothbrush Xiaoming 3 Daily necessities 3 Refrigerator Xiaogang 4 Home appliances 3 Refrigerator Xiaogang 5 Large items 3 Refrigerator Xiaogang 6 Daily necessities 4 TV Xiaoming 7 Daily necessities 4 TV Xiaogang 8 home appliances 4 TV Xiaoming 9 home appliances 4 TV Xiaogang 10 large 4 TV Xiaoming 11 large 4 TV XiaogangCopy the code

4 On Specifies the usage of the parameter

The on parameter is typically used to specify the join key, which is a common column in both tables. The effect is the same as using the default public column

Commodity Information Sheet

User shopping list

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 Code Product Name 0 fruit 0 Apple 1 Fruit 1 Orange 2 daily necessities 2 toothbrush 3 Daily necessities 3 refrigerator 4 Daily necessities 4 TV 5 Food 0 Apple 6 Food 1 Orange 7 Home appliances 3 refrigerator 8 home appliances 4 TV 9 Large items 3 refrigerator 10 large pieces 4 TV setsCopy 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 No. 0 Xiaoming 0 1 Xiaoming 2 2 Xiaoming 4 3 Xiaogang 7 4 Xiaogang 5 5 Xiaogang 4 6 Xiaogang 3Copy the code
New_df = pd.merge(goods_df, user_df, on=[' product id ']) print(new_df)Copy the code

result:

Category Item No. Commodity Name User Name 0 Fruit 0 Apple Xiaoming 1 Food 0 Apple Xiaoming 2 Daily necessities 2 Toothbrush Xiaoming 3 Daily necessities 3 Refrigerator Xiaogang 4 Home appliances 3 Refrigerator Xiaogang 5 Large items 3 Refrigerator Xiaogang 6 Daily necessities 4 TV Xiaoming 7 Daily necessities 4 TV Xiaogang 8 home appliances 4 TV Xiaoming 9 home appliances 4 TV Xiaogang 10 large 4 TV Xiaoming 11 large 4 TV XiaogangCopy the code

5 Connection of a common column with multiple columns

Commodity Information Sheet

User shopping list

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 Code Product Name 0 fruit 0 Apple 1 Fruit 1 Orange 2 daily necessities 2 toothbrush 3 Daily necessities 3 refrigerator 4 Daily necessities 4 TV 5 Food 0 Apple 6 Food 1 Orange 7 Home appliances 3 refrigerator 8 home appliances 4 TV 9 Large items 3 refrigerator 10 Big 4 TV 11 big 5 Coffee 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:

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)
print(new_df)
Copy the code

result:

Category Item No. Commodity Name User Name 0 Fruit 0 Apple Xiaoming 1 Food 0 Apple Xiaoming 2 Daily necessities 2 Toothbrush Xiaoming 3 Daily necessities 3 Refrigerator Xiaogang 4 Home appliances 3 Refrigerator Xiaogang 5 Large items 3 Refrigerator Xiaogang 6 Daily necessities 4 TV Xiaoming 7 Household items 4 TV Xiaogang 8 home appliances 4 TV Xiaoming 9 home appliances 4 TV Xiaogang 10 large 4 TV Xiaoming 11 large 4 TV Xiaogang 12 large 5 tea table Xiaogang 13 household items 7 hand warming baby XiaogangCopy the code