Блог

У меня есть фрейм данных, который включает дублирующие данные по некоторым атрибутам [‘Stage1’, ‘Stage2′,’Stage3’] и не дублирующие данные по другим [‘Key’, ‘Agg1’, ‘Agg2’]. Я хотел бы сопоставить дублирующие атрибуты [‘Stage1’, ‘Stage2′,’Stage3’] и агрегировать не дублирующие атрибуты. Агрегированные атрибуты должны быть разделены запятыми. Атрибуты, которые я не хочу агрегировать [‘Title’], следует игнорировать. Вот пример данных.

Я пробовал несколько вариантов, включая duplicated и groupby, но не добился этих результатов. Я довольно новичок в python, поэтому, пожалуйста, извините за код взлома:

code

 import pandas as pd
import numpy as np

DataInput = pd.DataFrame(
np.array([["Key.1","Group 1","A", "One", "G_One, S_One","G_One, S_Two", "G_One, S_Three"],
["Key.2","Group 1","B", "Two", "G_One, S_One","G_One, S_Two", "G_One, S_Three"],
["Key.3","Group 1","C", "Three", "G_One, S_One","G_One, S_Two", "G_One, S_Three"],
["Key.4","Group 2","A", "Two", "G_Two, S_One","G_Two, S_Two", "G_Two, S_Three"],
["Key.5","Group 2","C", "Three", "G_Two, S_One","G_Two, S_Two", "G_Two, S_Three"],
["Key.6","Group 3","A", "One", "G_Three, S_One","G_Three, S_Two", "G_One, S_Three"],
["Key.7","Group 4","B", "Two", "G_Three, S_One","G_Three, S_Two Different", "G_Three, S_Three"]]),
columns=["Key","Title","Agg1","Agg2","Stage1","Stage2","Stage3"]
)

DataOutput = pd.DataFrame(
np.array([["Key.1, Key.2, Key.3","Group 1","A, B, C", "One, Two, Three", "G_One, S_One","G_One, S_Two", "G_One, S_Three"],
["Key.4, Key.5","Group 2","A, C", "Two, Three", "G_Two, S_One","G_Two, S_Two", "G_Two, S_Three"],
["Key.6","Group 3","A", "One", "G_Three, S_One","G_Three, S_Two", "G_One, S_Three"],
["Key.7","Group 4","B", "Two", "G_Three, S_One","G_Three, S_Two Different", "G_Three, S_Three"]]),
columns=["Key","Title","Agg1","Agg2","Stage1","Stage2","Stage3"]
)

# Input
print(DataInput)
# Expected Output
print(DataOutput)


ColumnNames = ['Stage1','Stage2','Stage3']
all_duplicates = DataInput.duplicated(subset=ColumnNames, keep=False)
unique_duplicates = DataInput.duplicated(subset=ColumnNames)

# Create listing of just the duplicates
duplicate_compare = all_duplicates.compare(unique_duplicates, keep_shape=True, keep_equal=True)
keeplist_bool = duplicate_compare['other'][duplicate_compare.other == False]
droplist_bool = duplicate_compare['other'][duplicate_compare.other == True]
# Listing of all the unique items that should be updated

DataInput_Keep = DataInput.loc[keeplist_bool.index]
DataInput_Drop = DataInput.loc[droplist_bool.index]
print(DataInput_Keep)
print(DataInput_Drop)
# Defaults to keep first
DataInput_unique = DataInput.drop_duplicates(subset=ColumnNames)
print(DataInput_Keep)
print(DataInput_Drop)
print(DataInput_unique)
# Ideas on using this to hack through the solution
# Create new DF with all the duplicates
# Drop all but the first duplicates from the original
# Iterate through original and update with the aggregates.

## Kind of works, missing data in Key.7
#grouped = DataInput.groupby(ColumnNames)['Key'].agg(','.join)#['Agg2'].agg(','.join)
#grouped = DataInput.groupby(ColumnNames)['Key'].apply(', '.join)
#print(grouped)