[FIXED] Pandas Series/Dataframe: Format string numbers with commas as Thousands Separators

Issue

Suppose I have the following series that is currently of type object:

s = {'A': "Hello", 'B': "593753", 'C': "16.8|", "D" : np.nan, "E":"%"}
s = pd.Series(data=s, index=['A', 'B', 'C',"D","E"])

I would like to have numbers formatted using commas as thousands separator.

I converted the series using:

s2 = pd.to_numeric(s, errors="coerce")

That of course changes the dtype to float. Converting the series back to an object removes the commas again.

Is there a way to format numbers stored as string so that they have commas as thousand separator? At the end the series has to be of type object, as I need to be able to search the series for "," using df.str.contains()

Solution

Solution using apply lambda

import pandas as pd
import numpy as np

def format_float(x):
    try:
       flt = float(x)
       return "{:,}".format(flt)
    except:
       return x

s = {'A': "Hello", 'B': "593753", 'C': "16.8|", "D" : np.nan, "E":"%"}
s = pd.Series(data=s, index=['A', 'B', 'C',"D","E"])

s2 = s.apply(lambda x: format_float(x))

Answered By - Pankaj Saini