Creation of DataFrame with specific conditions on rows

Carola 0
Viewed 41

Be the following python pandas DataFrame:

ID country money other money_add
832932 France 12131 19 82932
217#8#
1329T2
832932 France 30
31728#

I would like to make the following modifications for each row:

  • If the ID column has any '#' value, the row is left unchanged.
  • If the ID column has no '#' value, and country is NaN, "Other" is added to the country column, and a 0 is added to other column.

Finally, only if the money column is NaN and the other column has value, we assign the values money and money_add from the following table:

other_ID money money_add
19 4532 723823
50 1213 238232
18 1813 273283
30 1313 83293
0 8932 3920

Example of the resulting table:

ID country money other money_add
832932 France 12131 19 82932
217#8#
1329T2 Other 8932 0 3920
832932 France 1313 30 83293
31728#
dataframe pandas python
1 Answers

First set values to both columns if match both conditions by list, then filter non # rows and update values by DataFrame.update only matched rows:

m1 = df['ID'].str.contains('#')
m2 = df['country'].isna()

df.loc[~m1 & m2, ['country','other']] = ['Other',0]

df1 = df1.set_index(df1['other_ID'])
df = df.set_index(df['other'].mask(m1))
df.update(df1, overwrite=False)
df = df.reset_index(drop=True)
print (df)
       ID country   money  other  money_add
0  832932  France   12131   19.0    82932.0
1  217#8#     NaN       ;    NaN        NaN
2  1329T2   Other  8932.0    0.0     3920.0
3  832932  France  1313.0   30.0    83293.0
4  31728#     NaN     NaN    NaN        NaN
jezrael0
Related