Different na_rep per column

Question

How do I format NaN values differently in different columns? Say with .to_string. I would like some columns to use '-', other columns '?' or just ''. But na_rep only allows a single string.

Until recently, one could use a formatter function:

df.to_string(float_format=lambda x: "-" if pd.isna(x) else f"{x:0.2f}")

Using formatters similarly, one could format NaNs in different columns differently.

However, this behavior was considered a bug and has been fixed recently (pandas 1.2.0).

So now that NaNs don't go through formatters, how do I apply different formats to NaNs in different columns?

I don't know if its a long shot, but I think you can "just" write your own class NAN that inherits NaN but then you overload the __repr__ and/or __str__ and replace all NaN with NAN — CutePoison
– CutePoison, Commented Nov 3, 2021 at 10:21

Stef · Accepted Answer · 2021-11-03 11:51:32Z

Not a real solution but a workaround: you could fillna the columns of a copy of the dataframe. This can be conventiently done for all columns in one call using assign:

import pandas as pd import numpy as np df = pd.DataFrame([[np.nan, np.nan],[1,2]], columns=['a','b']) print(df.assign(a=df.a.fillna('-'), b=df.b.fillna('n/a')).to_string()) # a b #0 - n/a #1 1.0 2.0 print(df) # a b #0 NaN NaN #1 1.0 2.0

That would turn the float column into objects, which has downsides. But the idea is great: replace NaN by (say) -inf and use if ... else in a formatter again!
the column types will be object, but the float values remain floats (verify by .applymap(type)), so you still can use custom formatting, e.g. .to_string(float_format=lambda x: f"{x:0.5f}")

Collectives™ on Stack Overflow

Different na_rep per column

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related