This is my current df:
| Name | Absences | 9/1 | 9/2 | 9/3 | 9/4 | 9/5 | 9/6 |
|---|---|---|---|---|---|---|---|
| Student1 | 9/1,9/3,9/4,9/5 | 0 | 0 | 0 | 0 | 0 | 0 |
| Student2 | 9/1,9/2,9/6 | 0 | 0 | 0 | 0 | 0 | 0 |
| Student3 | 9/1,9/2,9/3 | 0 | 0 | 0 | 0 | 0 | 0 |
| Student4 | 9/2 | 0 | 0 | 0 | 0 | 0 | 0 |
I want to populate the date columns with a 1 if they appear in the Absences column. Unfortunately this is how the data is formatted. I created the date columns, so if you have a slightly different idea please let me know. I am only give ‘Name’ and ‘Absences’.
Desired end result:
| Name | Absences | 9/1 | 9/2 | 9/3 | 9/4 | 9/5 | 9/6 |
| ——– | ————– | — | — | — | — | — | — |
| Student1 | 9/1,9/3,9/4,9/5| 1 | 0 | 1 | 1 | 1 | 0 |
| Student2 | 9/1,9/2,9/6 | 1 | 1 | 0 | 0 | 0 | 1 |
| Student3 | 9/1,9/2,9/3 | 1 | 1 | 1 | 0 | 0 | 0 |
| Student4 | 9/2 | 0 | 1 | 0 | 0 | 0 | 0 |
I feel like this can be achieved with apply and a custom function. My two ideas have been to iterate thru the list and populate the corresponding column, or to iterate thru the columns and if its in the absences list, then populate with 1. Not succeeding in either
Thank you.