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

matplotlib powered data graphics.

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 campaign contributions v date for Portland City Council Position #2, use the PyQL:
D,1*S(A)@(('Julia DeGraw' in F or 'Nick Fish' in F) and D>=20170907 and 'Contrib' in ST) and F

To see details of all reported transations for a candidate use (eg): Date,F,CP,A,ST@'Nick Fish' in F

And likewise for Portland City Council Position #3:
Date,1*S(A) as 'Campaign Contributions'@(('Loretta' in F or 'Jo Ann' in F or 'Felicia Williams' in F or 'Emmons' in F or 'Vald' in F) and Date>=20170907 and 'Contrib' in ST) and Filer

To see details of all reported transations for a candidate use (eg): Date,F,CP,A,ST@'Loretta' in F

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.