I have a pandas dataframe that has information about total attendance for schools grouped by School, District, Program, Grade, and Month #. The data looks like the following (df): School District Program Grade Month Count 123 456 A 9-12 10 100 123 456 B 9-12 10 95 321 654 A 9-12 10 23 321 456 A 7-8 10 40 Some of the counts are inflated and need to be reduced based on the data from another dataframe (ToSubtract): School District Program Grade Month Count 123 456 A 9-12 10 10 321 654 A 9-12 10 8 Both dataframes are already grouped so there will be no duplicate grouping. Subtracting ToSubtract from df will result in: School District Program Grade Month Count X 123 456 A 9-12 10 90 * 123 456 B 9-12 10 95 321 654 A 9-12 10 15 * 321 456 A 7-8 10 40 (X column to be marked with * to indicate value was modified). df has a lot more entries for all of the other schools, districts, months, etc. I was looking into df.sub() but it looks like the elements have to be lined up. My other idea was to use df.iterrows() to go through each row of df and check if there is a corresponding row in ToSubtract but this seems very inefficient. What would be the best way to subtract one dataframe from another, matching several columns Continue reading...
