Overview
You are currently volunteering on the campaign of a politician you greatly admire. She is running for one of the seats in the House of Representatives associated with your home state. The finance director for the campaign is very interested in data involving the correlation between campaign financing and the outcome of the elections. He has procured a large dataset containing information on campaign financing on elections around 2016 and, knowing of your exceptional data analytics skills, has asked you to summarize the data into a more digestible format. After considering the data, you find that many different campaigns are included (Presidential, Senate, etc.) that are not relevant to your analysis. Also, since your candidate is a member of one of the major parties, you determine that information on the Democratic and Republican parties is the most relevant. You finally determine that Pivot Tables would be appropriate to filter out irrelevant data and draw meaningful conclusions.
Instructions
The director has three specific questions he wants you to explore:
First, the director thinks that Challenger campaigns (where the candidate is challenging an incumbent) generally require more financing because of the incumbent’s track record and experience in the position. Since your candidate is a Challenger, the director wants you to verify whether House Challenger campaigns are on average more costly than incumbent campaigns or campaigns for open seats.
Create a Pivot Table comparing the average contributions of all Challenger campaigns to those of Incumbent and Open campaigns. Include a breakout of whether the candidate was a Democrat or Republican within each candidate designation (Challenger, Incumbent, Open Seat). Remember to filter out the irrelevant data mentioned above. Is your director correct in assuming Challengers have higher average contributions compared to the other designations?
Second, the director wants to know how many dollars were spent on the campaign per vote for both Republican and Democrat House candidates who won their races.
Create a Pivot Table that shows the sum of the total contributions, the sum of the votes received, and a calculated field showing the average amount of money spent for each vote. Break out each of these values by party and filter out irrelevant data. Include only winners. How much can the director expect to spend per desired vote?
Third, the director has told you that the candidate, a very wealthy businessperson, wants to personally contribute a substantial sum of money to her campaign. The director is hesitant to allow her to do so, worried that if the candidate loses the race, she will suffer a substantial financial loss. The director wants to know whether there is evidence that candidates who have personally contributed large sums of money to fund their own campaign have changed the outcome of their elections.
Create a Pivot Table that separately displays the top ten candidates of each major party (Democratic and Republican) who have personally contributed the most to their campaigns. Determine which candidates won their race and whether there is an obvious correlation between personal contributions and winning the race.
This was a great challenge for anyone looking to get more practice with pivot tables! Make sure to reference the data dictionary because the column titles are a tad confusing at first, and there are a ton to sift through.
My experience is limited, so I learned a few new things. I had never used the calculated field function within the PivotTable Analyze tab, but it’s a nifty tool. I also hadn’t used value filters for any of my tables in the past, but it’s great for showing top 10!
This challenge took me a minute to grasp what I was doing cuz I read the first paragraph of the instructions and didn’t quite realize the second paragraph provided more detailed guidance, but after reading through the data dictionary and comparing the columns to the question, I figured out what I was doing.
These pivot tables are so useful, I can see how they would be useful in analyzing large amounts of data like this!! I thought a correlation analysis would be more applicable for the third question, but the pivot table seems to work just as well!!
Pivot Tables are so amazing because that is way too much data for any person to sort through, but in just a few minutes we have some very real, useful data!
Also, as for the third problem, it’s hard to know whether its causation or correlation, but if the candidate insists on spending a lot of their own money, odds are against them winning the race regardless of party which I found interesting and not what I expected.
This was a great challenge to dive into pivot tables and learn how to use them. There were a lot of items, so the dictionary is required in order to get to the solution. I eventually got the same answers as the solution. Even though I had learned about pivot tables, it was nice to see a new and different challenge that allowed me to test what I had learned. Overall, it was a great challenge to learn more about such a powerful tool.
This was a good challenge. It was similar to what I had recently learned in my class! I feel like for all students who want to learn excel, Pivot Tables are one of the most useful features. I use these in my job, so it didn’t prove to be too challenging for me!
Great challenge. Pivot tables are a super great way to quickly visualize groupings and aggregations of data. This also works super well with Challenge 3 (both work on pivot tables)!
It took me a moment to understand the data and get moving, but with the help of the data dictionary and some more reading I was able to understand what was happening.
As I worked through this problem, I learned about creating calculations between pivot table cells and filtering to the top ten. There are many aspects of pivot tables that I still am learning of, but I was able to get the correct answers without using the solutions video.
Pivot tables are incredibly efficient, transforming extensive datasets into actionable insights swiftly. Furthermore, regarding the third issue, it’s challenging to determine if it’s a direct cause or merely a coincidental association. Nevertheless, it appears that candidates who heavily invest their personal funds tend to have lower chances of election success, irrespective of their political affiliation—a surprising discovery that contrasts with my initial expectations.
