pandas dataframe convert columns as rows

Question

I am looking for a way to convert my dataframe columns as rows. Following is my example dataframe:

mylist= [['xx'], [None, 'yy'], ['xx', None], ['xx',None],['xx','yy']]

pd.DataFrame(mylist,index=['A','B','C','D','E'],columns=['Col1','Col2'])

Input DataFrame:

-------------------
Ind | Col1 | Col2 |
-------------------
A   | xx   | None |
B   | None | yy   |
C   | xx   | None |
D   | xx   | None |
E   | xx   | yy   |
-------------------

I want to split my columns as separate rows in the dataframe. Below is how my desired output looks like. Can anyone suggest how to acheive the following.

Desired dataframe:

------------------------
Ind | Values | Columns |
------------------------
A   | xx     | Col1    |
B   | yy     | Col2    |
C   | xx     | Col1    |
D   | xx     | Col1    |
E   | xx     | Col1    |
E   | xx     | Col2    |
------------------------

Thanks,
Rtut

akuiper · Accepted Answer · 2016-06-24 18:11:01Z

3

Another option is to use melt:

longDf = pd.melt(df.reset_index(), id_vars=['index'], var_name=['Columns'])
longDf[pd.notnull(longDf.value)]

  index Columns value
0   A   Col1    xx
2   C   Col1    xx
3   D   Col1    xx
4   E   Col1    xx
6   B   Col2    yy
9   E   Col2    yy

answered Jun 24, 2016 at 18:11

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

akuiper Over a year ago

@bernie Thanks. stack() is also a great way to do it.

mechanical_meat · Accepted Answer · 2016-06-24 18:03:33Z

2

df = pd.DataFrame(mylist,index=['A','B','C','D','E'],columns=['Col1','Col2'])
# rotate df
stacked_df = pd.DataFrame(df.stack().reset_index())
# name columns
stacked_df.columns = ['Ind','Columns','Values']
# reorder columns
reordered_df = pd.DataFrame(stacked_df,columns=['Ind','Values','Columns'])

Results in:

>>> reordered_df
  Ind Values Columns
0   A     xx    Col1
1   B     yy    Col2
2   C     xx    Col1
3   D     xx    Col1
4   E     xx    Col1
5   E     yy    Col2

answered Jun 24, 2016 at 18:03

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Comments

Alexander · Accepted Answer · 2016-06-24 18:16:35Z

1

In the case of two values, it appears that you only want the first (e.g. the last row of your example).

You can use loc to first set the second value to None in the case both columns have values.

df.loc[(df.Col1.notnull()) & (df.Col2.notnull()), 'Col2'] = None

You can then melt your results.

>>> pd.melt(df.reset_index(), id_vars='index', var_name='Columns', value_name='Values'
            ).dropna().set_index('index')[['Values', 'Columns']]

      Values Columns
index               
A         xx    Col1
C         xx    Col1
D         xx    Col1
E         xx    Col1
B         yy    Col2

answered Jun 24, 2016 at 18:16

Alexander

111k32 gold badges212 silver badges208 bronze badges

Collectives™ on Stack Overflow

pandas dataframe convert columns as rows

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related