transpose pandas dataframe with dynamic columns

Question

I have a Input dataframe:

import pandas as pd

# Define the input data
data = {
    'ID':  [500, 500, 500, 500, 500, 400, 400, 400, 400, 300, 200],
    'item': ['A', 'B', 'C', 'D', 'E', 'A', 'B', 'C',  'E',   'D',    'E'],
    'Quantity': [1, 2, 3, 4, 5, 1, 2, 2, 1, 1, 5]
}
# Convert the input data to a Pandas DataFrame
df = pd.DataFrame(data)

I need to transform this input as you can see in below output example:

If you have any ideas please share. Thank you very much!

as @Psidom said, pivot should work. `df.pivot(index='ID', columns='item', values='Quantity')` — Abdelrahman, Apr 22 '23 at 16:09

score 1 · Answer 1 · answered Apr 22 '23 at 16:12

1

Try this:

pd.get_dummies(df, columns=['item'], prefix="", prefix_sep="").groupby(['ID'], as_index=False).sum().drop("Quantity", axis=1)

answered Apr 22 '23 at 16:12

Homayoon

87
7

1

I don't see how this answers the question… this is not at all what is described in the question (and if it was, a `crosstab` would be much better) – mozway Apr 22 '23 at 16:21
@mozway Well it outputs the desired state, isn't that what matters for an answer? :D. But I should confess that I was not familiar with `pivot` and actually learned something new while looking at what others said. – Homayoon Apr 22 '23 at 16:27
2

It does **not** give the desired output (check again). It fully ignores the Quantity and only counts the number of occurrences of each "item" (like a `crosstab`) – mozway Apr 22 '23 at 16:29
1

you are totally right, my bad @mozway. Thanks for your guidance. – Homayoon Apr 22 '23 at 16:35

transpose pandas dataframe with dynamic columns

1 Answers1