I have data in four columns in excel, to be transposed to two rows. The first column to be the header row for the data in the second column and the third column to be the header for the data in the fourth column as below:
The data is as below
A 1 G 7
B 2 H 8
C 3 I 9
D 4 J 10
E 5 K 11
F 6 L 12
And need to transpose
A B C D E F G H I J K L
1 2 3 4 5 6 7 8 9 10 11 12
Appreciating your valuable inputs.
For general solution working with 4, 6, 10 columns select pairs and unpairs columns by indexing, flatten in numpy array and pass to DataFrame constructor:
c = df.iloc[:, ::2].T.to_numpy().ravel()
v = df.iloc[:, 1::2].T.to_numpy().ravel()
df = pd.DataFrame([v], columns=c)
print (df)
A B C D E F G H I J K L
0 1 2 3 4 5 6 7 8 9 10 11 12
Or if want use pandas solution create MultiIndex by modulo and integer division of np.arange by length of columns and then reshape by DataFrame.stack, then transpose and some data cleaning:
a = np.arange(len(df.columns))
df.columns = [a % 2, a // 2]
df = (df.stack()
.sort_index(level=1)
.set_index(0)[[1]]
.T
.rename_axis(None, axis=1)
.reset_index(drop=True))
print (df)
A B C D E F G H I J K L
0 1 2 3 4 5 6 7 8 9 10 11 12
Use pd.concat and transpose(.T):
In [2139]: d = pd.concat([df.iloc[:, :2].T.reset_index(drop=True), df.iloc[:, 2:].T.reset_index(drop=True)],1)
In [2144]: d.columns = d.loc[0]
In [2147]: d = d.drop(0)
In [2148]: d
Out[2148]:
0 A B C D E F G H I J K L
1 1 2 3 4 5 6 7 8 9 10 11 12
You can use np.reshape, np.vstack, and then set_index and Transpose:
>>> pd.DataFrame(np.vstack(np.split(df, 2, axis=1))).set_index(0).T
0 A B C D E F G H I J K L
1 1 2 3 4 5 6 7 8 9 10 11 12