How can use pandas group-by while adding new columns on a single level?

Question

The original data looks like this:

    Date        E   
0   2017-09-01  -   
1   2017-09-01  +   
2   2017-09-01  +   
3   2017-09-01  +  
...
... 

After applying groupby:

df.groupby(['Date', 'E'])['Date'].count().to_frame(name = 'Count').reset_index()

I get a dataframe that looks like this:

    Date        E   Count
0   2017-09-01  +   11
1   2017-09-01  -   1
2   2017-09-04  +   1
3   2017-09-04  -   7
4   2017-09-05  +   1
5   2017-09-05  -   23

How can I transform this into a dataframe that instead looks like this:

    Date        +   -
0   2017-09-01  11  1
2   2017-09-04  1   7
4   2017-09-05  1   23

Answer 1

I think better is use GroupBy.size, because GroupBy.count is used for count non NaN values.

Then reshape by unstack:

df = df.groupby(['Date', 'E'])['Date'].size().unstack(fill_value=0).reset_index()
print (df)
E        Date  +  -
0  2017-09-01  3  1

Less typing solution, but in larger df slowier is crosstab:

df = pd.crosstab(df['Date'], df['E'])
print (df)
E           +  -
Date            
2017-09-01  3  1