Grouping and counting to get ratios in pandas

Question

Here's another great question on dataframes asked in the r that would benefit from a pandas solution. Here's the question.

I want to count per country the number of times the status is open and the number of times the status is closed. Then calculate the closerate per country.

Data:

  customer country   closeday status
1        1      BE 2017-08-23 closed
2        2      NL 2017-08-05   open
3        3      NL 2017-08-22 closed
4        4      NL 2017-08-26 closed
5        5      BE 2017-08-25 closed
6        6      NL 2017-08-13   open
7        7      BE 2017-08-30 closed
8        8      BE 2017-08-05   open
9        9      NL 2017-08-23 closed

The idea is to get an output depicting the number of open and closed status, and the close_ratio. This is the desired output:

country   closed  open  closed_ratio                         
BE            3     1          0.75
NL            3     2          0.60

Look forward to your suggestions.

Solution included below in an answer. Welcome other solutions.

Answer 1

Here's are some ways

1)

In [420]: (df.groupby(['country', 'status']).size().unstack()
             .assign(closed_ratio=lambda x: x.closed / x.sum(1)))
Out[420]:
status   closed  open  closed_ratio
country
BE            3     1          0.75
NL            3     2          0.60

2)

In [422]: (pd.crosstab(df.country, df.status)
             .assign(closed_ratio=lambda x: x.closed/x.sum(1)))
Out[422]:
status   closed  open  closed_ratio
country
BE            3     1          0.75
NL            3     2          0.60

3)

In [424]: (df.pivot_table(index='country', columns='status', aggfunc='size')
             .assign(closed_ratio=lambda x: x.closed/x.sum(1)))
Out[424]:
status   closed  open  closed_ratio
country
BE            3     1          0.75
NL            3     2          0.60

4) borrowed from piRSquared

In [430]: (df.set_index('country').status.str.get_dummies().sum(level=0)
             .assign(closed_ratio=lambda x: x.closed/x.sum(1)))
Out[430]:
         closed  open  closed_ratio
country
BE            3     1          0.75
NL            3     2          0.60

Answer 2

df

   customer country    closeday  status
1         1      BE  2017-08-23  closed
2         2      NL  2017-08-05    open
3         3      NL  2017-08-22  closed
4         4      NL  2017-08-26  closed
5         5      BE  2017-08-25  closed
6         6      NL  2017-08-13    open
7         7      BE  2017-08-30  closed
8         8      BE  2017-08-05    open
9         9      NL  2017-08-23  closed

---

Apply the groupby, and count each group with size, and then unstack the first level.

df2 = df.groupby(['country', 'status']).status.size().unstack(level=1)
df2

status   closed  open
country              
BE            3     1
NL            3     2

Now, calculate closed_ratio:

df2['closed_ratio'] = df2.closed / df2.sum(1)     
df2

status   closed  open  closed_ratio
country                            
BE            3     1          0.75
NL            3     2          0.60