quick csv grouping script in python re: https://twitter.com/rachelwalexande/status/1034236011830697986

fixcsv.py

import csv
from collections import OrderedDict

CLEANED_DICT = OrderedDict()
METADATA_FIELDS = ['districtID','district','schoolID','school']
VALUE_FIELDS = []

with open('schools.csv') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        if (row['schoolID'] not in CLEANED_DICT.keys()):
            # we don't have a row for this school yet,
            # so create it with the basic metadata
            CLEANED_DICT[row['schoolID']] = {
                k:row[k] for k in METADATA_FIELDS
            }
        
        # create a column designator based on the student group in this csv row
        # e.g. 'all_students' or 'economically_disadvantaged'
        value = row['students'].lower().replace(' ','_').replace('/','_')

        value_types = ['total','pct']
        for value_type in value_types:
            # make a csv-friendly header for this column, e.g. 'total_all_students'
            column_header = value_type + '_' + value
            # add it to our running list of fields for the output csv
            if column_header not in VALUE_FIELDS: VALUE_FIELDS.append(column_header)
            # add value for that group of students to this school's row
            CLEANED_DICT[row['schoolID']].update({column_header: row[value_type]})

# create the output csv
with open('schools_cleaned.csv', 'w') as csvfile:
    fieldnames = METADATA_FIELDS + VALUE_FIELDS
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames, lineterminator='\n')

    writer.writeheader()
    for k,v in CLEANED_DICT.items():
        writer.writerow(v)

schools.csv

schools_cleaned.csv