Skip to content

Instantly share code, notes, and snippets.

@rolandcrosby
Created January 26, 2020 00:32
Show Gist options
  • Save rolandcrosby/b0b2f19990e20727d45dd39834a4c1b2 to your computer and use it in GitHub Desktop.
Save rolandcrosby/b0b2f19990e20727d45dd39834a4c1b2 to your computer and use it in GitHub Desktop.
Generate a BigQuery query that joins together all the FEC individual contributor data
out = []
for i in range(2020, 1978, -2):
yr = "%02d" % (i % 100)
out.append("""(
SELECT
`bigquery-public-data.fec.indiv{0}`.cmte_id,
cmte_nm,
amndt_ind,
rpt_tp,
transaction_pgi,
cast(image_num as string),
transaction_tp,
entity_tp,
name,
city,
state,
zip_code,
employer,
occupation,
transaction_dt,
transaction_amt,
other_id,
tran_id,
file_num,
memo_cd,
memo_text,
sub_id
FROM
`bigquery-public-data.fec.indiv{0}`
LEFT JOIN
`bigquery-public-data.fec.cm{0}`
ON
`bigquery-public-data.fec.indiv{0}`.cmte_id = `bigquery-public-data.fec.cm{0}`.cmte_id
)""".format(yr))
print("#standardSQL")
print("\nUNION ALL\n".join(out))
print(";")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment