Python Pandas Filling missing values in dataframe column with 'prefix' respective Index-CodePudding

I want to fill missing values from object column to be replaced with 'any_fixed_prefix' '_' 'corresponding index'.

Dataframe look like:

Required Dataframe after applying logic:

I tried several ways, but doesn't work like fillna or map method:

df['col1'].fillna(str(df.index))

df['col1].fillna('PRE_'   str(df.index))

DDL to generate DataFrame:

df = pd.DataFrame({'col1': ['A', 'B', np.nan , np.nan ,'E'],
                   'col2': ['S4', 'S8', 'AA', 'EE', 'T4'],
                   'col3': [2017, 2019, 2021, 2014, 2011]})

CodePudding user response：

If you only want to fill NaN values in col1, you could use fillna:

df['col1'] = df['col1'].fillna('PRE_'   df.index.to_series().astype(str))

If you want to fill NaN values in all object dtype columns, you could use mask on axis to fill in with the index (as Series). This will fill NaN values col2 in the same way:

tmp = df.select_dtypes('object')
df = tmp.mask(tmp.isna(), 'PRE_'   tmp.index.to_series().astype(str), axis=0).combine_first(df)

Output:

    col1 col2  col3
0      A   S4  2017
1      B   S8  2019
2  PRE_2   AA  2021
3  PRE_3   EE  2014
4      E   T4  2011