data_combiner.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import glob"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Contribution - Data Combination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = r'Contributions'\n",
    "all_files = glob.glob(path + \"/*.csv\")\n",
    "\n",
    "li = []\n",
    "\n",
    "for filename in all_files:\n",
    "    df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
    "    li.append(df)\n",
    "\n",
    "contribution_data = pd.concat(li, axis=0, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(contribution_data.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Contribution - Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "contribution_data.info()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "contribution_data.to_csv('contribution_data.csv')"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Expenditures - Data Combination"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 13935: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 41416: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: DtypeWarning: Columns (10) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 29707: expected 18 fields, saw 19\\n'\n",
      "b'Skipping line 34228: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: DtypeWarning: Columns (10) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 37869: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 10676: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 6922: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 18155: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 28044: expected 18 fields, saw 19\\nSkipping line 28147: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: DtypeWarning: Columns (14) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 3796: expected 18 fields, saw 19\\nSkipping line 3797: expected 18 fields, saw 19\\nSkipping line 5911: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 32685: expected 18 fields, saw 19\\nSkipping line 32686: expected 18 fields, saw 19\\nSkipping line 32687: expected 18 fields, saw 20\\nSkipping line 32688: expected 18 fields, saw 20\\n'\n",
      "b'Skipping line 39538: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 21757: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "b'Skipping line 46532: expected 18 fields, saw 19\\nSkipping line 46533: expected 18 fields, saw 19\\n'\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: DtypeWarning: Columns (10) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
      "/var/folders/8s/t2_j_2bj0v940314cf44s5k80000gn/T/ipykernel_10282/1357906399.py:7: DtypeWarning: Columns (10) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n"
     ]
    }
   ],
   "source": [
    "path = r'Expenditures'\n",
    "all_files = glob.glob(path + \"/*.csv\")\n",
    "\n",
    "li = []\n",
    "\n",
    "for filename in all_files:\n",
    "    df = pd.read_csv(filename, index_col=None, header=0, encoding='latin-1', error_bad_lines=False)\n",
    "    li.append(df)\n",
    "\n",
    "expenditure_data = pd.concat(li, axis=0, ignore_index=True)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  FileNumber  CommitteeType                                Committee  \\\n",
      "0         17  Regular Party  Indiana Republican State Committee, Inc   \n",
      "1         17  Regular Party  Indiana Republican State Committee, Inc   \n",
      "2         17  Regular Party  Indiana Republican State Committee, Inc   \n",
      "3         17  Regular Party  Indiana Republican State Committee, Inc   \n",
      "4         17  Regular Party  Indiana Republican State Committee, Inc   \n",
      "\n",
      "  CandidateName ExpenditureCode                                      Name  \\\n",
      "0           NaN      Operations  Anthem Blue Cross Blue Shield of Indiana   \n",
      "1           NaN      Operations  INDIANA REPUBLICAN STATE COMMITTEE, INC.   \n",
      "2           NaN      Operations               Ft. Harrison State Park Inn   \n",
      "3           NaN      Operations                 Principal Financial Group   \n",
      "4           NaN      Unitemized                           Rent-A-Bit Inc.   \n",
      "\n",
      "                            Address          City State    Zip Occupation  \\\n",
      "0                  220 Virginia Ave  Indianapolis    IN  46204      Other   \n",
      "1  101 WEST OHIO STREET, SUITE 2200  INDIANAPOLIS    IN  46204      Other   \n",
      "2                    5830 N Post Rd  Indianapolis    IN  46216      Other   \n",
      "3                      PO Box 10372    Des Moines    IA  50306        NaN   \n",
      "4    8444 Castlewood Dr, Suite 1400  Indianapolis    IN  46250      Other   \n",
      "\n",
      "  OfficeSought ExpenditureType Description Purpose    Amount  \\\n",
      "0          NaN          Direct         NaN     NaN    429.46   \n",
      "1          NaN          Direct         NaN     NaN  37530.94   \n",
      "2          NaN          Direct         NaN     NaN    923.15   \n",
      "3          NaN          Direct         NaN     NaN    657.48   \n",
      "4          NaN      Unitemized         NaN     NaN     48.15   \n",
      "\n",
      "      Expenditure_Date  Amended  \n",
      "0  2021-01-04 00:00:00      0.0  \n",
      "1  2021-01-04 00:00:00      0.0  \n",
      "2  2021-01-04 00:00:00      0.0  \n",
      "3  2021-01-04 00:00:00      0.0  \n",
      "4  2021-01-04 00:00:00      0.0  \n"
     ]
    }
   ],
   "source": [
    "print(expenditure_data.head())"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 653539 entries, 0 to 653538\n",
      "Data columns (total 18 columns):\n",
      " #   Column            Non-Null Count   Dtype  \n",
      "---  ------            --------------   -----  \n",
      " 0   FileNumber        653539 non-null  object \n",
      " 1   CommitteeType     653535 non-null  object \n",
      " 2   Committee         653383 non-null  object \n",
      " 3   CandidateName     283879 non-null  object \n",
      " 4   ExpenditureCode   653531 non-null  object \n",
      " 5   Name              641152 non-null  object \n",
      " 6   Address           594693 non-null  object \n",
      " 7   City              613691 non-null  object \n",
      " 8   State             617185 non-null  object \n",
      " 9   Zip               592545 non-null  object \n",
      " 10  Occupation        30883 non-null   object \n",
      " 11  OfficeSought      134302 non-null  object \n",
      " 12  ExpenditureType   653521 non-null  object \n",
      " 13  Description       894 non-null     object \n",
      " 14  Purpose           334056 non-null  object \n",
      " 15  Amount            653526 non-null  object \n",
      " 16  Expenditure_Date  651641 non-null  object \n",
      " 17  Amended           653515 non-null  float64\n",
      "dtypes: float64(1), object(17)\n",
      "memory usage: 89.8+ MB\n"
     ]
    }
   ],
   "source": [
    "expenditure_data.info()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "expenditure_data.to_csv('expenditure_data.csv')"
   ],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.12 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "c781c38cc189b27740678b404b8ae9f6d98515be7375d30988f0e032192a35f7"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}