折叠pandas数据帧中类似前缀的列，转换为row_index

Question

简而言之，我只想要"ts_；带前缀的列转换为行索引。我打算使用"ts"one_answers"id"列作为多索引。

rows = [{'id':1, 'a_ts':'2020-10-02','a_energy':6,'a_money':2,'b_ts':'2020-10-02', 'b_color':'blue'},
{'id':2, 'a_ts':'2020-02-02','a_energy':2,'a_money':5, 'a_color':'orange', 'b_ts':'2012-08-11', 'b_money':10, 'b_color':'blue'},
{'id':3,'a_ts':'2011-02-02', 'a_energy':4}]
df = pd.DataFrame(rows)
id        a_ts  a_energy  a_money        b_ts b_color a_color  b_money
0   1  2020-10-02         6      2.0  2020-10-02    blue     NaN      NaN
1   2  2020-02-02         2      5.0  2012-08-11    blue  orange     10.0
2   3  2011-02-02         4      NaN         NaN     NaN     NaN      NaN

我希望我的输出看起来像这样。

energy  money   color
id ts                               
1  2020-10-02     6.0    2.0    blue
2  2020-02-02     2.0    5.0  orange
2012-08-11     NaN   10.0    blue
3  2011-02-02     4.0    NaN     NaN

我能想到的最好的办法是用下划线分割列并重置索引，但这会创建id和时间戳为NaN的行。

我不能简单地用NaN创建行，然后去掉所有这些行。因为我将丢失关于哪些ID不包含时间戳或哪些时间戳没有匹配的ID的信息(这是因为数据帧是联接的结果(。

df.columns = df.columns.str.split("ts_", expand=True)
df = df.stack().reset_index(drop=True)

Answer 1

使用：

df = df.set_index(['id'])
df.columns = df.columns.str.split("_", expand=True)
df = df.stack(0).reset_index(level=-1,drop=True).reset_index()
print (df)
id   color  energy  money          ts
0   1     NaN     6.0    2.0  2020-10-02
1   1    blue     NaN    NaN  2020-10-02
2   2  orange     2.0    5.0  2020-02-02
3   2    blue     NaN   10.0  2012-08-11
4   3     NaN     4.0    NaN  2011-02-02

然后通过自定义lambda函数对只删除NaN行的每个组的值进行移位：

f = lambda x: x.apply(lambda y: pd.Series(y.dropna().tolist()))
df = df.set_index(['id','ts']).groupby(['id','ts']).apply(f).droplevel(-1)
print (df)
color  energy  money
id ts                               
1  2020-10-02    blue     6.0    2.0
2  2012-08-11    blue     NaN   10.0
2020-02-02  orange     2.0    5.0
3  2011-02-02     NaN     4.0    NaN