Icon

congressional_​results

1) Added the 2016 Candidate Master File: https://www.fec.gov/data/browse-data/?tab=bulk-data2) Converted Numbers to Strings: Committee Formation Year, House District,Zip Code3) Added Header Names 1) Added the 2016 Operating Expenditure File: https://www.fec.gov/data/browse-data/?tab=bulk-data2) Added Header Names3) Changed Transaction Date to Date/Time format from String4) Created a Constant Value for election year. Since all data was pulled from2018, this new constant value is 2018 1) Created a Join based on matchingvariables - Cand_PCC (Principal campaigncommittee) from Candidate Master.Column 10- CMTE_ID (Filer identification number)from Operating Expenditures.Column 1 Removed all Presidential and Senatecandidates by filtering for House only. Filtered for "H" reducing total values countfrom 1,182,460 to 622,130 There was a problem with "Unopposed" string values being placed in the General Votes variable. To solve: 1) Split "Unopposed" entries with numbers values2) Add new column with string of "0" in each row to indicate unopposed3) Convert all 4 "Unopposed" values into null values4) Covnert General Votes to Integer from String To retify on the second split:1) Converted general votes to integer2) Add Opposed Column with string of "1"to indicated candidate was opposed Prepped data for join with Candidate Master and Expenditure data1) Converting Election Year to String enables a future match betweenthe corresponding variable in other data set2) Underscores makes sure naming conventions are the same Joined based on CAND_ID (Candidate ID) from CandidateMaster/ExpendituresFEC ID# (Federal Elections Commission ID Number) And Year because each election year results needed to bematched with the correct expenditures from that cycle FINALDATASET Operating Expenditures 2016Added Header NamesCandidate Master 2016Added Header NamesJoin by Candidate CommitteeFilter for 'H' only candidatesOperating Expenditures 2018Added Header NamesCandidate Master 2018Added Header NamesJoin by Candidate CommitteeFilter for 'H' only candidatesJoin by Candidate CommitteeFilter for 'H' only candidatesOperating Expenditures 2020Added Header NamesCandidate Master 2020Added Header NamesJoin by Candidate CommitteeFilter for 'H' only candidatesOperating Expenditures 2022Added Header NamesCandidate Master 2022Added Header NamesCombined 2020 and 2020 datasetsNode 37Node 38Node 39Resorted columnsCombined 2016 and 2018 datasetsCombine the 2016 & 2018 with2020 & 2022 datasets, resulting in 3,201,089 fieldsCategoryCategory DescriptionPurposePartyCand-IDHouse Results 2016House Results 2018House Results 2020Combine 2016 and 2018 ResultsCombine 2016 & 2018 with 2020 ResultsCand-IDElection Cycle Year AddedElection Cycle Year AddedElection Cycle Year AddedElection Cycle Year AddedNode 72Node 73Join by Candidate CommitteeConvert Year to StringNode 77using regex (2)Case INSENSITIVE matchPurposePurposeusing regex (2)Case INSENSITIVE matchPurposeusing regex (2)Case INSENSITIVE matchPurposeNode 87Change Committee Formation Year,House District, & Zip to StringsChange Committee Formation Year,House District, & Zip to StringsChange Committee Formation Year,House District, & Zip to StringsChange Committee Formation Year,House District, & Zip to StringsChange Transaction Date to Date/TimeChange Transaction Date to Date/TimeChange Transaction Date to Date/TimeChange Transaction Date to Date/TimeAdded Underscores to matchNaming conventionsConvert General Votes to Integer from StringSort Unopposed from Vote NumbersAdd Opposed Columnwith "1" for OpposedAdd Opposed Columnwith "0" for UnopposedTurn "Unopposed" intonull valueConvert General Votes to Integer from StringCombine two sets with new column string foroppposed and all votes as integersCombine two sets with new column string foroppposed and all votes as integersConvert General Votes to Integer from StringSort Unopposed from Vote NumbersAdd Opposed Columnwith "1" for OpposedAdd Opposed Columnwith "0" for UnopposedTurn "Unopposed" intonull valueConvert General Votes to Integer from StringCombine two sets with new column string foroppposed and all votes as integersConvert General Votes to Integer from StringSort Unopposed from Vote NumbersAdd Opposed Columnwith "1" for OpposedAdd Opposed Columnwith "0" for UnopposedTurn "Unopposed" intonull valueConvert General Votes to Integer from StringResorted ColumnsNode 126File Reader(Complex Format) Column Rename File Reader Column Rename Joiner Row Filter File Reader(Complex Format) Column Rename File Reader Column Rename Joiner Row Filter Joiner Row Filter File Reader(Complex Format) Column Rename File Reader Column Rename Joiner Row Filter File Reader(Complex Format) Column Rename File Reader Column Rename Concatenate Column Resorter Column Resorter Column Resorter Column Resorter Concatenate Concatenate Value Counter Value Counter Value Counter Value Counter Value Counter Excel Reader Excel Reader Excel Reader Concatenate Concatenate Value Counter ConstantValue Column ConstantValue Column ConstantValue Column ConstantValue Column Row Filter Rule-basedRow Filter Joiner Number To String Row Filter Rule-basedRow Filter Value Counter Value Counter Rule-basedRow Filter Value Counter Rule-basedRow Filter Value Counter CSV Writer Number To String Number To String Number To String Number To String String to Date&Time String to Date&Time String to Date&Time String to Date&Time Column Rename String To Number Rule-basedRow Splitter ConstantValue Column ConstantValue Column String Manipulation String To Number Concatenate Concatenate String To Number Rule-basedRow Splitter ConstantValue Column ConstantValue Column String Manipulation String To Number Concatenate String To Number Rule-basedRow Splitter ConstantValue Column ConstantValue Column String Manipulation String To Number Column Resorter Column Resorter 1) Added the 2016 Candidate Master File: https://www.fec.gov/data/browse-data/?tab=bulk-data2) Converted Numbers to Strings: Committee Formation Year, House District,Zip Code3) Added Header Names 1) Added the 2016 Operating Expenditure File: https://www.fec.gov/data/browse-data/?tab=bulk-data2) Added Header Names3) Changed Transaction Date to Date/Time format from String4) Created a Constant Value for election year. Since all data was pulled from2018, this new constant value is 2018 1) Created a Join based on matchingvariables - Cand_PCC (Principal campaigncommittee) from Candidate Master.Column 10- CMTE_ID (Filer identification number)from Operating Expenditures.Column 1 Removed all Presidential and Senatecandidates by filtering for House only. Filtered for "H" reducing total values countfrom 1,182,460 to 622,130 There was a problem with "Unopposed" string values being placed in the General Votes variable. To solve: 1) Split "Unopposed" entries with numbers values2) Add new column with string of "0" in each row to indicate unopposed3) Convert all 4 "Unopposed" values into null values4) Covnert General Votes to Integer from String To retify on the second split:1) Converted general votes to integer2) Add Opposed Column with string of "1"to indicated candidate was opposed Prepped data for join with Candidate Master and Expenditure data1) Converting Election Year to String enables a future match betweenthe corresponding variable in other data set2) Underscores makes sure naming conventions are the same Joined based on CAND_ID (Candidate ID) from CandidateMaster/ExpendituresFEC ID# (Federal Elections Commission ID Number) And Year because each election year results needed to bematched with the correct expenditures from that cycle FINALDATASET Operating Expenditures 2016Added Header NamesCandidate Master 2016Added Header NamesJoin by Candidate CommitteeFilter for 'H' only candidatesOperating Expenditures 2018Added Header NamesCandidate Master 2018Added Header NamesJoin by Candidate CommitteeFilter for 'H' only candidatesJoin by Candidate CommitteeFilter for 'H' only candidatesOperating Expenditures 2020Added Header NamesCandidate Master 2020Added Header NamesJoin by Candidate CommitteeFilter for 'H' only candidatesOperating Expenditures 2022Added Header NamesCandidate Master 2022Added Header NamesCombined 2020 and 2020 datasetsNode 37Node 38Node 39Resorted columnsCombined 2016 and 2018 datasetsCombine the 2016 & 2018 with2020 & 2022 datasets, resulting in 3,201,089 fieldsCategoryCategory DescriptionPurposePartyCand-IDHouse Results 2016House Results 2018House Results 2020Combine 2016 and 2018 ResultsCombine 2016 & 2018 with 2020 ResultsCand-IDElection Cycle Year AddedElection Cycle Year AddedElection Cycle Year AddedElection Cycle Year AddedNode 72Node 73Join by Candidate CommitteeConvert Year to StringNode 77using regex (2)Case INSENSITIVE matchPurposePurposeusing regex (2)Case INSENSITIVE matchPurposeusing regex (2)Case INSENSITIVE matchPurposeNode 87Change Committee Formation Year,House District, & Zip to StringsChange Committee Formation Year,House District, & Zip to StringsChange Committee Formation Year,House District, & Zip to StringsChange Committee Formation Year,House District, & Zip to StringsChange Transaction Date to Date/TimeChange Transaction Date to Date/TimeChange Transaction Date to Date/TimeChange Transaction Date to Date/TimeAdded Underscores to matchNaming conventionsConvert General Votes to Integer from StringSort Unopposed from Vote NumbersAdd Opposed Columnwith "1" for OpposedAdd Opposed Columnwith "0" for UnopposedTurn "Unopposed" intonull valueConvert General Votes to Integer from StringCombine two sets with new column string foroppposed and all votes as integersCombine two sets with new column string foroppposed and all votes as integersConvert General Votes to Integer from StringSort Unopposed from Vote NumbersAdd Opposed Columnwith "1" for OpposedAdd Opposed Columnwith "0" for UnopposedTurn "Unopposed" intonull valueConvert General Votes to Integer from StringCombine two sets with new column string foroppposed and all votes as integersConvert General Votes to Integer from StringSort Unopposed from Vote NumbersAdd Opposed Columnwith "1" for OpposedAdd Opposed Columnwith "0" for UnopposedTurn "Unopposed" intonull valueConvert General Votes to Integer from StringResorted ColumnsNode 126File Reader(Complex Format) Column Rename File Reader Column Rename Joiner Row Filter File Reader(Complex Format) Column Rename File Reader Column Rename Joiner Row Filter Joiner Row Filter File Reader(Complex Format) Column Rename File Reader Column Rename Joiner Row Filter File Reader(Complex Format) Column Rename File Reader Column Rename Concatenate Column Resorter Column Resorter Column Resorter Column Resorter Concatenate Concatenate Value Counter Value Counter Value Counter Value Counter Value Counter Excel Reader Excel Reader Excel Reader Concatenate Concatenate Value Counter ConstantValue Column ConstantValue Column ConstantValue Column ConstantValue Column Row Filter Rule-basedRow Filter Joiner Number To String Row Filter Rule-basedRow Filter Value Counter Value Counter Rule-basedRow Filter Value Counter Rule-basedRow Filter Value Counter CSV Writer Number To String Number To String Number To String Number To String String to Date&Time String to Date&Time String to Date&Time String to Date&Time Column Rename String To Number Rule-basedRow Splitter ConstantValue Column ConstantValue Column String Manipulation String To Number Concatenate Concatenate String To Number Rule-basedRow Splitter ConstantValue Column ConstantValue Column String Manipulation String To Number Concatenate String To Number Rule-basedRow Splitter ConstantValue Column ConstantValue Column String Manipulation String To Number Column Resorter Column Resorter

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: cholb
Created at: 2023-06-16
On NodePit since: 2023-06-16
Last update: 2024-04-27
Created with KNIME version: v4.6.3

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!