Thanks in advance for the help! I've got four years of daily school attendance data spread across 40+ Excel files (one for each month) and the sheets are set up in a truly annoying fashion, with each date in one merged cell in the first row while the total enrollment and daily absences are split into two columns below it (see screenshot). I'm moving the data into python/pandas and I'm wondering if there's an efficient way to get useable column headers, other than manually re-typing them all? For example, when reading in the data, is there a way to automatically concatenate each date header in row 1 with the relevant "Total Membership" and "Abs." headers in row 2, in order to get columns like "April 3, 2023 Total Membership" and "April 3, 2023 Abs."? Ultimately, I need to transform the data into this format: Date School Total Membership Absent 2023-04-03 School A 1234 12 2023-04-04 School A 1234 13 2923-04-05 School A 1234 10 2023-04-03 School B 2345 23 2023-04-04 School B 2345 21 Note: dates that fall on weekends or school breaks aren't included in the monthly Excel files. Continue reading...
