# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: py_36_env
#     language: python
#     name: py_36_env
# ---

import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns
from matplotlib import pyplot as plt
import numpy as np


# +
import psycopg2
import os
from urllib.parse import urlparse
    
# pyodbc connection string
# connection_string = "Driver={Amazon Redshift (x64)}; Server=openalex-4.cnw77bm9bpcp.us-east-1.redshift.amazonaws.com; Database=dev; UID=awsuser; PWD=<PASSWORD>!; Port=5439"
# conn = pyodbc.connect(connection_string, autocommit=True)

connection_string = urlparse(os.getenv("DATABASE_URL_OPENALEX_REDSHIFT"))
username = connection_string.username
password = <PASSWORD>
database = connection_string.path[1:]
hostname = connection_string.hostname
port = connection_string.port

conn = psycopg2.connect(
    database = database,
    user = username,
    password = password,
    host = hostname,
    port = port
)


# +

# SQL query to Dremio
sql_query = r""" SELECT * 
    FROM util.temp_data_estimated_citation 
    where citation_count > 0
    order by random() 
    limit 10000"""

df = pd.read_sql(sql_query, conn)

# -

df.hist(figsize=(12,10))


df

# df.drop(['dteday', 'instant'], axis=1, inplace=True)
sns.jointplot('citation_count','estimated_citation', data=df)

sns.heatmap(df.corr(), annot=True, linewidth=2, cmap=plt.cm.Blues)


# +
import math

df = pd.read_sql(sql_query, conn)

# - single author or not
# - log years since published
# - is it a book, monograph, report, or book-chapter
# - is it a journal-article (because interacts with publishers and big journals below)
# - is it a big publisher
# - lookup for big journals , maybe using 122 journals with avg multiplier in the last 10 years with multiplier over 1.05

journals = """64187185
125754415
3121261024
21442059
1010394304
3880285
9692511
13479253
24807848
45305740
98026630
111155417
2493613807
140251998
104917558
49861241
109565702
177147899""".split()

publishers = """Society for Neuroscience
Association for Computing Machinery
Journal of Bone and Joint Surgery
American Diabetes Association
American Society for Microbiology
American Economic Association
American Psychological Association
EMBO
Institute for Operations Research and the Management Sciences
Cold Spring Harbor Laboratory
The Endocrine Society
The Rockefeller University Press
American Society for Clinical Investigation
The American Association of Immunologists
Annual Reviews
Ovid Technologies Wolters Kluwer -American Heart Association
Proceedings of the National Academy of Sciences""".split()

df['log_citation_count'] = [math.log10(count+1.0) for count in df['citation_count']]
df['log_estimated_citation'] = [math.log10(count+1.0) for count in df['estimated_citation']]

if True:
    df['log_years_old'] = [math.log10(age+1.0) for age in df['years_old']]
    # df['log_num_authors'] = np.log(df.num_authors+0.01)
#     df['is_single_author'] = [1 if n > 1 else 0 for n in df['num_authors']]
#     df['has_references'] = [1 if n > 1 else 0 for n in df['reference_count']]
    # df['log_reference_count'] = np.log(df.reference_count+0.01)
#     df['is_journal'] = [1 if g == 'journal-article' else 0 for g in df['genre']]
#     df['is_other_cited_genre'] = [1 if g in ['book', 'report', 'monograph', 'book-chapter'] else 0 for g in df['genre']]
#     df['boost_journal'] = [1 if str(j) in journals else 0 for j in df['journal_id']]
    df['big_publisher'] = [1 if p in publishers else 0 for p in df['publisher']]


# df.drop(['years_old', 'reference_count', 'num_authors', 'paper_id', 'genre', 'display_name', 'publisher', 'multiplier', 'journal_id'], axis=1, inplace=True)

# df.drop(['paper_id', 'genre', 'display_name', 'publisher', 'multiplier', 'journal_id'], axis=1, inplace=True)

if False:
    df.drop(['citation_count', 'estimated_citation', 'years_old', 'reference_count', 'num_authors', 'paper_id', 'genre', 'display_name', 'publisher', 'multiplier', 'journal_id'], axis=1, inplace=True)
else:
    df.drop(['citation_count', 'estimated_citation', 'years_old', 'reference_count', 'num_authors', 'paper_id', 'genre', 'display_name', 'publisher', 'multiplier', 'journal_id'], axis=1, inplace=True)
    
# -

df.describe()
  

# +
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler
# data features and target feature

# x = df.drop('estimated_citation', axis=1)
# y = df['estimated_citation']

x = df.drop('log_estimated_citation', axis=1)
y = df['log_estimated_citation']


# split data
x_train, x_test, y_train, y_test = train_test_split(x,
                                                   y,
                                                   random_state=0,
                                                   test_size=0.3)

# +
from sklearn.linear_model import LinearRegression
from sklearn import metrics

# Linear Regression Model

# model and fit
model = LinearRegression(fit_intercept=False)
model.fit(x_train, y_train)
# prediction
predictions = model.predict(x_test)
# plot prediction
plt.scatter(y_test, predictions)
plt.title('Linear Regression Model')
plt.xlabel("Test")
plt.ylabel("Prediction")
plt.grid(True)
plt.show()
# metrics
print('Accuracy metrics:')
print('MAE: ', metrics.mean_absolute_error(y_test, predictions))
print('MSE: ', metrics.mean_squared_error(y_test, predictions))
print('Root MSE: ', np.sqrt(metrics.mean_squared_error(y_test,
                                                      predictions)))
print('Score: ', model.score(x_test, y_test))



# -

model.feature_names_in_

model.coef_


pow(10, model.predict(np.array([[math.log10(1000), math.log10(10), 1]])))

pow(10, math.log10(1000) * 1.07156829 + math.log10(10) * -0.02960674 + math.log10(10) * 0.07476177)





StarcoderdataJupyterScriptsDedupFiltered