CODEWITHSUNDEEP
pythonpandaspivot
tblznbits
tblznbits

Reputation: 6778

Cast pandas data frame from long to wide without pd.pivot

I have a pandas dataframe that looks like the following:

df = pd.DataFrame([['joe', 21, 'M'], 
                   ['jane', 22, 'F'], 
                   ['Alice', 34, 'F']], 
                  columns=['name', 'age', 'sex'])

Which looks like this:

    name  age sex
0    joe   21   M
1   jane   22   F
2  Alice   34   F

This dataframe is obviously a 3x3 matrix, and what I'd like to end up with a 1x9 matrix that looks like the following:

  name_1  age_1 sex_1 name_2  age_2 sex_2 name_3  age_3 sex_3
0    joe     21     M   jane     22     F  Alice     34     F

I can't use 'pivot' because I don't have one column to use as columns and another to use as values. I simply want to move all of my rows so that they are side-by-side and I can't seem to wrap my head around how to do this in a pythonic way. Do I need to just loop through the rows, append the row to a list, turn the list into a dataframe, and then rename the columns?

Upvotes: 2

Views: 322

Answers (2)

piRSquared
piRSquared

Reputation: 294488

Option 1
Somewhat simple version

d = df.unstack()
d.index = d.index.map('{0[0]}_{0[1]}'.format)
d.to_frame().T

  name_0 name_1 name_2 age_0 age_1 age_2 sex_0 sex_1 sex_2
0    joe   jane  Alice    21    22    34     M     F     F

Option 2
Complicate things but probably faster

from numpy.core.defchararray import add

cols = np.tile(df.columns.values, df.shape[0]).astype(str)
rows = np.arange(1, df.shape[0] + 1).repeat(df.shape[1]).astype(str)
vals = df.values.reshape(1, -1)
pd.DataFrame(vals, columns=add(cols, add('_', rows)))

  name_1 age_1 sex_1 name_2 age_2 sex_2 name_3 age_3 sex_3
0    joe    21     M   jane    22     F  Alice    34     F

Upvotes: 5

BENY
BENY

Reputation: 323316

Try this one , I break down the steps . 

df=df.reset_index()
df=pd.melt(df,'index')
df['index']=df['index']+1

df.variable=df.variable+'_'+df['index'].astype(str)
df.sort_values('index').drop('index',1).set_index('variable',drop=True).T


Out[2375]: 
variable name_1 age_1 sex_1 name_2 age_2 sex_2 name_3 age_3 sex_3
value       joe    21     M   jane    22     F  Alice    34     F

Upvotes: 3

Related Questions