Pivot column and column values in pandas dataframe

Question

I have a dataframe that looks like this, but with 26 rows and 110 columns:

index/io   1   2   3   4
  0        42  53  23  4
  1        53  24  6   12
  2        63  12  65  34
  3        13  64  23  43

Desired output:

index  io  value
0      1   42
0      2   53
0      3   23
0      4   4
1      1   53
1      2   24
1      3   6
1      4   12
2      1   63
2      2   12
... 

I have tried with dict and lists by transforming the dataframe to dict, and then create a new list with index values and update in new dict with io.

indx = []

for key, value in mydict.iteritems():
    for k, v in value.iteritems():
        indx.append(key)
indxio = {}
for element in indx:
    for key, value in mydict.iteritems():
        for k, v in value.iteritems():
            indxio.update({element:k})

I know this is too far probably, but it's the only thing I could think of. The process was too long, so I stopped.

Answer 1

You can use set_index, stack, and reset_index().

df.set_index("index/io").stack().reset_index(name="value")\
  .rename(columns={'index/io':'index','level_1':'io'})

Output:

    index io  value
0       0  1     42
1       0  2     53
2       0  3     23
3       0  4      4
4       1  1     53
5       1  2     24
6       1  3      6
7       1  4     12
8       2  1     63
9       2  2     12
10      2  3     65
11      2  4     34
12      3  1     13
13      3  2     64
14      3  3     23
15      3  4     43