Pandas convert rows of dataframe to diagonal dataframe

user_14449311 0
Viewed 58

I have a dataframe where I want to convert each row into a diagonal dataframe and bind all the resulting dataframes into 1 large dataframe. Input:

            a   b   c
2021-11-06  1   2   3
2021-11-07  4   5   6

Desired output:


                   a    b   c
Date                
2021-11-06     a    1   0   0
               b    0   2   0
               c    0   0   3
2021-11-07     a    4   0   0
               b    0   5   0
               c    0   0   6

I tried using apply on each row of the original dataframe.

data = pd.DataFrame([[1,2,3],[4,5,6]], columns=['a', 'b', 'c'], index=pd.date_range('2021-11-06', '2021-11-07')) 

def convert_dataframe(ser):
    df_ser = pd.DataFrame(0.0, index=ser.index, columns=ser.index)
    np.fill_diagonal(df_ser.values, ser)
    return df_ser

data.apply(lambda x: convert_dataframe(x), axis=1)

However, the output is not the multi-index dataframe that I expected. The output is instead a single index dataframe where each row is a reference to the diagonal dataframe returned.

Any help is much appreciated. Thanks in advance.

pandas dataframe python
1 Answers

Use MultiIndex.droplevel for remove first level of MultiIndex and call function after DataFrame.stack in GroupBy.apply:

def convert_dataframe(ser):
    ser = ser.droplevel(0)
    df_ser = pd.DataFrame(0, index=ser.index, columns=ser.index)
    np.fill_diagonal(df_ser.values, ser)
    
    return df_ser



data = data.stack().groupby(level=0).apply(convert_dataframe)
print (data)
              a  b  c
2021-11-06 a  1  0  0
           b  0  2  0
           c  0  0  3
2021-11-07 a  4  0  0
           b  0  5  0
           c  0  0  6
user_29010020
Related