Joining tables in python with different lengths based on a key field

Question

So I want to make a join on two tables with a key field which both tables contain, so I can make a side by side comparison.

Table A has 1164 rows and table B has 74 rows. And the common field in Table A is called EmployeeID and the 'same' field in Table B is called UserID.

Table A

Table B

I want to have the output in 3 forms:

1. Table 1 with the records where the key field values were only found in TableA. (UNMATCHED LEFT)
2. Table 2 with the matching records (so the key field value was found in Table A and B. (INNER JOIN)
3. Table 3 with the records that were only in Table B. (UNMATCHED RIGHT)

What is the best way to tackle this problem?

When I used this code:

data_left_join = pd.merge(table_a, table_b, how='left')

I got 48268 rows as result.

All the articles I could find were in SQL or R.

I managed to import the tables and make some modifications to the tables. But I got stuck here.

Thank in advance.

Answer 1

You can use merge() with the left_on and right_on arguments to specify your key field, then check for NaN values to find which rows are not in a table:

import pandas as pd

# Create dataframes to test with
table_a = pd.DataFrame({
    "value": [1, 2, 3, 4, 5],
    "employee_id": [100, 200, 300, 400, 500]
})
print "Table A:\n", table_a

table_b = pd.DataFrame({
    "value": [1, 2, 3, 4, 5],
    "user_id": [100, 200, 300, 1000, 2000],
    "age": [40, 50, 60, 70, 80]
})
print "\nTable B:\n", table_b

# Merge table A (left) on employee_id, and table B (right) on user_id
merged = table_a.merge(table_b, left_on="employee_id", right_on="user_id", how="outer", suffixes=("_tableA", "_tableB"))
print "\nMerged:\n", merged

# Table A-columns with NaNs are not present in table B
only_in_table_a = merged.loc[merged.value_tableB.isnull()]
print "\nOnly in table A:\n", only_in_table_a

# Table B-columns with NaNs are not present in table A
only_in_table_b = merged.loc[merged.value_tableA.isnull()]
print "\nOnly in table B:\n", only_in_table_b

# Rows with no NaNs are in both tables
in_both = merged.dropna(subset=["employee_id", "user_id"])
print "\nIn both:\n", in_both

Which yields:

Table A:
   employee_id  value
0          100      1
1          200      2
2          300      3
3          400      4
4          500      5

Table B:
   age  user_id  value
0   40      100      1
1   50      200      2
2   60      300      3
3   70     1000      4
4   80     2000      5

Merged:
   employee_id  value_tableA   age  user_id  value_tableB
0        100.0           1.0  40.0    100.0           1.0
1        200.0           2.0  50.0    200.0           2.0
2        300.0           3.0  60.0    300.0           3.0
3        400.0           4.0   NaN      NaN           NaN
4        500.0           5.0   NaN      NaN           NaN
5          NaN           NaN  70.0   1000.0           4.0
6          NaN           NaN  80.0   2000.0           5.0

Only in table A:
   employee_id  value_tableA  age  user_id  value_tableB
3        400.0           4.0  NaN      NaN           NaN
4        500.0           5.0  NaN      NaN           NaN

Only in table B:
   employee_id  value_tableA   age  user_id  value_tableB
5          NaN           NaN  70.0   1000.0           4.0
6          NaN           NaN  80.0   2000.0           5.0

In both:
   employee_id  value_tableA   age  user_id  value_tableB
0        100.0           1.0  40.0    100.0           1.0
1        200.0           2.0  50.0    200.0           2.0
2        300.0           3.0  60.0    300.0           3.0