Python Pandas Filling missing values in dataframe column with 'prefix' + respective Index numbers

Question

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))

or

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]})

user7864386 · Accepted Answer · 2022-04-18 17:29:10Z

3

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

answered Apr 18, 2022 at 17:29

user7864386

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python Pandas Filling missing values in dataframe column with 'prefix' + respective Index numbers

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related