for example I have a pandas DataFrame of the test results in some class. It could look like this table:
| Name | English | French | History | Math | Physic | Chemistry | Biology |
|---|---|---|---|---|---|---|---|
| Mike | 3 | 3 | 4 | 5 | 6 | 5 | 4 |
| Tom | 4 | 4 | 3 | 4 | 4 | 5 | 5 |
| Nina | 5 | 6 | 4 | 3 | 3 | 3 | 5 |
| Anna | 4 | 3 | 4 | 5 | 5 | 3 | 3 |
| Musa | 5 | 5 | 4 | 4 | 4 | 6 | 5 |
| Maria | 4 | 3 | 5 | 4 | 3 | 2 | 3 |
| Chris | 6 | 5 | 5 | 5 | 5 | 5 | 6 |
For every student I want to create at least two columns with the best test result and best subject. Important: every student can have more than only one best subject (the results are similar)!
For the example above it should be look like this:
| Name | English | French | History | Math | Physic | Chemistry | Biology | Best result | Best subject 1 | Best subject 2 |
|---|---|---|---|---|---|---|---|---|---|---|
| Mike | 3 | 3 | 4 | 5 | 6 | 5 | 4 | 6 | Physic | None |
| Tom | 4 | 4 | 3 | 4 | 4 | 5 | 5 | 5 | Chemistry | Biology |
| Nina | 5 | 6 | 4 | 3 | 3 | 3 | 5 | 6 | French | None |
| Anna | 4 | 3 | 4 | 5 | 5 | 3 | 3 | 5 | Math | Physic |
| Musa | 5 | 5 | 4 | 4 | 4 | 6 | 5 | 6 | Chemistry | None |
| Maria | 4 | 3 | 5 | 4 | 3 | 2 | 3 | 5 | History | None |
| Chris | 6 | 5 | 5 | 5 | 5 | 5 | 6 | 6 | English | Biology |
what is the best way to do it in Pandas? Thank you in advance!
what is the best way to do it in Pandas? Thank you in advance!
>Solution :
Another possible solution :
tmp = df.set_index("Name") # a DataFrame
bre = tmp.max(axis=1) # a Series
bsu = (
((tmp.columns + "|") @ tmp.eq(bre, axis=0).T)
.str.strip("|").str.split("|", expand=True)
.rename(lambda x: f"Best subject {x+1}", axis=1)
)
out = tmp.assign(**{"Best result": bre}).join(bsu).reset_index()#.fillna("None")
Output :
| Name | English | French | History | Math | Physic | Chemistry | Biology | Best result | Best subject 1 | Best subject 2 | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Mike | 3 | 3 | 4 | 5 | 6 | 5 | 4 | 6 | Physic | |
| 1 | Tom | 4 | 4 | 3 | 4 | 4 | 5 | 5 | 5 | Chemistry | Biology |
| 2 | Nina | 5 | 6 | 4 | 3 | 3 | 3 | 5 | 6 | French | |
| 3 | Anna | 4 | 3 | 4 | 5 | 5 | 3 | 3 | 5 | Math | Physic |
| 4 | Musa | 5 | 5 | 4 | 4 | 4 | 6 | 5 | 6 | Chemistry | |
| 5 | Maria | 4 | 3 | 5 | 4 | 3 | 2 | 3 | 5 | History | |
| 6 | Chris | 6 | 5 | 5 | 5 | 5 | 5 | 6 | 6 | English | Biology |