8. Merge

Our next job is to filter down the contributions list, which includes all disclosed contributions to all proposition campaigns, to just those linked to Proposition 64.

8.1. Inspect DataFrames

When joining two tables together, the first step is to look carefully at the columns in each table. We can do that with the info command we learned earlier.

import pandas as pd
committee_list = pd.read_csv("https://raw.githubusercontent.com/california-civic-data-coalition/first-python-notebook/master/docs/src/_static/committees.csv")
contrib_list = pd.read_csv("https://raw.githubusercontent.com/california-civic-data-coalition/first-python-notebook/master/docs/src/_static/contributions.csv")
my_prop = 'PROPOSITION 064- MARIJUANA LEGALIZATION. INITIATIVE STATUTE.'

First the contributions.

contrib_list.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 56379 entries, 0 to 56378
Data columns (total 13 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   calaccess_committee_id        56379 non-null  int64  
 1   committee_name                56379 non-null  object 
 2   calaccess_filing_id           56379 non-null  int64  
 3   date_received                 56379 non-null  object 
 4   contributor_lastname          56375 non-null  object 
 5   contributor_firstname         53411 non-null  object 
 6   contributor_city              56369 non-null  object 
 7   contributor_state             56363 non-null  object 
 8   contributor_zip               56366 non-null  object 
 9   contributor_employer          48572 non-null  object 
 10  contributor_occupation        53273 non-null  object 
 11  contributor_is_self_employed  56379 non-null  bool   
 12  amount                        56379 non-null  float64
dtypes: bool(1), float64(1), int64(2), object(9)
memory usage: 5.2+ MB

Now compare that to the committee file.

committee_list.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 102 entries, 0 to 101
Data columns (total 8 columns):
 #   Column                  Non-Null Count  Dtype 
---  ------                  --------------  ----- 
 0   ocd_prop_id             102 non-null    object
 1   calaccess_prop_id       102 non-null    int64 
 2   ccdc_prop_id            102 non-null    int64 
 3   prop_name               102 non-null    object
 4   ccdc_committee_id       102 non-null    int64 
 5   calaccess_committee_id  102 non-null    int64 
 6   committee_name          102 non-null    object
 7   committee_position      102 non-null    object
dtypes: int64(4), object(4)
memory usage: 6.5+ KB

You will notice that each file contains a field called calaccess_committee_id.

That’s because these two files are drawn from a “relational database” that stores data in an array of tables linked together by common identifiers. In this case, the unique identifying codes of committees in one table can be expected to match those found in another.

We can therefore join the two files using the pandas merge method.

Note

If you are familar with traditional databases, you may recognize that the merge method in pandas is similar to SQL’s JOIN statement. If you dig into merge’s documentation you will see it has many of the same options.

8.2. Merge DataFrames

Merging two DataFrames is as simple as passing both to pandas built-in merge method and specifying which field we’d like to use to connect them together. We will save the result into another new variable, merged_everything.

merged_everything = pd.merge(committee_list, contrib_list, on="calaccess_committee_id")

That new DataFrame variable can be inspected like any other.

merged_everything.head()
ocd_prop_id calaccess_prop_id ccdc_prop_id prop_name ccdc_committee_id calaccess_committee_id committee_name_x committee_position committee_name_y calaccess_filing_id date_received contributor_lastname contributor_firstname contributor_city contributor_state contributor_zip contributor_employer contributor_occupation contributor_is_self_employed amount
0 ocd-contest/b51dc64d-3562-4913-a190-69f5088c22a6 1376258 70 PROPOSITION 051 - SCHOOL BONDS. FUNDING FOR K-... 382 1374469 YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... SUPPORT YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... 1965180 2015-01-15 CALIFORNIA BUILDING INDUSTRY ASSOCIATION ISSUE... NaN SACRAMENTO CA 95814 NaN NaN False 50000.0
1 ocd-contest/b51dc64d-3562-4913-a190-69f5088c22a6 1376258 70 PROPOSITION 051 - SCHOOL BONDS. FUNDING FOR K-... 382 1374469 YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... SUPPORT YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... 1965180 2015-01-16 COALITION FOR ADEQUATE SCHOOL HOUSING ISSUES C... NaN SAN RAFAEL CA 94901 NaN NaN False 55000.0
2 ocd-contest/b51dc64d-3562-4913-a190-69f5088c22a6 1376258 70 PROPOSITION 051 - SCHOOL BONDS. FUNDING FOR K-... 382 1374469 YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... SUPPORT YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... 1965180 2015-01-28 CALIFORNIA BUILDING INDUSTRY ASSOCIATION ISSUE... NaN SACRAMENTO CA 95814 NaN NaN False 140000.0
3 ocd-contest/b51dc64d-3562-4913-a190-69f5088c22a6 1376258 70 PROPOSITION 051 - SCHOOL BONDS. FUNDING FOR K-... 382 1374469 YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... SUPPORT YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... 1965180 2015-01-28 COALITION FOR ADEQUATE SCHOOL HOUSING ISSUES C... NaN SAN RAFAEL CA 94901 NaN NaN False 140000.0
4 ocd-contest/b51dc64d-3562-4913-a190-69f5088c22a6 1376258 70 PROPOSITION 051 - SCHOOL BONDS. FUNDING FOR K-... 382 1374469 YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... SUPPORT YES ON PROPOSITION 51 - CALIFORNIANS FOR QUALI... 1965180 2015-03-23 CALIFORNIA BUILDING INDUSTRY ASSOCIATION ISSUE... NaN SACRAMENTO CA 95814 NaN NaN False 50000.0

By looking at the columns you can check how many rows survived the merge.

merged_everything.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 90264 entries, 0 to 90263
Data columns (total 20 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   ocd_prop_id                   90264 non-null  object 
 1   calaccess_prop_id             90264 non-null  int64  
 2   ccdc_prop_id                  90264 non-null  int64  
 3   prop_name                     90264 non-null  object 
 4   ccdc_committee_id             90264 non-null  int64  
 5   calaccess_committee_id        90264 non-null  int64  
 6   committee_name_x              90264 non-null  object 
 7   committee_position            90264 non-null  object 
 8   committee_name_y              90264 non-null  object 
 9   calaccess_filing_id           90264 non-null  int64  
 10  date_received                 90264 non-null  object 
 11  contributor_lastname          90256 non-null  object 
 12  contributor_firstname         86685 non-null  object 
 13  contributor_city              90251 non-null  object 
 14  contributor_state             90244 non-null  object 
 15  contributor_zip               90248 non-null  object 
 16  contributor_employer          81684 non-null  object 
 17  contributor_occupation        86545 non-null  object 
 18  contributor_is_self_employed  90264 non-null  bool   
 19  amount                        90264 non-null  float64
dtypes: bool(1), float64(1), int64(5), object(13)
memory usage: 13.9+ MB

You can also see that the DataFrame now contains all of the columns in both tables. Columns with the same name have had a suffix of _x or _y_ automatically appended to indicate whether they came from the first or second DataFrame submitted to the merge.

8.3. Filter to a single proposition

The combined table now joins all contributions to all committees, which the info command reveals is more than 90,000 records. To zero in on just the contributions to committees in the contest over Proposition 64, we’ll need to filter out data, much like we did in the last chapter. Only this time, we’ll filter our new merged_everything DataFrame instead.

merged_prop = merged_everything[merged_everything.prop_name == my_prop]

We have now created a new dataset limited to the contributions supporting and opposing Proposition 64. If we run the info command we can see that reduces the DataFrame to 860 records.

merged_prop.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 860 entries, 2102 to 83888
Data columns (total 20 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   ocd_prop_id                   860 non-null    object 
 1   calaccess_prop_id             860 non-null    int64  
 2   ccdc_prop_id                  860 non-null    int64  
 3   prop_name                     860 non-null    object 
 4   ccdc_committee_id             860 non-null    int64  
 5   calaccess_committee_id        860 non-null    int64  
 6   committee_name_x              860 non-null    object 
 7   committee_position            860 non-null    object 
 8   committee_name_y              860 non-null    object 
 9   calaccess_filing_id           860 non-null    int64  
 10  date_received                 860 non-null    object 
 11  contributor_lastname          860 non-null    object 
 12  contributor_firstname         750 non-null    object 
 13  contributor_city              860 non-null    object 
 14  contributor_state             860 non-null    object 
 15  contributor_zip               860 non-null    object 
 16  contributor_employer          657 non-null    object 
 17  contributor_occupation        742 non-null    object 
 18  contributor_is_self_employed  860 non-null    bool   
 19  amount                        860 non-null    float64
dtypes: bool(1), float64(1), int64(5), object(13)
memory usage: 135.2+ KB

With a tidy table of Prop. 64 contributions prepared, we’re ready to start interviewing the data.