Python/Pandas formatting values of a column if its header contains "Price"-CodePudding

Using pandas, I have read an .xlsx file that contains 4 columns: ID, Product, Buy Price and Sell Price.

I would like to format values under the columns that contain "Price" in their headers in the following way:

1399 would become $1,399.00
1538.9 would become $1,538.90

I understand how to address the column headers and impose the desired condition, but I don't know how to format the values themselves. This is how far I got:

for col in df.columns:
   if "Price" in col:
      print("This header has 'Price' in it")
   else:
      print(col) 

ID
Name
This header has 'Price' in it
This header has 'Price' in it

How can I do this?

CodePudding user response：

Try:

for col in df.columns:
   if "Price" in col:
       print("This header has 'Price' in it")
       df[col] = df[col].map('${:,.2f}'.format)
   else:
       print(col)

Or if get all columns names to list is possible use DataFrame.applymap:

cols = df.filter(like='Price').columns
df[cols] = df[cols].applymap('${:,.2f}'.format)

In the string formatting, :, puts a comma for as the thousands separator, and .2f formats the floats to 2 decimal points, to become cents.

CodePudding user response：

I suggest you use py-moneyed, see below how to use it for transforming it to a string representing money:

import pandas as pd
from moneyed import Money, USD

res = pd.Series(data=[1399, 1538.9]).map(lambda x: str(Money(x, USD)))
print(res)

Output

0    $1,399.00
1    $1,538.90
dtype: object

Full code

import pandas as pd
from moneyed import Money, USD

# toy data
columns = ["ID", "Product", "Buy Price", "Sell Price"]
df = pd.DataFrame(data=[[0, 0, 1399, 1538.9]], columns=columns)

# find columns with Price in it
filtered = df.columns.str.contains("Price")

# transform the values of those columns
df.loc[:, filtered] = df.loc[:, filtered].applymap(lambda x: str(Money(x, USD)))
print(df)

Output

   ID  Product  Buy Price Sell Price
0   0        0  $1,399.00  $1,538.90