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.
$\begingroup$
$\endgroup$
1
-
$\begingroup$ Something like a groupby? $\endgroup$– Matthieu BrucherJan 3, 2019 at 16:59
Add a comment
|
2 Answers
$\begingroup$
$\endgroup$
0
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")
$\begingroup$
$\endgroup$
- 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 }
- Now create a new column with unique count with respective to TeamID using apply function
data["TeamCount"] = data.EventID.apply(lambda x : uCountDict[x])
