Need output in a specific format in Pandas groupby

Question

I ran the below command on my dataset:

data.groupby(['month', 'item'])['date'].median()

Output is as below:

month    item
2014-11  call    107
         data     29
         sms      94
2014-12  call     79
         data     30
         sms      48

But, I need output like this:

 month    item
    2014-11  call    107
    2014-11  data     29
    2014-11  sms      94
    2014-12  call     79
    2014-11  data     30
    2014-11  sms      48

What can i use to achieve the above?

Answer 1

It's not exactly clear from your question what you'd like your DataFrame to look like (in particular, what should the index be).

If you follow your operation by .reset_index(), you'll get something like this:

df = pd.DataFrame({'a': [1, 1, 2, 2], 'b': [1, 2, 1, 2], 'c': range(4)})
df.c.groupby([df.a, df.b]).sum().reset_index()

    a   b   c
0   1   1   0
1   1   2   1
2   2   1   2
3   2   2   3

I.e., a DataFrame with a running index.

If you follow your operation by .reset_index().set_index('month'), you'll get something like this:

df = pd.DataFrame({'a': [1, 1, 2, 2], 'b': [1, 2, 1, 2], 'c': range(4)})
df.c.groupby([df.a, df.b]).sum().reset_index().set_index('a')
    b   c
a       
1   1   0
1   2   1
2   1   2
2   2   3