How to check if a row of a Pandas dataframe has a cell with a specific value and if it does modify the last cell?

Lindeby 0
Viewed 47

I have a dataframe df:

name age_5_9 age_10_14 age_15_19
Alice no bones broken no bones broken broke 1 bone
Bob no bones broken broke 2 bones no bones broken
Charles no bones broken no bones broken no bones broken

I would like to create a column broke_a_bone that is 1 when any of the rows has a value 'broke 1 bone' or 'broke 2 bones' in any of the columns age_5_9, age_10_14, or age_15_19; otherwise it should be 0.

It should look like this:

name age_5_9 age_10_14 age_15_19 broke_a_bone
Alice no bones broken no bones broken broke 1 bone 1
Bob no bones broken broke 2 bones no bones broken 1
Charles no bones broken no bones broken no bones broken 0

I tried to use .iterrows or .apply() but I just can't seem to make it work.

Thanks in advance.

dataframe pandas python
3 Answers

You can use filter to select the "age" columns, then check if any value per row is not (ne) "no bones broken".

Convert the resulting boolean to integer for 0/1:

df['broke_a_bone'] = (df.filter(like='age_').ne('no bones broken')
                        .any(axis=1).astype(int)
                     )

output:

      name          age_5_9        age_10_14        age_15_19  broke_a_bone
0    Alice  no bones broken  no bones broken     broke 1 bone             1
1      Bob  no bones broken    broke 2 bones  no bones broken             1
2  Charles  no bones broken  no bones broken  no bones broken             0
mozway0

I'd advise converting everything to numeric:

age_cols = df.filter(like='age').columns

df[age_cols] = df[age_cols].apply(lambda x: pd.to_numeric(x.str.replace('\D', '', regex=True)).fillna(0).astype(int))

print(df)

Output:

      name  age_5_9  age_10_14  age_15_19
0    Alice        0          0          1
1      Bob        0          2          0
2  Charles        0          0          0

Now, we can sum rows easily!

df['num_broken'] = df[age_cols].sum(axis=1)

print(df)

# Output:

      name  age_5_9  age_10_14  age_15_19  num_broken
0    Alice        0          0          1           1
1      Bob        0          2          0           2
2  Charles        0          0          0           0
BeRT2me0

you can use np.where (faster)


a1 = df["age_5_9"].isin(['broke 1 bone', 'broke 2 bones'])
a2 = df["age_10_14"].isin(['broke 1 bone', 'broke 2 bones'])
a3 = df["age_15_19"].isin(['broke 1 bone', 'broke 2 bones'])

df['broke_a_bone'] = np.where((a1|a2|a3), 1, 0)

#or:
a1 = df["age_5_9"].eq(['no bones broken'])
a2 = df["age_10_14"].eq(['no bones broken'])
a3 = df["age_15_19"].eq(['no bones broken'])

df['broke_a_bone'] = np.where((a1&a2&a3), 0, 1)
khaled koubaa0
Related