Fill null values based on condition for another column (pandas)

Question

I'm trying to fill in null values in my dataset. The description column has the type of apartment. For Studio, I'm trying to fill in as 0 bedroom while for Rooms I'm trying to fill in as 1 bedroom.

I tried

df.loc[df['Description'] == 'Studio', 'Bedrooms'] = df['Bedrooms'].fillna(0)
df.loc[df['Description'] == 'Rooms', 'Bedrooms'] = df['Bedrooms'].fillna(1)

but it doesn't work. Does anyone have any suggestions?

Answer 1

without sample DataFrame I had to quess what exactly you wanted, hopefully I was right.

You can use simple lambda function:

 # Import pandas, numpy
    import pandas as pd
    import numpy as np    

# Sample df
    d = {'Desc': ['Studio', 'Rooms', 'Studio', 'Studio', 'Studio', 'Rooms','Rooms','Rooms','Studio', 'Rooms']}
    df = pd.DataFrame(data=d)
    df['Rooms'] = np.nan

 # Lambda function       
    df['Rooms'] = df.apply(
            lambda row: 0 if row['Desc'] == 'Studio' else 1,
            axis=1
        )

Or use list + for loop and add new column to your df.

# Import pandas, numpy
import pandas as pd
import numpy as np

# Sample df
d = {'Desc': ['Studio', 'Rooms', 'Studio', 'Studio', 'Studio', 'Rooms','Rooms','Rooms','Studio', 'Rooms']}
df = pd.DataFrame(data=d)

# Create empty list for bedroom numbers.
bedrooms = []

# Fill the list with 0/1 base on your Studio/Rooms option.
for i in range(0,len(df.index)):
    if df['Desc'].loc[i].lower() == 'studio':
        bedrooms.append(0)
    else:
        bedrooms.append(1)

# Add new column to your DataFrame
df['Rooms'] = np.array(bedrooms)

Both ways will show results (0/1) in new column called 'Rooms'.

If I didnt correctly guess your issue, let me know.

Good luck!