Блог

У меня есть следующий список:

 data = [
  {"name":"John","subject":"Maths","marks":"99"},
  {"name":"John","subject":"English","marks":"95"},
  {"name":"John","subject":"Science","marks":"97"},
  {"name":"Robert","subject":"Maths","marks":"98"},
  {"name":"Robert","subject":"Maths","marks":"98"},
  {"name":"Robert","subject":"Science","marks":"89"},
  {"name":"Robert","subject":"English","marks":"92"},
  {"name":"Lucifer","subject":"Maths","marks":"98"},
  {"name":"Lucifer","subject":"Science","marks":"87"},
  {"name":"Lucifer","subject":"English","marks":"98"},
  {"name":"Lucifer","subject":"English","marks":"98"}
]
 

Я преобразовал его в фрейм данных pandas originalDF

originalDF = pd.DataFrame(data)

        name  subject marks
0      John    Maths    99
1      John  English    95
2      John  Science    97
3    Robert    Maths    98
4    Robert    Maths    98 (duplicate)
5    Robert  Science    89
6    Robert  English    92
7   Lucifer    Maths    98
8   Lucifer  Science    87
9   Lucifer  English    98
10  Lucifer  English    98 (duplicate)
 

Теперь я мог извлекать как уникальные строки, так и дублированные строки

 uniqueDF = originalDF.drop_duplicates()
duplicates = originalDF.duplicated()
duplicates = duplicates[duplicates == True]
duplicateDF = originalDF.loc[list(duplicates.index)]
 

Теперь у меня есть как уникальные строки uniqueDF , так и дублированные строки duplicateDF

 uniqueDF
      name  subject marks
0     John    Maths    99
1     John  English    95
2     John  Science    97
3   Robert    Maths    98
5   Robert  Science    89
6   Robert  English    92
7  Lucifer    Maths    98
8  Lucifer  Science    87
9  Lucifer  English    98

duplicateDF
       name  subject marks
4    Robert    Maths    98
10  Lucifer  English    98
 

Теперь я хочу добавить еще один столбец copyOf , в duplicateDF котором будет указан индекс его уникальной строки из unique DF. Я хочу, чтобы конечный результат был следующим:

        name  subject marks  copyof
4    Robert    Maths    98  3
10  Lucifer  English    98  9
 

Есть идеи, как это рассчитать? Заранее спасибо.

Вот решение для объединения всех дублированных значений индекса в новый столбец с join :

 c = originalDF.columns.tolist()
#filtered only duplciated rows
df = originalDF[originalDF.duplicated(keep=False)]

df = (df.rename(index=str)
        .reset_index()
        .groupby(c)
        .agg(copyof=('index',lambda x: ','.join(x[:-1])),
             orig=('index', 'last'))
        .reset_index()
        .set_index('orig'))

print (df)
         name  subject marks copyof
orig                               
10    Lucifer  English    98      9
4      Robert    Maths    98      3
 

Проверка на наличие нескольких значений:

 data = [
  {"name":"John","subject":"Maths","marks":"99"},
  {"name":"John","subject":"English","marks":"95"},
  {"name":"John","subject":"Science","marks":"97"},
  {"name":"Robert","subject":"Maths","marks":"98"},
  {"name":"Robert","subject":"Maths","marks":"98"},
  {"name":"Robert","subject":"Maths","marks":"98"},
  {"name":"Robert","subject":"Science","marks":"89"},
  {"name":"Robert","subject":"English","marks":"92"},
  {"name":"Lucifer","subject":"Maths","marks":"98"},
  {"name":"Lucifer","subject":"Science","marks":"87"},
  {"name":"Lucifer","subject":"English","marks":"98"},
  {"name":"Lucifer","subject":"English","marks":"98"}
]

originalDF = pd.DataFrame(data)
print (originalDF)
 

 c = originalDF.columns.tolist()
df = originalDF[originalDF.duplicated(keep=False)]

df = (df.rename(index=str)
        .reset_index()
        .groupby(c)
        .agg(copyof=('index',lambda x: ','.join(x[:-1])),
             orig=('index', 'last'))
        .reset_index()
        .set_index('orig'))

print (df)
         name  subject marks copyof
orig                               
11    Lucifer  English    98     10
5      Robert    Maths    98    3,4
 

Это работает, хотя-

*Предположение: каждая строка будет иметь только одну повторяющуюся строку вместе с ней.

 data = [
  {"name":"John","subject":"Maths","marks":"99"},
  {"name":"John","subject":"English","marks":"95"},
  {"name":"John","subject":"Science","marks":"97"},
  {"name":"Robert","subject":"Maths","marks":"98"},
  {"name":"Robert","subject":"Maths","marks":"98"},
  {"name":"Robert","subject":"Science","marks":"89"},
  {"name":"Robert","subject":"English","marks":"92"},
  {"name":"Lucifer","subject":"Maths","marks":"98"},
  {"name":"Lucifer","subject":"Science","marks":"87"},
  {"name":"Lucifer","subject":"English","marks":"98"},
  {"name":"Lucifer","subject":"English","marks":"98"},
]

originalDF = pd.DataFrame(data)

uniqueDF = originalDF.drop_duplicates()

duplicates = originalDF.duplicated(keep=False)
duplicates = duplicates[duplicates == True]
duplicateDF = originalDF.loc[list(duplicates.index)]
duplicateDF.reset_index(inplace=True)

duplicates = list()
for i, row in duplicateDF.iterrows():
    if i % 2 == 0:
        duplicates.append(row['index'])
        
duplicateDF_ = duplicateDF[np.arange(len(duplicateDF)) % 2 != 0]
duplicateDF_['copyof'] = duplicates