How to rewrite Pandas frame.append with concat

Question

I have the following code which works perfectly putting in subtotals and grand totals. With the frame.append method deprecated how should this be rewritten?

pvt = pd.concat([y.append(y.sum()
                           .rename((x, 'Total')))
                for x, y in table.groupby(level=0)
                 ]).append(table.sum()
                                 .rename(('Grand', 'Total')))

Prior to this, I created a pivot table. So I'm looking for the totals to be stacked, not added as another column

pivot = pd.pivot_table(data=df2,
            index=['date_created','BuyerName'],
            aggfunc='sum').round()

I get the following error with suggestion #2 ---> 17 pvt = pd.concat([x for _, y in table.groupby(level=0) for x in (y, y.sum().rename((x, 'Total')))] +
18 [table.sum().rename(('Grand', 'Total'))]) 'Total'))) 25 return(pvt)

UnboundLocalError: local variable 'x' referenced before assignment

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jul 25 '22 at 23:16

score 0 · Answer 1 · answered Jul 25 '22 at 18:49

Use

pvt = pd.concat([y for x, y in table.groupby(level=0)] + \
                [y.sum().rename((x, 'Total')) for x, y in table.groupby(level=0)] + \
                [table.sum().rename(('Grand', 'Total'))])

# or

pvt = pd.concat([x for _, y in table.groupby(level=0) for x in (y, y.sum().rename((x, 'Total')))] + \
                [table.sum().rename(('Grand', 'Total'))])

score 0 · Answer 2 · answered Feb 20 '23 at 20:48

Ynjxsjmh's answer wouldn't compile for me, so here's what I ended up using:

pd.concat([x for a, y in table.groupby(level=0) for x in (y, y.sum().rename((a, 'Total')).to_frame().T)] + \
          [table.sum().rename(('Grand', 'Total')).to_frame().T])

From what I can tell, the code works like this:

table.groupby(level=0) returns a tuple of (entry_name,subframe) that we can operate on. We want both the original subframe (y) and a calculation off the subframe (y.sum()...) so we do a multi-for list comprehension to return both of them.

Unfortunately if y.sum() returns a series (as it's apt to do) we need to pull it back to a frame with to_frame(). I'm not sure why this needs transposed but it fixed some errors I had so ymmv.

How to rewrite Pandas frame.append with concat

2 Answers2