How To transpose specific columns into rows in pandas associate other column value

Question

Hi I am trying to do transpose operation in pandas, but the condition is the value of one column should be associated with the transposed rows. The example given below will explain the better way: the data is looks like:

A   1   2   3   4  51  52 53 54 
B   11  22  23  24 71  72 73 74 

The result I am trying to do like this:

A   1   51
A   2   52
A   3   53
A   4   54
B   11  71
B   22  72
B   23  73
B   24  74

In first row, the data is in single row, I want to transpose data from 1 to 4 with the value 'A' in other column. Can anyone suggest how can I do this??

Dorty

Answer 1

It seems you need melt or stack:

print (df)
   0   1   2   3   4
0  A   1   2   3   4
1  B  11  22  23  24

df1 = pd.melt(df, id_vars=0).drop('variable', axis=1).sort_values(0)
df1.columns = list('ab')
print (df1)
   a   b
0  A   1
2  A   2
4  A   3
6  A   4
1  B  11
3  B  22
5  B  23
7  B  24

df2 = df.set_index(0).stack().reset_index(level=1, drop=True).reset_index(name='a')
df2.columns = list('ab')
print (df2)
   a   b
0  A   1
1  A   2
2  A   3
3  A   4
4  B  11
5  B  22
6  B  23
7  B  24

EDIT by comment:

#set index with first column
df = df.set_index(0)

#create MultiIndex
cols = np.arange(len(df.columns))
df.columns = [ cols // 4, cols % 4]
print (df)
    0               1            
    0   1   2   3   0   1   2   3
0                                
A   1   2   3   4  51  52  53  54
B  11  22  23  24  71  72  73  74

#stack, reset index names, remove level and reset index
df1 = df.stack().rename_axis((None, None)).reset_index(level=1, drop=True).reset_index()
#set new columns names
df1.columns = ['a','b','c']
print (df1)
   a   b   c
0  A   1  51
1  A   2  52
2  A   3  53
3  A   4  54
4  B  11  71
5  B  22  72
6  B  23  73
7  B  24  74

Seabrooke