how to use column names as values in a pandas data frame?

Question

I have this pandas data frame:

df = DataFrame({'B' : ('A','B','C','D','E','F','G'), 'C' : (1,3,5,6,8,2,5), 'D' : (5,2,6,9,3,7,3)})




    B  C  D
 0  A  1  5
 1  B  3  2
 2  C  5  6
 3  D  6  9
 4  E  8  3
 5  F  2  7
 6  G  5  3

I need to make computations excel. The format that would be convenient for me is:

    B description  value
0   A           C      1
1   B           C      3
2   C           C      5
3   D           C      6
4   E           C      8
5   F           C      2
6   G           C      5
7   A           D      5
8   B           D      2
9   C           D      6
10  D           D      9
11  E           D      3
12  F           D      7
13  G           D      3

is there a way to use the column names as value

It's unclear how your desired output is generated can you explain this clearly — EdChum, Aug 11 '15 at 09:37
basically what I want is to append the values of each column (but B) to the data frame and add one more column that has as values the name of the column associated with each value. I'll edit the question to make it more clear — Blue Moon, Aug 11 '15 at 09:41

score 10 · Answer 1 · answered Aug 11 '15 at 09:43

You could use pd.melt:

In [13]: pd.melt(df, id_vars=['B'], var_name='description')
Out[13]: 
    B description  value
0   A           C      1
1   B           C      3
2   C           C      5
3   D           C      6
4   E           C      8
5   F           C      2
6   G           C      5
7   A           D      5
8   B           D      2
9   C           D      6
10  D           D      9
11  E           D      3
12  F           D      7
13  G           D      3

joris · Accepted Answer · 2015-08-11T09:48:15.887

You can use the convenient pd.melt for this, see the answer of @unutbu. A more general approach is to use stack, but this requires a bit more manual adaptations:

In [139]: df.set_index('B').stack().reset_index()
Out[139]:
    B level_1  0
0   A       C  1
1   A       D  5
2   B       C  3
3   B       D  2
4   C       C  5
5   C       D  6
6   D       C  6
7   D       D  9
8   E       C  8
9   E       D  3
10  F       C  2
11  F       D  7
12  G       C  5
13  G       D  3

After some renaming and sorting:

In [150]: result = df.set_index('B').stack().reset_index()

In [151]: result = result.rename(columns={'level_1':'description', 0:'value'})

In [152]: result.sort(['description', 'B']).reset_index(drop=True)
Out[152]:
    B description  value
0   A           C      1
1   B           C      3
2   C           C      5
3   D           C      6
4   E           C      8
5   F           C      2
6   G           C      5
7   A           D      5
8   B           D      2
9   C           D      6
10  D           D      9
11  E           D      3
12  F           D      7
13  G           D      3

how to use column names as values in a pandas data frame?

2 Answers2