Pandas df manipulation - breaking data into 2 columns

user_15637200 0
Viewed 41

I have an example data frame with 2 indicating columns (example below, product and version - where version numbers can only be 1 or 2) and a 3rd column with actual data.

product       version       data    
  a              1          8000
  a              2          1000
  b              1          4000
  b              2          2000
  c              1          9000
  c              2          1000
  d              1          2500
  d              2          3000

I am trying to manipulate my df so that instead of the above, I have 2 data columns (say data1 and data2) which correspond to the version of that product (thereby making the version column redundant). See below:

product       data1       data2    
  a           8000        1000
  b           4000        2000
  c           9000        1000
  d           2500        3000

Is anyone able to advise on a simple solution to achieve this?

pandas dataframe python
2 Answers

You can pivot the table using pandas function .pivot

>>> result = df.pivot(index='product', columns='version', values='data').reset_index()
>>> result
version product     1     2
0             a  8000  1000
1             b  4000  2000
2             c  9000  1000
3             d  2500  3000

>>> result.index.name = None
>>> result.columns = ['product', 'data1', 'data2']
>>> result
  product  data1  data2
0       a   8000   1000
1       b   4000   2000
2       c   9000   1000
3       d   2500   3000
user_89174260

Basically, you need to use pandas method pivot and then rename columns.

import pandas as pd

data = df.pivot(index='product', values='data', columns='version').reset_index(drop=True)
data.columns = ['data1', 'data2']
data.index.name = 'product'
user_67972500
Related