How to create multiple columns in Pandas Dataframe?

Question

I have data as you can see in the terminal. I need it to be converted to the Excel sheet format as you can see in the Excel sheet file by creating multi-levels in columns.

I researched this and reached many different things but cannot achieve my goal then, I reached "transpose", and it gave me the shape that I need but unfortunately that it did reshape from a column to a row instead where I got the wrong data ordering.

Current result:

enter image description here

Desired result:

enter image description here

What can I try next?

Your input and desired output don't match. for example cel1 for palyer==2, all values are 0 but your desired output you type 1, 3,1 — Mahdi F.
– Mahdi F., Commented Oct 22, 2022 at 13:06
OK, explain why you have 0, 11, 12? in palyer=10& cel=1? — Mahdi F.
– Mahdi F., Commented Oct 22, 2022 at 13:09
Ok again, maybe I didn't mention that excuse me, 0, 11, and 12 are values for the cel1 column player number 10 is sharing with this column (cel1) by these values. what I didn't mention is that I gave the values as examples that don't exist in the excel sheet but the player number are the same in the excell sheet and terminal — Abdelhamed Abdin
– Abdelhamed Abdin, Commented Oct 22, 2022 at 13:14
DO NOT post images of code, links to code, data, error messages, etc. - copy or type the text into the question — itprorh66
– itprorh66, Commented Oct 22, 2022 at 14:10

Azhar Khan · Accepted Answer · 2022-10-22 16:24:17Z

1

You can use pivot() function and reorder multi-column levels.

Before that, index/group data for repeated iterations/rounds:

data=[
    (2,0,0,1),
    (10,2,5,3),
    (2,0,0,0),
    (10,1,1,1),
    (2,0,0,0),
    (10,1,2,1),
]

columns = ["player_number", "cel1", "cel2", "cel3"]

df = pd.DataFrame(data=data, columns=columns)

df_nbr_plr = df[["player_number"]].groupby("player_number").agg(cnt=("player_number","count"))
df["round"] = list(itertools.chain.from_iterable(itertools.repeat(x, df_nbr_plr.shape[0]) for x in range(df_nbr_plr.iloc[0,0])))

[Out]:
   player_number  cel1  cel2  cel3  round
0              2     0     0     1      0
1             10     2     5     3      0
2              2     0     0     0      1
3             10     1     1     1      1
4              2     0     0     0      2
5             10     1     2     1      2

Now, pivot and reorder the colums levels:

df = df.pivot(index="round", columns="player_number").reorder_levels([1,0], axis=1).sort_index(axis=1)

[Out]:
player_number   2              10          
              cel1 cel2 cel3 cel1 cel2 cel3
round                                      
0                0    0    1    2    5    3
1                0    0    0    1    1    1
2                0    0    0    1    2    1

answered Oct 22, 2022 at 16:24

Azhar Khan

4,14811 gold badges31 silver badges34 bronze badges

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

3 Comments

Abdelhamed Abdin Over a year ago

Yes, actually when I used the above answer I got missing some of the data when I create a new player number but this answer was correct whenever I do change my data no side effect does

Abdelhamed Abdin Over a year ago

could you please let me know what itertools are used here?

Azhar Khan Over a year ago

itertools.repeat() repeats 0,1,2 as [0,0] [1,1][2,2] for player rounds/iterations (you've repeated 2,10,2,10,...). chain.from_iterable() joins them in a sequence to make column "round".

Tranbi · Accepted Answer · 2022-10-22 14:09:52Z

1

This can be done with unstack after setting player__number as index. You have to reorder the Multiindex columns and fill missing values/delete duplicates though:

import pandas as pd

data = {"player__number": [2, 10 , 2, 10, 2, 10],
        "cel1": [0, 2, 0, 1, 0, 1],
        "cel2": [0, 5, 0, 1, 0, 2],
        "cel3": [1, 3, 0, 1, 0, 1],
}

df = pd.DataFrame(data).set_index('player__number', append=True)
df = df.unstack('player__number').reorder_levels([1, 0], axis=1).sort_index(axis=1) # unstacking, reordering and sorting columns
df = df.ffill().iloc[1::2].reset_index(drop=True) # filling values and keeping only every two rows
df.to_excel('output.xlsx')

Output:

edited Oct 22, 2022 at 14:09

answered Oct 22, 2022 at 14:04

Tranbi

12.8k6 gold badges19 silver badges39 bronze badges

1 Comment

halfer Over a year ago

If you can, please use text output for questions. Images do not work well with screen-readers, clipboards, or search engine robots. The above image could be rendered as a preformatted table or, even better, a Markdown table. See Azhar's answer for the former.

Collectives™ on Stack Overflow

How to create multiple columns in Pandas Dataframe?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related