CODEWITHSUNDEEP
pythonpandas
Kevin Bell
Kevin Bell

Reputation: 337

Python Pandas Indexing Reform

I have a CSV table with data similar to below:

Customer    Product    Quantity

C1          P1         2
C2          P1         3
C1          P2         4
C3          P2         1
C3          P1         0
C2          P2         5

I would like to convert it to the format below:

Customer    P1    P2

C1          2     4
C2          3     5
C3          1     0

I know I can import the file using pandas as below:

import pandas as pd
data = pd.read_csv('my_file.csv')

However, I don't know what to do afterwards.

I have tried to use pandas multi-indexing, but couldn't quite understand it. How do I re-build my new CSV data object? Any help would be appreciated, thanks.

P.S. May I complete my question considering multiple indices. For example lets say we have added a column to the left hand of the table with date of purchase. How could this be handled?

Upvotes: 1

Views: 220

Answers (1)

DSM
DSM

Reputation: 353179

This is an example of a pivot operation:

>>> df
  Customer Product  Quantity
0       C1      P1         2
1       C2      P1         3
2       C1      P2         4
3       C3      P2         1
4       C3      P1         0
5       C2      P2         5
>>> df.pivot(index="Customer", columns="Product", values="Quantity")
Product   P1  P2
Customer        
C1         2   4
C2         3   5
C3         0   1

The part of the documentation on reshaping is recommended reading.

(PS: I'm assuming your file actually has commas, even though you didn't show any-- otherwise pd.read_csv would need an extra argument, like delim_whitespace=True or something.)

Upvotes: 5

Related Questions