Changing values in each row of a column based on values in other columns of the corresponding row (Python/Pandas)

Question

data = [{'a': 12, 'b': 2, 'c': 3, 'd': 'bat'},
        {'a': 'NaN', 'b': 20, 'c': 30, 'd': 'ball'},
        {'a': 4, 'b': 20, 'c': 30, 'd': 'pin'}]

df = pd.DataFrame(data)

I'm having a hard time figuring out how to replace the NaN values in column A with values in column b based on conditions set on columns c and d. For example, if I wanted to replace the NaN values in the a column with the values of column b (2 and 20, respectively) under circumstances where the value of c > 20 and 'd' = 'ball.

Could someone help me with this?

I've tried a number of solutions with df.loc and df.mask that have not worked.

Unfortunately, no. The issue is that I am trying to replace values in column a with corresponding row values from column b. I am not simply trying to replace all NaN values with a particular number or string, as many of these examples show. — Aleisha Khan
– Aleisha Khan, Commented Jul 26, 2023 at 19:55
You can use df['other_column'] as the source value instead of a particular number. — Barmar
– Barmar, Commented Jul 26, 2023 at 19:56

Andrey · Accepted Answer · 2023-07-26 20:29:48Z

0

You can use df.apply for this

def fill_na(row: pd.Series):
    if pd.isna(row[0]):
        if row[2] > 20 and row[3] == "ball":
            row[0] = row[1]
    return row

df = df.apply(fill_na, axis=1)

As you don't have rows that would satisfy your criteria, you can use this for testing

[{'a': np.NaN, 'b': 2, 'c': 3, 'd': 'bat'},
 {'a': 10, 'b': 20, 'c': 30, 'd': 'ball'},
 {'a': np.NaN, 'b': 20, 'c': 30, 'd': 'pin'},
 {'a': np.NaN, 'b': 15, 'c': 30, 'd': 'ball'}]

edited Jul 26, 2023 at 20:29

answered Jul 26, 2023 at 20:01

Andrey

3382 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jjk · Accepted Answer · 2023-07-26 20:12:19Z

0

Try:

df['a'].loc[(df['a'].isna())&(df['c'] > 20) & (df['d'] == 'ball')] =df['b'].loc[(df['a'].isna())&(df['c'] > 20) & (df['d'] == 'ball')]

edited Jul 26, 2023 at 20:12

answered Jul 26, 2023 at 20:05

jjk

1159 bronze badges

2 Comments

Aleisha Khan Over a year ago

When I try this, it I get the warning: value is trying to be set on a copy of a slice from a DataFrame. I need the change to be applied to the original dataframe.

moken Over a year ago

Answer needs supporting information Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Changing values in each row of a column based on values in other columns of the corresponding row (Python/Pandas)

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related