Only format df rows that are not NaN

I have the following df:

df = pd.DataFrame({'A': [0.0137, 0.1987, 'NaN', 0.7653]})

Output:

        A
0  0.0137
1  0.1987
2     NaN
3  0.7653

I am trying to format each row from column A, using .iloc (because I have many columns in my actual code) into, e.g. 1.37%.

However, If I perform

df.iloc[:, 0] = (df.iloc[:, 0] * 100).astype(float).map('{:,.2f}%'.format)

All the NaN rows receive a trailing '%', yielding 'NaN%'

So if I try:

df.iloc[:, 0] = df.iloc[:, 0].apply(
        lambda x: (x * 100).astype(float).map('{:,.2f}%'.format) if x.notna()
        else None)

I get IndexError: single positional indexer is out-of-bounds.

How can I properly format every row of my df that is not a NaN?

Note: I’m specifically using df.iloc before the equal sign because I only want to inplace change those columns.

>Solution :

Try this:

df.loc[~df['A'].isna(), 'A'] = (df.loc[~df['A'].isna(), 'A'] * 100).apply('{:,.2f}%'.format)

But careful, you are using NaN value as a string. I recommend to use numpy value. This should be:

import pandas as pd
import numpy as np

df = pd.DataFrame({'A': [0.0137, 0.1987, np.nan, 0.7653]})
df.loc[~df['A'].isna(), 'A'] = (df.loc[~df['A'].isna(), 'A'] * 100).apply('{:,.2f}%'.format)

Leave a Reply