0
$\begingroup$

I am searching for a way to create a new column in my data. I have tried using iterows() but found it extremely time consuming in my dataset containing 40 lakh rows. So here is what I want. Consider I have 2 columns: Event ID, TeamID ,I want to find the no. of unique TeamID under each EventID as a new column. In other words, I want to find the number of teams participating in each event as a new column.

$\endgroup$
  • $\begingroup$ Something like a groupby? $\endgroup$ – Matthieu Brucher Jan 3 '19 at 16:59
1
$\begingroup$

You can try something like this to get a new dataframe that has pairs of (EventID, TeamCount):

event_id_team_count = data.groupby('EventID').agg({'TeamID': lambda x: x.nunique()})
event_id_team_count.rename(columns={"TeamID": "TeamCount"}, inplace=True)

If you want to have this new column in the original dataframe, all you need to do is to join the original dataframe with the one you have just created:

data = data.join(other=event_id_team_count, on="EventID")
| improve this answer | |
$\endgroup$
0
$\begingroup$
  1. Create a dictionary with the unique count of TeamID with respective to EventID
uCountDict = dict(data.groupby("EventID").TeamID.count())
uCountDict

Sample output {'A': 4, 'C': 3, 'D': 2, 'F': 1 }

  1. Now create a new column with unique count with respective to TeamID using apply function
data["TeamCount"] = data.EventID.apply(lambda x : uCountDict[x])
| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.