Add Thousands Separator to a DataFrame that Has Empty Strings using Pandas Style

Question

This is a follow-up question to this.

Using Pandas Style, I manage to format all of the values in a dataframe into values that have commas as thousands separators. However, when there are empty strings in a dataframe, the formatting fails.

Basically, my goal is convert from this: to this:

Can anyone help me with this?

This is the code I have so far:

import pandas as pd
from IPython.display import HTML

styles = [
    hover(),
    dict(selector = "th",
         props = [("font-size", "110%"),
                  ("text-align", "left"),
                  ("background-color", "#cacaca")
                 ]
        )
    ]

column_01 = ["", 2000000000, "", 21000000, 3000]
df = pd.DataFrame(column_01)

int_frmt = lambda x: "{:,}".format(x) # Integer
float_frmt = lambda x: "{:,.0f}".format(x) if x > 1e3 else "{:,.2f}".format(x) # Float
str_frmt = lambda x: "{:}".format(x) # <----- Added for empty strings but fails
frmt_map = {np.dtype("int64"): int_frmt,
            np.dtype("float64"): float_frmt,  
            np.dtype("S"): str_frmt # <----- Added for empty strings but fails
           }
frmt = {col: frmt_map[df.dtypes[col]] for col in df.columns if df.dtypes[col] in frmt_map.keys()}

html = (df.style.set_table_styles(styles).format(frmt))

html

Answer 1

Using NumPy you could create a function to do the conversion, and vectorize() it. This can then be applied to your dataframe as follows:

import numpy as np

def thousands(x):
    try:
        return '{:,}'.format(int(x))
    except ValueError as e:
        return x

data = np.array(["","2000000000", "", "21000000", "3000"])
f_thousands = np.vectorize(thousands)
print f_thousands(data)

Giving you:

['' '2,000,000,000' '' '21,000,000' '3,000']    

This attempts to convert the entry to an integer, and then use format's thousands separator. If the conversion fails, it returns the passed entry unchanged, e.g. blank

See also Python's Format Specification Mini-Language for more information.

---

Using Pandas, this could be done as follows:

import pandas as pd

def thousands(x):
    try:
        return '{:,}'.format(int(x))
    except ValueError as e:
        return x

data = pd.DataFrame(["","2000000000", "", "21000000", "3000"])
print data.applymap(thousands)

Giving you:

               0
0               
1  2,000,000,000
2               
3     21,000,000
4          3,000