Update values in rows with null values in dummy dataframe

Álvaro V. 0
Viewed 36

I'm trying to update values of a Pandas dataframe inside a for loop. The dataframe consists of dummy categorical columns (i.e., the original categorical variables have converted to 0-1 for each possible category). Then, I'd like to update the rows referring to original null values, from this:

Feat1-valA   Feat1-valB   Feat1-NaN   Feat2-valC   Feat2-valD   Feat2-NaN
1            0            0           1            0            0
0            1            0           1            0            0
0            0            1           1            0            0
0            1            0           0            0            1
0            0            1           0            0            1

to this:

Feat1-valA   Feat1-valB   Feat1-NaN   Feat2-valC   Feat2-valD   Feat2-NaN
1            0            0           1            0            0
0            1            0           1            0            0
nan          nan          1           1            0            0
0            1            0           nan          nan          1
nan          nan          1           nan          nan          1

For if it is of any help, I'm attaching an image of some data values that should be updated to Nan: enter image description here

To do that, I've tried the following piece of code:

# Get the name of all the dummy columns for null values
nan_cols_names = [col for col in dummies.columns if "nan" in col]

# For each of those columns, tell if they have non-zero values (i.e., if the original non-dummy column actually has "nan" values)
nan_cols_mask = dummies[nan_cols_names].sum() > 0

# List the dummy columns pertaining to features that have "nan" values
nan_cols_true = list(nan_cols_mask[nan_cols_mask == True].index)

# Display the number of "nan" values in each of these columns
display(dummies[nan_cols_true].sum())

for feature_nan in nan_cols_true:
    
    # Get the general label (remove "_nan")
    feature_label = feature_nan[:-4]
    
    # Get the columns of the feature, except the "_nan" column
    feature_cols = [col for col in dummies.columns if feature_label in col][:-1]
    
    # Update the values of the non-"_nan" columns of the feature to be null values (instead of 0's, as they currently are)    
    dummies.loc[dummies[feature_nan] == 1, feature_cols] = np.nan
    
# Display the number of "nan" values in each of these columns
display(dummies[nan_cols_true].sum())

However, I see that the values don't get updated. What am I doing wrong?

Thanks everyone,

dataframe pandas null for-loop python
1 Answers

IIUC, use boolean indexing:

# get NaN columns
nan_col = dummies.filter(regex='(?i)nan')

# are all (or any if you prefer) columns 1?
mask = nan_col.eq(1).all(1)

# mask matching rows
dummies.loc[mask, dummies.columns.difference(nan_col.columns)] = pd.NA

output:

  Feature1-valueA Feature1-valueB  Feature1-valueNaN
0               1               0                  0
1               0               1                  0
2            <NA>            <NA>                  1
mozway0
Related