Merge CSVs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\megas\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:27: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n"
     ]
    },
    {
     "ename": "PermissionError",
     "evalue": "[Errno 13] Permission denied: 'finalMerged.csv'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mPermissionError\u001b[0m                           Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-36-e181866b360a>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m     40\u001b[0m \u001b[0mfinalFile\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfinalFile\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcommonColumns\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     41\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 42\u001b[1;33m \u001b[0mfinalFile\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'finalMerged.csv'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     43\u001b[0m \u001b[1;31m#finalFile = finalFile.drop_duplicates()\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     44\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfinalFile\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36mto_csv\u001b[1;34m(self, path_or_buf, sep, na_rep, float_format, columns, header, index, index_label, mode, encoding, compression, quoting, quotechar, line_terminator, chunksize, tupleize_cols, date_format, doublequote, escapechar, decimal)\u001b[0m\n\u001b[0;32m   1743\u001b[0m                                  \u001b[0mdoublequote\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdoublequote\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1744\u001b[0m                                  escapechar=escapechar, decimal=decimal)\n\u001b[1;32m-> 1745\u001b[1;33m         \u001b[0mformatter\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msave\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1746\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1747\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mpath_or_buf\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\io\\formats\\csvs.py\u001b[0m in \u001b[0;36msave\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    154\u001b[0m             f, handles = _get_handle(self.path_or_buf, self.mode,\n\u001b[0;32m    155\u001b[0m                                      \u001b[0mencoding\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 156\u001b[1;33m                                      compression=self.compression)\n\u001b[0m\u001b[0;32m    157\u001b[0m             \u001b[0mclose\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    158\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\io\\common.py\u001b[0m in \u001b[0;36m_get_handle\u001b[1;34m(path_or_buf, mode, encoding, compression, memory_map, is_text)\u001b[0m\n\u001b[0;32m    398\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0mencoding\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    399\u001b[0m             \u001b[1;31m# Python 3 and encoding\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 400\u001b[1;33m             \u001b[0mf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath_or_buf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmode\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mencoding\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mencoding\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    401\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0mis_text\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    402\u001b[0m             \u001b[1;31m# Python 3 and no explicit encoding\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mPermissionError\u001b[0m: [Errno 13] Permission denied: 'finalMerged.csv'"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "frames = []\n",
    "\n",
    "pd_1_a = pd.read_csv(\"1_a\"+\".csv\")\n",
    "pd_1_b = pd.read_csv(\"1_b\"+\".csv\")\n",
    "pd_2_a = pd.read_csv(\"2_a\"+\".csv\")\n",
    "pd_2_b = pd.read_csv(\"2_b\"+\".csv\")\n",
    "pd_3_a = pd.read_csv(\"3_a\"+\".csv\")\n",
    "pd_3_b = pd.read_csv(\"3_b\"+\".csv\")\n",
    "pd_4_a = pd.read_csv(\"4_a\"+\".csv\")\n",
    "pd_4_b = pd.read_csv(\"4_b\"+\".csv\")\n",
    "pd_5_a = pd.read_csv(\"5_a\"+\".csv\")\n",
    "pd_6 = pd.read_csv(\"6\"+\".csv\")\n",
    "\n",
    "list1 = set(list(pd_1_a))\n",
    "list2 = set(list(pd_1_b))\n",
    "\n",
    "list3 = list(list1.intersection(list2))\n",
    "# print(list3) only incommon are population, hebrew name, locality code\n",
    "\n",
    "merged_1 = pd_1_a.merge(pd_1_b, left_on='LocNameHeb', right_on='LocNameHeb', how='outer')\n",
    "merged_2 = pd_2_a.merge(pd_2_b, left_on='order', right_on='order', how='outer')\n",
    "merged_3 = pd_3_a.merge(pd_3_b, left_on='order', right_on='order', how='outer')\n",
    "merged_4 = pd_4_a.merge(pd_4_b, left_on='LocNameHeb', right_on='LocNameHeb', how='outer')\n",
    "\n",
    "listOfFiles = [merged_1, merged_2, merged_3, merged_4]\n",
    "finalFile = pd.concat(listOfFiles)\n",
    "\n",
    "x1 = set(list(merged_1))\n",
    "x2 = set(list(merged_2))\n",
    "x3 = set(list(merged_3))\n",
    "x4 = set(list(merged_4))\n",
    "\n",
    "commonColumns = u = set.intersection(x1, x2, x3, x4)\n",
    "commonColumns = list(commonColumns)\n",
    "commonColumns.append('LocNameHeb')\n",
    "commonColumns.append('order')\n",
    "\n",
    "\n",
    "finalFile = finalFile[commonColumns]\n",
    "\n",
    "finalFile.to_csv('finalMerged.csv')\n",
    "#finalFile = finalFile.drop_duplicates()\n",
    "print(finalFile.shape)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2322, 100)\n",
      "(2296, 222)\n",
      "(2322, 321)\n"
     ]
    }
   ],
   "source": [
    "merged_3 = pd_3_a.merge(pd_3_b, left_on='order', right_on='order', how='outer')\n",
    "print(pd_3_a.shape)\n",
    "print(pd_3_b.shape)\n",
    "print(merged_3.shape)\n",
    "\n",
    "names1 =set(list(pd_3_a['LocNameHeb']))\n",
    "names2 = set(list(pd_3_b['LocNameHeb']))\n",
    "\n",
    "#print((names1.intersection(names2)))\n",
    "#print(names2-names1)\n",
    "\n",
    "#print(merged_3.head)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "common: 400\n",
      "(161752, 532)\n",
      "(400, 2)\n",
      "(161752, 534)\n"
     ]
    }
   ],
   "source": [
    "file = pd.read_csv(\"tzeva_adom_alerts.csv\")\n",
    "x = (file['place'])\n",
    "x = set(x)\n",
    "\n",
    "y = finalFile['LocNameHeb']\n",
    "y = set(y)\n",
    "\n",
    "common = list(x.intersection(y))\n",
    "print(\"common:\" ,len(common))\n",
    "\n",
    "countsDF = pd.DataFrame()\n",
    "countsDF['place'] = common\n",
    "counts = []\n",
    "\n",
    "for city in common:\n",
    "    counts.append(file.place.value_counts()[city])\n",
    "    \n",
    "countsDF['count'] = counts\n",
    "# countsDF.to_csv('missileStrikeCounts.csv')\n",
    "\n",
    "print(finalFile.shape)\n",
    "print(countsDF.shape)\n",
    "\n",
    "merged2 = finalFile.merge(countsDF, left_on = 'LocNameHeb', right_on = 'place', how = 'left')\n",
    "print(merged2.shape)\n",
    "\n",
    "# merged2.to_csv('mergedCensusMissileStrikeCounts.csv')\n",
    "\n",
    "places = list(merged2['place'])\n",
    "#for item in places:\n",
    "#     print(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "file = pd.read_csv(\"missing_coordinates.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}