python - 如何将数据帧分割成 block 并在每次索引重新开始时水平合并

Question

我有一个数据帧，它实际上是垂直连接的多个数据帧。我想水平合并它们，但我无法按索引值将其拆分。我想开始一个索引为“完成”的新 block ，并且我想避免手动执行此操作，因为实际数据帧有大约 20 个部分，并且每个部分的长度不同。

这是原始数据框。

        12_boys  12_girls
finish                   
1        Team A    Team A
2        Team B    Team B
3        Team C    Team C
4        Team D    Team D
5        Team E    Team E
finish  14_boys  14_girls
1        Team A    Team A
2        Team B    Team B
3        Team C    Team C
4        Team D    Team D
finish  16_boys  16_girls
1        Team A    Team A
2        Team B    Team B
3        Team C    Team C
4        Team D    Team D

这就是我想要的。

       12_boys 14_boys 16_boys 12_girls 14_girls 16_girls
finish                                                   
1       Team A  Team A  Team A   Team A   Team A   Team A
2       Team B  Team B  Team B   Team B   Team B   Team B
3       Team C  Team C  Team C   Team C   Team C   Team C
4       Team D  Team D  Team D   Team D   Team D   Team D
5       Team E     NaN     NaN   Team E      NaN      NaN

我能想到的最接近的是手动拆分和合并，但这不会转置列名称。

data1 = data.iloc[0:6]
data2 = data.iloc[6:10]
data3 = data.iloc[11:15]
data_merge = pd.merge(data1, data2, on='finish', how='outer')
data_merge = pd.merge(data_merge, data3, on='finish', how='outer')

Output:

    12_boys_x   12_girls_x  12_boys_y   12_girls_y  12_boys     12_girls
finish                      
1   Team A  Team A  Team A  Team A  Team A  Team A
2   Team B  Team B  Team B  Team B  Team B  Team B
3   Team C  Team C  Team C  Team C  Team C  Team C
4   Team D  Team D  Team D  Team D  Team D  Team D
5   Team E  Team E  NaN     NaN     NaN     NaN
finish  14_boys     14_girls    NaN     NaN     NaN     NaN

Answer 1

我们可以首先通过检查索引更改为finish的位置来识别组。然后我们使用 GroupBy 和 pd.concat 将每个组彼此相邻:

grps = (df.index == 'finish').cumsum()

dfs = []
for grp, dfg in df.groupby(grps):
    if grp != 0:
        dfg.columns = dfg.iloc[0].values
        dfs.append(dfg)
    else: dfs.append(dfg)

df_new = pd.concat(dfs, axis=1, sort=False).iloc[:-1]

  12_boys 12_girls 14_boys 14_girls 16_boys 16_girls
1  Team A   Team A  Team A   Team A  Team A   Team A
2  Team B   Team B  Team B   Team B  Team B   Team B
3  Team C   Team C  Team C   Team C  Team C   Team C
4  Team D   Team D  Team D   Team D  Team D   Team D
5  Team E   Team E     NaN      NaN     NaN      NaN