CODEWITHSUNDEEP
pythonpandasfillna
Harry Jones
Harry Jones

Reputation: 157

Fillna by relating multiple columns using a created function

I have 3 columns in the dataframe. houses = ["house 1", "house 2", "house 3", "house 4", "house 5", "house 6", "house 7", "house 8", "house 9"] room = ["Kitchen", "Bathroom", "Bedroom"] m2 = [8.4.7, NaN, NaN, NaN, 6.3.7]. What I want to do is fill in the blanks with a pattern I set, which would be: If the room column is Kitchen, m2 is 5. If the room column is Bathroom, m2 is 2. If the room column is Bedroom and m2 is 4.

Input:
    houses      room        m2
0   house 1  Kitchen         8
1   house 2  Bathroom        4
2   house 3  Bedroom         7   
3   house 4  Kitchen       NaN
4   house 5  Bathroom      NaN
5   house 6  Bedroom       NaN
6   house 7  Kitchen         6
7   house 8  Bathroom        3
8   house 9  Bedroom         7

Tried df.loc[(df["m2"].isnull() & df["room"] == "Kitchen"), "m2"] == 5
df.loc [(df ["m2"]. isnull () & df ["room"] == "Bathroom"), "m2"] == 2
df.loc [(df ["m2"]. isnull () & df ["room"] == "Bedroom"), "m2"] == 4

but it did not work.

FutureWarning: elementwise comparison failed; returning scalar, but in the future will perform elementwise comparison

Expected output:

    houses      room        m2
0   house 1  Kitchen         8
1   house 2  Bathroom        4
2   house 3  Bedroom         7   
3   house 4  Kitchen         5
4   house 5  Bathroom        2
5   house 6  Bedroom         4
6   house 7  Kitchen         6
7   house 8  Bathroom        3
8   house 9  Bedroom         7

Upvotes: 0

Views: 73

Answers (1)

Henry Ecker
Henry Ecker

Reputation: 35646

A fillna + map option:

df['m2'] = df['m2'].fillna(
    df['room'].map({'Kitchen': 5, 'Bathroom': 2, 'Bedroom': 4})
).astype(int)

A np.where + replace option:

df['m2'] = np.where(
    df['m2'].isna(),
    df['room'].replace({'Kitchen': 5, 'Bathroom': 2, 'Bedroom': 4}),
    df['m2']
).astype(int)

    houses      room  m2
0  house 1   Kitchen   8
1  house 2  Bathroom   4
2  house 3   Bedroom   7
3  house 4   Kitchen   5
4  house 5  Bathroom   2
5  house 6   Bedroom   4
6  house 7   Kitchen   6
7  house 8  Bathroom   3
8  house 9   Bedroom   7

Upvotes: 1

Related Questions