Reshaping into binary variables using pandas python

Question

I am still new to Python pandas' pivot_table and im trying to reshape the data to have a binary indicator if a value is in a certain observation. I have follow some previous codes and got some encouraging results, however instead of 1 and zeros as Is my ideal result I get a sum. Please see a small sample data set below

    ID          SKILL     NUM
    1             A        1
    1             A        1
    1             B        1
    2             C        1
    3             C        1
    3             C        1
    3             E        1

The results I am aiming for is:

    ID    A         B        C    E
    1     1         1        0    0
    2     0         0        1    0
    3     0         0        0    1

My code atm get the following result:

    ID    A         B        C    E
    1     2         1        0    0
    2     0         0        2    0
    3     0         0        0    1

Should I remove the duplicates first??

The code I'm using atm is below;

  df_pivot =  df2.pivot_table(index='Job_posting_ID', columns='SKILL', aggfunc=len, fill_value=0)

Answer 1

You can use get_dummies with set_index for indicator columns and then get max values per index:

df = pd.get_dummies(df.set_index('ID')['SKILL']).max(level=0)

For better performance remove duplicates by drop_duplicates and reshape by set_index with unstack:

df = df.drop_duplicates(['ID','SKILL']).set_index(['ID','SKILL'])['NUM'].unstack(fill_value=0)

Solution with pivot, but then is necessary replace NaNs to 0:

df = df.drop_duplicates(['ID','SKILL']).pivot('ID','SKILL','NUM').fillna(0).astype(int)

If want use your solution, just remove duplicates, but better is unstack, beacuse data are never aggregated, because not duplicated pairs ID with SKILL:

df2 = df.drop_duplicates(['ID','SKILL'])
df_pivot = (df2.pivot_table(index='ID', 
                            columns='SKILL', 
                            values='NUM',
                            aggfunc=len, 
                            fill_value=0))
print (df_pivot)
SKILL  A  B  C  E
ID               
1      1  1  0  0
2      0  0  1  0
3      0  0  1  1