CODEWITHSUNDEEP
pythonpandasdataframedata-cleaning
Qaisar Shah
Qaisar Shah

Reputation: 15

Filling Null Values based on conditions on other columns

I want to fill the Null values in the first column based on the value of the 2nd column. (For example)

  1. For "Apples" in col2, the value should be 12 in places of Nan in the col1
  2. For "Vegies", in col2 the value should be 134 in place of Nan in col1

For every description, there is a specific code(number) in the 1st column. I need to map it somehow.

(IGNORE the . (dots)

All I can think of is to make a dictionary of codes and replace null but's that very hardcoded.

Can anyone help? 

col1. col2

12.     Apple

134.    Vegies

23.     Oranges

Nan.    Apples

Nan.    Vegies

324.    Sugar

Nan.    Apples

Upvotes: 0

Views: 1692

Answers (1)

Jovan
Jovan

Reputation: 815

**Reupdate

Here, I replicate your DF, and the implementation:

import pandas as pd
import numpy as np
l1 = [12, 134, 23, np.nan, np.nan, 324, np.nan,np.nan,np.nan,np.nan]
l2 = ["Apple","Vegies","Oranges","Apples","Vegies","Sugar","Apples","Melon","Melon","Grapes"]
df = pd.DataFrame(l1, columns=["col1"])
df["col2"] = pd.DataFrame(l2)

df
Out[26]: 
    col1     col2
0   12.0    Apple
1  134.0   Vegies
2   23.0  Oranges
3    NaN   Apples
4    NaN   Vegies
5  324.0    Sugar
6    NaN   Apples
7    NaN    Melon
8    NaN    Melon
9    NaN   Grapes

Then to Replace the Null values based on your rules:

df.loc[df.col2 == "Vegies", 'col1'] = 134
df.loc[df.col2 == "Apple", 'col1'] = 12

If you want to apply these to a larger scales, consider make a dictionary first: for example is: 

item_dict = {"Apples":12, "Melon":65, "Vegies":134, "Grapes":78}

Then apply all of these to your dataframe with this custom function:

 def item_mapping(df, dictionary, colsource, coltarget):
    dict_keys = list(dictionary.keys())
    dict_values = list(dictionary.values())
    for x in range(len(dict_keys)):
        df.loc[df[colsource]==dict_keys[x], coltarget] = dict_values[x]
    return(df)

Usage Examples:

item_mapping(df, item_dict, "col2", "col1")
    col1     col2
0   12.0    Apple
1  134.0   Vegies
2   23.0  Oranges
3   12.0   Apples
4  134.0   Vegies
5  324.0    Sugar
6   12.0   Apples
7   65.0    Melon
8   65.0    Melon
9   78.0   Grapes

Upvotes: 1

Related Questions