Pandas aggregate data and arrange over columns

Question

I have a dataframe that looks like this:

     Values  State   Sort
3    a       Open    42
4    b       Close   42
5    d       Open    42
6    c       Open    42
7    b       Open    42
8    a       Open    45
9    d       Open    45
10   a       Open    45
11   c       Open    45
12   c       Open    45
13   d       Open    45
14   d       Open    46
15   b       Open    46
16   b       Open    46
17   c       Open    46
18   a       Open    46

How can I aggregate the data so that it counts the values in columns "Values" and arranges the data over columns as per the values in column "Sort" to look like below:

Values   State   42   45   46
a        Open    1    2    1
b        Close   1    0    0
b        Open    1    0    2
c        Open    1    2    1
d        Open    1    2    1

I wrote the following code for the same

tables_df = pd.DataFrame(alerts_df.groupby(['Values','State','Sort']).size().rename('Count'))
tables_df = pd.pivot_table(alerts_df, index=['Values','State'], columns=['Sort'], values=['Count'], aggfunc=np.sum)
tables_df=tables_df.reset_index()

But it's returning an empty dataframe.

What am I doing wrong? Also is there a way to do this without using pivot table in pandas?

You can check the answer I linked – BENY Dec 08 '17 at 15:55 — BENY, Dec 08 '17 at 15:55

score 1 · Answer 1 · answered Dec 08 '17 at 15:55

I think you need:

alerts_df = (alerts_df.groupby(['Values','State','Sort'])
                     .size()
                     .unstack(fill_value=0)
                     .reset_index()
                     .rename_axis(None, 1))
print (alerts_df)
  Values  State  42  45  46
0      a   Open   1   2   1
1      b  Close   1   0   0
2      b   Open   1   0   2
3      c   Open   1   2   1
4      d   Open   1   2   1

Pandas aggregate data and arrange over columns

1 Answers1