{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Clean & Extract Photo Coordinates of data" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "from soc_module import *\n", "import re" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Load in alameda geojson file" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "alameda = geojson.load(open(\"data/alameda-2010.geojson\"))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Write function to filter any old index cols in data" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [], "source": [ "def filter_old_index(name):\n", " if re.match(r\"Unnamed: 0.*\", name):\n", " return False\n", " return True" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Load in survey data" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [], "source": [ "data = pd.read_csv('data/ORIGINAL_RESPONSES.csv', usecols=filter_old_index)\n", "data.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Separate new responses from old ones (bc get_coords takes a long time)" ] }, { "cell_type": "code", "execution_count": 60, "metadata": {}, "outputs": [], "source": [ "old = pd.read_csv(\"data/all-responses.csv\", usecols=filter_old_index)\n", "new = data[~(data[\"Timestamp\"].isin(old[\"Timestamp\"]))]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Get image coordinates - this takes a while, 38 mins when doing it on all ~150 rows" ] }, { "cell_type": "code", "execution_count": 61, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CPU times: user 13.6 ms, sys: 1.31 ms, total: 14.9 ms\n", "Wall time: 14.1 ms\n" ] } ], "source": [ "%%time\n", "new_coords = get_coords(new, alameda, \"sociology-130ac\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "bring new coords back into old data, write to csv" ] }, { "cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [], "source": [ "data = pd.concat([old, new_coords])#.iloc[1:,]\n", "data.to_csv(\"data/all-responses.csv\", index=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Define mapping to rename cols" ] }, { "cell_type": "code", "execution_count": 82, "metadata": {}, "outputs": [], "source": [ "new_col_names = {\n", " 'On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how many empty beer or liquor bottles are visible in streets, yards, or alleys? ': \"Amount of Beer/Liquor Bottles (1 to 5 scale)\",\n", " 'On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how many cigarette or cigar butts or discarded cigarette packages are on the sidewalk or in the gutters? ': \"Amount of Cigarette/Cigar Butts (1 to 5 scale)\",\n", " 'On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how many condoms are present on the sidewalk, in the gutters, or street of block face? ': \"Amount of Condoms (1 to 5 scale)\",\n", " 'On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how much garbage, litter, or broken glass in the street or on the sidewalks? ': \"Amount of Garbage (1 to 5 scale)\",\n", " ' Are there abandoned cars in the neighborhood? How many do you see? ': \"Abandoned Cars (Y?N)\",\n", " 'On a scale of 1-5 where 1 is \"Friendly Responses / Greetings / Helpful\" and 5 is \"Treated with Suspicion\", How were you regarded by the people in the block face?': \"Treatment (1 (Friendly) to 5 (Suspicion) scale)\",\n", " 'On a scale of 1 - 4, where 1 is \"Very well kept / good condition\" and 4 is \"Poor / badly deteriorated condition\", in general, how would you rate the condition of buildings on the block face? (includes residential buildings, recreational facilities, manufacturing plants, business / industrial headquarters, etc)': \"Condition of Buildings (1 (Good) to 4 (Poor) scale)\",\n", " 'Is there graffiti or evidence of graffiti that has been painted over on buildings, signs, or walls? (Questions 22-23)': \"Graffiti (Y/N)\",\n", " 'On a scale of 1 - 4, where 1 is \"No fencing\" and 4 is \"High mesh fencing with barbed wire or spiked tops\", is there fencing and what kind? (includes all property)': \"Fenching (1 (None) to 4 (High mesh) scale)\",\n", " 'Are any commercial/residential buildings being renovated?': \"Renovations (Y/N)\",\n", " 'What kinds of establishments are there on the block face? Select all that apply.': \"Types of Establishments\",\n", " 'On a scale of 1-3, where 1 is \"Few or none\" and 3 is a \"Most/all of it\", how many trees are linking the street of the block face? ': \"Amount of Trees Linked the Block Fence (1 (Few) to 3 (Most) scale)\",\n", " 'Is there public transportation available in the block face? ': \"Public Transporation Available (Y/N)\",\n", " 'Are private security guards visible?': \"Private Security Visible (Y/N)\",\n", " 'Is there a police officer visible? ': \"Police Visible (Y/N)\",\n", " 'Full Address of Block Face in Image #1 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #1 Address\",\n", " 'Full Address of Block Face in Image #2 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #2 Address\",\n", " 'Full Address of Block Face in Image #3 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #3 Address\",\n", " 'Full Address of Block Face in Image #4 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #4 Address\",\n", " 'Full Address of Block Face in Image #5 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #5 Address\",\n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Extract class_data and image_data" ] }, { "cell_type": "code", "execution_count": 83, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Image #1Image #1 AddressOther thoughts or comments for Image #1Image #2Image #2 AddressOther thoughts or comments for Image #2Image #3Image #3 AddressOther thoughts or comments for Image #3Image #4...Image #4 AddressImage #5Image #5 AddressOther thoughts or comments for Image #5Image #1 coordinatesImage #2 coordinatesImage #3 coordinatesImage #4 coordinatesImage #5 coordinatesCensus Tract
1https://drive.google.com/open?id=1t7Cm2UkrM467...2150 Channing Way, Berkeley, CA, 94704I found this graffiti on a wall in the parking...https://drive.google.com/open?id=1-Kz4-Fbk5G7w...Channing Way and Shattuck Ave, Berkeley, CA, 9...There is a Ford Bike Station and it is almost ...https://drive.google.com/open?id=1zjA9f2t64BX0...2344 Fulton St, Berkeley, CA, 94704This is a house for Cal's track and field team.https://drive.google.com/open?id=1ZGfWt-nuNBqy......Channing Way and Fulton St, Berkeley, CA, 94704https://drive.google.com/open?id=1f2JyD7z6fSZC...2121 Channing Way, Berkeley, CA, 94704There is a Buddhist Temple which shows the div...[37.8658887573699, -122.266275305354](37.8678899, -122.269247)[37.8666288180719, -122.265876910725](37.8678899, -122.269247)[37.866019739311, -122.266542125136]4229.00
2https://drive.google.com/open?id=1K2U0ccdwjWB5...5634 Shattuck Ave.This art shop looked out of place next to the ...https://drive.google.com/open?id=1ON0rVTsybNXE...5699 Shattuck Ave.This was the only apparent property that was f...https://drive.google.com/open?id=13vvO_O3GiEu0...608 Shattuck Ave.The side of the block near Shattuck was more d...NaN...NaNNaNNaNNaN[37.8724895, -122.2684359][37.8724895, -122.2684359][35.130098959596, -119.460902121212]NaNNaN4006.00
3https://drive.google.com/open?id=1bC5tf0RrhU8u...2067 University Ave, Berkeley, CA, 94704.It seems that the wall was not designed for lo...https://drive.google.com/open?id=1lq2-AA8zbS9U...2067-2071 University Ave, Berkeley, CA, 94704.There were groups of homeless people outside t...https://drive.google.com/open?id=1XZ7O9Iv-vJlp...2011 Berkeley Way, Berkeley, CA, 94704.There are No-parking signs on the fence, but s...https://drive.google.com/open?id=1xAhyRXCveyHC......2016 Berkeley Way, Berkeley, CA, 94704.https://drive.google.com/open?id=1IRztM7ZX4HzL...2005 Berkeley Way, Berkeley, CA, 94704.The fence and iron gate shows that crimes may ...[37.8720195886659, -122.269816641442][37.8720195886659, -122.269816641442][37.8728409227947, -122.270248811613][37.8726479989409, -122.2704598844][37.8728723, -122.2704417]4224.00
4https://drive.google.com/open?id=1DAEeW4g8askp...535 8th St, Oakland, CA, 94607There were a few of these green dumpsters thro...https://drive.google.com/open?id=1HNYxCgjW-eQQ...517 8th St, Oakland, CA, 94607There were some \"pay and display\" parking tick...https://drive.google.com/open?id=1kyhPjq_yANAl...530 8th St, Oakland, CA, 94607This was one of four buildings that seemed aba...https://drive.google.com/open?id=147w7g4FMLP4X......810 Clay St, Oakland, CA 94607https://drive.google.com/open?id=177FjcfjP9mOJ...512 8th St, Oakland, CA 94607I was really surprised to find a temple on thi...[37.7964115, -122.2643766][37.7964115, -122.2643766][37.8009167156494, -122.275402395618][37.801272625156, -122.276025874901][37.8008494265101, -122.275235462283]4031.00
5https://drive.google.com/open?id=1J7FBN9UWvZIh...3033 Shattuck Ave, Berkeley, CA 94705View from the corner of Essex and Shattuckhttps://drive.google.com/open?id=1CSHGT6G4Tayq...3045 Shattuck Ave, Berkeley, CA 94705View facing Shattuck Avehttps://drive.google.com/open?id=12aeAsa3F1uLP...3051 Shattuck Ave, Berkeley, CA 94705Down Shattuck Avehttps://drive.google.com/open?id=1UxrYJd9YcdFy......3028 Shattuck Ave, Berkeley, CA 94705https://drive.google.com/open?id=1fpi2WbAMu9LV...3040 Shattuck Ave, Berkeley, CA 92405Shattuck Ave residences[37.8540256666667, -122.266213][37.8536801, -122.266042711164][37.8536181290323, -122.266159419355][37.8541573333333, -122.266228][37.8537052419355, -122.26617166129]4239.01
\n", "

5 rows × 21 columns

\n", "
" ], "text/plain": [ " Image #1 \\\n", "1 https://drive.google.com/open?id=1t7Cm2UkrM467... \n", "2 https://drive.google.com/open?id=1K2U0ccdwjWB5... \n", "3 https://drive.google.com/open?id=1bC5tf0RrhU8u... \n", "4 https://drive.google.com/open?id=1DAEeW4g8askp... \n", "5 https://drive.google.com/open?id=1J7FBN9UWvZIh... \n", "\n", " Image #1 Address \\\n", "1 2150 Channing Way, Berkeley, CA, 94704 \n", "2 5634 Shattuck Ave. \n", "3 2067 University Ave, Berkeley, CA, 94704. \n", "4 535 8th St, Oakland, CA, 94607 \n", "5 3033 Shattuck Ave, Berkeley, CA 94705 \n", "\n", " Other thoughts or comments for Image #1 \\\n", "1 I found this graffiti on a wall in the parking... \n", "2 This art shop looked out of place next to the ... \n", "3 It seems that the wall was not designed for lo... \n", "4 There were a few of these green dumpsters thro... \n", "5 View from the corner of Essex and Shattuck \n", "\n", " Image #2 \\\n", "1 https://drive.google.com/open?id=1-Kz4-Fbk5G7w... \n", "2 https://drive.google.com/open?id=1ON0rVTsybNXE... \n", "3 https://drive.google.com/open?id=1lq2-AA8zbS9U... \n", "4 https://drive.google.com/open?id=1HNYxCgjW-eQQ... \n", "5 https://drive.google.com/open?id=1CSHGT6G4Tayq... \n", "\n", " Image #2 Address \\\n", "1 Channing Way and Shattuck Ave, Berkeley, CA, 9... \n", "2 5699 Shattuck Ave. \n", "3 2067-2071 University Ave, Berkeley, CA, 94704. \n", "4 517 8th St, Oakland, CA, 94607 \n", "5 3045 Shattuck Ave, Berkeley, CA 94705 \n", "\n", " Other thoughts or comments for Image #2 \\\n", "1 There is a Ford Bike Station and it is almost ... \n", "2 This was the only apparent property that was f... \n", "3 There were groups of homeless people outside t... \n", "4 There were some \"pay and display\" parking tick... \n", "5 View facing Shattuck Ave \n", "\n", " Image #3 \\\n", "1 https://drive.google.com/open?id=1zjA9f2t64BX0... \n", "2 https://drive.google.com/open?id=13vvO_O3GiEu0... \n", "3 https://drive.google.com/open?id=1XZ7O9Iv-vJlp... \n", "4 https://drive.google.com/open?id=1kyhPjq_yANAl... \n", "5 https://drive.google.com/open?id=12aeAsa3F1uLP... \n", "\n", " Image #3 Address \\\n", "1 2344 Fulton St, Berkeley, CA, 94704 \n", "2 608 Shattuck Ave. \n", "3 2011 Berkeley Way, Berkeley, CA, 94704. \n", "4 530 8th St, Oakland, CA, 94607 \n", "5 3051 Shattuck Ave, Berkeley, CA 94705 \n", "\n", " Other thoughts or comments for Image #3 \\\n", "1 This is a house for Cal's track and field team. \n", "2 The side of the block near Shattuck was more d... \n", "3 There are No-parking signs on the fence, but s... \n", "4 This was one of four buildings that seemed aba... \n", "5 Down Shattuck Ave \n", "\n", " Image #4 ... \\\n", "1 https://drive.google.com/open?id=1ZGfWt-nuNBqy... ... \n", "2 NaN ... \n", "3 https://drive.google.com/open?id=1xAhyRXCveyHC... ... \n", "4 https://drive.google.com/open?id=147w7g4FMLP4X... ... \n", "5 https://drive.google.com/open?id=1UxrYJd9YcdFy... ... \n", "\n", " Image #4 Address \\\n", "1 Channing Way and Fulton St, Berkeley, CA, 94704 \n", "2 NaN \n", "3 2016 Berkeley Way, Berkeley, CA, 94704. \n", "4 810 Clay St, Oakland, CA 94607 \n", "5 3028 Shattuck Ave, Berkeley, CA 94705 \n", "\n", " Image #5 \\\n", "1 https://drive.google.com/open?id=1f2JyD7z6fSZC... \n", "2 NaN \n", "3 https://drive.google.com/open?id=1IRztM7ZX4HzL... \n", "4 https://drive.google.com/open?id=177FjcfjP9mOJ... \n", "5 https://drive.google.com/open?id=1fpi2WbAMu9LV... \n", "\n", " Image #5 Address \\\n", "1 2121 Channing Way, Berkeley, CA, 94704 \n", "2 NaN \n", "3 2005 Berkeley Way, Berkeley, CA, 94704. \n", "4 512 8th St, Oakland, CA 94607 \n", "5 3040 Shattuck Ave, Berkeley, CA 92405 \n", "\n", " Other thoughts or comments for Image #5 \\\n", "1 There is a Buddhist Temple which shows the div... \n", "2 NaN \n", "3 The fence and iron gate shows that crimes may ... \n", "4 I was really surprised to find a temple on thi... \n", "5 Shattuck Ave residences \n", "\n", " Image #1 coordinates \\\n", "1 [37.8658887573699, -122.266275305354] \n", "2 [37.8724895, -122.2684359] \n", "3 [37.8720195886659, -122.269816641442] \n", "4 [37.7964115, -122.2643766] \n", "5 [37.8540256666667, -122.266213] \n", "\n", " Image #2 coordinates \\\n", "1 (37.8678899, -122.269247) \n", "2 [37.8724895, -122.2684359] \n", "3 [37.8720195886659, -122.269816641442] \n", "4 [37.7964115, -122.2643766] \n", "5 [37.8536801, -122.266042711164] \n", "\n", " Image #3 coordinates \\\n", "1 [37.8666288180719, -122.265876910725] \n", "2 [35.130098959596, -119.460902121212] \n", "3 [37.8728409227947, -122.270248811613] \n", "4 [37.8009167156494, -122.275402395618] \n", "5 [37.8536181290323, -122.266159419355] \n", "\n", " Image #4 coordinates \\\n", "1 (37.8678899, -122.269247) \n", "2 NaN \n", "3 [37.8726479989409, -122.2704598844] \n", "4 [37.801272625156, -122.276025874901] \n", "5 [37.8541573333333, -122.266228] \n", "\n", " Image #5 coordinates Census Tract \n", "1 [37.866019739311, -122.266542125136] 4229.00 \n", "2 NaN 4006.00 \n", "3 [37.8728723, -122.2704417] 4224.00 \n", "4 [37.8008494265101, -122.275235462283] 4031.00 \n", "5 [37.8537052419355, -122.26617166129] 4239.01 \n", "\n", "[5 rows x 21 columns]" ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "class_data = data.copy()\n", "\n", "class_data = class_data.rename(new_col_names, axis=1)\n", "\n", "class_data['Timestamp'] = class_data['Timestamp'].astype('str')\n", "class_data = class_data.iloc[1:,:] # why??\n", "\n", "# rekey yes and no responses\n", "for c in class_data.columns:\n", " if \"Yes\" in set(class_data[c]):\n", " class_data[c] = class_data[c].map({\"Yes\": 1, \"No\": 0})\n", "\n", "# # limit to submissions from Spring 2018\n", "# class_data['Timestamp'] = [datetime.strptime(time, '%m/%d/%Y %H:%M:%S') for time in class_data['Timestamp']]\n", "# class_data = class_data[[time.year == 2018 for time in class_data['Timestamp']]]\n", "# class_data = class_data.reset_index(drop=True)\n", "\n", "image_data = class_data.iloc[:,17:]\n", "\n", "image_data['Census Tract'] = class_data['Census Tract']\n", "image_data.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Select columns for class_data and change some entries of establishments col" ] }, { "cell_type": "code", "execution_count": 84, "metadata": {}, "outputs": [], "source": [ "class_data = class_data.iloc[:,:17]\n", "\n", "class_data[\"Types of Establishments\"] = class_data[\"Types of Establishments\"].str.replace('Bodega, deli, corner-store, convenience store', 'Bodega deli corner-store convenience store')\n", "class_data[\"Types of Establishments\"] = class_data[\"Types of Establishments\"].str.replace('Payday lenders, check cashers, or pawn shops', 'Payday lenders check cashers or pawn shops')\n", "class_data[\"Types of Establishments\"] = class_data[\"Types of Establishments\"].str.replace('Professional offices \\(doctor, dentist, lawyer, accountant, real estate\\)', 'Professional offices (doctor dentist lawyer accountant real estate)')" ] }, { "cell_type": "code", "execution_count": 85, "metadata": {}, "outputs": [], "source": [ "image_data.to_csv(\"data/image_data.csv\", index=False)\n", "class_data.to_csv(\"data/class_data.csv\", index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "@webio": { "lastCommId": null, "lastKernelId": null }, "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.6.5" }, "notify_time": "5", "varInspector": { "cols": { "lenName": 16, "lenType": 16, "lenVar": 40 }, "kernels_config": { "python": { "delete_cmd_postfix": "", "delete_cmd_prefix": "del ", "library": "var_list.py", "varRefreshCmd": "print(var_dic_list())" }, "r": { "delete_cmd_postfix": ") ", "delete_cmd_prefix": "rm(", "library": "var_list.r", "varRefreshCmd": "cat(var_dic_list()) " } }, "types_to_exclude": [ "module", "function", "builtin_function_or_method", "instance", "_Feature" ], "window_display": false } }, "nbformat": 4, "nbformat_minor": 2 }