multiple rows into single row in pandas

Question

I wish to flatten(I am not sure whether is the correct thing to call it flatten) the columns with rows. multiple rows into single row with column change to column_rows I have a dataframe as below:

data = {"a":[3,4,5,6],
      "b":[88,77,66,55],
      "c":["ts", "new", "thing", "here"],
      "d":[9.1,9.2,9.0,8.4]}
df = pd.DataFrame(data)

my current output is:

    a   b   c   d
0   3   88  ts  9.1
1   4   77  new 9.2
2   5   66  thing   9.0
3   6   55  here    8.4

my expected otput:

    a_0  a_1 a_2 a_3 b_0 b_1 b_2 b_3 c_0 c_1 c_2 c_3 d_0 d_1 d_2 d_3
0   3 4 5 6 88 77 66 55 ts new thing here 9.1 9.2 9.0 8.4

from shape (4,4) to (1, 16)

Scott Boston · Accepted Answer · 2022-09-26 01:31:23Z

5

Update let's use the walrus operator new in Python 3.8 to create a one-liner:

(df_new := df.unstack().to_frame().T).set_axis(
    [f"{i}_{j}" for i, j in df_new.columns], axis=1
)

Output:

  a_0 a_1 a_2 a_3 b_0 b_1 b_2 b_3 c_0  c_1    c_2   c_3  d_0  d_1  d_2  d_3
0   3   4   5   6  88  77  66  55  ts  new  thing  here  9.1  9.2  9.0  8.4

Try this, using unstack, to_frame and transpose. Next, flatten the column headers using list comprehension:

df_new = df.unstack().to_frame().T 
df_new.columns = [f'{i}_{j}' for i, j in df_new.columns]
df_new

Output:

  a_0 a_1 a_2 a_3 b_0 b_1 b_2 b_3 c_0  c_1    c_2   c_3  d_0  d_1  d_2  d_3
0   3   4   5   6  88  77  66  55  ts  new  thing  here  9.1  9.2  9.0  8.4

edited Sep 26, 2022 at 1:31

answered Sep 12, 2022 at 1:08

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

Comments

sammywemmy · Accepted Answer · 2022-09-12 04:11:33Z

1

One option is with pivot_wider from pyjanitor:

# pip install pyjanitor
import pandas as pd
import janitor

(df
.assign(
    header = df.index, 
    index = 0)
.pivot_wider(
    index = 'index', 
    names_from = 'header',
    names_sep = '_')
.drop(columns='index')
)
   a_0  a_1  a_2  a_3  b_0  b_1  b_2  b_3 c_0  c_1    c_2   c_3  d_0  d_1  d_2  d_3
0    3    4    5    6   88   77   66   55  ts  new  thing  here  9.1  9.2  9.0  8.4

answered Sep 12, 2022 at 4:11

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

multiple rows into single row in pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related