需要从一个热编码形状检索原始数据形状

Question

我收到了一个数据集，其中包括先前转换为一个热编码的列。我想检索其旧形状以进行一些预处理和填充NAS方法，当然也可以阅读数据集的统计模型。

我得到的数据列：

team2_offistion_derived_var_0	team2_offistion_derived_derived_var_1	team2_offistion_derived_derived_var_var_2	team2_offence_derives_derived_derived_var_3	team2_offistion_derived_derives_var_var_4	team2_offensive_derived_var_9	team2_offensive_derives_var_10	team2	0	0	0 0 0 0 0 0 0
0 0	0 0	0 0	0	0	0	0 0	0 0	0	0	0
0	0	0	0	0	0	0 0 0 0 0 0	0 0	0	0	0
0 0 0	0	0	0	0	0	0	1	0	0 0	0
0	0	0	0	0	0 0	1	0	0	0	0
0	0	0	0	0	0	0	0	0	0	0
0	0	0	0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0	0	0	0	0	0	0	0
0	0	0	0 0	0	0	0	0	0	0 0	0
0 0 0	0	0	0	0	0 0	0	0	0	0	0
0	0	0	0	0	0	0	0	0	我	0
0	0	0	0	0 0	0 0	0 0 0	0	0	0 0	0 0 0
0 0	0 0 0 0	0	0	0	0	0	0	0	0	0

想将其形状转换为

row_id	team2_offissives_ferived
0	var 10 var 10
1	var 9
2	var 8
3	var 7
4	var 6
5	var 5
6	var 4
7	var 3
8	var 2
9	var 1
10	var 0

列，例如：

team2_other_ratio_var_42	team2 team2_other_ratio_var_43	_other_ratio_var_48	team2_other_ratio_var_49	team2_other_ratio_var_50	team2_other_ratio_var_51	team2_other_ratio_var_52	i还	0.0	一个	0.0
获得了	0.400	0.200	0.000	0.750	0.250	0.341121	0.375	0.354167	0.184211	0.000

，但我很困惑如何将其检索到原始形状？ “分类”，但我不知道如何？

谢谢大家的帮助

Answer 1

您可以使用stack：

cols = ['row_id', 'team2_offensive_derived']
out = df.replace(0, pd.NA).stack().rename_axis(cols).reset_index()[cols]

输出：

    row_id         team2_offensive_derived
0        0  team2_offensive_derived_var_10
1        1   team2_offensive_derived_var_9
2        2   team2_offensive_derived_var_8
3        3   team2_offensive_derived_var_7
4        4   team2_offensive_derived_var_6
5        5   team2_offensive_derived_var_5
6        6   team2_offensive_derived_var_4
7        7   team2_offensive_derived_var_3
8        8   team2_offensive_derived_var_2
9        9   team2_offensive_derived_var_1
10      10   team2_offensive_derived_var_0

具有不同的列名称：

out = (df
       .replace(0, pd.NA)
       .rename(columns=lambda x: x.replace('team2_offensive_derived_', ''))
       .stack()
       .rename_axis(cols)
       .reset_index()[cols]
      )

输出：

    row_id team2_offensive_derived
0        0                  var_10
1        1                   var_9
2        2                   var_8
3        3                   var_7
4        4                   var_6
5        5                   var_5
6        6                   var_4
7        7                   var_3
8        8                   var_2
9        9                   var_1
10      10                   var_0

Answer 2

使用：

#split by last previous _
df.columns = df.columns.str.rsplit('_', 2, expand=True)
#replace 0 to NaNs, so reshape remove rows with 0
df = df.replace(0, np.nan).stack([1,2])
#join MultiIndex value
df.index = df.index.map(lambda x: f'{x[1]} {x[2]}')
#create DataFrame
df = df.index.to_frame(name='team2_offensive_derived',index=False)

print (df)
   team2_offensive_derived
0                   var 10
1                    var 9
2                    var 8
3                    var 7
4                    var 6
5                    var 5
6                    var 4
7                    var 3
8                    var 2
9                    var 1
10                   var 0