How to convert a values like 1225002 to 1.2M in a column using pandas?

Question

I have got a dataframe using instagramy. The dataframe consists of columns like Usernames, Followers, Following, Posts.

The dataframe looks like this

         Usernames  Followers  Following  Posts
0          A        1225002       1675   5647
1          B        11565253        998  12806
2          C         433688        895    994
3          D        7600455         31  15295
4          E           6706         33    478
5          F           1425        162     12
.          .            .           .       .
.          .            .           .       .
n          n           n            n       n

What I want to do is simply convert the values of followers from 1225002 to 1.2M 11565253 to 11.5M So that the final dataframe will look like this

         Usernames  Followers  Following  Posts
0          A        1.2M       1675   5647
1          B        11.5M        998  12806
2          C         433K        895    994
3          D        7.6M         31  15295
4          E           6706         33    478
5          F           1425        162     12
.          .            .           .       .
.          .            .           .       .
n          n           n            n       n

I have tried to use numpy and pandas for converting the absolute numbers to a numerical string like these but I do not know the correct way of doing that. How can I do it? Please help!

Shivam Roy · Answer 1 · 2021-05-16T12:00:15.427

1

You can write a function to do this.Replace every thousands place by the suffix 'K' or 'M', likewise:

def words(num):
    thousands = 0
    while num >= 1000:
        thousands += 1
        num /= 1000
    return '%s%s' % (str(int(num)), ['', 'K', 'M'][thousands])

df['Followers'].apply(lambda num: words(num))

edited May 16 '21 at 12:00

answered May 16 '21 at 11:43

Shivam Roy

1,961
3
10
23

1

Thanks for your immediate response, this works just fine – technophile_3 May 16 '21 at 11:53
1

`.0f` automatically rounds off the value I guess. – ThePyGuy May 16 '21 at 11:56
1

@ThePyGuy Hi thank you so much for pointing that out, didn't notice that. I've fixed it. – Shivam Roy May 16 '21 at 12:00

score 1 · Accepted Answer · answered May 16 '21 at 11:48

1

You can use apply and lambda along with formatted string:

df['Followers'].apply(lambda x: f'{x/1000000:.1f}M' if x/1000000>=1 else f'{int(x/1000)}K' if x/10000>=1 else f'{x}')

OUTPUT:

0     1.2M
1    11.6M
2     433K
3     7.6M
4     6706
5     1425
Name: Followers, dtype: object

answered May 16 '21 at 11:48

ThePyGuy

17,779
5
18
45

1

Thanks for your immediate response, this worked fine! never thought this could be done by using apply() – technophile_3 May 16 '21 at 11:54

How to convert a values like 1225002 to 1.2M in a column using pandas?

2 Answers2

Linked