Pivot table error:1 ndim Categorical are not supported at this time

Question

My goal is to box-plot the 'score' by 'label', I don't care about "date" and "Cusip". I want to use 'pivot' to reshape the data, so that each Label is in one column and I can boxplot it.

              date   Cusip    Label Score
663182  2015-07-31  00846UAG    AAA 138.15
663183  2015-07-31  00846UAH    AAA 171.93
663184  2015-07-31  00846UAJ    AAA 175.67
663185  2015-07-31  023767AA    BB  187.92
663186  2015-07-31  023770AA    BB  176.25

t.pivot(index=['date','Cusip'],columns='Label',values='Score')

Errors shows:

NotImplementedError: > 1 ndim Categorical are not supported at this time

More details:

C:\Anaconda3\lib\site-packages\pandas\core\categorical.py in __init__(self, values, categories, ordered, name, fastpath, levels)
    285             try:
--> 286                 codes, categories = factorize(values, sort=True)
    287             except TypeError:

C:\Anaconda3\lib\site-packages\pandas\core\algorithms.py in factorize(values, sort, order, na_sentinel, size_hint)
    184     uniques = vec_klass()
--> 185     labels = table.get_labels(vals, uniques, 0, na_sentinel, True)
    186 

pandas\hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_labels (pandas\hashtable.c:13921)()

ValueError: Buffer has wrong number of dimensions (expected 1, got 2)

Answer 1

You really should be using pivot_table as you have got duplicate entries in your date column.

pd.pivot_table(df, values='Score', index=['date', 'Cusip'], columns=['Label']).boxplot()

Answer 2

As an alternative to .pivot_table(), which might do unwanted aggregations, you can do

df.set_index(['date', 'Cusip','Label'])['Score'].unstack()