Pandas: Unpivot Excel Data Where Category and Children Labels Are In Same Column

Basically, I think the easiest way to explain this is, I am trying expand a multi-indexed table, but the indexes are both in the same column.

My data is structured like this:

What I would like out is a Dataframe like this:

Is there any built in pandas methods, or a straight forward approach to handling this type of translation?

I have tried manually breaking down the table and recreating it by collecting the row labels that are repeated, and making columns from them, with the data from rows with that label, but, the tricky spot is where child data is missing; like in the example above Collection2 Data2 doesn't exist. With this approach I could calculate for each row if Data1 equals Collection1 and if it does, add a 0 to Data2 at that index. But, it seems super ugly, and figured there is probably a much more elegant approach.

Using a 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()

Output:

         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

I'm not sure if exists some straightforward Pandas solution, but you can try this example:

# 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)

Prints:

    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

Welcome to SO. Simplest lines possible, in my opinion:

With input data:

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) Reformulate the table so it can be pivoted

(Parse Collection and Data information)

# 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')]

Reformulated input table:

  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) Pivot then complete the table with sums in both directions:

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)

Final output:

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

Another possible solution:

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
展开收缩
)
 .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'))

Output:

    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