{ "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\n", "import datetime\n", "from datetime import date, datetime" ] }, { "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": 3, "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": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " | Timestamp | \n", "Census Tract | \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? | \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? | \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? | \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? | \n", "Are there abandoned cars in the neighborhood? How many do you see? | \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? | \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) | \n", "Is there graffiti or evidence of graffiti that has been painted over on buildings, signs, or walls? (Questions 22-23) | \n", "... | \n", "Other thoughts or comments for Image #2 | \n", "Image #3 | \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. | \n", "Other thoughts or comments for Image #3 | \n", "Image #4 | \n", "Other thoughts or comments for Image #4 | \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. | \n", "Image #5 | \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. | \n", "Other thoughts or comments for Image #5 | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "10/25/2019 12:52:40 | \n", "4030.0 | \n", "1 | \n", "2 | \n", "1 | \n", "2 | \n", "0 | \n", "4 | \n", "4 | \n", "Yes | \n", "... | \n", "The Salvation Army was closed on this particul... | \n", "https://drive.google.com/open?id=1KY1Mvgc9-eLq... | \n", "601 Webster Street, Oakland, CA, 94609 | \n", "I should have gone in here to try the food, i... | \n", "https://drive.google.com/open?id=1keCgzxOTujwn... | \n", "UGS crew --- possibly a gang or street dance t... | \n", "601 Webster St, Oakland, CA, 94609 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "
1 | \n", "10/25/2019 12:54:25 | \n", "4205.0 | \n", "1 | \n", "1 | \n", "1 | \n", "1 | \n", "0 | \n", "1 | \n", "1 | \n", "No | \n", "... | \n", "NaN | \n", "https://drive.google.com/open?id=1-eg5yVHgMiRt... | \n", "1391 Solano Ave, Albany, CA, 94706 | \n", "NaN | \n", "https://drive.google.com/open?id=1mouPFYSJ16OW... | \n", "NaN | \n", "1391 Solano Ave, Albany, CA, 94706 | \n", "https://drive.google.com/open?id=1ymFvN6smtsGK... | \n", "708 Solano Ave, Albany, CA, 94706 | \n", "NaN | \n", "
2 | \n", "10/25/2019 14:32:16 | \n", "4214.0 | \n", "1 | \n", "1 | \n", "1 | \n", "1 | \n", "0 | \n", "2 | \n", "2 | \n", "No | \n", "... | \n", "Another example of a well-maintained house on ... | \n", "https://drive.google.com/open?id=12Fqi0YrnKfV9... | \n", "1136 Spruce Street, Berkeley, CA, 94704 | \n", "One of many houses that’s currently being reno... | \n", "https://drive.google.com/open?id=189j-3sZAefhs... | \n", "PG&E is currently working on Spruce Street, wi... | \n", "1128 Spruce Street, Berkeley, CA, 94704 | \n", "https://drive.google.com/open?id=1Hn70fF7xfZh1... | \n", "1133 Spruce Street, Berkeley, CA, 94704 | \n", "This house is located at the east side of Spru... | \n", "
3 | \n", "10/25/2019 15:27:57 | \n", "4030.0 | \n", "1 | \n", "2 | \n", "1 | \n", "3 | \n", "0 | \n", "3 | \n", "2 | \n", "Yes | \n", "... | \n", "NaN | \n", "https://drive.google.com/open?id=1ykCymHyvIELa... | \n", "376 8th Street, Oakland, CA, 94607 | \n", "NaN | \n", "https://drive.google.com/open?id=1rfVrj-7QZkbf... | \n", "NaN | \n", "800 Franklin Street, Oakland, CA, 94607 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "
4 | \n", "10/25/2019 21:24:53 | \n", "4213.0 | \n", "1 | \n", "1 | \n", "1 | \n", "1 | \n", "0 | \n", "2 | \n", "4 | \n", "No | \n", "... | \n", "For the stretch from the intersection of Carlo... | \n", "https://drive.google.com/open?id=1S_BJ03A64lP9... | \n", "1647 Hopkins Street, Berkeley, CA, 94707 | \n", "NaN | \n", "https://drive.google.com/open?id=1a4rgJSw2qIMk... | \n", "I felt that this residence was fairly represen... | \n", "1288 Carlotta Avenue, Berkeley, CA, 94707 | \n", "https://drive.google.com/open?id=1clCPXj-QBzMp... | \n", "1611 Hopkins Street, Berkeley, CA, 94707 | \n", "This building had a mattress leaning on one of... | \n", "
5 rows × 32 columns
\n", "\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", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "https://drive.google.com/open?id=1RmWTmeeYjbIg... | \n", "601 Webster St, Oakland, CA, 94609 | \n", "This was one of a few stores that were gated u... | \n", "https://drive.google.com/open?id=1PXD3bu_7j2vV... | \n", "601 Webster Street, Oakland, CA, 94609 | \n", "The Salvation Army was closed on this particul... | \n", "https://drive.google.com/open?id=1KY1Mvgc9-eLq... | \n", "601 Webster Street, Oakland, CA, 94609 | \n", "I should have gone in here to try the food, i... | \n", "https://drive.google.com/open?id=1keCgzxOTujwn... | \n", "... | \n", "601 Webster St, Oakland, CA, 94609 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "[37.798025, -122.272283] | \n", "[37.798025, -122.272283] | \n", "[37.798025, -122.272283] | \n", "[37.798025, -122.272283] | \n", "NaN | \n", "4030 | \n", "
1 | \n", "https://drive.google.com/open?id=1stJ2WAm5XvqD... | \n", "931 Carmel Ave, Albany, CA, 94706 | \n", "NaN | \n", "https://drive.google.com/open?id=1UlT_fZkGjDLH... | \n", "1362 Marin Ave, Albany, CA , 94706 | \n", "NaN | \n", "https://drive.google.com/open?id=1-eg5yVHgMiRt... | \n", "1391 Solano Ave, Albany, CA, 94706 | \n", "NaN | \n", "https://drive.google.com/open?id=1mouPFYSJ16OW... | \n", "... | \n", "1391 Solano Ave, Albany, CA, 94706 | \n", "https://drive.google.com/open?id=1ymFvN6smtsGK... | \n", "708 Solano Ave, Albany, CA, 94706 | \n", "NaN | \n", "[37.8899946030529, -122.289521691888] | \n", "[37.8883455055134, -122.288877697661] | \n", "[37.8908002222222, -122.290461888889] | \n", "[37.8908002222222, -122.290461888889] | \n", "[37.888633027305, -122.307014522052] | \n", "4205 | \n", "
2 | \n", "https://drive.google.com/open?id=1kpIEXVR335w3... | \n", "2200 Los Angeles St, Berkeley, CA, 94704 | \n", "The only house on Los Angeles Street with a si... | \n", "https://drive.google.com/open?id=1zRsqDdsZBeYD... | \n", "2210 Los Angeles Street, Berkeley, CA, 94704 | \n", "Another example of a well-maintained house on ... | \n", "https://drive.google.com/open?id=12Fqi0YrnKfV9... | \n", "1136 Spruce Street, Berkeley, CA, 94704 | \n", "One of many houses that’s currently being reno... | \n", "https://drive.google.com/open?id=189j-3sZAefhs... | \n", "... | \n", "1128 Spruce Street, Berkeley, CA, 94704 | \n", "https://drive.google.com/open?id=1Hn70fF7xfZh1... | \n", "1133 Spruce Street, Berkeley, CA, 94704 | \n", "This house is located at the east side of Spru... | \n", "(37.8883409, -122.270922) | \n", "(37.8883409, -122.270922) | \n", "[37.8876024042097, -122.266919689902] | \n", "[37.8877886656757, -122.266948315416] | \n", "[37.8879082848895, -122.26673971951] | \n", "4214 | \n", "
3 | \n", "https://drive.google.com/open?id=15rdCtKIgX-Fb... | \n", "378 8th Street, Oakland, CA, 94607 | \n", "NaN | \n", "https://drive.google.com/open?id=1hMNbBE8pc9Q8... | \n", "360 8th Street, Oakland, CA, 94607 | \n", "NaN | \n", "https://drive.google.com/open?id=1ykCymHyvIELa... | \n", "376 8th Street, Oakland, CA, 94607 | \n", "NaN | \n", "https://drive.google.com/open?id=1rfVrj-7QZkbf... | \n", "... | \n", "800 Franklin Street, Oakland, CA, 94607 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "[37.7995886695836, -122.272012934734] | \n", "[37.7994604111548, -122.27167993785] | \n", "[37.8020326, -122.2784424] | \n", "[37.799789, -122.272409] | \n", "NaN | \n", "4030 | \n", "
4 | \n", "https://drive.google.com/open?id=1F1B-1oHlh9y6... | \n", "1645 Hopkins Street, Berkeley, CA, 94707 | \n", "Unfortunately, this building was at the end of... | \n", "https://drive.google.com/open?id=1ycZ91fitTHwh... | \n", "1226 Carlotta Avenue, Berkeley, CA, 94707 | \n", "For the stretch from the intersection of Carlo... | \n", "https://drive.google.com/open?id=1S_BJ03A64lP9... | \n", "1647 Hopkins Street, Berkeley, CA, 94707 | \n", "NaN | \n", "https://drive.google.com/open?id=1a4rgJSw2qIMk... | \n", "... | \n", "1288 Carlotta Avenue, Berkeley, CA, 94707 | \n", "https://drive.google.com/open?id=1clCPXj-QBzMp... | \n", "1611 Hopkins Street, Berkeley, CA, 94707 | \n", "This building had a mattress leaning on one of... | \n", "[37.88256465, -122.28065260328] | \n", "[37.8839914809146, -122.28048481675] | \n", "[37.8824167547207, -122.280525537024] | \n", "[37.8826557841063, -122.280673827285] | \n", "[37.8822196, -122.281236836644] | \n", "4213 | \n", "
5 rows × 21 columns
\n", "