In pandas, how can I set the number format in a single cell?

user_9078185 0
Viewed 2448

In pandas, one can use either map or applymap to display a set of ints or floats to look like typical number format. For example....

df['Big_num'] = df['Big_num'].map('{:,.0f}'.format)

... will turn a column with 123456.789 into string that looks nicer: 123,457.

I am working with an app where the user will do some actions and cause individual cells within the dataframe to recalculate. As a result 'Number' at a given row will go back to being a float and then need reformatting.

In this case, you can't use the map functionality, because most of the column is a string. It will throw a ValueError. Here's a toy version of what I'm trying to do:

import pandas as pd
import numpy as np

df_data = {'Location' : ['Denver', 'Boulder', 'San Diego', 'Reno', 'Portland',
    'Eugene', 'San Francisco'], 'State' : ['co', 'co', 'ca', 'nv',
    'or', 'or', 'ca'], 'Rando_num': [18.56, 5.12, 34.87, 11.22, 72.68, 42.90, 9.38],
    'Other_num': [113, 260, 557, 134, 883, 422, 22]}
df = pd.DataFrame(data = df_data)
df['Big_num'] = df['Rando_num'] * df['Other_num']
df['Big_num'] = df['Big_num'].map('{:,.0f}'.format)

print(df)

add_num = input('Type a number to add to row 3 ')

df.loc[3, 'Big_num'] = df.loc[3, 'Rando_num'] * df.loc[3, 'Other_num'] + float(add_num)

#Neither of these work
#df.loc[3, 'Big_num'] = df.loc[3, 'Big_num'].map('{0:.2f}%'.format)
#df.loc['Big_num'] = df['Big_num'].map('{0:.2f}%'.format)

print(df)

I'm looking for output where the sell at [3, 'Big_num'] should look something like

1,503

Any ideas? Thanks in advance.

pandas python
2 Answers

Your problem is that you cannot run df['Big_num'] = df['Big_num'].map('{:,.0f}'.format) twice. Even just once right after the other.

Also I advise using df.update.

Here is some working code:

import pandas as pd
import numpy as np

df_data = {'Location' : ['Denver', 'Boulder', 'San Diego', 'Reno', 'Portland',
    'Eugene', 'San Francisco'], 'State' : ['co', 'co', 'ca', 'nv',
    'or', 'or', 'ca'], 'Rando_num': [18.56, 5.12, 34.87, 11.22, 72.68, 42.90, 9.38],
    'Other_num': [113, 260, 557, 134, 883, 422, 22]}
df = pd.DataFrame(data = df_data)
df['Big_num'] = df['Rando_num'] * df['Other_num']
#dm = df.copy() #If you want to print twice, uncomment these 3 lines.
#dm['Big_num'] = dm['Big_num'].map('{:,.0f}'.format)
#print(dm)
add_num = input('Type a number to add to row 3 ')


new_num = df.loc[3, 'Rando_num'] * df.loc[3, 'Other_num'] + float(add_num)
new_num = int(new_num)
new_col = pd.Series([new_num], name='Big_num', index=[3])
df.update(new_col)
df['Big_num'] = df['Big_num'].map('{:,.0f}'.format)
print(df)
user_33125950

You can just apply your string formatting to a scalar. pd.DataFrame.at is optimised for accessing and setting scalar values by label.

add_num = float(input('Type a number to add to row 3 '))  # 123

df.at[3, 'Big_num'] = '{:,.0f}'.format(df.at[3, 'Rando_num'] *
                                       df.at[3, 'Other_num'] + add_num)

print(df)

#         Location State  Rando_num  Other_num Big_num
# 0         Denver    co      18.56        113   2,097
# 1        Boulder    co       5.12        260   1,331
# 2      San Diego    ca      34.87        557  19,423
# 3           Reno    nv      11.22        134   1,626
# 4       Portland    or      72.68        883  64,176
# 5         Eugene    or      42.90        422  18,104
# 6  San Francisco    ca       9.38         22     206
user_92095460
Related