Python Pandas: multi-index unstack taking forever

Question

I've read a DataFrame from a .csv file with the following columns:

columns = ['Year', 'month', 'column1', 'column2','column3', 'column4', 'column5', 'column6', 'column7', 'column8','Value']

The dataframe has 116408 rows but after df = df.drop_duplicates() it now has 98829 (I don't know why it had duplicates in the first place)

I need to unstack it like this:

                         1              2              3              ....
                         2016 2017 2018 2016 2017 2018 2016 2017 2018 .... 
column1 column2 .......  
     a1      a2    ...     24   12   20   22   15   21   12   11   13  ...
     b1      b2    ...     18   11   21   21   11   31   14   41   14  ...

So far, I've tried:

df = df.set_index(columns[:-1], append=True)
df = df.unstack(level=[0,1])

But this takes forever. (If I remove the append a get this error: ValueError: Index contains duplicate entries, cannot reshape)

Does anyone have another option or any idea why it's taking so long? I haven't seen the result nor any error.

Answer 1

I believe you are unstacking the wrong levels. Because you have append=True when you set the index, the first value in your new index is whatever it was (you don't indicate what this index value is, so I am just assuming a continuous range starting at zero). The next two levels would then be Year and month.

So, try this to get your desired output:

df.unstack(level=[1, 2])

---

np.random.seed(0)
columns = ['Year', 'month', 'column1', 'column2', 'column3', 'column4', 'column5', 'column6', 'column7', 'column8','Value']
df = pd.DataFrame(np.random.randn(99, 11), columns=columns)
df.loc[:, 'Year'] = [2016, 2017, 2018] * 33
df.loc[:, 'month'] = [1, 2, 3] * 33

>>> df.set_index(columns[:-1], append=True).unstack(level=[1,2]).head()
                                                                                      Value  \
Year                                                                                   2016   
month                                                                                     1   
  column1   column2   column3   column4   column5   column6   column7   column8               
0  0.978738  2.240893  1.867558 -0.977278  0.950088 -0.151357 -0.103219  0.410599  0.144044   
1  0.121675  0.443863  0.333674  1.494079 -0.205158  0.313068 -0.854096 -2.552990       NaN   
2  2.269755 -1.454366  0.045759 -0.187184  1.532779  1.469359  0.154947  0.378163       NaN   
3  0.156349  1.230291  1.202380 -0.387327 -0.302303 -1.048553 -1.420018 -1.706270  1.950775   
4 -1.252795  0.777490 -1.613898 -0.212740 -0.895467  0.386902 -0.510805 -1.180632       NaN   

                                                                                             \
Year                                                                                   2017   
month                                                                                     2   
  column1   column2   column3   column4   column5   column6   column7   column8               
0  0.978738  2.240893  1.867558 -0.977278  0.950088 -0.151357 -0.103219  0.410599       NaN   
1  0.121675  0.443863  0.333674  1.494079 -0.205158  0.313068 -0.854096 -2.552990  0.653619   
2  2.269755 -1.454366  0.045759 -0.187184  1.532779  1.469359  0.154947  0.378163       NaN   
3  0.156349  1.230291  1.202380 -0.387327 -0.302303 -1.048553 -1.420018 -1.706270       NaN   
4 -1.252795  0.777490 -1.613898 -0.212740 -0.895467  0.386902 -0.510805 -1.180632 -0.028182   


Year                                                                                   2018  
month                                                                                     3  
  column1   column2   column3   column4   column5   column6   column7   column8              
0  0.978738  2.240893  1.867558 -0.977278  0.950088 -0.151357 -0.103219  0.410599       NaN  
1  0.121675  0.443863  0.333674  1.494079 -0.205158  0.313068 -0.854096 -2.552990       NaN  
2  2.269755 -1.454366  0.045759 -0.187184  1.532779  1.469359  0.154947  0.378163 -0.887786  
3  0.156349  1.230291  1.202380 -0.387327 -0.302303 -1.048553 -1.420018 -1.706270       NaN  
4 -1.252795  0.777490 -1.613898 -0.212740 -0.895467  0.386902 -0.510805 -1.180632       NaN