Suppose I have this data frame
id x y
0 a hello
0 b test
1 c hi
2 a hi
3 d bar
I want to concat x and y into a single column like this keeping their ids
id x
0 a
0 b
1 c
2 a
3 d
0 hello
0 test
1 hi
2 hi
3 bar
And how if I want to give a new name for the concat column? (e.g. 'x' to 'xy')
I don't think pandas.concat includes the option to set new column names (see docs), but you could assign like so:
Starting from:
id x y
0 0 a hello
1 0 b test
2 1 c hi
3 2 a hi
4 3 d bar
df.set_index('id', inplace=True)
pd.DataFrame(pd.concat([df.x, df.y]), columns=['xy']).reset_index()
id xy
0 0 a
1 0 b
2 1 c
3 2 a
4 3 d
5 0 hello
6 0 test
7 1 hi
8 2 hi
9 3 bar
id as index before concat.If ordering of rows is not important, you can use stack:
print df
id x y
0 0 a hello
1 0 b test
2 1 c hi
3 2 a hi
4 3 d bar
s = df.set_index('id').stack()
s.index = s.index.droplevel(-1)
s.name = 'xy'
print pd.DataFrame(s).reset_index()
id xy
0 0 a
1 0 hello
2 0 b
3 0 test
4 1 c
5 1 hi
6 2 a
7 2 hi
8 3 d
9 3 bar
