Try to implement the privote_table of pandas to produce a table for each of party and each state shows how much the party receievd in total contributions from the state.
Is this the right way to do or i has to get into the data base and get fectched out. However the code below gives error.
party_and_state = candidates.merge(contributors, on='id')
party_and_state.pivot_table(df,index=["party","state"],values=["amount"],aggfunc=[np.sum])
The expected result could be something like the table below. The first coulmn is the state name then the party D underneath the party D is the total votes from each state, the same applies with the party R
+-----------------+---------+--------+
| state | D | R |
+-----------------+---------+--------+
| AK | 500 | 900 |
| IL | 600 | 877 |
| FL | 200 | 400 |
| UT | 300 | 300 |
| CA | 109 | 90 |
| MN | 800 | 888 |
Consider the generalized pandas merge with
pd
as qualifier instead of a dataframe since the join fields are differently named hence requiring left_on and right_on args. Additionally, do not pass indf
if runningpivot_table
as method of a dataframe since the called df is passed into the function.Below uses the contributors and contributors_with_candidates text files. Also, per your desired results, you may want to use the values arg of pivot_table:
Do note, you can do the merge as an inner join in SQL with read_sql: