Reshape dataframe using pandas/numpy in a particular manner - convert multiple columns into two

Question

Given the following data:

df = pd.DataFrame(
    dict(
        x1=["zero", "one", "two"],
        x2=["three", "four", "five"],
        x3=["six", "seven", "eight"],
        x4=["nine", "ten", "eleven"],
    )
)

which looks as:

In [2]: df
Out[2]:
     x1     x2     x3      x4
0  zero  three    six    nine
1   one   four  seven     ten
2   two   five  eight  eleven

I would like to reshape it to the following

x1      x2
zero    three
one     four
two     five
three   six
four    seven
five    eight
six     nine
seven   ten
eight   eleven

The following works, but I do not thing the approach is sound:

c1 = df.columns[: df.shape[1] - 1]
c2 = df.columns[1:]
d1 = df.loc[:, c1].T.values.flatten()
d2 = df.loc[:, c2].T.values.flatten()
pd.DataFrame(dict(x1=d1, x2=d2))

Reshape like that is easy with a numpy array, for example the df.values one. — hpaulj
– hpaulj, Commented Mar 7, 2020 at 2:11
@hpaulj feel free to add an example I'd be interested to see it — baxx
– baxx, Commented Apr 28, 2020 at 22:25
Since you are taking overlapping ranges of columns, I can't do any better than you, d1 = df.values[:,:-1].ravel(order='F') and d2=df.values[:,1:].ravel(order='F'). — hpaulj
– hpaulj, Commented Apr 28, 2020 at 23:54

Chris Adams · Accepted Answer · 2020-03-07 02:20:54Z

1

Try np.vstack with iloc slicing in a list comprehension:

df_new = (pd.DataFrame(np.vstack([df.iloc[:,i:i+2].to_numpy()
                                   for i in range(df.shape[1]-1)]),
                      columns=['x1', 'x2']))

[out]

      x1      x2
0   zero   three
1    one    four
2    two    five
3  three     six
4   four   seven
5   five   eight
6    six    nine
7  seven     ten
8  eight  eleven

edited Mar 7, 2020 at 2:20

answered Mar 7, 2020 at 2:16

Chris Adams

18.7k4 gold badges26 silver badges44 bronze badges

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

1 Comment

baxx Over a year ago

that's great - this is how I initially considered things (with i+2), but with for loops instead of using numpy/pandas methods (so it was poor). I've not used vstack before, but it looks handy. Thanks

Collectives™ on Stack Overflow

Reshape dataframe using pandas/numpy in a particular manner - convert multiple columns into two

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related