query  data
a pythonic query language
Sign in or create a new account.

S(1@1) R(ST@1)
66509.0 Personal Expenditure for Reimbursement
2384.0 Loan Received (Non-Exempt)
379328.0 Cash Expenditure
729348.0 Cash Contribution
14345.0 Account Payable
3273.0 Refunds and Rebates
948.0 Cash Balance Adjustment
47480.0 In-Kind Contribution
9222.0 Interest/Investment Income
15035.0 Miscellaneous Other Receipt
30959.0 Items Sold at Fair Market Value
2678.0 Miscellaneous Other Disbursement
5743.0 Expenditure Made by an Agent
3269.0 Pledge of Cash
31.0 Loan Received (Exempt)
1312.0 Loan Payment (Non-Exempt)
584.0 Loan Forgiven (Non-Exempt)
29.0 Loan Payment (Exempt)
2388.0 Return or Refund of Contribution
296.0 Miscellaneous Account Receivable
225.0 Account Payable Rescinded
931.0 In-Kind/Forgiven Personal Expenditures
2410.0 Lost or Returned Check
283.0 In-Kind/Forgiven Account Payable
57.0 Nonpartisan Activity
39.0 Unexpended Agent Balance
176.0 Pledge of In-Kind
193.0 Uncollectible Pledge of Cash
6.0 Pledge of Loan
7.0 Uncollectible Pledge of In-Kind
67.0 Personal Expenditure Balance Adjustment

AggregateAmount, Amount, City, ContributorPayee, County, Date, FiledByName, Filer, OriginalId, PurposeCodes, State, SubType, TransactionId, Zip

The PyQL query format is: fields @ conditions.
  fields is a field or a comma delimited list of fields.
 conditions is a condition or an and delimited list of conditions.

Both fields and conditions are made up of terms.
A term is a valid Python expression in a name space made up of: database parameters; any imported python modules; PyQL Aggregators such as Average (A), Sum (S), and Replace (R); and other domain specific terms.

About the Oregon Campaign Finance DatabaseSample Queries
The Oregon Campaign Finance Database originates with the Oregon Secretary of State website at: https://secure.sos.state.or.us/orestar/gotoPublicTransactionSearch.do. The raw data files are avaiable throught the data link in the upper left.

The original data headers were mapped into CamelCase PyQL parameters. This allows easy access by just the capital letters or the first few letters of a parameter. For example, to see the Date, Filer, ContributorPayee, and Amount for Ted Wheeler's transactions use the PyQL:
D,F,CP,A@'Ted Wheeler' in F
Note that I can't use single letter short cuts for ContributorPayee since 'C' matches more than one parameter.

All of the transaction amounts are given as posititve numbers. Use the parameter SubType to help tease out the various machinations. For example, to see the total amount of transactions for each of Ted Wheeler's reported SubTypes, use the PyQL:
R(ST),S(A)@'Ted Wheeler' in F and ST

To start exploring a database, the PyQL Syntax S(1),R(Parameter)@Parameter is often handy. Let's start here by looking at the break down of transactions by SubType with the PyQL:
Sorting on the first column by clicking on that header, shows that Cash Constibutions is the most popular way to get money into the system.

Similarly, to see the total amount of transactions breaken down by state, use the PyQL:

To make a scatter plot of total Amount vs Zip for Zip codes in Oregon, use the PyQL:
R(Z),S(A),R((Z,S(A)))@Z and 97000<Z<97921
Note how the optional third parameter to the scatter plot gives MouseOver readouts of the Zip and total Amount from that Zip.

To plot rather the number of transactions for each Zip, use the PyQL:
R(Z),S(1),R((Z,S(1)))@Z and 97000<Z<97921

To plot cumulative transaction Amounts for Ted Wheeler and Jules Baily, use the PyQL:
D,1*S(A)@('Jules' in F or 'Ted Wheeler' in F) and F
Note how the implicit grouping by Filer in the second conditional causes these to be color coded.

To see transactions for Kate Brown and Bud Pierce, use the PyQL:
D,1/1000000*S(A) as 'Millions in Transactions'@F and ('Kate Brown' in F or 'Bud Pierce' in F) and ('Contribution' in ST,)?legend_show=1&columns=1
Note how I:
  changed units by dividing the Amount by a million;
  labeled the y-axis using the PyQL 'as' keyword;
  moved the implicit group by of Filer to the first condition so as to generate a separate scatter plot for each candidate.
  and used an explicit group by in the second condition to color by whether the transaction was a contribution.