brittenb brittenb - 1 month ago 10
Python Question

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?

Answer Source

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