How to create columns from matching data?

Question

Data that I have:

    df = pd.DataFrame(pd.DataFrame([('Bus Route A', 'Beach Station'),
 ('Bus Route B', 'Village Hotel '),
 ('Bus Route A', 'Amara Sanctuary Resort'),
 ('Bus Route C', 'Village Hotel '),
 ('Bus Route B', 'Beach Station'),
 ('Bus Route C', 'Beach Station')]))

What I is need is:

Data (binary) that looks like: [('Beach Station',1,1,1),('Village Hotel ',0,1,1), ( 'Amara Sanctuary Resort',1,0,0)]
Since Beach Station is present in all three routes, its values are 1, likewise for others as well.
So that I can create three columns based on this created data.

jezrael · Accepted Answer · 2021-09-17 06:32:24Z

3

Use crosstab with comapre for greater or equal 1 and cast mask to integers:

df = pd.crosstab(df[1], df[0]).ge(1).astype(int)
print (df)
0                       Bus Route A  Bus Route B  Bus Route C
1                                                            
Amara Sanctuary Resort            1            0            0
Beach Station                     1            1            1
Village Hotel                     0            1            1


print (df.reset_index().apply(tuple, axis=1).tolist())
[('Amara Sanctuary Resort', 1, 0, 0), 
 ('Beach Station', 1, 1, 1), 
 ('Village Hotel ', 0, 1, 1)]

answered Sep 17, 2021 at 6:32

jezrael

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

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

Collectives™ on Stack Overflow

How to create columns from matching data?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related