# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + [markdown] id="jbIYHpra2dRD" colab_type="text"
# # Access to data.

# + id="X33TTOs0AVZ7" colab_type="code" outputId="f873f490-8327-4308-b300-aab859effacb" executionInfo={"status": "ok", "timestamp": 1578627221903, "user_tz": 480, "elapsed": 2692, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mBauDRR6uSGBvI9oYit5cGU6T8SkJRV4CKiB98le2E=s64", "userId": "02236610529346300368"}} colab={"base_uri": "https://localhost:8080/", "height": 33}
# pip install psycopg2-binary.
# !pip install psycopg2-binary

# + id="BWgjEF66_0BS" colab_type="code" colab={}
# imports.
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
from google.cloud import bigquery
from google.oauth2 import service_account

# + id="rNImaNZrAG-S" colab_type="code" colab={}
# connection to postgresss database.
engine = create_engine('postgres://ibnzqkfl:<EMAIL>:5432/ibnzqkfl')

# + id="VtKXPcEcrBVM" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 166} outputId="5ea711a5-dcd9-48d5-995b-4a22a4b4e03d" executionInfo={"status": "error", "timestamp": 1578629940881, "user_tz": 480, "elapsed": 466, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mBauDRR6uSGBvI9oYit5cGU6T8SkJRV4CKiB98le2E=s64", "userId": "02236610529346300368"}}
pg_conn.close

# + id="vHCS8FuyJVXe" colab_type="code" colab={}
pg_conn = engine.connect()

# + id="IyhXPQtqCUGu" colab_type="code" colab={}
 # google service account credentials.
 credentials = service_account.Credentials.from_service_account_file('HackerNews-a13892bba4db.json')
 # label the project.
project_id = 'SaltyNews-HackerNews'

# + id="LJ8RbL8gES8k" colab_type="code" colab={}
# set the bigquery client.
client = bigquery.Client(credentials=credentials, project=project_id)

# + id="wZqm9pPDIF57" colab_type="code" colab={}
# set the referenced dataset and proect from bigquery.
dp_ref = client.dataset('hacker_news', project='bigquery-public-data')

# + id="jwpJ5AvyIOI4" colab_type="code" colab={}
# set the referenced tabe 'comments' from the bigquery hacker news dataset.
table_ref = dp_ref.table('comments')
# get the table from big query.
comments_table = client.get_table(table_ref)
# create the dataframe with 30,000 rows, ElephantSQL limits it 20MB.
HNcommentsDB = client.list_rows(comments_table, max_results=30000).to_dataframe()

# + id="N1f7_z_Ji4I5" colab_type="code" outputId="321fd587-1904-462a-e082-14f529679784" executionInfo={"status": "ok", "timestamp": 1578627305794, "user_tz": 480, "elapsed": 385, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mBauDRR6uSGBvI9oYit5cGU6T8SkJRV4CKiB98le2E=s64", "userId": "02236610529346300368"}} colab={"base_uri": "https://localhost:8080/", "height": 205}
print(HNcommentsDB.shape)
HNcommentsDB.head()

# + id="b1MJppTRx-Bj" colab_type="code" outputId="dfd1c4a2-f7b9-43da-99ab-670ff6f9d3ca" executionInfo={"status": "ok", "timestamp": 1578627310050, "user_tz": 480, "elapsed": 539, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mBauDRR6uSGBvI9oYit5cGU6T8SkJRV4CKiB98le2E=s64", "userId": "02236610529346300368"}} colab={"base_uri": "https://localhost:8080/", "height": 348}
# drop unecessary columns.
HNcommentsDB = HNcommentsDB.drop(columns=['dead', 'deleted', 'by'])
HNcommentsDB = HNcommentsDB.rename(columns={"time":"order"})
# use datetime on time_ts column.
HNcommentsDB['time_ts'] = pd.to_datetime(HNcommentsDB['time_ts'], infer_datetime_format=True)
# seperate the date and time from time_ts column.
HNcommentsDB['date'] = [d.date() for d in HNcommentsDB['time_ts']]
HNcommentsDB['time'] = [d.time() for d in HNcommentsDB['time_ts']]
# sepereate the year, month, day for date column.
HNcommentsDB['year'] = HNcommentsDB['date'].map(lambda x: x.year)
HNcommentsDB['month'] = HNcommentsDB['date'].map(lambda x: x.month)
HNcommentsDB['day'] = HNcommentsDB['date'].map(lambda x: x.day)
# drop the time_ts column.
HNcommentsDB = HNcommentsDB.drop(columns=['time_ts', 'date'])
# reorganize the columns.
HNcommentsDB = HNcommentsDB[['year', 'month', 'day', 'time', 'order', 'author', 'id', 'text', 'ranking']]
# drop any duplicate columns based on id column.
HNcommentsDB.drop_duplicates(subset ="id", keep = "first", inplace = True)
# keep the most frequest 1000 authors and their comments.
n = 1000
frequent_list = HNcommentsDB['author'].value_counts()[:n].index.tolist()
top_commentors = HNcommentsDB[HNcommentsDB['author'].isin(frequent_list)]
# show data frame shape.
print(top_commentors.shape)
# show the data frame with headers.
top_commentors.head(10)

# + id="bM03neWihaw2" colab_type="code" colab={}
top_commentors = top_commentors.reindex(columns = mydf.columns.tolist() + ['newcol1','newcol2'])

# + id="SZMzzp0mN0x1" colab_type="code" outputId="ea45b68b-0756-454b-aed6-c651c5a8cb62" executionInfo={"status": "ok", "timestamp": 1578605362351, "user_tz": 480, "elapsed": 227, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mBauDRR6uSGBvI9oYit5cGU6T8SkJRV4CKiB98le2E=s64", "userId": "02236610529346300368"}} colab={"base_uri": "https://localhost:8080/", "height": 181}
top_commentors.isnull().sum()

# + id="nMgw9FFC5bL-" colab_type="code" colab={}
# convert dataframe to SQL, push to ElephantSQL.
top_commentors.to_sql('HNTopCommentors', con=engine, if_exists='replace', index=False)

# + [markdown] id="W4VjjfDDNqYK" colab_type="text"
#
#
#

StarcoderdataJupyterScriptsDedupFiltered