unable to get the transposed data in a particular format in pandas

user_8814173 0
Viewed 67

I have a dataframe as below:

import pandas as pd

df_vals = {'A':['X','Y','Z','X','Y','Z'],
           'B':[23,12,68,2,6,15],
           'C':[34,11,256,321,138,56],
           'D':['02-Feb-14',    '04-Mar-14',    '23-Jan-13',    '18-Feb-14',    '02-Jun-14',    '04-Aug-13']
           }

# Creating the DataFrame

df = pd.DataFrame(data=df_vals)

print(df) # Prints the created dataframe

I used the below code:

# Way : 1
df1_transposed = df.T
print(df1_transposed)

This is not returning the results as expected.

Neither the below code :

# Way : 2
df2_transposed = df.transpose()
print(df2_transposed)

What I want to transpose the data to look something like in the below snapshot:

enter image description here

pandas python
3 Answers

You can pivot (set_index+unstack) using a helper column (df.groupby+cumcount) and then transpose finally drop the unwanted index.

out = (df.set_index(["A",df.groupby("A").cumcount()]).unstack()
        .T.sort_index(level=-1)
        .droplevel(-1).rename_axis(None,axis=1))

print(out)

           X          Y          Z
B         23         12         68
C         34         11        256
D  02-Feb-14  04-Mar-14  23-Jan-13
B          2          6         15
C        321        138         56
D  18-Feb-14  02-Jun-14  04-Aug-13
user_98406370

with this solution you have to know the final number of columns, in your example 3: first of, you need to set the index of the dataframe to 'A' and then traspose:

df1_transposed = df.set_index('A').T

now, you have to concat consecutive slices of your dataframe:

pd.concat([df1_transposed.iloc[:,i*3:i*3+3] for i in 
           range(0,int(np.ceil(len(df1_transposed.columns)/3)))], axis=0)

in this case you want to group columns 3 by 3, if your columns was W, X, Y, Z you have to replace with

pd.concat([df1_transposed.iloc[:,i*4:i*4+4] for i in 
           range(0,int(np.ceil(len(df1_transposed.columns)/4)))], axis=0)
user_151972130

Try the following:

pd.concat([df.set_index('A')[i:i+3][[k for k in df.columns if k!='A']].T for i in range(0, df.shape[0], 3)])

A          X          Y          Z
B         23         12         68
C         34         11        256
D  02-Feb-14  04-Mar-14  23-Jan-13
B          2          6         15
C        321        138         56
D  18-Feb-14  02-Jun-14  04-Aug-13
user_82285580
Related