CODEWITHSUNDEEP
pythonpandas
Sukhi Kaur
Sukhi Kaur

Reputation: 84

re-arranging rows into columns in a pandas dataframe

I am trying to find some elegant ways of rearranging a pandas dataframe.

My initial dataframe looks like this:

    PS  PSS 10PS 10PSS  5PS 5PSS
1   6   263 5    23     2   101
2   5   49  2    30     1   30

desired arrangement would be:

  1-PS  1-PSS 1-10PS 1-10PSS  1-5PS 1-5PSS 2-PS  2-PSS 2-10PS 2-10PSS 2-5PS 2-5PSS 
A 6     263   5      23       2      101   5     49     2     30      1     30

Where A is a new index and I would like the rows to be merged with the columns.

Upvotes: 2

Views: 144

Answers (2)

BENY
BENY

Reputation: 323236

You need stack here , with column join 

s=df.stack().to_frame('A')
s.index=s.index.map('{0[0]}-{0[1]}'.format) 
s.T
Out[42]: 
   1-PS  1-PSS  1-10PS  1-10PSS  1-5PS  1-5PSS  2-PS  2-PSS  2-10PS  2-10PSS  \
A     6    263       5       23      2     101     5     49       2       30   
   2-5PS  2-5PSS  
A      1      30

Upvotes: 1

Anton vBR
Anton vBR

Reputation: 18916

Hopefully these lines can help you out:

# Put a pandas Series from each line in a generator
series = (pd.Series(i, index=['{}-{}'.format(ind,x) for x in df.columns]) 
          for ind, i in zip(df.index,df.values))

# Concatenate and convert to frame + transpose
df = pd.concat(series).to_frame('A').T

Full example:

import pandas as pd

data = '''\
index    PS  PSS 10PS 10PSS  5PS 5PSS
1   6   263 5    23     2   101
2   5   49  2    30     1   30'''

df = pd.read_csv(pd.compat.StringIO(data), sep='\s+').set_index('index')

# Put a pandas Series from each line in a generator
series = (pd.Series(i, index=['{}-{}'.format(ind,x) for x in df.columns]) 
          for ind, i in zip(df.index,df.values))

# Concatenate and convert to frame + transpose
df = pd.concat(series).to_frame('A').T

Upvotes: 1

Related Questions