How to append multiple columns into two?

Question

The data I am working with is in a large set of columns, with related values - for example:

| YearQ  | Area A | Area B | Area C |
+--------+--------+--------+--------+
| 2017Q1 | 1234.0 | 9252.0 | 3421.0 |
| 2017Q2 | 1245.0 | 9368.0 | 3321.0 |
| 2017Q3 | 1350.0 | 9440.0 | 3225.0 |
| 2017Q4 | 1333.0 | 9501.0 | 3625.0 |

In order to join this data with another data set, I need to append these values into one column, preserving the Area column data, as well as the YearQ data:

| YearQ  |  Area  |  Value  |
+--------+--------+---------+
| 2017Q1 | Area A | 1234.0  |
| 2017Q1 | Area B | 9252.0  |
| 2017Q1 | Area C | 3421.0  |
| 2017Q2 | Area A | 1245.0  |
| 2017Q2 | Area B | 9368.0  |
| 2017Q2 | Area C | 3321.0  |

I've tried using df.append and pivot_table, but am so far unable to get the required result .. which pandas function should I be using here?

Answer 1

Use melt with sort_values:

df = df.melt('YearQ', var_name='Area', value_name='Value').sort_values(['YearQ','Area'])

A bit slowier alternative with set_index, stack and reset_index:

df = df.set_index('YearQ').stack().rename_axis(('YearQ','Area')).reset_index(name='Value')

print (df)
     YearQ    Area   Value
0   2017Q1  Area A  1234.0
4   2017Q1  Area B  9252.0
8   2017Q1  Area C  3421.0
1   2017Q2  Area A  1245.0
5   2017Q2  Area B  9368.0
9   2017Q2  Area C  3321.0
2   2017Q3  Area A  1350.0
6   2017Q3  Area B  9440.0
10  2017Q3  Area C  3225.0
3   2017Q4  Area A  1333.0
7   2017Q4  Area B  9501.0
11  2017Q4  Area C  3625.0