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

import pandas as pd

df_data = pd.DataFrame(pd.read_csv("../Data/Framework_Conditions.csv"))

df_data

df_data.isna()

df_data.isnull().values.any()

df_data.isnull().sum()

df_data

df_data.dtypes

df_data = df_data.set_index(["code"])
df_data

df_data = df_data.dropna()

# +
df_data.columns = df_data.columns.str.replace(' ','_').str.lower()

df_data
# -

df_data = df_data.rename(columns=
                         {"economy":"country",
                         "r&d_transfer":"research_and_development"})
df_data



# +
from sqlalchemy import create_engine
import pandas as pd
from db_conn import user_name
from db_conn import password
import psycopg2

conn = psycopg2.connect(
   database="postgres", user=f'{user_name}', password=f'{password}', host='127.0.0.1', port= '5432'
)

conn.autocommit = True

cursor = conn.cursor()

cursor.execute("SELECT datname FROM pg_database;")

list_database = cursor.fetchall()

dbname = "gem_db"

try:

    if (dbname,) not in list_database:

        cur = conn.cursor()
        cur.execute('CREATE DATABASE ' + dbname)

        cur.close()
        conn.close()

        print("Creating Database...")

        engine = create_engine(f'postgresql://{user_name}:{password}@localhost:5432/{dbname}')

        connection = engine.connect()

        print('-'*30)
        print("Creating Tables, Please wait...")
        print('-'*30)

        df_data.to_sql("framework_conditions", engine)
    #         zhvi_complete_df.to_sql('sales',engine)
        print("Table framework_conditions created successfully")

        connection.close()

        print('-'*30)
        print("Database is ready to use.")
    else:
        print("Database is already exists.")
except:
    print("Something went wrong.")
# -



# +
import json

engine = create_engine(f'postgresql://{user_name}:{password}@localhost:5432/gem_db')
connection = engine.connect()

data = pd.read_sql("SELECT * FROM framework_conditions WHERE country= 'United States'",connection)
data = data.set_index(["year"])
print(data)

data = pd.DataFrame.from_dict(data, orient='columns')
print(data.to_json())
# data.values.to_list()
# -



import numpy as np
import json
data = pd.read_sql("SELECT year,financing_for_entrepreneurs FROM framework_conditions WHERE country= 'United States'",connection)
# data = data.set_index(["financing_for_entrepreneurs"])
# data = data.set_index(["year"])
# print(data)
print(data)
x = []
y = []
for index, row in data.iterrows():
    x.append(row["year"])
    y.append(row["financing_for_entrepreneurs"])
f = {}
f["year"]=x
f["test"]=y
#data = pd.DataFrame.from_dict(data, orient='columns')
#data1 = pd.DataFrame(data=x, dtype=int, columns=["year"])
#data1["test"]=y
#data1=data1.set_index("year")
# print(data1.to_json())
#data1 = data1.append(y)
#print(data1)
print(json.dumps(f))
# print(data.to_json())
# json1 = data.to_json()
# print(json1)
# data1.to_json()





df_data.columns




# +
countryData = pd.read_sql("SELECT * FROM framework_conditions WHERE country= 'United States' AND year=2019",connection)

labels=['country', 'financing_for_entrepreneurs','governmental_support_and_policies', 'taxes_and_bureaucracy',
   'governmental_programs','basic_school_entrepreneurial_education_and_training', 'post_school_entrepreneurial_education_and_training', 'research_and_development',
   'commercial_and_professional_infrastructure','internal_market_dynamics', 'internal_market_openness','physical_and_services_infrastructure', 'cultural_and_social_norms']
dataPoints=[]

for item in labels:
    dataPoints.append(countryData[item][0])

c = {}
c["labels"]=labels
c["dataPoints"]=dataPoints
print(countryData)
print(dataPoints)
print(c)
print(json.dumps(c))
# -



# +
indicators={
        'Financing for Entrepreneurs':'financing_for_entrepreneurs',
        'Governmental Support and Policies':'governmental_support_and_policies', 
        'Taxes and Bureaucracy':'taxes_and_bureaucracy',
        'Governmental Programs':'governmental_programs',
        'Basic School Entrepreneurial Education and Training':'basic_school_entrepreneurial_education_and_training', 
        'Post School Entrepreneurial Education and Training':'post_school_entrepreneurial_education_and_training', 
        'Research and Development':'research_and_development',
        'Commercial and Professional Infrastructure':'commercial_and_professional_infrastructure',
        'Internal Market Dynamics':'internal_market_dynamics', 
        'Internal Market Openness':'internal_market_openness',
        'Physical and Services Infrastructure':'physical_and_services_infrastructure', 
        'Cultural and Social Norms':'cultural_and_social_norms'}

keys = []
values=[]

for key in indicators.keys():
    keys.append(key)

    
for value in indicators.values():
    values.append(value)
    
data_dict = {}

# data_dict["country"] = country_list
data_dict["indicator_keys"] = keys
data_dict["indicator_values"] = values
print(keys)
print(data_dict)
# -

d = df_data.loc[df_data["country"] == "United States"]


d.to_html()









StarcoderdataJupyterScriptsDedupFiltered