How to convert a column with values like 6200000 to 6.2M or 1900 to 1.90K in pandas DataFrame?

Question

For Example for this data frame

df = pd.DataFrame({'Age':['12',np.nan,'32','21','55'],
        'Height':["5'7","5'8","5'5",np.nan,"5'10"],
                  'Weight':[np.nan,'160','165','155','170'],
                  'Gender':['M','M',np.nan,'F',np.nan],
                  'Salary':[2900,6550000,7840000,6550000,8950000]})

I want output as:

        Age Height  Weight  Gender  Salary
    0   12  5'7     NaN     M       2.9K
    1   NaN 5'8     160     M       6.55M
    2   32  5'5     165     NaN     7.84M
    3   21  NaN     155     F       6.55M
    4   55  5'10    170     NaN     8.95M

yes, but I am not getting the right logic for it. I want to write it in python. — Pikesh Patel, Jul 08 '19 at 21:01
[`df.Salary.apply(human_format)`](https://stackoverflow.com/a/45846841/3483203) using the function from the linked answer — user3483203, Jul 08 '19 at 22:07

score 3 · Accepted Answer · answered Jul 08 '19 at 21:05

One option:

df = pd.DataFrame({'Age':['12',np.nan,'32','21','55'],
        'Height':["5'7","5'8","5'5",np.nan,"5'10"],
                  'Weight':[np.nan,'160','165','155','170'],
                  'Gender':['M','M',np.nan,'F',np.nan],
                  'Salary':[29000,650,7840000,6550000,8950000]})

df['s'] = df['Salary'].apply(lambda x: 
                             str(x/1e6).format('{:.2}')+'M' 
                                 if x >= 1e6 
                             else str(x/1e3).format('{:.2}')+'K'
                                 if x > 1e3
                             else str(x).format('{:,}'))

gives

   Age Height Weight Gender   Salary      s
0   12    5'7    NaN      M    29000  29.0K
1  NaN    5'8    160      M      650    650
2   32    5'5    165    NaN  7840000  7.84M
3   21    NaN    155      F  6550000  6.55M
4   55   5'10    170    NaN  8950000  8.95M

How to convert a column with values like 6200000 to 6.2M or 1900 to 1.90K in pandas DataFrame?

1 Answers1