使用pivot_table：

# identify groups
m = df['Row Labels'].str.match(r'Collection\d+|Total')

# reshape
out = (df
   .assign(index=df['Row Labels'].where(m).ffill(),
           col=df['Row Labels'].mask(m, 'Sum')
          )
   .pivot_table(index='index', columns='col', values='Sum', fill_value=0)
   .rename_axis(columns=None)
)

# recompute Total
out.loc['Total'] = out.drop('Total').sum()

out = out.reset_index()

字符串
输出量：

index  Data 1  Data 2  Sum
0  Collection1      10      12   22
1  Collection2      33       0   33
2  Collection3      14      31   45
3        Total      57      43  100

型

我不确定是否存在一些简单的Pandas解决方案，但你可以试试这个例子：

# remove the Total row - will recreate it as last step
df = df[df["Row Labels"] != "Total"]

# find the indices for pivoting
mask = df["Row Labels"].str.startswith("Collection")
df["idx"] = mask.cumsum()

# do the actual transformation here: pivot + merge
df = (
    pd.merge(
        df[mask],
        df[~mask].pivot(index="idx", columns="Row Labels", values="Sum"),
        left_on="idx",
        right_index=True,
    )
    .drop(columns=["idx"])
    .fillna(0)
)

# add Total row back
df = pd.concat(
    [
        df,
        pd.DataFrame(
            {"Row Labels": ["Total"], **{c: [df[c].sum()] for c in df.loc[:, "Sum":]}}
        ),
    ]
)

print(df)

字符串
图纸：

Row Labels  Sum  Data 1  Data 2
0  Collection1   22    10.0    12.0
3  Collection2   33    33.0     0.0
5  Collection3   45    14.0    31.0
0        Total  100    57.0    43.0

型

欢迎来到SO。在我看来，最简单的台词是：
输入数据：

df = pd.DataFrame(columns = ['Row Labels', 'Sum'],
                  data =   [['Collection1', 22],
                            ['Data 1',      10],
                            ['Data 2',      12],
                            ['Collection2', 33],
                            ['Data 1',      33],
                            ['Collection3', 45],
                            ['Data 1',      14],
                            ['Data 2',      31],
                            ['Total',      100]])
    Row Labels  Sum
0  Collection1   22
1       Data 1   10
2       Data 2   12
3  Collection2   33
4       Data 1   33
5  Collection3   45
6       Data 1   14
7       Data 2   31
8        Total  100

字符串

（1）重新制定表格，以便它可以旋转

（解析 * 收集 * 和 * 数据 * 信息）

# Extract Collection number as a new row
df['Collection'] = df['Row Labels'].str.extract("Collection\s*(\d)", expand=True).ffill()
#df['Collection'] = df['Row Labels'].str.startswith('Coll').cumsum()#old: assumed Collection always came in natural order -removed following mozway's comment, thank you!

# keep only 'Data' rows, because pivoting won't work well with redundant information.
df = df[df['Row Labels'].str.contains('Data')]

型
重新制定的输入表：

Row Labels  Sum  Collection
1     Data 1   10           1
2     Data 2   12           1
4     Data 1   33           2
6     Data 1   14           3
7     Data 2   31           3

型

（2）透视，然后用两个方向的总和完成表格：

pt = pd.pivot_table(data    = df,
                    values  = 'Sum',
                    index   = 'Collection',
                    columns = 'Row Labels', 
                    fill_value=0)

# Recalculate the sums and totals
pt['Sum']       = pt.sum(axis=1)
pt.loc['Total'] = pt.sum(axis=0)

型
最终输出：

Row Labels  Data 1  Data 2  Sum
Collection                     
1               10      12   22
2               33       0   33
3               14      31   45
Total           57      43  100

型

另一种可能的解决方案：

s = df['Row Labels'].str.startswith('Collection')

(df.assign(aux = s.cumsum())
 .pivot(index='aux', columns='Row Labels', values='Sum')
 .set_axis(df['Row Labels'].loc[s])
 .filter(like='Data')
 .rename_axis(None, axis=1)
 .fillna(0)
 .pipe(lambda x: pd.concat([x, x.sum().to_frame().T.set_axis(['Total'])]))
 .assign(Sum = lambda x: x.sum(axis=1))
 .reset_index(names = 'Row Labels'))

字符串
输出量：

Row Labels  Data 1  Data 2    Sum
0  Collection1    10.0    12.0   22.0
1  Collection2    33.0     0.0   33.0
2  Collection3    14.0    31.0   45.0
3        Total    57.0    43.0  100.0

型