Derive a new column based on the first two column values

Asked Feb 13 '19 at 13:25

Active Feb 13 '19 at 13:25

Viewed 14 times

I have a pandas data frame as shown below, I would like to derive transform my data frame as shown here.

Logic: For the same id, if a country gets repeated then I would like flag it with the count. For example, for the id 1, first occurrence of USA, I would like to count it as 1, then when USA occurs second time for the same user then 2 and so on, like 3rd time if USA is assigned for the same user id 1, then I would expect 3 in the count.

Input:

id  country
1   USA
1   USA
1   UK
2   USA
2   India
2   India
3   India
3   India

Output:

id  country count
1   USA 1
1   USA 2
1   UK  1
2   USA 1
2   India   1
2   India   2
3   India   1
3   India   2

asked Feb 13 '19 at 13:25

221B

2

Use `df['count'] = df.groupby(['id','country']).cumcount() + 1` – jezrael Feb 13 '19 at 13:26
@jezrael thanks, on the similar topic, how do we achieve same thing in R? may be using dplyr? – 221B Feb 13 '19 at 13:36
Unfortunately I have no idea. – jezrael Feb 13 '19 at 13:39

Derive a new column based on the first two column values

0 Answers0