Perform column update of a table with given conditions in a DataFrame pandas

Carola 0
Viewed 30

Be the following python pandas DataFrame:

| ID       | country        | money     | code     | money_add | other |
| -------- | -------------- | --------- | -------- | --------- | ----- |
| 832932   | Other          | NaN       | 00000    |   NaN     | NaN   |
| 217#8#   | NaN            | NaN       | NaN      |   NaN     | NaN   |
| 1329T2   | France         | 12131     | 00020    |   3452    | 123   |
| 124932   | France         | NaN       | 00016    |   NaN     | NaN   |
| 194022   | France         | NaN       | 00000    |   NaN     | NaN   |

If code column is not NaN and the money column is NaN, we update the values money, other and money_add from the following table. Using the code and cod_t columns as a key.


| cod_t    | money  | money_add | other |
| -------- | ------ | --------- | ----- |
| 00000    |  4532  | 72323     | 321   |
| 00016    |  1213  | 23822     | 843   |
| 00018    |  1313  | 8393      | 183   |
| 00020    |  1813  | 27328     | 128   |
| 00030    |  8932  | 3204      | 829   |

Example of the resulting table:

| ID       | country        | money     | code     | money_add | other |
| -------- | -------------- | --------- | -------- | --------- | ----- |
| 832932   | Other          | 4532      | 00000    |   72323   | 321   |
| 217#8#   | NaN            | NaN       | NaN      |   NaN     | NaN   |
| 1329T2   | France         | 12131     | 00020    |   3452    | 123   |
| 124932   | France         | 1213      | 00016    |   23822   | 843   |
| 194022   | France         | 4532      | 00000    |   72323   | 321   |
dataframe pandas python
1 Answers

Test duplicates in df1 first:

print (df1[df1.duplicated('cod_t', keep=False)])

Also is possible test if same dtypes:

cols = df.columns.intersection(df1.columns)
print (df[cols].dtypes.eq(df1[cols].dtypes))
money        True
money_add    True
other        True
dtype: bool

Then use simplified previous solution with removed duplicates:

df1 = df1.drop_duplicates('cod_t').set_index('cod_t')
df = df.set_index(df['code'])
df.update(df1, overwrite=False)
df = df.reset_index(drop=True).reindex(df.columns, axis=1)
print (df)
       ID country  money   code money_add other
0  832932   Other   4532  00000     72323   321
1  217#8#     NaN    NaN    NaN       NaN   NaN
2  1329T2  France  12131  00020      3452   123
3  124932  France   1213  00016     23822   843
4  194022  France   4532  00000     72323   321
jezrael0
Related