jake wong jake wong - 3 months ago 43
Python Question

pandas merge columns in same dataframe

I have dataframe with 4 columns.

Column1 Column2 Column3 Column4
0 Item1 Value1 Item2 Value2
1 Item3 Value3 Item4 Value4
2 Item5 Value5 Item6 Value6
3 Item7 Value7 Item8 Value8
4 Item9 Value9 Item10 Value10
5 Item11 Value11 Item12 Value12
6 Item13 Value13 Item14 Value14


Is there a way for me to combine
Column1
and
Column3
together? and with
Column2
and
Column4
? To get the below

Column1 Column2
0 Item1 Value1
1 Item2 Value2
2 Item3 Value3
3 Item4 Value4
4 Item5 Value5
5 Item6 Value6
6 Item7 Value7
7 ...


I've tried playing with
append
,
concat
and
split
but cant seem to work it out..

Answer

Okay, maybe this might help:

In [571]: df
Out[571]: 
  Column1  Column2 Column3  Column4
0   Item1   Value1   Item2   Value2
1   Item3   Value3   Item4   Value4
2   Item5   Value5   Item6   Value6
3   Item7   Value7   Item8   Value8
4   Item9   Value9  Item10  Value10
5  Item11  Value11  Item12  Value12
6  Item13  Value13  Item14  Value14

In [572]: pd.DataFrame({'Column1': pd.concat([df.Column1, df.Column3]), 'Column2': pd.concat([df.Column2, df.Column4])}).sort_index()
Out[572]: 
  Column1  Column2
0   Item1   Value1
0   Item2   Value2
1   Item3   Value3
1   Item4   Value4
2   Item5   Value5
2   Item6   Value6
3   Item7   Value7
3   Item8   Value8
4   Item9   Value9
4  Item10  Value10
5  Item11  Value11
5  Item12  Value12
6  Item13  Value13
6  Item14  Value14

You can also reset the index:

In [574]: pd.DataFrame({'Column1': pd.concat([df.Column1, df.Column3]), 'Column2': pd.concat([df.Column2, df.Column4])}).sort_index().reset_index(drop=True)
Out[574]: 
   Column1  Column2
0    Item1   Value1
1    Item2   Value2
2    Item3   Value3
3    Item4   Value4
4    Item5   Value5
5    Item6   Value6
6    Item7   Value7
7    Item8   Value8
8    Item9   Value9
9   Item10  Value10
10  Item11  Value11
11  Item12  Value12
12  Item13  Value13
13  Item14  Value14