How to count number of occurrences of value in one column per value in other column using pandas?

user_14001820 0
Viewed 107

I have a dataframe with a unique index and columns 'users', 'tweet_time' and 'tweet_id'.

I want to count the number of duplicate tweet_time values per user.

users = ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C']
tweet_times = ['01-01-01 01:00', '02-02-02 02:00', '03-03-03 03:00', '09-09-09 09:00',
               '04-04-04 04:00', '04-04-04 04:00', '05-05-05 05:00', '09-09-09 09:00',
               '06-06-06 06:00', '06-06-06 06:00', '07-07-07 07:00', '07-07-07 07:00']

d = {'users': users, 'tweet_times': tweet_times} 
df = pd.DataFrame(data=d)

Desired Output

A: 0

B: 1

C: 2

I manage to get the desired output (except for the A: 0) using the code below. But is there a more pythonic / efficient way to do this?

# group by both columns
df2 = pd.DataFrame(df.groupby(['users', 'tweet_times']).tweet_id.count())

# filter out values < 2
df3 = df2[df2.tweet_id > 1]

# turn multi-index level 1 into column
df3.reset_index(level=[1], inplace=True)

# final groupby
df3.groupby('users').tweet_times.count()
pandas group-by python
5 Answers

We can use crosstab to create a frequency table then check for counts greater than 1 to create a boolean mask then sum this mask along axis=1

pd.crosstab(df['users'], df['tweet_times']).gt(1).sum(1)

 users
A    0
B    1
C    2
dtype: int64
user_128331660

This works,

df1 = pd.DataFrame(df.groupby(['users'])['tweet_times'].value_counts()).reset_index(level = 0)
df1.groupby('users')['tweet_times'].apply(lambda x: sum(x>1))

users
A    0
B    1
C    2
Name: tweet_times, dtype: int64
user_110409860

you can use a custom boolean with your groupby.

the keep=False returns True when a value is duplicated and false if not.

# df['tweet_times'] = pd.to_datetime(df['tweet_times'],errors='coerce')

df.groupby([df.duplicated(subset=['tweet_times'],keep=False),'users']
                                                 ).nunique().loc[True]

       tweet_times
users             
A                0
B                1
C                2
user_93751020

There might be a simpler way, but this is all I can come up with for now :)

df.groupby("users")["tweet_times"].agg(lambda x: x.count() - x.nunique()).rename("count_dupe")

Output:

users
A    0
B    1
C    2
Name: count_dupe, dtype: int64
user_113019000

This looks quite pythonic to me:

df.groupby("users")["tweet_times"].count() - df.groupby("users")["tweet_times"].nunique()

Output:

users
A    0
B    1
C    2
Name: tweet_times, dtype: int64
user_133847740
Related