Creating a new column based off repeating rows

Question

My goal is to create a new column that includes the total repetitions per date.

Before

     date
0  6/1/18
1  6/1/18
2  6/4/18
3  6/5/18
4  6/6/18
6  6/6/18
7  6/6/18

After

     date  count
0  6/1/18    2
1  6/1/18    2
2  6/4/18    1
3  6/5/18    1
4  6/6/18    3
6  6/6/18    3
7  6/6/18    3

I tried using some similar solutions on this site to fit my criteria to no luck.

# Possible format I can use, but not what I am looking for.
df.loc[df['date'] == 1, 'b'].sum()
# I changed it to this to this to no solution.
df['Count'] = df.loc[df['date'] == df['date']].sum()

Perhaps I am on the wrong path, but any insight would be appreciated.

to no solution. What does that mean? Please provide a minimal reproducible example, and see How to Ask, help center. create a new column that includes the total repetitions per date Why keep that same format, then, instead of just the unique dates? — AMC
– AMC, Commented May 4, 2020 at 0:56

BENY · Accepted Answer · 2020-05-04 00:00:45Z

3

Let us do

df['count']=df.date.groupby(df.date).transform('count')
df
     date  count
0  6/1/18      2
1  6/1/18      2
2  6/4/18      1
3  6/5/18      1
4  6/6/18      3
6  6/6/18      3
7  6/6/18      3

answered May 4, 2020 at 0:00

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rickey · Accepted Answer · 2020-05-04 00:26:03Z

0

If you're trying to get rid of the duplicates, but keep the count...

import pandas as pd

data = ['6/1/18', '6/1/18', '6/4/18', '6/5/18', '6/6/18', '6/6/18','6/6/18']

df = pd.DataFrame({'Date': data})
df = df.pivot_table(columns=['Date'], aggfunc='size').reset_index()
df.rename(columns={0: 'Count'})

     Date  Count
0  6/1/18      2
1  6/4/18      1
2  6/5/18      1
3  6/6/18      3

answered May 4, 2020 at 0:26

Rickey

5241 gold badge4 silver badges5 bronze badges

2 Comments

sammywemmy Over a year ago

ur answer doesnt seem to match what the OP wants

Rickey Over a year ago

the OPs "after" sample didn't make sense to me, this does.

Collectives™ on Stack Overflow

Creating a new column based off repeating rows

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related