在Pandas DataFrame中按顺序重复编号 [英] Number duplicates sequentially in Pandas DataFrame

问题描述

我有一个Pandas DataFrame，它的列基本上是外键，如下所示:

I have a Pandas DataFrame that has a column that is basically a foreign key, as below:

Index   |  f_key  |    values
  0     |    1    |     red 
  1     |    2    |     blue 
  2     |    1    |     green 
  3     |    2    |     yellow 
  4     |    3    |     orange 
  5     |    1    |     violet

我想添加一列，按顺序标记重复的外键，如下面的"dup_number"所示:

What I would like is to add a column that labels the repeated foreign keys sequentially, as in "dup_number" below:

Index   | dup_number |  f_key  |    values
  0     |     1      |    1    |     red 
  1     |     1      |    2    |     blue 
  2     |     2      |    1    |     green 
  3     |     2      |    2    |     yellow 
  4     |     1      |    3    |     orange 
  5     |     3      |    1    |     violet

可以根据需要对行进行重新排序，我只需要在其中获取"dup_number"键即可.我编写了以下代码，效果很好，它给了我一个系列，然后可以将其添加到DataFrame中，但是它非常慢(for循环会浪费时间)，而且我觉得它比所需的方法更复杂:

The rows can be reordered if needed, I just need to get the "dup_number" keys in there. I wrote following code, which works fine, it gives me a Series which I can then add into the DataFrame, but it is very slow (that for loop is what kills the time), and I feel like it's way more complicated than is needed:

df = pd.DataFrame({'f_key': [1,2,1,2,3,1], 'values': ['red', 'blue', 'green', 'yellow', 'orange', 'violet']})
df_unique = df['f_key'].drop_duplicates().reset_index(drop=True)
dup_number = pd.DataFrame(columns = ['dup_number', 'temp_index'])
for n in np.arange(len(df_unique)):
    sub_df = df.loc[df['f_key'] == df_unique[n]].reset_index()
    dup_index = pd.DataFrame({'dup_number': sub_df.index.values[:]+1, 'temp_index': sub_df['index']})
    dup_number = dup_number.append(dup_index)
dup_number = dup_number.set_index(dup_number['temp_index'].astype(int))
dup_number = dup_number['dup_number'].sort_index()

任何对更快，更简单的方法的建议，将不胜感激！

Any suggestions on faster/simpler ways to do this are appreciated!

推荐答案

您可以使用 查看全文