Python Pandas: How can I group by and assign an id to all the items in a group?

user_2058811 0
Viewed 10144

I have df:

domain           orgid
csyunshu.com    108299
dshu.com        108299
bbbdshu.com     108299
cwakwakmrg.com  121303
ckonkatsunet.com    121303

I would like to add a new column with replaces domain column with numeric ids per orgid:

domain           orgid   domainid
csyunshu.com    108299      1
dshu.com        108299      2
bbbdshu.com     108299      3
cwakwakmrg.com  121303      1
ckonkatsunet.com 121303     2

I have already tried this line but it does not give the result I want:

df.groupby('orgid').count['domain'].reset_index()

Can anybody help?

pandas indexing group-by python
3 Answers

It's very intuitive with dplyr

df %>% group_by(orgid) %>% mutate(domainid=row_number())

I am porting this to python, with datar:

from datar.all import *

df = tibble(
    domain=['csyunshu.com', 'dshu.com', 'bbbdshu.com', 'cwakwakmrg.com', 'ckonkatsunet.com'],
    orgid=[108299,108299,108299,121303,121303]
)

df >> group_by(f.orgid) >> mutate(domainid=row_number())
# <pandas.core.groupby.generic.DataFrameGroupBy object at 0x7ff728cba490>

df >> group_by(f.orgid) >> mutate(domainid=row_number()) >> showme()
[2021-03-13 00:55:12][datar][   INFO] # [DataFrameGroupBy] Groups: ['orgid'] (2)
#              domain   orgid  domainid
# 0      csyunshu.com  108299         0
# 1          dshu.com  108299         1
# 2       bbbdshu.com  108299         2
# 3    cwakwakmrg.com  121303         0
# 4  ckonkatsunet.com  121303         1

df >> group_by(f.orgid) >> mutate(domainid=row_number()+1) >> showme()
[2021-03-13 00:55:26][datar][   INFO] # [DataFrameGroupBy] Groups: ['orgid'] (2)
#              domain   orgid  domainid
# 0      csyunshu.com  108299         1
# 1          dshu.com  108299         2
# 2       bbbdshu.com  108299         3
# 3    cwakwakmrg.com  121303         1
# 4  ckonkatsunet.com  121303         2
user_50881650
Related