用行值填充列

Question

我有一个像这样的数据框

key            values
Interface       InterfaceA
State           Up
Line Status     Up
ID              9000
Interface       InterfaceB
State           Down
Line Status     Down
ID              9001

我想把它变成这样

Interface        State        Line Status       ID
InterfaceA        Up             Up             9000
InterfaceB        Down           Down           9001

我尝试使用loc逐列插入该列，但当它到达第2列时

ValueError: cannot reindex from a duplicate axis

出现上述错误

final_df['Interface'] = commands_df.loc[commands_df['key'].str.contains('Interface'), 'values']
final_df['State'] = commands_df.loc[commands_df['key'].str.contains('State'), 'values'] <-- Error starts here

ValueError: cannot reindex from a duplicate axis

Answer 1

带有cumcount和unstack的简单set_index

df_final = df.set_index([df.groupby('key').cumcount(),'key'])['values'].unstack()

Out[423]:
key    ID   Interface Line-Status State
0    9000  InterfaceA          Up    Up
1    9001  InterfaceB        Down  Down

另一种方法是使用pd.crosstab

df_final = pd.crosstab(df.groupby('key')['values'].cumcount(), 
                       df['key'], 
                       df['values'], aggfunc='first')

Out[424]:
key      ID   Interface Line-Status State
row_0
0      9000  InterfaceA          Up    Up
1      9001  InterfaceB        Down  Down

Answer 2

这里有一个可能的解决方案-

import pandas as pd

df = pd.DataFrame(data=['InterfaceA', 'Up', 'Up', 9000, 'InterfaceB', 'Down', 'Down', 9001],
                  index=['Interface', 'State', 'Line Status', 'ID', 'Interface', 'State', 'Line Status', 'ID'])

df = df.T
print(df.groupby(df.columns.values, axis=1).agg(lambda x: x.values.tolist()).sum().apply(pd.Series).T)

     ID   Interface Line Status State
0  9000  InterfaceA          Up    Up
1  9001  InterfaceB        Down  Down

本answer的学分

Answer 3

df = df.assign(Interface=df[df['key'] == 'Interface']['values']).ffill()
print(df.pivot(index='Interface', columns='key', values='values').drop(columns='Interface'))

印刷品：

key           ID Line Status State
Interface                         
InterfaceA  9000          Up    Up
InterfaceB  9001        Down  Down