How to exclude nans from formatting-CodePudding

I have a data frame with a nan value. An example here:

data = {'a': [0.3, 0.42, 0.33],
        'b': [0.24, 0.2, np.nan],
        'c': [0.66,0.29,0.7]}
index=['row1','row2','row3']
df = pd.DataFrame(data,index=index)

I format the data row-wise in my code (presumably I could also do in one command, but in some cases I want to apply slightly different format):

df.loc['row1']=df.loc['row1'].apply('{:.2%}'.format)
df.loc['row2']=df.loc['row2'].apply('{:.2%}'.format)
df.loc['row3']=df.loc['row3'].apply('{:.2%}'.format)

The resulting frame has the '%' applied to the nan value:

        a       b       c
row1    30.00%  24.00%  66.00%
row2    42.00%  20.00%  29.00%
row3    33.00%  nan%    70.00%

How do I tell pandas to not format that nan value and keep it as 'nan'?

CodePudding user response：

Let's try na_action of DataFrame.applymap

out = df.applymap('{:.2%}'.format, na_action='ignore')

# If you want to use it for one row
df.loc[['row3']]=df.loc[['row3']].applymap('{:.2%}'.format, na_action='ignore')

print(out)

           a       b       c
row1  30.00%  24.00%  66.00%
row2  42.00%  20.00%  29.00%
row3  33.00%     NaN  70.00%

Or you can conditionally assign the result

df.loc['row3', df.loc['row3'].notna()] = df.loc['row3'].apply('{:.2%}'.format)

CodePudding user response：

First of all, to apply the formatting to every entry of a dataframe, you could use applymap instead of separately using apply to each column.

From there, one way to handle the nan case is to do the following.

data = {'a': [0.3, 0.42, 0.33],
        'b': [0.24, 0.2, np.nan],
        'c': [0.66,0.29,0.7]}
index=['row1','row2','row3']
df = pd.DataFrame(data,index=index)

df[~pd.isna(df)] = df.applymap('{:.2%}'.format)

A similar approach can be taken on a column-by-column basis if that is preferred.

The result:

           a       b       c
row1  30.00%  24.00%  66.00%
row2  42.00%  20.00%  29.00%
row3  33.00%     NaN  70.00%

CodePudding user response：

or even just accept the change and then remove it!

df = df.replace('nan%', np.nan)

CodePudding user response：

df.style.format('{:.2%}',na_rep=np.nan)

You can use style formatting to format your dataframe columns.To use dataframe style first you have install jinja2 . na_rep use to identify and replace nan values. Refer following link for more info.