<jupyter_start><jupyter_text>Hack for Heat 3: Number of complaints over timeThis time, we're going to look at raw 311 complaint data. The data that I was working with previously was summarized data.This dataset is much bigger, which is nice because it'll give me a chance to maintain my SQL-querying-from-memory-skills.First, we're going to have to load all of this data into a postgres database. I wrote [this](http://pew3.com/311schema.txt) tablebase.  SQL-ing thisThe python library [psycopg2](http://initd.org/psycopg/) lets us work with postgres databases in python. We first create a connection object, that encapsulates the connection to the database, then create a cursor class that lets us make queries from that database.<jupyter_code>connection = psycopg2.connect('dbname = threeoneone user=threeoneoneadmin password=<PASSWORD>')
cursor = connection.cursor()<jupyter_output><empty_output><jupyter_text>For example, we might want to extract the column names from our table:<jupyter_code>cursor.execute('''SELECT * FROM threeoneone.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'service'; ''')
columns = cursor.fetchall()
columns = [x[3] for x in columns]
columns[0:5]<jupyter_output><empty_output><jupyter_text>Complaints over timeLet's start with something simple. First, let's extract a list of all complaints, and the plot the number of complaints by month.<jupyter_code>cursor.execute('''SELECT createddate FROM service;''')
complaintdates = cursor.fetchall()
complaintdates = pd.DataFrame(complaintdates)
complaintdates.head()<jupyter_output><empty_output><jupyter_text>Renaming our column:<jupyter_code>complaintdates.columns = ['Date']<jupyter_output><empty_output><jupyter_text>Next we have to convert these tuples into strings:<jupyter_code>complaintdates['Date'] = [x [0] for x in complaintdates['Date']]<jupyter_output><empty_output><jupyter_text>Normally, if these were strings, we'd use the extract_dates function we wrote in a previous post. However, because I typed these as datetime objects, we can just extract the .year(), .month(), and .day() attributes:<jupyter_code>type(complaintdates['Date'][0])
complaintdates['Day'] = [x.day for x in complaintdates['Date']]
complaintdates['Month'] = [x.month for x in complaintdates['Date']]
complaintdates['Year'] = [x.year for x in complaintdates['Date']]<jupyter_output><empty_output><jupyter_text>This is how many total complaints we have:<jupyter_code>len(complaintdates)<jupyter_output><empty_output><jupyter_text>We can group them by month:<jupyter_code>bymonth = complaintdates.groupby(by='Month').count()
bymonth<jupyter_output><empty_output><jupyter_text>By year:<jupyter_code>byyear = complaintdates.groupby(by='Year').count()
byyear
byday = complaintdates.groupby(by='Day').count()
bydate = complaintdates.groupby(by='Date').count()<jupyter_output><empty_output><jupyter_text>Some matplotlib<jupyter_code>plt.figure(figsize = (12,10))
x = range(0,12)
y = bymonth['Date']
plt.plot(x,y)
plt.figure(figsize = (12,10))
x = range(0,7)
y = byyear['Date']

plt.plot(x,y)
plt.figure(figsize = (12,10))
x = range(0,len(byday))
y = byday['Date']

plt.plot(x,y)<jupyter_output><empty_output><jupyter_text>The sharp decline we see at the end is obviously because not all months have the same number of days.<jupyter_code>plt.figure(figsize=(12,10))
x = range(0,len(bydate))
y = bydate['Year'] #This is arbitrary - year, month, and day are all series that store the counts

plt.plot(x,y)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12051649928263988, 'fraction_numerical': 0.0109038737446198, 'mean_word_length': 7.5024390243902435, 'pattern_counts': {'":': 0, '<': 46, '<?xml version=': 0, '>': 46, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '306515', 'license': 'MIT', 'n_tokens_mistral': 1176, 'n_tokens_neox': 1117, 'n_words': 315, 'path': 'src/bryan analyses/Hack for Heat #3.ipynb', 'repo_name': 'heatseeknyc/data-science', 'size': '3491'}