How to append column total to pandas dataframe every 4th row?

user_10592904 0
Viewed 77

I have the following dataframe with weekly numbers for column A, B and C:

week  A B C
0     1 0 0
1     1 0 1
2     0 1 0
3     1 1 1
4     1 0 0
5     0 0 1
6     0 1 0
7     1 1 1
8     1 0 0
9     0 0 1
10    0 1 0
11    1 1 1

and I want to append the dataframe with a row consisting of the monthly total for each column (so the previous 4 weeks together), with a wanted outcome something like this:

week   A B C
0      1 0 0
1      1 0 1
2      0 1 0
3      1 1 1
total  3 2 2
4      1 0 0
5      0 0 1
6      1 1 0
7      2 1 0
total  4 2 1
8      1 0 0
9      0 0 1
10     0 0 0
11     1 0 1
total  2 0 2

I have used

df.groupby(df.index // 4).sum(numeric_only=True, axis=0)

to get the monthly numbers but I do not know how to get it appended in the df. Any ideas? Thanks

pandas dataframe python
2 Answers

pd.concat

Iterating through the groupby object allows us to append the total row to each sub-dataframe. By passing this through a dictionary comprehension/pd.concat we conveniently get month added as a level in the index to disambiguate the 'total' identifier in the index.

pd.concat({
    m: d.append(d.sum().rename('total'))
    for m, d in df.groupby(df.index // 4)
}, names=['month'])

             A  B  C
month week          
0     0      1  0  0
      1      1  0  1
      2      0  1  0
      3      1  1  1
      total  3  2  2
1     4      1  0  0
      5      0  0  1
      6      0  1  0
      7      1  1  1
      total  2  2  2
2     8      1  0  0
      9      0  0  1
      10     0  1  0
      11     1  1  1
      total  2  2  2
user_23366540

I assume that week in your data sample is a regular column (not index).

Your code generates only "sum" rows for each group, but the proper solution is to:

  • generate a "sum" row for the current group,
  • return the original group with this row appended.

To do it define a function adding the "total" row to the current group and returning it:

def addTotal(grp):
    wrk = grp.loc[:, 'A':'C'].sum().append(
        pd.Series(['total'], index=['week'])).rename('total')
    return grp.append(wrk)

Then group the source DataFrame as you did so far and apply this function:

result = df.groupby(df.index // 4, group_keys=False)\
    .apply(addTotal).reset_index(drop=True)

(an additional step is to reset the index).

The result, for your data sample, is:

     week  A  B  C
0       0  1  0  0
1       1  1  0  1
2       2  0  1  0
3       3  1  1  1
4   total  3  2  2
5       4  1  0  0
6       5  0  0  1
7       6  0  1  0
8       7  1  1  1
9   total  2  2  2
10      8  1  0  0
11      9  0  0  1
12     10  0  1  0
13     11  1  1  1
14  total  2  2  2

Note: Your data sample and the expected result are inconsistent. E.g. row for week == 7, column A has:

  • 1 in data sample,
  • but 2 in the expected result.

This explains some discrepancies between your expected result and mine.

user_73884770
Related