Transpose dataframe with respect to a column without duplicate columns

user_10083365 0
Viewed 95

I have a DataFrame which looks like:

    ftr_1  ftr_2  ftr_3  ftr_4
    1      0.1    A      10
    2      0.2    A      11
    3      0.3    B      12
    4      0.4    B      13 
    5      0.5    C      14
    6      0.6    C      15
    7      0.7    D      16
    8      0.8    D      17

Now I want to transpose this DataFrame so that my columns becomes rows/index and my ftr_3 column becomes columns as shown below:

        A    B    C    D
ftr_1   1    3    5    7
ftr_1   2    4    6    8
ftr_2   0.1  0.3  0.5  0.7
ftr_2   0.2  0.4  0.6  0.8
ftr_4   10   12   14   16 
ftr_4   11   13   15   17

I want to transpose with respect to ftr_3 column but don't want to have duplicate columns at the same time don't want to lose the data also.

I tried the following approach but ended up having duplicate columns:-

    df.set_index(['ftr_3'],inplace=True,drop=True)
    df = df.T

This may be a simple pivot but i am stuck at this. Please help and thanks in advance.

pandas matrix python
2 Answers

You can try:

df.set_index(["ftr_3",df.groupby("ftr_3").cumcount()]).unstack().T.droplevel(1)

ftr_3     A     B     C     D
ftr_1   1.0   3.0   5.0   7.0
ftr_1   2.0   4.0   6.0   8.0
ftr_2   0.1   0.3   0.5   0.7
ftr_2   0.2   0.4   0.6   0.8
ftr_4  10.0  12.0  14.0  16.0
ftr_4  11.0  13.0  15.0  17.0

To remove the index name:

(df.set_index(["ftr_3",df.groupby("ftr_3").cumcount()]).unstack()
   .T.droplevel(1).rename_axis(None,axis=1))

          A     B     C     D
ftr_1   1.0   3.0   5.0   7.0
ftr_1   2.0   4.0   6.0   8.0
ftr_2   0.1   0.3   0.5   0.7
ftr_2   0.2   0.4   0.6   0.8
ftr_4  10.0  12.0  14.0  16.0
ftr_4  11.0  13.0  15.0  17.0
user_98406370

You can use df.pivot_table with aggfunc as list then use pd.Series.explode

df.pivot_table(columns='ftr_3', aggfunc=list).apply(pd.Series.explode)
# df.groupby('ftr_3').agg(list).T.apply(pd.Series.explode)

# ftr_3    A    B    C    D
# ftr_1    1    3    5    7
# ftr_1    2    4    6    8
# ftr_2  0.1  0.3  0.5  0.7
# ftr_2  0.2  0.4  0.6  0.8
# ftr_4   10   12   14   16
# ftr_4   11   13   15   17

Though this method might be inefficient(haven't tested it).

user_124164530
Related