我有一个包含多个标识符的数据框。我想为每个唯一的标识符组合创建一个新的“组标识符”——稍后,我想使用 statsmodels 运行回归。也就是说,假设我有
id1 id2 id3
A 1 100
A 1 101
B 1 100
B 1 100
我想得到
id1 id2 id3 groupid
A 1 100 0
A 1 101 1
B 1 100 2
B 1 100 2
以id1、id2、id3作为标识符集。我知道我可以获取 unique() 来获取唯一组,但是我如何有效地将行编码到它们所属的唯一组中?
调整@Bernie 的回答以适应潜在的“NaN”:
# get a DataFrame with just the unique "keys"
df2 = df.replace(np.NaN, -1)
g = df2.groupby([u'id1',u'id2',u'id3'])
gdf = pd.DataFrame(g.groups.keys(),columns=df.columns)
gdf = gdf.replace(-1, np.NaN)
# an idea is to re-use the index as the 'group_id'
# the next three commands support that
gdf.sort([u'id1',u'id2',u'id3'],inplace=True)
gdf.reset_index(drop=True,inplace=True)
gdf['group_id'] = gdf.index
# merge on the three id columns
mdf = df.merge(gdf,how='inner',on=df.columns.tolist())
请您参考如下方法:
当然有无数种解决方案。这就是我到达的......
>>> df
id1 id2 id3
0 A 1 100
1 A 1 101
2 B 1 100
3 B 1 100
# get a DataFrame with just the unique "keys"
g = df.groupby([u'id1',u'id2',u'id3'])
gdf = pd.DataFrame(g.groups.keys(),columns=df.columns)
# an idea is to re-use the index as the 'group_id'
# the next three commands support that
gdf.sort([u'id1',u'id2',u'id3'],inplace=True)
gdf.reset_index(drop=True,inplace=True)
gdf['group_id'] = gdf.index
# merge on the three id columns
mdf = df.merge(gdf,how='inner',on=df.columns.tolist())
产生:
id1 id2 id3 group_id 0 A 1 100 0 1 A 1 101 1 2 B 1 100 2 3 B 1 100 2
