# ---
# 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="view-in-github" colab_type="text"
# <a href="https://colab.research.google.com/github/s2t2/tweet-analysis-2020/blob/news/app/news/User_Details_20210806_Exporting_to_CSV.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>

# + [markdown] id="qXTCv562wHXp"
# # Setup

# + colab={"base_uri": "https://localhost:8080/"} id="gFjn74symzsA" outputId="0a61aabf-3e0a-4280-c465-f2057adc2918"
#
# SETUP GOOGLE DRIVE
#

import os
from google.colab import drive

drive.mount('/content/drive')
print(os.getcwd(), os.listdir(os.getcwd())) #> 'content', ['.config', 'drive', 'sample_data']

DIRPATH = '/content/drive/My Drive/NeuralNetworkOpinions/Impeachment'
print(DIRPATH)
os.path.isdir(DIRPATH)

# + colab={"base_uri": "https://localhost:8080/"} id="8r6SH2zV4c0z" outputId="4939e62d-d1c6-471e-9a26-6fa2d976087f"
#
# BIG QUERY CREDENTIALS
#

# google.cloud checks the file at path designated by the GOOGLE_APPLICATION_CREDENTIALS env var
# so we set it here using the shared credentials JSON file from our shared google drive
# and verify it for good measure

GOOGLE_CREDENTIALS_FILEPATH = os.path.join(DIRPATH, "credentials", "tweet-research-shared-268bbccc0aac.json") 
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = GOOGLE_CREDENTIALS_FILEPATH

GOOGLE_APPLICATION_CREDENTIALS = os.getenv("GOOGLE_APPLICATION_CREDENTIALS") # implicit check by google.cloud
print(GOOGLE_APPLICATION_CREDENTIALS) # verification for implicit check
print(os.path.isfile(GOOGLE_APPLICATION_CREDENTIALS)) # verification for implicit check

# + id="GZ4lLKlA5H53"
#
# BIG QUERY SERVICE
#

from google.cloud import bigquery
from pandas import DataFrame

class BigQueryService():
    def __init__(self):
        self.client = bigquery.Client()

    def execute_query(self, sql, verbose=True):
        if verbose == True: 
            print(sql)
        job = self.client.query(sql)
        return job.result()
    
    def query_to_df(self, sql, verbose=True):
        records = [dict(row) for row in list(self.execute_query(sql, verbose=verbose))]
        return DataFrame(records)



# + [markdown] id="Xjh7FqhV77_u"
# # Data Dictionary

# + [markdown] id="K6HmK0498ACX"
#
#
#
# column_name | datatype | description
# --- | --- | ---
# user_id	| INTEGER | unique identifier for each user in our "impeachment 2020" dataset
# created_on	| DATE | date the user was created
# screen_name_count	| INTEGER | number of screen names used 
# screen_names	| STRING | all screen names used
# is_bot	| BOOLEAN | whether or not we classified this user as a "bot" / automated account
# bot_rt_network	| INTEGER | for bots, which retweet network (0:anti-trump, 1:pro-trump)
# is_q	| BOOLEAN | whether or not this user tweeted Q-anon language / hashtags
# q_status_count	| INTEGER | the number of tweets with Q-anon language / hashtags
# status_count	| INTEGER | number of total tweets authoried by this user (in our "impeachment 2020" dataset only)
# rt_count	| INTEGER | number of total retweets authoried by this user (in our "impeachment 2020" dataset only)
# avg_score_lr	| FLOAT | avergage opinion score from our Logistic Regression model (0:anti-trump, 1:pro-trump)
# avg_score_nb	| FLOAT | avergage opinion score from our Naive Bayes model (0:anti-trump, 1:pro-trump)
# avg_score_bert	| FLOAT | avergage opinion score from our BERT Transformer model (0:anti-trump, 1:pro-trump) 
# opinion_community	| INTEGER | binary classification of average opinion (0:anti-trump, 1:pro-trump)
# follower_count	| INTEGER | number of followers (in our "impeachment 2020" dataset only)
# follower_count_b	| INTEGER | ... who are bots
# follower_count_h	| INTEGER | ... who are humans
# friend_count	| INTEGER | number of friends (in our "impeachment 2020" dataset only)
# friend_count_b	| INTEGER | ... who are bots
# friend_count_h	| INTEGER | ... who are humans
# avg_toxicity	| FLOAT | average "toxicity" score from the Detoxify model
# avg_severe_toxicity	| FLOAT | average "sever toxicity" score from the Detoxify model
# avg_insult	| FLOAT | average "insult" score from the Detoxify model
# avg_obscene	| FLOAT | average "obscene" score from the Detoxify model
# avg_threat	| FLOAT | average "threat" score from the Detoxify model
# avg_identity_hate	| FLOAT | average "identity hate" score from the Detoxify model
# urls_shared_count (TODO)	| INTEGER | number of tweets with URLs in them (TODO)
# fact_scored_count	| INTEGER | number of tweets with URL domains that we have rankings for
# avg_fact_score	| FLOAT | average fact score of links shared (1: fake news, 5: mainstream media)

# + [markdown] id="kPM5owKI53BA"
# # CSV Pipeline 

# + colab={"base_uri": "https://localhost:8080/"} id="P1sUiWSd5466" outputId="21921fca-d1b6-4c82-d7a0-dae2b52ac944"
from pandas import read_csv

DESTRUCTIVE = True

csv_filepath = os.path.join(DIRPATH, "user_details_20210806.csv")

if os.path.isfile(csv_filepath) and not DESTRUCTIVE:
    print("FOUND EXISTING CSV FILE!")
    df = read_csv(csv_filepath)
else:
    print("GENERATING NEW CSV FILE!")
    bq_service = BigQueryService()
    sql = f"""
        SELECT *
        FROM `tweet-research-shared.impeachment_2020.user_details_v20210806_slim` u
    """
    df = bq_service.query_to_df(sql)
    df.to_csv(csv_filepath, index=False)


# + colab={"base_uri": "https://localhost:8080/", "height": 260} id="4T7B_KIi9DQr" outputId="3a5e3a7c-a939-4fc1-c66d-97b37c636871"
print(len(df))
df.head()

StarcoderdataJupyterScriptsDedupFiltered