{ "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", " | Image #1 | \n", "Image #1 Address | \n", "Other thoughts or comments for Image #1 | \n", "Image #2 | \n", "Image #2 Address | \n", "Other thoughts or comments for Image #2 | \n", "Image #3 | \n", "Image #3 Address | \n", "Other thoughts or comments for Image #3 | \n", "Image #4 | \n", "... | \n", "Image #4 Address | \n", "Image #5 | \n", "Image #5 Address | \n", "Other thoughts or comments for Image #5 | \n", "Image #1 coordinates | \n", "Image #2 coordinates | \n", "Image #3 coordinates | \n", "Image #4 coordinates | \n", "Image #5 coordinates | \n", "Census Tract | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | \n", "https://drive.google.com/open?id=1t7Cm2UkrM467... | \n", "2150 Channing Way, Berkeley, CA, 94704 | \n", "I found this graffiti on a wall in the parking... | \n", "https://drive.google.com/open?id=1-Kz4-Fbk5G7w... | \n", "Channing Way and Shattuck Ave, Berkeley, CA, 9... | \n", "There is a Ford Bike Station and it is almost ... | \n", "https://drive.google.com/open?id=1zjA9f2t64BX0... | \n", "2344 Fulton St, Berkeley, CA, 94704 | \n", "This is a house for Cal's track and field team. | \n", "https://drive.google.com/open?id=1ZGfWt-nuNBqy... | \n", "... | \n", "Channing Way and Fulton St, Berkeley, CA, 94704 | \n", "https://drive.google.com/open?id=1f2JyD7z6fSZC... | \n", "2121 Channing Way, Berkeley, CA, 94704 | \n", "There is a Buddhist Temple which shows the div... | \n", "[37.8658887573699, -122.266275305354] | \n", "(37.8678899, -122.269247) | \n", "[37.8666288180719, -122.265876910725] | \n", "(37.8678899, -122.269247) | \n", "[37.866019739311, -122.266542125136] | \n", "4229.00 | \n", "
2 | \n", "https://drive.google.com/open?id=1K2U0ccdwjWB5... | \n", "5634 Shattuck Ave. | \n", "This art shop looked out of place next to the ... | \n", "https://drive.google.com/open?id=1ON0rVTsybNXE... | \n", "5699 Shattuck Ave. | \n", "This was the only apparent property that was f... | \n", "https://drive.google.com/open?id=13vvO_O3GiEu0... | \n", "608 Shattuck Ave. | \n", "The side of the block near Shattuck was more d... | \n", "NaN | \n", "... | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "[37.8724895, -122.2684359] | \n", "[37.8724895, -122.2684359] | \n", "[35.130098959596, -119.460902121212] | \n", "NaN | \n", "NaN | \n", "4006.00 | \n", "
3 | \n", "https://drive.google.com/open?id=1bC5tf0RrhU8u... | \n", "2067 University Ave, Berkeley, CA, 94704. | \n", "It seems that the wall was not designed for lo... | \n", "https://drive.google.com/open?id=1lq2-AA8zbS9U... | \n", "2067-2071 University Ave, Berkeley, CA, 94704. | \n", "There were groups of homeless people outside t... | \n", "https://drive.google.com/open?id=1XZ7O9Iv-vJlp... | \n", "2011 Berkeley Way, Berkeley, CA, 94704. | \n", "There are No-parking signs on the fence, but s... | \n", "https://drive.google.com/open?id=1xAhyRXCveyHC... | \n", "... | \n", "2016 Berkeley Way, Berkeley, CA, 94704. | \n", "https://drive.google.com/open?id=1IRztM7ZX4HzL... | \n", "2005 Berkeley Way, Berkeley, CA, 94704. | \n", "The fence and iron gate shows that crimes may ... | \n", "[37.8720195886659, -122.269816641442] | \n", "[37.8720195886659, -122.269816641442] | \n", "[37.8728409227947, -122.270248811613] | \n", "[37.8726479989409, -122.2704598844] | \n", "[37.8728723, -122.2704417] | \n", "4224.00 | \n", "
4 | \n", "https://drive.google.com/open?id=1DAEeW4g8askp... | \n", "535 8th St, Oakland, CA, 94607 | \n", "There were a few of these green dumpsters thro... | \n", "https://drive.google.com/open?id=1HNYxCgjW-eQQ... | \n", "517 8th St, Oakland, CA, 94607 | \n", "There were some \"pay and display\" parking tick... | \n", "https://drive.google.com/open?id=1kyhPjq_yANAl... | \n", "530 8th St, Oakland, CA, 94607 | \n", "This was one of four buildings that seemed aba... | \n", "https://drive.google.com/open?id=147w7g4FMLP4X... | \n", "... | \n", "810 Clay St, Oakland, CA 94607 | \n", "https://drive.google.com/open?id=177FjcfjP9mOJ... | \n", "512 8th St, Oakland, CA 94607 | \n", "I was really surprised to find a temple on thi... | \n", "[37.7964115, -122.2643766] | \n", "[37.7964115, -122.2643766] | \n", "[37.8009167156494, -122.275402395618] | \n", "[37.801272625156, -122.276025874901] | \n", "[37.8008494265101, -122.275235462283] | \n", "4031.00 | \n", "
5 | \n", "https://drive.google.com/open?id=1J7FBN9UWvZIh... | \n", "3033 Shattuck Ave, Berkeley, CA 94705 | \n", "View from the corner of Essex and Shattuck | \n", "https://drive.google.com/open?id=1CSHGT6G4Tayq... | \n", "3045 Shattuck Ave, Berkeley, CA 94705 | \n", "View facing Shattuck Ave | \n", "https://drive.google.com/open?id=12aeAsa3F1uLP... | \n", "3051 Shattuck Ave, Berkeley, CA 94705 | \n", "Down Shattuck Ave | \n", "https://drive.google.com/open?id=1UxrYJd9YcdFy... | \n", "... | \n", "3028 Shattuck Ave, Berkeley, CA 94705 | \n", "https://drive.google.com/open?id=1fpi2WbAMu9LV... | \n", "3040 Shattuck Ave, Berkeley, CA 92405 | \n", "Shattuck Ave residences | \n", "[37.8540256666667, -122.266213] | \n", "[37.8536801, -122.266042711164] | \n", "[37.8536181290323, -122.266159419355] | \n", "[37.8541573333333, -122.266228] | \n", "[37.8537052419355, -122.26617166129] | \n", "4239.01 | \n", "
5 rows × 21 columns
\n", "