Switch columns and rows in dataframe with correct order-CodePudding

I have a DataFrame which I wanted to modify but I'm struggling with the results:

df = pd.DataFrame({"ID": ['2425128168','4055518718', '4055497871', '4055497871'],
                   "To": ['A', 'A', 'B', 'C'],
                   "From": ['D', 'C', 'A', 'A'],
                   "Qty":[10,20,30,40]})
df['Qty'] = df['Qty'].astype(int)

    ID          To From Qty
0   2425128168  A   D   10
1   4055518718  A   C   20
2   4055497871  B   A   30
3   4055497871  C   A   40

The idea is to switch column From into different columns from A to F and write a value from Qty column in correct place, for this example the result should be like that:

    ID          To From Qty A  B C  D  E F
0   2425128168  A   D   10  0  0 0  10 0 0 
1   4055518718  A   C   20  0  0 20 0  0 0
2   4055497871  B   A   30  30 0 0  0  0 0
3   4055497871  C   A   40  40 0 0  0  0 0

I was trying to do that but I get wrong values when the ID are not unique. This is my solution(wrong):

grouped = df.pivot(index=['ID','To'], columns='From', values='Qty')
grouped = grouped.fillna(0)

for i in ['A', 'B', 'C', 'D', 'E', 'F']:
    try:
        df[i] = grouped[i].reset_index(drop = True).astype(int)
    except:
        df[i] = 0

df = 

      ID    To  From    Qty A   B   C   D   E   F
0   2425128168  A   D   10  0   0   0   10  0   0
1   4055518718  A   C   20  30  0   0   0   0   0
2   4055497871  B   A   30  40  0   0   0   0   0
3   4055497871  C   A   40  0   0   20  0   0   0

CodePudding user response：

You can use get_dummies and multiply by Qty, then reindex and join:

cols = ['A', 'B', 'C', 'D', 'E', 'F']

out = df.join(pd.get_dummies(df['From'])
                .mul(df['Qty'], axis=0)
                .reindex(columns=cols,
                         fill_value=0)
             )

output:

           ID To From  Qty   A  B   C   D  E  F
0  2425128168  A    D   10   0  0   0  10  0  0
1  4055518718  A    C   20   0  0  20   0  0  0
2  4055497871  B    A   30  30  0   0   0  0  0
3  4055497871  C    A   40  40  0   0   0  0  0