0

If you make a pivot table in excel it will default to something with this format:

Excel Pivot Table

source: https://pbpython.com/pandas-pivot-report.html

Specific to my problem see rows 4, 15, and 25 of the picture.

When generating a pivot table with Pandas, the best I can come up with is something that is missing the information in rows 4, 15, and 25. Basically, rows 4, 5, and 6 get condensed to a single row. (Same with rows 15, 16, 17.)

Here is what my df looks like prior to generating a pivot table:

idx Votes   1st 2nd 3rd 4th
1   212 C   A   D   B
2   403 C   A   B   D
3   76  C   D   A   B
4   233 C   D   B   A
5   602 C   B   D   A
6   396 C   B   A   D
7   375 A   C   D   B
8   491 A   C   B   D
9   196 A   D   C   B
10  37  A   D   B   C
11  171 A   B   C   D
12  599 A   B   D   C
13  487 B   C   A   D
14  611 B   C   D   A
15  396 B   D   C   A
16  247 B   D   A   C
17  289 B   A   C   D
18  692 B   A   D   C
19  556 D   C   A   B
20  518 D   C   B   A
21  555 D   B   C   A
22  639 D   B   A   C
23  433 D   A   B   C
24  608 D   A   C   B

The code I'm using for the pivot table:

pivot = pd.pivot_table(df, index=["1st", "2nd", "3rd"], values="votes", aggfunc=np.sum

The result:

Votes 1st  2nd  3rd
A    B    C      171  <-- notice that A, B, C are on same row,
          D      599      and so there is no total for A and then B.
     C    B      491
          D      375
     D    B       37
          C      196
B    A    C      289
          D      692
     C    A      487
          D      611
     D    A      247
          C      396
C    A    B      403
          D      212
     B    A      396
          D      602
     D    A       76
          B      233
D    A    B      433
          C      608
     B    A      639
          C      555
     C    A      556
          B      518

It should look like this:

Votes 1st  2nd  3rd
A               1869  # Total A 1st place votes  *
     B           770  # Total B 2nd with A 1st   *
          C      171  # Total C 3rd with A 1st and B 2nd
          D      599  # Total C 3rd with A 1st and B 2nd
     C           866  # Total C 2nd with A 1st   *
          B      491  # Total B 3rd with A 1st and C 2nd
          D      375  # Total D 3rd with A 1st and C 2nd
     D           233  # Total D 2nd with A 1st   *
          B       37  # Total B 3rd with A 1st and D 2nd
          C      196  # Total C 3rd with A 1st and D 2nd
B               2722  # Total B 1st place votes  *
     A           981  # Total A 2nd with B 1st   *
          C      289  # etc...
          D      692
     C           ...
          A      487
          D      611

The rows with an asterisk (star) are missing in the Pandas pivot table (or rather these are condensed so that you only see A, B, C together with the total for C, etc.).

Is it possible to get a pivot table like the one Excel makes by default?

extDependency
  • 97
  • 9
  • Does this https://stackoverflow.com/questions/41383302/pivot-table-subtotals-in-pandas answer what you are looking? – Vivek Oct 25 '21 at 02:13
  • @Vivek, unfortunately I don't see how to get the layout I'm looking for from that answer. (cf. my updated post where I tried to clarify and provide a more robust example). It comes out looking like this (e.g, first row): `(A, B, C) 171.0 NaN NaN NaN NaN` – extDependency Oct 26 '21 at 00:03

0 Answers0