How to convert values of panda dataframe to columns

Question

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?

itprorh66 · Accepted Answer · 2020-12-23T14:25:27.640

1

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

edited Dec 23 '20 at 14:25

answered Dec 23 '20 at 00:27

itprorh66

3,110
4
9
21

It does not return the data that I want. It returns (8,6) pandas dataframe instead of (7,2). – user3104352 Dec 23 '20 at 01:03
Sorry about that, I provided an edit to my solution. – itprorh66 Dec 23 '20 at 14:25
1

Glad to hear it. – itprorh66 Dec 23 '20 at 21:17

How to convert values of panda dataframe to columns

1 Answers1