DATA PROCESSING: USING RULES-COMMANDS TO COMBINE MULTIPLE ROWS INTO COLUMNS IN 80K RECORDS
Budget: $250 – $750 USD
SCOPE OF WORK
DONOR DATABASE
1.0 The Parameters/Basics
The parameters:
• We will use an excel/sheets dataset that has approximately 80,000 records (rows). The date is (mostly) in hand. The last bit I should have in the next 24-48 hours;
• Each row represents a donation made to a political campaign. The rows contain
o Contributor Name (first and initial – and last)
o Address 01,
o Address 02,
o City,
o State,
o Zip code,
o Code of donation (whether individual, committee, corp, etc.),
o Occupation,
o Employer,
o Amount of donation,
o Recipient committee (who received the donation)
o Date of donation.
• The basic problem with the dataset: This dataset tracks the donations given to all campaigns. What it fails to do is track the DONORS who donate to political campaigns. I need to rearrange things so that every row contains a single unique donor; and then for each donor we track what political campaigns they have donated to.
• Purpose: With the revised list you can then determine who you’re most valuable contacts are, by ordering a list from the most prolific and generous donors to the least prolific and generous donors.
2.0 The Transformation from Donation to Donor List
We need to transform the list of donations we have into a list of donors. The list will show up, basically, separated by donations to each committee.
2.1 How I envision converting donation records (rows) into data stored into columns
Aside from the basics for a donor (name, address, occupation, etc.) – we’ll need to add a series of additional columns that will be populated as necessary for each donor. I envision that every campaign committee that we pulled will get a few of its own columns.
First, we will need a column from each committee name and ID number for that committee. Underneath that column will tell us whether the individual donor donated to that committee (a zero will indicate never donated), while a number 1 or more, demonstrates they did donate to the committee, and how many times. For each committee, we’ll need an additional column to track how much each donor gave to every committee the donor donated to. Finally, we’ll have three summation columns: the first tells us how many total donations for this donor across all committees; the next will tell us the total amount donated by this donor; the third, will provide us with the average donation for this donor (total donated / total donations given). Table 1 contains a list of all of the columns that must be created to create the database.
In order to move all of the data from the rows to the columns we need to manage two things:
· We need to delete every duplicate row but only after we move the data from that row into a column; and
· For the first row we need to take the donation from the first row and move it into the proper column; indeed, I think we’ll need to do this for every record, even the donor who only donates to a single campaign – that way the data is identical throughout the database (perhaps we do this step first)
I believe we pulled data from 150 committees or so. And so if we add two columns for each committee (subtotal donations, and subtotal donation amount) then we add about 300 additional columns. The list of the committees is attached as TABLE 1.
2.3 Particulars On LOOK UP coding for this case
A record for a donor matches when two fields match: (1) Contributor’s last name; and (2) address 01.
· Here’s my thinking: We could require a match to include a first name match as well. But, sometimes it’s ROBERT, or ROB, or BOB or R. And if we require that as a match, we’re going to call those contributions individuals. And to avoid that problem I would use last name exclusively.
· Some households will have husband and wife with the same last name. This will bunch all of the donations under one of the two of them. That’s fine.
· Sometimes the donation will say “BOULEVARD” and other times it will say “BLVD.” and others “BLVD”. Those won’t match, technically. Therefore, in TABLE 2, I’ve provided a lookup table for street name suffixes. And so when we must first convert all forms of the street suffix into a single but consistent word or abbreviation. And then, it should all match.
2.4 Gathering, Sorting, and Adding Up Donations and cumulative donation amounts for each committee
All of the matches should be gathered and sorted together. Once together the program determines how many total donations that individual made. And then, for every donation (row) the program checks what campaign committee received the money (here, one simply matches the campaign committee ID number in the donation row, and then one matches that number to the campaign committee ID number that’s in a column. Once the match for that donor is determined, then one inserts the donation amount for that donor to the committee id number column that received the money and then one counts that donation to that committee (If three donational provided to a committee, well count number of donations (three) and then count each donation made to the target committee and provide the total. (Example: Andy Adams donated $100, $200, and $300 to Brenda Barbie in 2022. So, Andy Adams shows three donations, across three rows. When the data is converted Andy Adams will then write into the Brenda Barbie column to the right of his donor name “’1’ – ‘$100’”, followed by “’2’ “$300” (EG., $100 + $200 = $300) followed by “3” “$600” (again, $100 + $200 + $300 = $600”).
2.4.1 Generating a donors total donations made and total value of donations
The data is sorted, and each donor’s record is tallied into what committees they donated to, how many times, and how much total, as is shown in 2.4. Then, the program will tally the total amount of dollars donated and how many total donations were made and write it into a column labeled “total donations by donor” and a second column “total value of donations by donor” this calculation will continue for each donor (every row will have one unique donor by this point).
2.5 City-County Lookup
We need to know what county the donor is from. The data doesn’t contain it. But, every city belongs to a single County in California. The city-county lookup table has already been provided and is available as Table 2. When the other entries are completed, the program will then run a match for city, and when city matches in the city-county lookup table, we will write the county into the original table under a heading called “donor’s county.”
3.0 REturn of data
The data will be returned as a spreadsheet. IF the data is too large for the spreadsheet than the freelancer will upload it to gigasheet so it can be utilized there in a usable manner.
5. I need this job completed in 7 days. I will pay a bonus to anyone who can deliver it in five days or less. Please include that as part of your bid.
DONOR DATABASE
1.0 The Parameters/Basics
The parameters:
• We will use an excel/sheets dataset that has approximately 80,000 records (rows). The date is (mostly) in hand. The last bit I should have in the next 24-48 hours;
• Each row represents a donation made to a political campaign. The rows contain
o Contributor Name (first and initial – and last)
o Address 01,
o Address 02,
o City,
o State,
o Zip code,
o Code of donation (whether individual, committee, corp, etc.),
o Occupation,
o Employer,
o Amount of donation,
o Recipient committee (who received the donation)
o Date of donation.
• The basic problem with the dataset: This dataset tracks the donations given to all campaigns. What it fails to do is track the DONORS who donate to political campaigns. I need to rearrange things so that every row contains a single unique donor; and then for each donor we track what political campaigns they have donated to.
• Purpose: With the revised list you can then determine who you’re most valuable contacts are, by ordering a list from the most prolific and generous donors to the least prolific and generous donors.
2.0 The Transformation from Donation to Donor List
We need to transform the list of donations we have into a list of donors. The list will show up, basically, separated by donations to each committee.
2.1 How I envision converting donation records (rows) into data stored into columns
Aside from the basics for a donor (name, address, occupation, etc.) – we’ll need to add a series of additional columns that will be populated as necessary for each donor. I envision that every campaign committee that we pulled will get a few of its own columns.
First, we will need a column from each committee name and ID number for that committee. Underneath that column will tell us whether the individual donor donated to that committee (a zero will indicate never donated), while a number 1 or more, demonstrates they did donate to the committee, and how many times. For each committee, we’ll need an additional column to track how much each donor gave to every committee the donor donated to. Finally, we’ll have three summation columns: the first tells us how many total donations for this donor across all committees; the next will tell us the total amount donated by this donor; the third, will provide us with the average donation for this donor (total donated / total donations given). Table 1 contains a list of all of the columns that must be created to create the database.
In order to move all of the data from the rows to the columns we need to manage two things:
· We need to delete every duplicate row but only after we move the data from that row into a column; and
· For the first row we need to take the donation from the first row and move it into the proper column; indeed, I think we’ll need to do this for every record, even the donor who only donates to a single campaign – that way the data is identical throughout the database (perhaps we do this step first)
I believe we pulled data from 150 committees or so. And so if we add two columns for each committee (subtotal donations, and subtotal donation amount) then we add about 300 additional columns. The list of the committees is attached as TABLE 1.
2.3 Particulars On LOOK UP coding for this case
A record for a donor matches when two fields match: (1) Contributor’s last name; and (2) address 01.
· Here’s my thinking: We could require a match to include a first name match as well. But, sometimes it’s ROBERT, or ROB, or BOB or R. And if we require that as a match, we’re going to call those contributions individuals. And to avoid that problem I would use last name exclusively.
· Some households will have husband and wife with the same last name. This will bunch all of the donations under one of the two of them. That’s fine.
· Sometimes the donation will say “BOULEVARD” and other times it will say “BLVD.” and others “BLVD”. Those won’t match, technically. Therefore, in TABLE 2, I’ve provided a lookup table for street name suffixes. And so when we must first convert all forms of the street suffix into a single but consistent word or abbreviation. And then, it should all match.
2.4 Gathering, Sorting, and Adding Up Donations and cumulative donation amounts for each committee
All of the matches should be gathered and sorted together. Once together the program determines how many total donations that individual made. And then, for every donation (row) the program checks what campaign committee received the money (here, one simply matches the campaign committee ID number in the donation row, and then one matches that number to the campaign committee ID number that’s in a column. Once the match for that donor is determined, then one inserts the donation amount for that donor to the committee id number column that received the money and then one counts that donation to that committee (If three donational provided to a committee, well count number of donations (three) and then count each donation made to the target committee and provide the total. (Example: Andy Adams donated $100, $200, and $300 to Brenda Barbie in 2022. So, Andy Adams shows three donations, across three rows. When the data is converted Andy Adams will then write into the Brenda Barbie column to the right of his donor name “’1’ – ‘$100’”, followed by “’2’ “$300” (EG., $100 + $200 = $300) followed by “3” “$600” (again, $100 + $200 + $300 = $600”).
2.4.1 Generating a donors total donations made and total value of donations
The data is sorted, and each donor’s record is tallied into what committees they donated to, how many times, and how much total, as is shown in 2.4. Then, the program will tally the total amount of dollars donated and how many total donations were made and write it into a column labeled “total donations by donor” and a second column “total value of donations by donor” this calculation will continue for each donor (every row will have one unique donor by this point).
2.5 City-County Lookup
We need to know what county the donor is from. The data doesn’t contain it. But, every city belongs to a single County in California. The city-county lookup table has already been provided and is available as Table 2. When the other entries are completed, the program will then run a match for city, and when city matches in the city-county lookup table, we will write the county into the original table under a heading called “donor’s county.”
3.0 REturn of data
The data will be returned as a spreadsheet. IF the data is too large for the spreadsheet than the freelancer will upload it to gigasheet so it can be utilized there in a usable manner.
5. I need this job completed in 7 days. I will pay a bonus to anyone who can deliver it in five days or less. Please include that as part of your bid.