I have a dataframe which looks like this
| index | School | id | Subjects |
|---|---|---|---|
| 0 | Sch1 | 123 | English maths social |
| 2 | Sch67 | 789 | English maths science |
| 3 | Sch12 | 123 | English Telugu Hindi |
But I want to convert it into below dataframe with each subject count and groupby id
| id | English | Telugu | maths | Hindi | science | social |
|---|---|---|---|---|---|---|
| 123 | 2 | 1 | 1 | 1 | 0 | 1 |
| 789 | 1 | 0 | 1 | 0 | 1 | 0 |
I tried with group by for each column and it didnt work for me
I want the code to be dynamic because the input is a large dataframe and I have taken only a chunk of df
One approach is to split the subject strings into separate columns, then run pd.get_dummies on these columns, then group by id and sum:
dummies = pd.get_dummies(df1['Subjects'].str.split(expand=True))
# print(dummies):
# 0_English 1_Telugu 1_maths 2_Hindi 2_science 2_social
# 0 1 0 1 0 0 1
# 1 1 0 1 0 1 0
# 2 1 1 0 1 0 0
# Delete the prepended integers and underscores from column names
dummies.columns = [c.split('_')[1] for c in dummies.columns]
# Bring in the id of each row
dummies['id'] = df1['id']
dummies.groupby('id', as_index=False).sum()
Result:
| id | English | Telugu | maths | Hindi | science | social |
|---|---|---|---|---|---|---|
| 123 | 2 | 1 | 1 | 1 | 0 | 1 |
| 789 | 1 | 0 | 1 | 0 | 1 | 0 |