Search code examples
pythonpandasgroup-bymulti-index

How to move pandas data from index to column after multiple groupby


I have the following pandas dataframe:

token    year    uses  books
  386   xanthos  1830    3     3
  387   xanthos  1840    1     1
  388   xanthos  1840    2     2
  389   xanthos  1868    2     2
  390   xanthos  1875    1     1

I aggregate the rows with duplicate token and years like so:

dfalph = dfalph[['token','year','uses','books']].groupby(['token', 'year']).agg([np.sum])
dfalph.columns = dfalph.columns.droplevel(1)

which results in

               uses  books
token    year       
xanthos  1830    3     3
         1840    3     3
         1867    2     2
         1868    2     2
         1875    1     1

Instead of having the 'token' and 'year' fields in the index, I would like to return them to columns and have an integer index.


Solution

  • Method #1: reset_index()

    >>> g
                  uses  books
                   sum    sum
    token   year             
    xanthos 1830     3      3
            1840     3      3
            1868     2      2
            1875     1      1
    
    [4 rows x 2 columns]
    >>> g = g.reset_index()
    >>> g
         token  year  uses  books
                       sum    sum
    0  xanthos  1830     3      3
    1  xanthos  1840     3      3
    2  xanthos  1868     2      2
    3  xanthos  1875     1      1
    
    [4 rows x 4 columns]
    

    Method #2: don't make the index in the first place, using as_index=False

    >>> g = dfalph[['token', 'year', 'uses', 'books']].groupby(['token', 'year'], as_index=False).sum()
    >>> g
         token  year  uses  books
    0  xanthos  1830     3      3
    1  xanthos  1840     3      3
    2  xanthos  1868     2      2
    3  xanthos  1875     1      1
    
    [4 rows x 4 columns]