Skip to content
Advertisement

multiple rows into single row in pandas

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)

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement