I have a DataFrame with 100 columns (however I provide only three columns here) and I want to build a new DataFrame with two columns. Here is the DataFrame:

import pandas as pd
df = pd.DataFrame()
df ['id'] = [1,2,3]
df ['c1'] = [1,5,1]
df ['c2'] = [-1,6,5]
df

I want to stick the values of all columns for each id and put them in one columns. For example, for id=1 I want to stick 2, 3 in one column. Here is the DataFrame that I want.

Note: df.melt does not solve my question. Since I want to have the ids also.

Note2: I already use the stack and reset_index, and it can not help.

df = df.stack().reset_index()
df.columns = ['id','c']
df  

CodePudding user response：

You could first set_index with "id"; then stack reset_index:

out = (df.set_index('id').stack()
       .droplevel(1).reset_index(name='c'))

Output:

   id  c
0   1  1
1   1 -1
2   2  5
3   2  6
4   3  1
5   3  5