How to group similar rows?

user_14535260 0
Viewed 61

Hey guys I have a question for grouping rows based on a name.

Below is my dataset:

Name      University            Subject              

John      Harvard               English, French
John      MIT                   Economics
Alan      BU                    Data Science

I would like to have the following output:

Name   Harvard   MIT   BU    English   French   Economics   Data Science
  
John      1       1     0       1         1        1            0
Alan      0       0     1       0         0        0            1

Please let me know how to do this in Python. I am quite new to this, I understand that we can use one hot encoding to create binary variables but I am not sure how to merge the rows based on the same name and separate the subjects.

  1. How do you merge the rows based on the same name?
  2. How do you create separate columns as for subjects separated by ","
  3. How do you create binary variables for all of this after?
python
1 Answers

For a beginner your question is quite involved. With pandas you can do the following.

This step is not necessary when you load your data from a file.
Setup the dataframe with text input.

import pandas as pd
import io

t =  '''
Name      University            Subject              

John      Harvard               English, French
John      MIT                   Economics
Alan      BU                    Data Science'''

df = pd.read_csv(io.StringIO(t), sep='\s\s+', engine='python')
df

Out:

   Name University          Subject
0  John    Harvard  English, French
1  John        MIT        Economics
2  Alan         BU     Data Science

In column Subject split multiple values in one row into a list

df['Subject'] = df.Subject.str.split(', ')

Explode the lists and melt to long data format. Remember the order of the columns

ser = df.explode('Subject').melt('Name').drop('variable', 1)
order = ser.value.unique()

Get binary variables with get_dummies and groupby('Name')

(pd.get_dummies(
    ser, columns=['value'], prefix='', prefix_sep='')
    .groupby('Name', sort=False).max()
)[order]

Out:

      Harvard  MIT  BU  English  French  Economics  Data Science
Name                                                            
John        1    1   0        1       1          1             0
Alan        0    0   1        0       0          0             1
user_142777220
Related