Pandas conditional fillna based on another column values

user_13828275 0
Viewed 5444

I am working on bigmart dataset and I would like to substitute missing values of a column based on the values of another column, practically:

     Outlet_Size  sales_bin
0         Medium  3000-4000
1         Medium     0-1000
2         Medium  2000-3000
3            NaN     0-1000
4           High     0-1000
...          ...        ...
8518        High  2000-3000
8519         NaN     0-1000
8520       Small  1000-2000
8521      Medium  1000-2000
8522       Small     0-1000


So if train[“Outlet_Size”] value is a NaN and train[“sales_bin”] is  “0-1000”
train[“Outlet_Size”] value shoud become “Small”
else == Medium

But I really don’t know how to write it and all the information I found seems confusing to me

Is it possible to do it? How?

Many thanks

pandas conditional-statements dataframe nan python
3 Answers

You can use pandas.Series.map instead of numpy.where.

pandas.Series.map seems to be handier for these simple cases, which makes multiple imputations easier and explicit with dictionaries (say {'0-1000': 'Small', '2000-3000': 'High'}).

numpy.where is designed to handle more logic (ex: if a < 5 then a^2) which is not very useful in the OP use case, but comes at some cost, like making multiple imputations tricky to handle (nested if-else).

Steps :

  1. Generate a mask to tag the subset of the pandas.DataFrame with missing 'Outlet_Size' using pandas.Series.isna() ;
  2. Define a dictionary with mappings, e.g. from '0-1000' to 'Small' ;
  3. Replace 'Outlet_Size' values in the defined pandas.DataFrame subset using pandas.Series.map with the defined dictionary as args argument.
  4. Use pandas.Series.fillna() to catch the unmapped missing 'Outlet_Size' and impute them to a default value.

Example :

import pandas as pd
import numpy as np
fake_dataframe = pd.DataFrame({
    'Outlet_Size' : ['Medium', 'Medium', 'Medium', np.nan, 'High', 'High', np.nan, 'Small', 'Medium', 'Small', np.nan, np.nan],
    'sales_bin': ['3000-4000', '0-1000', '2000-3000', '0-1000', '0-1000', '2000-3000', '0-1000', '1000-2000', '1000-2000', '0-1000', '2000-3000', '1000-2000']
})
missing_mask = fake_dataframe['Outlet_Size'].isna()
mapping_dict = dict({'0-1000': 'Small'})
fake_dataframe.loc[missing_mask, 'Outlet_Size'] = fake_dataframe.loc[missing_mask, 'sales_bin'].map(mapping_dict)
fake_dataframe['Outlet_Size'] = fake_dataframe['Outlet_Size'].fillna('Medium')
print(fake_dataframe)
   Outlet_Size  sales_bin
0       Medium  3000-4000
1       Medium     0-1000
2       Medium  2000-3000
3        Small     0-1000
4         High     0-1000
5         High  2000-3000
6        Small     0-1000
7        Small  1000-2000
8       Medium  1000-2000
9        Small     0-1000
10      Medium  2000-3000
11      Medium  1000-2000

Example with multiple imputations :

import pandas as pd
import numpy as np
fake_dataframe = pd.DataFrame({
    'Outlet_Size' : ['Medium', 'Medium', 'Medium', np.nan, 'High', 'High', np.nan, 'Small', 'Medium', 'Small', np.nan, np.nan],
    'sales_bin': ['3000-4000', '0-1000', '2000-3000', '0-1000', '0-1000', '2000-3000', '0-1000', '1000-2000', '1000-2000', '0-1000', '2000-3000', '1000-2000']
})
missing_mask = fake_dataframe['Outlet_Size'].isna()
mapping_dict = dict({'0-1000': 'Small', '2000-3000': 'High'})
fake_dataframe.loc[missing_mask, 'Outlet_Size'] = fake_dataframe.loc[missing_mask, 'sales_bin'].map(mapping_dict)
fake_dataframe['Outlet_Size'] = fake_dataframe['Outlet_Size'].fillna('Medium')
print(fake_dataframe)
    Outlet_Size sales_bin
0   Medium  3000-4000
1   Medium  0-1000
2   Medium  2000-3000
3   Small   0-1000
4   High    0-1000
5   High    2000-3000
6   Small   0-1000
7   Small   1000-2000
8   Medium  1000-2000
9   Small   0-1000
10  High    2000-3000
11  Medium  1000-2000
user_75234990

Use Series.isna to create boolean mask, then use np.where + Series.eq to select choices from Small and Medium based on the condition where sales_bin equals 0-1000:

m = df['Outlet_Size'].isna()
df.loc[m, 'Outlet_Size'] = np.where(df.loc[m, 'sales_bin'].eq('0-1000'), 'Small', 'Medium')

Result:

print(df)
     Outlet_Size  sales_bin
0         Medium  3000-4000
1         Medium     0-1000
2         Medium  2000-3000
3          Small     0-1000
4           High     0-1000
8518        High  2000-3000
8519       Small     0-1000
8520       Small  1000-2000
8521      Medium  1000-2000
8522       Small     0-1000
user_128331660

Following the suggestion of Shubham Sharma (to use np.select) and using the feature "Item_Outlet_Sales" instead than "sales_bin"

So :

        Outlet_Size  Item_Outlet_Sales
0         Medium          3735.1380
1         Medium           443.4228
2         Medium          2097.2700
3            NaN           732.3800
4           High           994.7052
...          ...                ...
8518        High          2778.3834
8519         NaN           549.2850
8520       Small          1193.1136
8521      Medium          1845.5976
8522       Small           765.6700 

    missing = train["Outlet_Size"].isna()
    condlist = [train.loc[missing, "Outlet_Size"] & train.loc[missing,'sales_bin'] <=1000, 
    train.loc[missing, "Outlet_Size"] & train.loc[missing,'sales_bin'] > 1000] 
    choicelist = ["Small", "Medium"] #PS, If I got it well it is possible to add as # many contiontions as wanted, as long condlist and choicelist has the same lenght 
    train.loc[missing, 'Outlet_Size'] = np.select(condlist, choicelist)
    train["Outlet_Size"].value_counts(dropna=False)

Small     4798
Medium    2793
High       932

Many thanks for your suggestions and for this wonderful forum to exist : )

user_138282750
Related