table function result in R with three variables in Python pandas

Question

Let's say we have this sample data.

| mem_id | main_title | sub_title |
-----------------------------------
|   1    |      1     |    1      |
|   10   |      3     |    2      |
|   3    |      3     |    2      |
|   45   |      1     |    2      |
|   162  |      2     |    2      |

... 

1) summary of data

• mem_id : unique id of 200 people
• main_title : 3 unique labels (1,2,3)
• sub_title : 6 unique labels (1,2,3,4,5,6) and each main_title can have one of these sub_title.

possible to have repetition like one mem_id can have multiple case of (1 : main , 1 : sub)

2) question

I'd like to make R table function result in python.

R table function result is like this. I can make every possible combination from all main_title and sub_title. Also can get the count from each case by mem_id.

count.data <- table(data$mem_id, data$main_title, data$sub_title)
count.table <- as.data.frame(count.data)
===============================================
          mem_id   main_title   sub_title value
1            1         1              1     0
2            2         1              1     0
3            3         1              1     0
4            4         1              1     0
5            5         1              1     0
6            6         1              1     0
7            7         1              1     0
.
.
.

I've tried to get this result in Python and the result below is what i got so far.

cross_table1 = pd.melt(data, id_vars=['main_title ', 'sub_title'], value_vars='mem_id', value_name='mem_id')

==================================================
         main_title sub_title  variable   mem_id
1            1         1         mem_id     10
2            1         1         mem_id     10
3            3         1         mem_id     10
4            4         2         mem_id     10
5            1         4         mem_id     132
6            4         1         mem_id     65
7            4         3         mem_id     88
.
.
.

cross_table2 = cross_table1.pivot_table(index=['main_title ', 'sub_title', 'mem_id'], values='variable', aggfunc='count')

cross_table32.reset_index().sort_values('value')

==============================================
         main_title sub_title  mem_id    value
1            1         1         1         4
2            1         1         2         3
3            3         1         3         1
4            4         2         3         10
5            1         4         3         2
6            1         1         4         5
7            3         2         5         2
.
.
.

I recognize this only show the positive result of value(count of case) column.

What i need is to include all possible combination of main_title and sub_title, so like 1&1(main&sub) case has to have 200 rows with possible zero value in count column.

It would be so grateful if I can get any help or advice!! Thanks :)

Answer 1

In pandas you can do with groupby + reindex

s=df.groupby(df.columns.tolist()).size()
idx=pd.MultiIndex.from_product(list(map(set,df.values.T)))
s=s.reindex(idx,fill_value=0)
s
Out[15]: 
162  1  1    0
        2    0
     2  1    0
        2    1
     3  1    0
        2    0
1    1  1    1
        2    0
     2  1    0
        2    0
     3  1    0
        2    0
10   1  1    0
        2    0
     2  1    0
        2    0
     3  1    0
        2    1
3    1  1    0
        2    0
     2  1    0
        2    0
     3  1    0
        2    1
45   1  1    0
        2    1
     2  1    0
        2    0
     3  1    0
        2    0
dtype: int64