<jupyter_start><jupyter_text>ContributionsExtract campaign contributions linked to a committee<jupyter_code>import os
import settings
import pandas as pd<jupyter_output><empty_output><jupyter_text>Pull in all filings<jupyter_code>filings = pd.read_csv(os.path.join(settings.data_dir, "filings.csv"))
filings.head()<jupyter_output><empty_output><jupyter_text>Pull in all contributions<jupyter_code>monetary_sql_template = """
SELECT *
FROM calaccess_processed_form460scheduleaitem
WHERE filing_id IN ({})
"""
monetary_sql = monetary_sql_template.format(
    ",".join(map(str, list(filings.calaccess_filing_id)))
)
contribs = pd.read_sql_query(monetary_sql, settings.db_connection)
trimmed_contribs = contribs.rename(columns={
    'filing_id': 'calaccess_filing_id',
    'id': 'ccdc_contrib_id',
    'transaction_id': 'calaccess_contrib_id'
}).drop([
    'line_item',
    'date_received_thru',
    'transaction_type',
    'memo_reference_number',
    'cumulative_ytd_amount',
    'cumulative_election_amount',
], axis=1)
trimmed_contribs.head()<jupyter_output><empty_output><jupyter_text>Merge them<jupyter_code>merged = pd.merge(
    filings,
    trimmed_contribs,
    how="inner",
    on="calaccess_filing_id"
)
unwanted_columns = [
    'from_date',
    'thru_date',
    'total_contributions',
    'total_expenditures_made',
    'ccdc_contrib_id',
    'date_filed',
    'calaccess_contrib_id',
    'contributor_code',
    'contributor_committee_id',
    'contributor_title',
    'contributor_name_suffix',
    'intermediary_committee_id',
    'intermediary_title',
    'intermediary_lastname',
    'intermediary_firstname',
    'intermediary_name_suffix',
    'intermediary_city',
    'intermediary_state',
    'intermediary_zip',
    'intermediary_employer',
    'intermediary_occupation',
    'intermediary_is_self_employed'
]
trimmed_merged = merged.drop(unwanted_columns, axis=1)
trimmed_merged.head()
trimmed_merged.to_csv(
    os.path.join(settings.data_dir, 'contributions.csv'),
    index=False
)<jupyter_output><empty_output><jupyter_text>Filter down to 2016 contributions<jupyter_code>recent_filings = pd.read_csv(os.path.join(settings.data_dir, "filings-2016.csv"))
recent_contribs = trimmed_merged[trimmed_merged.calaccess_filing_id.isin(recent_filings.calaccess_filing_id)]
recent_contribs.head(10)
recent_contribs.to_csv(
    os.path.join(settings.data_dir, 'contributions-2016.csv'),
    index=False
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11774325429272281, 'fraction_numerical': 0.00776778413736713, 'mean_word_length': 6.599378881987578, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '469543', 'license': 'MIT', 'n_tokens_mistral': 932, 'n_tokens_neox': 877, 'n_words': 105, 'path': 'calaccess-exploration/propositions/06_contributions.ipynb', 'repo_name': 'california-civic-data-coalition/python-calacess-notebooks', 'size': '2446'}