Create a binary matrix from 2 dataframe columns in Python

Question

I need to create a binary matrix

Example Data in a pandas DataFrame

Using

A = pd.DataFrame(index=df.ID.values, columns=df.P.values, 
                       data=(df.P.values == df.P.values[:,None]).astype(int))

My current output

Which is correct in terms of where the '1's' hit, but I just want to have the column / row numbers consolidated and the row numbers in order as in:

index 1 2 3 4 5 6 7
1     0 1 1 1 0 1 0
2     1 0 0 0 1 0 1
3     0 1 0 1 1 1 0

If that's not clear, feel free to question!

jezrael · Accepted Answer · 2018-05-25 09:30:09Z

1

Use get_dumies with max:

df = pd.get_dummies(df.set_index('ID')['P'].astype(str)).max(level=0).sort_index()
print (df)
    1  2  3  4  5
ID               
1   0  1  1  1  0
2   1  0  0  0  1
3   0  1  0  0  1

answered May 25, 2018 at 9:30

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

C_psy Over a year ago

Worked a treat! Thanks @jezrael

Narendra Prasath · Accepted Answer · 2018-05-25 09:44:30Z

0

You could try pivot_table function

df["value"]=1
pd.pivot_table(df, values="value", index=["ID"], columns="P", fill_value=0)

answered May 25, 2018 at 9:44

Narendra Prasath

1,5311 gold badge10 silver badges20 bronze badges

Collectives™ on Stack Overflow

Create a binary matrix from 2 dataframe columns in Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related