drkane

{
    "E06000028": "E06000058",
    "E06000029": "E06000058",
    "E07000048": "E06000058",
    "E07000049": "E06000059",
    "E07000050": "E06000059",
    "E07000051": "E06000059",
    "E07000052": "E06000059",
    "E07000053": "E06000059",
    "E07000190": "E07000246",

drkane

import argparse
import csv

def faststream(fp, blocksize=65536, line_delimiter='*@@*', field_delimiter='@**@'):
    """Read a BCP file and yield the rows

    fp is a file pointer to a BCP file opened in 'r' mode.

    It is assumed the file is opened with the correct encoding, so that·
    fp.read() gives strings. A trailing newline may need to be added after this·

drkane

-- required fields from 360Giving standard
-- http://standard.threesixtygiving.org/en/latest/reference/#grants-sheet

with g as (select * from view_latest_grant)
select g.data->>'id' as "id",
	g.data->>'title' as "title",
	g.data->>'description' as "description",
	g.data->>'currency' as "currency",
	(g.data->>'amountAwarded')::float as "amountAwarded",
	to_date(g.data->>'awardDate', 'YYYY-MM-DD') as "awardDate",

drkane

drkane

# takes a pandas series
def normalize_string(s):
    s = s.astype(str)
    s = s.str.lower()
    
    # replace apostrophes
    def replace_apos(match):
        return match.group(0).replace("'", "")
    s = s.str.replace(r'\'s\b', replace_apos)
    

drkane

drkane

drkane

EmployerName,Address,CompanyNumber,SicCodes,DiffMeanHourlyPercent,DiffMedianHourlyPercent,DiffMeanBonusPercent,DiffMedianBonusPercent,MaleBonusPercent,FemaleBonusPercent,MaleLowerQuartile,FemaleLowerQuartile,MaleLowerMiddleQuartile,FemaleLowerMiddleQuartile,MaleUpperMiddleQuartile,FemaleUpperMiddleQuartile,MaleTopQuartile,FemaleTopQuartile,CompanyLinkToGPGInfo,ResponsiblePerson,EmployerSize,CurrentName,CharityNumber,CharityIncome,OrgType,OrgSubType,PaidMore,FemaleWorkforce
"""Bryanston School"",Incorporated","Bryanston House, Blandford, Dorset, United Kingdom, DT11 0PX",00226143,85310,18.0,28.2,0.0,0.0,0.0,0.0,24.4,75.6,50.8,49.2,49.2,50.8,51.5,48.5,https://www.bryanston.co.uk/employment,Nick McRobb (Bursar and Clerk to the Governors),500 to 999,"""Bryanston School"",Incorporated",306210,23130767.0,Charity,,Men,56.025
"""RED BAND"" CHEMICAL COMPANY, LIMITED","19, Smith's Place, Leith Walk, Edinburgh, EH6 8NU",SC016876,47730,2.3,-2.7,15.0,37.5,15.6,66.7,20.3,79.7,25.4,74.6,10.3,89.7,18.1,81.9,,Philip Galt (Man

drkane

drkane