Pandas. Need to merge tables with value mapping-CodePudding

merge two tables and assign values where both have the same values

I have two dataframe tables:

Table1=


| Column A | Column B | Column C |
| -------- | -------- | -------- |
| Cell 1   | Cell 2   |  None    |
| Cell 3   | Cell 4   |  None    |

Table2 =


| Column A | Column B | Column C |
| -------- | -------- | -------- |
| Cell 1   | Cell 2   |  Value1  |
| Cell 3   | Cell 4   |  Value2  |

Table1 =
| Column A | Column B | Column C |
| -------- | -------- | -------- |
| Cell 1   | Cell 2   |  None    |<--|
| Cell 3   | Cell 4   |  None    |   |
                                     |
Table2 =                             | Table1[A][0] == Table2[A][0] -> Table1[C][0] = Table2[C][0]
| Column A | Column B | Column C |   | And so with all the                            
                                       lines that have a match, for example, by the value in                                                                                       
| -------- | -------- | -------- |   | columns A
| Cell 1   | Cell 2   |  Value1  |<--|
| Cell 3   | Cell 4   |  Value2  |

The first table is larger than the second.

The first table and the second table have the same values. I want to fill in column C in table 1 for the same values in table 1.

Simply put, if table 1 and table 2 have the same values in the columns, for example, in column A, then the value in column C from the second table will be assigned to column C in the first table.

if Table1[A]['value'] == Table2[A]['value']: Table1[C]['value'] = Table2[C]['value']

Also I tried to merge the tables but the tables didn't merge (Table 1 remained unchanged):

df = Table1['C'].merge(Table2, on=['C'], how='left')

CodePudding user response：

Set the common columns as index then use update on table1 to substitute the values from table2

cols = ['Column A', 'Column B']

Table1 = Table1.set_index(cols)
Table1.update(Table2.set_index(cols))
Table1 = Table1.reset_index()

Result

print(Table1)

  Column A Column B Column C
0   Cell 1   Cell 2   Value1
1   Cell 3   Cell 4   Value2

CodePudding user response：

Providing you do not have any data in Table1['Table C'] that you want to keep, then you could drop Table C from the first table, and then merge

Table1 = Table1.drop(['Table C'], axis=1)
Table1 = Table1.merge(Table2, on=['Table A', 'Table B'], how='left')

Output:

Note:

If you want a one-liner:

Table1 = Table1.drop(['Table C'], axis=1).merge(Table2, on=['Table A', 'Table B'], how='left')