CODEWITHSUNDEEP
pandas
Ginam Kim
Ginam Kim

Reputation: 25

pandas value_counts() and keep it

I want to count the value with .value_counts() and keep it. for example

df = pd.DataFrame({ 'fruit':['apples']*3  + ['peaches']*5 + ['bananas']*3 +
                            ['carrots']*4 + ['apricots']*10 })

print (df)

    fruit
0   apples
1   apples
2   apples
3   peaches
4   peaches

df["fruit"].value_counts()

apricots    10
peaches      5
carrots      4
apples       3
bananas      3
Name: fruit, dtype: int64

and I want to align the values like,

print(df)

    fruit
0   apricots
1   apricots
2   apricots
3   apricots
4   apricots

How can I do that??

plz, give me some your idea Thank you!

Upvotes: 1

Views: 72

Answers (3)

Chris
Chris

Reputation: 29742

IIUC, use pandas.Series.value_counts with pd.Index.repeat:

s = df["fruit"].value_counts()
df["fruit"] = s.index.repeat(s)
print(df)

Output:

       fruit
0   apricots
1   apricots
2   apricots
3   apricots
...
21   bananas
22    apples
23    apples
24    apples

Upvotes: 0

r-beginners
r-beginners

Reputation: 35145

I used groupby and transform to make it in one sentence. 

df['count'] = df.groupby('fruit')['fruit'].transform('count')

Upvotes: 2

Jarad
Jarad

Reputation: 18923

Step 1: make value counts table

vc = df['fruit'].value_counts().reset_index()
      index  fruit
0  apricots     10
1   peaches      5
2   carrots      4
3    apples      3
4   bananas      3

Step 2: Merging

f = df.merge(vc, how='left', left_on='fruit', right_on='index')
     fruit_x     index  fruit_y
0     apples    apples        3
1     apples    apples        3
2     apples    apples        3
3    peaches   peaches        5
4    peaches   peaches        5
5    peaches   peaches        5
6    peaches   peaches        5
7    peaches   peaches        5
8    bananas   bananas        3
9    bananas   bananas        3
10   bananas   bananas        3
11   carrots   carrots        4
12   carrots   carrots        4
13   carrots   carrots        4
14   carrots   carrots        4
15  apricots  apricots       10
16  apricots  apricots       10
17  apricots  apricots       10
18  apricots  apricots       10
19  apricots  apricots       10
20  apricots  apricots       10
21  apricots  apricots       10
22  apricots  apricots       10
23  apricots  apricots       10
24  apricots  apricots       10

Step 3: Some clean-up

f = f.drop('index', axis=1).rename({'fruit_x': 'fruit', 'fruit_y': 'count'}, axis=1)
       fruit  count
0     apples      3
1     apples      3
2     apples      3
3    peaches      5
4    peaches      5
5    peaches      5
6    peaches      5
7    peaches      5
8    bananas      3
9    bananas      3
10   bananas      3
11   carrots      4
12   carrots      4
13   carrots      4
14   carrots      4
15  apricots     10
16  apricots     10
17  apricots     10
18  apricots     10
19  apricots     10
20  apricots     10
21  apricots     10
22  apricots     10
23  apricots     10
24  apricots     10

Upvotes: 0

Related Questions