[FIXED] Merge columns with same index into 1 single column (pandas)

Issue

I have a dataframe I would like to transform. I want to merge the columns together and have the values that have the same id appear as separate rows.

So From This:

id  children1       children2     children3
1   No Children    NaN           NaN
2   12-16 years    17+ years     Nan
3   No Children    Nan           Nan
4   5-7 years      8-11 years    12-16 years

To This:

id  children       
1   No Children    
2   12-16 years
2   17+ years     
3   No Children   
4   5-7 years
4   8-11 years
4   12-16 years

Is there an easy way to do this?

Data:

{'id': [1, 2, 3, 4],
 'children1': ['No Children', '12-16 years', 'No Children', '5-7 years'],
 'children2': [nan, '17+ years', nan, '8-11 years'],
 'children3': [nan, nan, nan, '12-16 years']}

Solution

I think you're looking for stack method, which converts columns to index. Now to get the exact shape, you'll need to set_index with "id" first (so that "id" is kept as index when stacking).

out = df.set_index('id').stack().droplevel(1).rename('children').reset_index()

Output:

   id     children
0   1  No Children
1   2  12-16 years
2   2    17+ years
3   3  No Children
4   4    5-7 years
5   4   8-11 years
6   4  12-16 years

Answered By - enke