Fill missing values using a nested dictionary-CodePudding

Here is my sample dataframe:

df = pd.DataFrame(data=[[3, np.nan, np.nan],[5, np.nan, np.nan]], index=['country1', 'country2'], columns=[2021, 2022, 2023])

Here is my sample dictionary:

d = {'country1': {'key1': 'a', 'key2': 'assumed','key3': {2022: '10', 2023: ' 20'}}, 'country2': {'key1': 'b', 'key2': 'assumed', 'key3': {2022: '30', 2023: ' 40'}}}

I am aiming to use the dictionary d to replace the missing values in the dataframe df. I thought I'd use something like:

df.fillna(d2)

where d2 is a dictionary based on dictionary d:

d2 = {'country1': {2022: '10', 2023: ' 20'}, 'country2': {2022: '30', 2023: ' 40'}}

I don't know how to generate d2 but it doesn't work anyway.

The result would look like this:

pd.DataFrame(data=[[3, 10, 20],[5, 30, 40]], index=['country1', 'country2'], columns=[2021, 2022, 2023])

CodePudding user response：

We can still use fillna but before that we have to normalize/transform the dictionary in a format which is suitable for fillna

df.T.fillna({k: v['key3'] for k, v in d.items()}).T

Result

         2021 2022 2023
country1  3.0   10   20
country2  5.0   30   40

CodePudding user response：

Rather than using df.fillna(d2), it looks like the best way of achieving this would be the following:

for country,country_dict in d.items():
    for year,value in country_dict['key3'].items():
        df.loc[country,year] = value