CODEWITHSUNDEEP
pythonpandasdataframe
Bjarne Timm
Bjarne Timm

Reputation: 115

Convert a Single Index Dataframe to Multiindex Dataframe with diagonal matrix

I have a single index DataFrame called rolling_vol_monthly:

The rolling_vol_monthly DataFrame (579 rows × 10 columns):

               NoDur    Durbl   Manuf   Enrgy   HiTec   Telcm   Shops   Hlth    Utils   Other
Date                                        
1972-11-30     0.00666  0.00939 0.00803 0.00851 0.01205 0.00799 0.00795 0.00819 0.00505 0.00892
1972-12-31     0.00664  0.00943 0.00800 0.00837 0.01185 0.00792 0.00794 0.00804 0.00504 0.00889

I would like to convert that DataFrame to:

                     NoDur  Durbl   Manuf   Enrgy   HiTec   Telcm   Shops   Hlth    Utils   Other
Date          lvl1                              
1972-11-30    NoDur  0.006660       0       0       0       0       0       0       0       0
              Durbl  0      0.00939 0       0       0       0       0       0       0       0
              Manuf  0      0       0.00803 0       0       0       0       0       0       0
              Enrgy  0      0       0       0.00851 0       0       0       0       0       0
              HiTec  0      0       0       0       0.01205 0       0       0       0       0
              Telcm  0      0       0       0       0       0.00799 0       0       0       0
              Shops  0      0       0       0       0       0       0.00795 0       0       0
              Hlth   0      0       0       0       0       0       0       0.00819 0       0
              Utils  0      0       0       0       0       0       0       0       0.00505 0
              Other  0      0       0       0       0       0       0       0       0       0.00892
         


                     NoDur  Durbl   Manuf   Enrgy   HiTec   Telcm   Shops   Hlth    Utils   Other
Date          lvl1                              
1972-11-31    NoDur  0.006640       0       0       0       0       0       0       0       0
              Durbl  0      0.00943 0       0       0       0       0       0       0       0
              Manuf  0      0       0.00800 0       0       0       0       0       0       0
              Enrgy  0      0       0       0.00837 0       0       0       0       0       0
              HiTec  0      0       0       0       0.01185 0       0       0       0       0
              Telcm  0      0       0       0       0       0.00792 0       0       0       0
              Shops  0      0       0       0       0       0       0.00794 0       0       0
              Hlth   0      0       0       0       0       0       0       0.00804 0       0
              Utils  0      0       0       0       0       0       0       0       0.00504 0
              Other  0      0       0       0       0       0       0       0       0       0.00889

The code I tried:

 rvm = rolling_vol_monthly.copy()
 rvm = rvm.groupby(level='Date').apply(lambda g: pd.DataFrame(data = np.diag(g.values) , index = rolling_cov_monthly.index , columns= rolling_vol_monthly.columns))

Where rolling_cov_monthly has the desired indexing.

Upvotes: 2

Views: 64

Answers (1)

jezrael
jezrael

Reputation: 862581

You need flatten values by selecting first values or by ravel:

f = lambda g: pd.DataFrame(data = np.diag(g.values[0]), 
                           index = rvm.columns,
                           columns= rvm.columns)

Or:

f = lambda g: pd.DataFrame(data = np.diag(g.values.ravel()), 
                           index = rvm.columns, 
                           columns= rvm.columns)

Or you can select first value by position by DataFrame.iloc:

f = lambda g: pd.DataFrame(data = np.diag(g.iloc[0].values), 
                           index = rvm.columns,
                           columns= rvm.columns)
rvm = rvm.groupby(level='Date').apply(f)

print (rvm)
                   NoDur    Durbl    Manuf    Enrgy    HiTec    Telcm  \
Date                                                                     
1972-11-30 NoDur  0.00666  0.00000  0.00000  0.00000  0.00000  0.00000   
           Durbl  0.00000  0.00939  0.00000  0.00000  0.00000  0.00000   
           Manuf  0.00000  0.00000  0.00803  0.00000  0.00000  0.00000   
           Enrgy  0.00000  0.00000  0.00000  0.00851  0.00000  0.00000   
           HiTec  0.00000  0.00000  0.00000  0.00000  0.01205  0.00000   
           Telcm  0.00000  0.00000  0.00000  0.00000  0.00000  0.00799   
           Shops  0.00000  0.00000  0.00000  0.00000  0.00000  0.00000   
           Hlth   0.00000  0.00000  0.00000  0.00000  0.00000  0.00000   
           Utils  0.00000  0.00000  0.00000  0.00000  0.00000  0.00000   
           Other  0.00000  0.00000  0.00000  0.00000  0.00000  0.00000   
1972-12-31 NoDur  0.00664  0.00000  0.00000  0.00000  0.00000  0.00000   
           Durbl  0.00000  0.00943  0.00000  0.00000  0.00000  0.00000   
           Manuf  0.00000  0.00000  0.00800  0.00000  0.00000  0.00000   
           Enrgy  0.00000  0.00000  0.00000  0.00837  0.00000  0.00000   
           HiTec  0.00000  0.00000  0.00000  0.00000  0.01185  0.00000   
           Telcm  0.00000  0.00000  0.00000  0.00000  0.00000  0.00792   
           Shops  0.00000  0.00000  0.00000  0.00000  0.00000  0.00000   
           Hlth   0.00000  0.00000  0.00000  0.00000  0.00000  0.00000   
           Utils  0.00000  0.00000  0.00000  0.00000  0.00000  0.00000   
           Other  0.00000  0.00000  0.00000  0.00000  0.00000  0.00000   

                    Shops     Hlth    Utils    Other  
Date                                                  
1972-11-30 NoDur  0.00000  0.00000  0.00000  0.00000  
           Durbl  0.00000  0.00000  0.00000  0.00000  
           Manuf  0.00000  0.00000  0.00000  0.00000  
           Enrgy  0.00000  0.00000  0.00000  0.00000  
           HiTec  0.00000  0.00000  0.00000  0.00000  
           Telcm  0.00000  0.00000  0.00000  0.00000  
           Shops  0.00795  0.00000  0.00000  0.00000  
           Hlth   0.00000  0.00819  0.00000  0.00000  
           Utils  0.00000  0.00000  0.00505  0.00000  
           Other  0.00000  0.00000  0.00000  0.00892  
1972-12-31 NoDur  0.00000  0.00000  0.00000  0.00000  
           Durbl  0.00000  0.00000  0.00000  0.00000  
           Manuf  0.00000  0.00000  0.00000  0.00000  
           Enrgy  0.00000  0.00000  0.00000  0.00000  
           HiTec  0.00000  0.00000  0.00000  0.00000  
           Telcm  0.00000  0.00000  0.00000  0.00000  
           Shops  0.00794  0.00000  0.00000  0.00000  
           Hlth   0.00000  0.00804  0.00000  0.00000  
           Utils  0.00000  0.00000  0.00504  0.00000  
           Other  0.00000  0.00000  0.00000  0.00889

Upvotes: 1

Related Questions