我需要将以下数据从长格式转换为宽格式。问题是该组的名称可能重复。另外,重复条目的数量未知,因此我也需要考虑到这一点。
这是期望的结果:
desired_data = [[1,'Joe Tell','111-222-3333','Al Mead','222-333-4444','Ted Shaw','444-344-2323'],
[2,'Don Roads','555-222-5213','','','Ted Frank','222-444-2323']]
df_result = pd.DataFrame(desired_data, columns = ['ID', 'Primary 1 - Name','Primary 1 - Phone',
'Primary 2 - Name','Primary 2 - Phone',
'Secondary 1 - Name','Secondary 1 - Phone'])
我尝试过旋转数据,但在重复组问题上出错。我尝试手动向“序列”名称添加前缀,但它变得混乱。
data = [[1,'Joe Tell','Primary','111-222-3333'],
[1,'Al Mead','Primary','222-333-4444'],
[1,'Ted Shaw','Secondary','444-344-2323'],
[2,'Don Roads','Primary','555-222-5213'],
[2,'Ted Frank','Secondary','222-444-2323']]
df = pd.DataFrame(data, columns = ['ID', 'Name','Sequence','Phone'])
df.pivot(index='ID', columns='Sequence', values=['Name','Phone'])