I have an excel file as follows:

ID      wk48     wk49    wk50    wk51   wk52   wk1   wk2
1123     10        22      233     2     4     22     11
1198      9         4       44    23    34     5     234
101       3         6        3    43    33     34     78

I want the output as follows in python

1123 wk48  10
1123 wk49  22
1123 wk50  233
1123 wk51  2
1123 wk52  4
1123 wk1   22
1123 wk2   11
1198 wk48  9
1198 wk49  4
1198 wk50  44
1198 wk51  23
1198 wk52  34
1198 wk1   5
1198 wk2   234

Any suggestions

The first thing I would do is set ID as your index with:

df.set_index('ID',inplace=True)

Next, you can use the following command to reorient your dataframe:

df = df.stack().reset_index()
print df
-------------------------------------------
Output:
      ID level_1    0
0   1123    wk48   10
1   1123    wk49   22
2   1123    wk50  233
3   1123    wk51    2
4   1123    wk52    4
5   1123     wk1   22
6   1123     wk2   11
7   1198    wk48    9
8   1198    wk49    4
9   1198    wk50   44
10  1198    wk51   23
11  1198    wk52   34
12  1198     wk1    5
13  1198     wk2  234
14   101    wk48    3
15   101    wk49    6
16   101    wk50    3
17   101    wk51   43
18   101    wk52   33
19   101     wk1   34
20   101     wk2   78

Related Articles