python-3.xdataframeunique-values

How to convert values of panda dataframe to columns

I have a dataset given below:

weekid type amount
1       A    10   
1       B    20
1       C    30
1       D    40
1       F    50

2       A    70
2       E    80
2       B    100

I am trying to convert it to another panda frame based on total number of type values defined with:

import pandas as pd
import numpy as np

df=pd.read_csv(INPUT_FILE)
for type in df["type"].unique():
    //todo

My aim is to get a data given below:

weekid type_A type_B type_C type_D type_E type_F
    1    10    20      30     40     0     50
    2    70    100     0      0      80    0

Is there any specific function that convert unique values as a column and fills the missing values as 0 for each weekId groups? I am wondering that how this conversion can be done efficiently?

Solution

  • You can use the following:

    df = df.pivot(columns=['type'], values=['amount'])
df.fillna(0)
dfp.columns = dfp.columns.droplevel(0)

    Given your input this yields:

    type    A   B   C   D   F
weekid                  
1   10.0    20.0    30.0    40.0    50.0
2   70.0    80.0    100.0   0.0 0.0