CODEWITHSUNDEEP
pandasdataframe
Windstorm1981
Windstorm1981

Reputation: 2680

Reshape Dataframe with Non-Unique Index in Python 2.7 and Pandas

I have a dataframe that looks like the following:

             Weight              Ticker
Date                                    
2002-12-31  0.005953              PWER
2002-12-31  0.010682               TUP
2002-12-31  0.012403             AAMRQ
2003-01-31  0.012960               GTW
2003-01-31  0.023259          2987536Q

I want to get it into the following form:

                   PWER      TUP      AAMRQ     GTW    2987536Q
Date
2002-12-31      0.005953   0.010682  0.012403    Nan     Nan
2003-01-31         Nan        Nan       Nan   0.012960 0.023259

I can't reshape using pivot (and resetting the index) because the date values are not unique. The only way I can think of accomplishing this is through brute force iteration.

Is there an easier way to do this?

Thanks in advance.

Upvotes: 1

Views: 1204

Answers (1)

unutbu
unutbu

Reputation: 880877

If you reset the index to make the Dates a column, then you can use pivot:

In [25]: df.reset_index().pivot(index='Date', columns='Ticker', values='Weight')
Out[25]: 
Ticker      2987536Q     AAMRQ      GTW      PWER       TUP
Date                                                       
2002-12-31       NaN  0.012403      NaN  0.005953  0.010682
2003-01-31  0.023259       NaN  0.01296       NaN       NaN

Note that if there are no duplicate (Date, Ticker) pairs, then you can use pivot. If there are duplicates, (as ayhan suggests) you'll need to use pivot_table and specify an aggfunc -- a function that aggregates the multiple values associated with the duplicate (Date, Ticker) pair into an single value.

Upvotes: 1

Related Questions