<jupyter_start><jupyter_text>Import Libraries<jupyter_code># To store data
import pandas as pd

# To connect to postgresql
import psycopg2

# To get plots
import matplotlib.pyplot as plt

# To get nicer plots
import seaborn as sns<jupyter_output><empty_output><jupyter_text>Connection<jupyter_code>def conn():
    conn = psycopg2.connect("user='postgres' host='192.168.0.2' password='<PASSWORD>' dbname=tankerkoenig16")
    return conn.cursor()<jupyter_output><empty_output><jupyter_text>Explore All Gas Stations<jupyter_code>cur = conn()
cur.execute('select * from gas_station')
data = cur.fetchall()

columns = ['id', 'version', 'verion_time', 'name', 'brand', 'Street', 'house_number', 'post_code', 'place', 'public_holiday_identifier', 'lat', 'lng', 'price_in_import', 'price_change', 'open_ts', 'ot_json', 'station_in_import', 'first_active']
ts = pd.DataFrame(data, columns=columns)
print('Shape: {}'.format(ts.shape))
ts.head()
lat, lng = zip(*ts[(ts['lat']!=0) & (ts['lng']!=0)][['lat', 'lng']].values)

title = 'All Gas Stations In Germany: Scatterplot'
plt.figure(figsize=(7, 10))
plt.plot(lng, lat, '.', alpha=0.25)
plt.title(title)
plt.xlabel('lng')
plt.ylabel('lat')
plt.savefig('build/{}.png'.format(title.replace(' ', '_')))
plt.show()
title = 'All Gas Stations In Germany: Hexplot'
sns.jointplot(x='lng', y='lat', data=ts[(ts['lat']!=0) & (ts['lng']!=0)], kind='hex')
plt.savefig('build/{}.png'.format(title.replace(' ', '_')))
plt.show()
title = 'Top 20 Brands By Count: Barplot'
plt.figure(figsize=(15,5))
ts['brand'].value_counts()[:20].plot(kind='barh')
plt.title(title)
plt.xlabel('Brand')
plt.ylabel('Count')
plt.savefig('build/{}.png'.format(title.replace(' ', '_')))
plt.show()<jupyter_output><empty_output><jupyter_text>Explore A Single Gas Stations Diesel Price<jupyter_code>cur = conn()
cur.execute("select diesel, date from gas_station_information_history where stid='2d21eb22-bb59-4a68-b258-45dc6f1b0d18'")
data = cur.fetchall()

columns = ['diesel', 'date']
pr = pd.DataFrame(data, columns=columns)
pr['date'] = pd.to_datetime(pr['date'], utc=True)
pr.set_index('date', inplace=True)
print('Shape: {}'.format(pr.shape))
pr.head()
print('First Entry:\t{}'.format(pr.index.min()))
print('Last Entry:\t{}'.format(pr.index.max()))
title = 'All Diesel Prices For A Random Gas Station Over Time'
pr.plot(figsize=(15,5))
plt.title(title)
plt.xlabel('Date')
plt.ylabel('Price')
plt.savefig('build/{}.png'.format(title.replace(' ', '_')))
plt.show()
day_price = pr.groupby(pd.TimeGrouper('d')).agg({'diesel':['count', 'mean', 'std', 'max', 'min', lambda x: x.max()-x.min()]})
day_price.columns = day_price.columns.get_level_values(1)
day_price.head()
title = 'All Diesel Prices Of A Random Gas Station Grouped By Day: max-mean-min'
day_price[['max', 'mean', 'min']].plot(figsize=(15, 5))
plt.title(title)
plt.xlabel('Date')
plt.ylabel('Price')
plt.savefig('build/{}.png'.format(title.replace(' ', '_')))
plt.show()
title = 'Price-Difference (max-min) Grouped By Day For A Random Gas Station'
plt.figure(figsize=(15,5))
plt.plot(day_price['<lambda>'])
plt.title(title)
plt.xlabel('Day')
plt.ylabel('Price-Difference')
plt.savefig('build/{}.png'.format(title.replace(' ', '_')))
plt.show()
title = 'Overall Diesel Mean Price Per Hour Of A Random Gas Station'
tmp = pr.groupby(pd.TimeGrouper('h')).mean().fillna(method='ffill')
tmp.groupby(tmp.index.hour).mean().plot(figsize=(15,5))
plt.title(title)
plt.xlabel('Hour')
plt.ylabel('Price')
plt.savefig('build/{}.png'.format(title.replace(' ', '_')))
plt.show()
title = 'Diesel Mean Price Per Weekday Of A Random Gas Station'
wd = {0:'Monday', 1:'Tuesday', 2:'Wednesday', 3:'Thursday', 4:'Friday', 5:'Saturday', 6:'Sunday'}
pr.groupby(pr.index.weekday).mean().plot(figsize=(15,5))
plt.title(title)
plt.xlabel('Weekday')
plt.ylabel('Price')
plt.xticks(range(7), [day for key, day in wd.items()])
plt.savefig('build/{}.png'.format(title.replace(' ', '_')))
plt.show()
cur = conn()
cur.execute("select version_time from gas_station where version_time<='2017-01-01 12:00:00' limit 10")
cur.fetchall()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.21402394331785976, 'fraction_numerical': 0.021744441729782557, 'mean_word_length': 7.2044088176352705, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '249638', 'license': 'MIT', 'n_tokens_mistral': 1647, 'n_tokens_neox': 1537, 'n_words': 289, 'path': 'PostgreSQL.ipynb', 'repo_name': 'argo2445/tapra_dohack18', 'size': '4089'}