import pandas as pd
from soc_module import *
import re
Load in alameda geojson file
alameda = geojson.load(open("data/alameda-2010.geojson"))
Write function to filter any old index cols in data
def filter_old_index(name):
if re.match(r"Unnamed: 0.*", name):
return False
return True
Load in survey data
data = pd.read_csv('data/ORIGINAL_RESPONSES.csv', usecols=filter_old_index)
data.head()
Separate new responses from old ones (bc get_coords takes a long time)
old = pd.read_csv("data/all-responses.csv", usecols=filter_old_index)
new = data[~(data["Timestamp"].isin(old["Timestamp"]))]
Get image coordinates - this takes a while, 38 mins when doing it on all ~150 rows
%%time
new_coords = get_coords(new, alameda, "sociology-130ac")
CPU times: user 13.6 ms, sys: 1.31 ms, total: 14.9 ms Wall time: 14.1 ms
bring new coords back into old data, write to csv
data = pd.concat([old, new_coords])#.iloc[1:,]
data.to_csv("data/all-responses.csv", index=False)
Define mapping to rename cols
new_col_names = {
'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)",
'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)",
'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)",
'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)",
' Are there abandoned cars in the neighborhood? How many do you see? ': "Abandoned Cars (Y?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)",
'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)",
'Is there graffiti or evidence of graffiti that has been painted over on buildings, signs, or walls? (Questions 22-23)': "Graffiti (Y/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)",
'Are any commercial/residential buildings being renovated?': "Renovations (Y/N)",
'What kinds of establishments are there on the block face? Select all that apply.': "Types of Establishments",
'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)",
'Is there public transportation available in the block face? ': "Public Transporation Available (Y/N)",
'Are private security guards visible?': "Private Security Visible (Y/N)",
'Is there a police officer visible? ': "Police Visible (Y/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",
'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",
'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",
'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",
'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",
}
Extract class_data and image_data
class_data = data.copy()
class_data = class_data.rename(new_col_names, axis=1)
class_data['Timestamp'] = class_data['Timestamp'].astype('str')
class_data = class_data.iloc[1:,:] # why??
# rekey yes and no responses
for c in class_data.columns:
if "Yes" in set(class_data[c]):
class_data[c] = class_data[c].map({"Yes": 1, "No": 0})
# # limit to submissions from Spring 2018
# class_data['Timestamp'] = [datetime.strptime(time, '%m/%d/%Y %H:%M:%S') for time in class_data['Timestamp']]
# class_data = class_data[[time.year == 2018 for time in class_data['Timestamp']]]
# class_data = class_data.reset_index(drop=True)
image_data = class_data.iloc[:,17:]
image_data['Census Tract'] = class_data['Census Tract']
image_data.head()
Image #1 | Image #1 Address | Other thoughts or comments for Image #1 | Image #2 | Image #2 Address | Other thoughts or comments for Image #2 | Image #3 | Image #3 Address | Other thoughts or comments for Image #3 | Image #4 | ... | Image #4 Address | Image #5 | Image #5 Address | Other thoughts or comments for Image #5 | Image #1 coordinates | Image #2 coordinates | Image #3 coordinates | Image #4 coordinates | Image #5 coordinates | Census Tract | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | https://drive.google.com/open?id=1t7Cm2UkrM467... | 2150 Channing Way, Berkeley, CA, 94704 | I 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, 94704 | This 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, 94704 | https://drive.google.com/open?id=1f2JyD7z6fSZC... | 2121 Channing Way, Berkeley, CA, 94704 | There 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 |
2 | https://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 | ... | NaN | NaN | NaN | NaN | [37.8724895, -122.2684359] | [37.8724895, -122.2684359] | [35.130098959596, -119.460902121212] | NaN | NaN | 4006.00 |
3 | https://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 |
4 | https://drive.google.com/open?id=1DAEeW4g8askp... | 535 8th St, Oakland, CA, 94607 | There were a few of these green dumpsters thro... | https://drive.google.com/open?id=1HNYxCgjW-eQQ... | 517 8th St, Oakland, CA, 94607 | There were some "pay and display" parking tick... | https://drive.google.com/open?id=1kyhPjq_yANAl... | 530 8th St, Oakland, CA, 94607 | This was one of four buildings that seemed aba... | https://drive.google.com/open?id=147w7g4FMLP4X... | ... | 810 Clay St, Oakland, CA 94607 | https://drive.google.com/open?id=177FjcfjP9mOJ... | 512 8th St, Oakland, CA 94607 | I 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 |
5 | https://drive.google.com/open?id=1J7FBN9UWvZIh... | 3033 Shattuck Ave, Berkeley, CA 94705 | View from the corner of Essex and Shattuck | https://drive.google.com/open?id=1CSHGT6G4Tayq... | 3045 Shattuck Ave, Berkeley, CA 94705 | View facing Shattuck Ave | https://drive.google.com/open?id=12aeAsa3F1uLP... | 3051 Shattuck Ave, Berkeley, CA 94705 | Down Shattuck Ave | https://drive.google.com/open?id=1UxrYJd9YcdFy... | ... | 3028 Shattuck Ave, Berkeley, CA 94705 | https://drive.google.com/open?id=1fpi2WbAMu9LV... | 3040 Shattuck Ave, Berkeley, CA 92405 | Shattuck Ave residences | [37.8540256666667, -122.266213] | [37.8536801, -122.266042711164] | [37.8536181290323, -122.266159419355] | [37.8541573333333, -122.266228] | [37.8537052419355, -122.26617166129] | 4239.01 |
5 rows × 21 columns
Select columns for class_data and change some entries of establishments col
class_data = class_data.iloc[:,:17]
class_data["Types of Establishments"] = class_data["Types of Establishments"].str.replace('Bodega, deli, corner-store, convenience store', 'Bodega deli corner-store convenience store')
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')
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)')
image_data.to_csv("data/image_data.csv", index=False)
class_data.to_csv("data/class_data.csv", index=False)