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
>Solution :
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