GridDBクラウドとPythonを用いた風力発電の分析

import os
import numpy as np
import pandas as pd
import nums_from_string as ns
from IPython.core.display import Image, display
import seaborn as sns
from matplotlib import pyplot as plt
from mapboxgl.utils import *
from mapboxgl.viz import *
from matplotlib.dates import DateFormatter
import plotly.graph_objects as go # For the map
sns.set()

Turbine_Data = pd.read_excel("Turbine_Data.xlsx", header=0,keep_default_na=True, index_col=None)

# Dropping rows that have the operational year (p_year) that are empty
print(len(Turbine_Data))
Turbine_Data.dropna(subset=['p_year'],inplace=True)
print(len(Turbine_Data))

#Check for missing values and fill it
Turbine_Data.fillna(Turbine_Data.median(),inplace=True)
Turbine_Data.head()

#Convert the datatypes of the data as needed
Turbine_Data['t_img_date'] = Turbine_Data['t_img_date'].astype(str)
Turbine_Data['usgs_pr_id'] = Turbine_Data['usgs_pr_id'].astype(str)
Turbine_Data['eia_id'] = Turbine_Data['eia_id'].astype(str)
Turbine_Data['t_cap'] = Turbine_Data['t_cap'].astype(str)
Turbine_Data.dtypes

#Manipulate the data as needed
Turbine_Data['p_year'] = Turbine_Data['p_year'].astype(int).astype(str) # Convert from an integer to a string
Turbine_Data['p_year'] = Turbine_Data['p_year'] + '-01-01T00:00:00.000Z' # Append '-01-01' to it
col = Turbine_Data.pop("p_year") # Pop the column and store it in the variable 'col'
Turbine_Data.insert(0, col.name, col) #Re-insert the column as the first column of the dataframe
Turbine_Data.head()

# Extracting column headers for the GridDB WebApi Container structure
col_vals = (list(Turbine_Data.columns.values))
col_dtypes = (list(Turbine_Data.dtypes))
data_tuples = list(zip(col_vals,col_dtypes)) # Binding lists to a tuple

# Converting list of tuples to pandas dataframe
container_columns = pd.DataFrame(data_tuples, columns=['col_vals','col_datatypes'])
container_columns.to_csv('container_columns.csv',index=False)
container_columns #column names for the container

import requests  # to make http calls
import http
http.client.HTTPConnection.debuglevel = 1 #Tip - to enable detailed logging of http calls; this is not needed in case you don't detailed logging

#Construct an object to hold the request headers (ensure that you replace the XXX placeholder with the correct value that matches the credentials for your GridDB instance)
header_obj = {"Authorization":"XXX","Content-Type":"application/json; charset=UTF-8","User-Agent":"PostmanRuntime/7.29.0"}

#Construct the base URL based on your GridDB cluster you'd like to connect to (ensure that you replace the placeholders in the URL below with the correct values that correspond to your GridDB instance)
#'https://[host]:[port]/griddb/v2/[clustername]/dbs/[database_name]'
base_url = 'https://[host]/griddb/v2/[clustername]/dbs/[databasename]'

#Construct an object to hold the request body (i.e., the container that needs to be created)
data_obj = {
    "container_name": "Wind_Turbine_Data",
    "container_type": "COLLECTION",
    "rowkey": False,
    "columns": [
    {
    "name": "p_year",
    "type": "TIMESTAMP"
    },
    {
    "name": "case_id",
    "type": "INTEGER"
    },
    {
    "name": "faa_ors",
    "type": "STRING"
    },
    {
    "name": "faa_asn",
    "type": "STRING"
    },
    {
    "name": "usgs_pr_id",
    "type": "STRING"
    },
    {
    "name": "eia_id",
    "type": "STRING"
    },
    {
    "name": "t_state",
    "type": "STRING"
    },        
    {
    "name": "t_county",
    "type": "STRING"
    },
    {
    "name": "t_fips",
    "type": "INTEGER"
    },
    {
    "name": "p_name",
    "type": "STRING"
    },
    {
    "name": "p_tnum",
    "type": "INTEGER"
    },
    {
    "name": "p_cap",
    "type": "FLOAT"
    },
    {
    "name": "t_manu",
    "type": "STRING"
    },
    {
    "name": "t_model",
    "type": "STRING"
    },
    {
    "name": "t_cap",
    "type": "STRING"
    },
    {
    "name": "t_hh",
    "type": "STRING"
    },
    {
    "name": "t_rd",
    "type": "STRING"
    },
    {
    "name": "t_rsa",
    "type": "STRING"
    },
    {
    "name": "t_ttlh",
    "type": "STRING"
    },
    {
    "name": "retrofit",
    "type": "INTEGER"
    },
    {
    "name": "retrofit_year",
    "type": "STRING"
    },
    {
    "name": "t_conf_atr",
    "type": "INTEGER"
    },
    {
    "name": "t_conf_loc",
    "type": "INTEGER"
    },
    {
    "name": "t_img_date",
    "type": "STRING"
    },
    {
    "name": "t_img_srce",
    "type": "STRING"
    },
    {
    "name": "xlong",
    "type": "FLOAT"
    },
    {
    "name": "ylat",
    "type": "FLOAT"
    }
    ]
}

#Set up the GridDB WebAPI URL
url = base_url + '/containers'

#Invoke the GridDB WebAPI with the headers and the request body
x = requests.post(url, json = data_obj, headers = header_obj)

#Convert the data in the dataframe to the JSON format
Turbine_Data_json = Turbine_Data.to_json(orient='values')
request_body = Turbine_Data_json

#Setup the URL to be used to invoke the GridDB WebAPI to register rows in the container created previously
url = 'https://[server]/griddb/v2/[clusterName]/dbs/[databasename]/containers/Wind_Turbine_Data/rows'

#Invoke the GridDB WebAPI using the request constructed
x = requests.put(url, data=request_body, headers=header_obj)

#Construct the SQL to be used
sql = ("SELECT p_year, count(*) as cnt FROM Wind_Turbine_Data where p_year >=TIMESTAMP('2000-01-01T00:00:00.000Z') group by p_year")

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + '/sql'

#Construct the request body
request_body = '[{"type":"sql-select", "stmt":"'+sql+'"}]'

#Validate the constructed request body
request_body

#Invoke the GridDB WebAPI
data_req1 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req1.json()
Turbines_after_2000 = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"]])
Turbines_after_2000=Turbines_after_2000.sort_values(by=['cnt'], ascending=False)

#Plot the data as a bar plot
Turbines_after_2000['year_formatted'] = pd.to_datetime(Turbines_after_2000['p_year'])
Turbines_after_2000['year'] = pd.DatetimeIndex(Turbines_after_2000['year_formatted']).year
fig,ax = plt.subplots(figsize=(9,6))
sns.barplot(Turbines_after_2000['year'], Turbines_after_2000['cnt'], ax=ax,order=Turbines_after_2000.sort_values('cnt',ascending = False).year)
ax.set_title('Number of Turbines by Year', fontsize = 16, loc='center')
ax.set_xlabel('Year', fontsize = 12)
ax.set_ylabel('Number of Turbines', fontsize = 12)
plt.xticks(rotation=45)
plt.show()

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + '/tql'

#Construct the request body which has the TQL that is to be used to retrieve the data
# Getting data for series_id APU0000709111 - Milk, fresh, whole, fortified; APU0000709211 - Milk, fresh, skim; APU0000709212 - Milk, fresh, low fat; APU0200709111 - Milk, fresh, whole, fortified; APU0200709211 - Milk, fresh, skim
request_body = '[{"name":"Wind_Turbine_Data", "stmt":"SELECT AVG(p_tnum) ", "columns":[]}]' 

#Invoke the GridDB WebAPI
data_req2 = requests.post(url, data=request_body, headers=header_obj)
data_req2

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req2.json()
Avg_Num_Turbines = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"]])

print('The average number of turbines created per project is', Avg_Num_Turbines['aggregationResult'][0])

#Construct the SQL to be used
sql = ("SELECT t_manu,SUM(p_tnum) as Total_Turbines FROM Wind_Turbine_Data where t_manu is NOT NULL GROUP BY t_manu HAVING SUM(p_tnum) >=200")

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + '/sql'

#Construct the request body which has the SQL that is to be used to retrieve the data
request_body = '[{"type":"sql-select", "stmt":"'+sql+'"}]'

#Invoke the GridDB WebAPI
data_req4 = requests.post(url, data=request_body, headers=header_obj)
data_req4

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req4.json()
manufacturers_and_models = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"],myJson[0]["columns"][1]["name"]])

from tabulate import tabulate
print(tabulate(manufacturers_and_models, headers='keys', tablefmt='psql'))

#Construct the sub-query SQL to be used
sql = ("SELECT t_manu,p_name,Turbine_Num, xlong,ylat, CASE WHEN Turbine_Num = 1 THEN '1 turbine' WHEN Turbine_Num >= 2 AND Turbine_Num <= 25 THEN '2-25 turbines' WHEN Turbine_Num >= 26 AND Turbine_Num <= 100 THEN '26-100 turbines' WHEN Turbine_Num >= 101 AND Turbine_Num <= 500 THEN '101-500 turbines' WHEN Turbine_Num >= 501 AND Turbine_Num <= 750 THEN '501-750 turbines' END Turbine_bins FROM (SELECT DISTINCT t_manu,p_name,xlong,ylat, Sum(p_tnum) Turbine_Num FROM Wind_Turbine_Data where t_manu is NOT NULL GROUP BY t_manu,p_name,xlong,ylat) t")

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + '/sql'

#Construct the request body which has the SQL that is to be used to retrieve the data
request_body = '[{"type":"sql-select", "stmt":"'+sql+'"}]'

#Invoke the GridDB WebAPI
data_req5 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req5.json()
Turbine_Locations = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"],myJson[0]["columns"][1]["name"],myJson[0]["columns"][2]["name"],myJson[0]["columns"][3]["name"],myJson[0]["columns"][4]["name"],myJson[0]["columns"][5]["name"]])

#Let's take a look at some of the data
Turbine_Locations.tail()

#Create a list which will have the bin names
cut_labels = ['1 turbine','2-25 turbines', '26-100 turbines', '101-500 turbines', '501-750 turbines']

#Plot the data
scale = 55

fig = go.Figure()
limits = [(0,1),(2,25),(26,100),(101,500),(501,750)]
colors = ["maroon","cyan","lime","mediumorchid","royalblue"]

#plot the data for each bin
for i in range(len(limits)):
    
    lim = limits[i]

    #filter the dataframe to get the records for the current bin
    Turbine_Data_filtered = Turbine_Locations.loc[(Turbine_Locations['Turbine_bins'] == cut_labels[i])]

    #plot the data
    fig.add_trace(go.Scattergeo(
        locationmode = 'USA-states',
        lon = Turbine_Data_filtered['xlong'],
        lat = Turbine_Data_filtered['ylat'],
        text = Turbine_Data_filtered['p_name'],
        marker = dict(
            size = Turbine_Data_filtered['Turbine_Num']/scale,  #size of the marker will depend on the number of turbines
            color = colors[i],
            line_color='rgb(40,40,40)',
            line_width=0.5,
            sizemode = 'area'
        ),
        name = '{0} - {1}'.format(lim[0],lim[1])
        )
    )

    
fig.update_layout(
        title_text = 'Turbine Distribution in the United States',
        showlegend = True,
        geo = dict(
            scope = 'usa',
            landcolor = 'rgb(217, 217, 217)',
        )

    )

fig.update_layout(
    title={
        'y':0.9,
        'x':0.5,
        'xanchor': 'center',
        'yanchor': 'top'})

fig.show()

sql = ("SELECT (SELECT COUNT(DISTINCT eia_id) FROM Wind_Turbine_Data WHERE retrofit = 1) plant_count,(SELECT SUM(p_tnum) as Turbine_Num FROM Wind_Turbine_Data WHERE retrofit =1) retrofitted_turbines,(SELECT SUM(p_tnum) as Turbine_Num FROM Wind_Turbine_Data WHERE retrofit =0) Turbines_not_retrofitted")

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + '/sql'

#Construct the request body which has the SQL that is to be used to retrieve the data
request_body = '[{"type":"sql-select", "stmt":"'+sql+'"}]'

#Invoke the GridDB WebAPI
data_req6 = requests.post(url, data=request_body, headers=header_obj)
data_req6

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req6.json()
Resulting_Data = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"],myJson[0]["columns"][1]["name"],myJson[0]["columns"][2]["name"]])

#This is how the results are written to the dataframe.
Resulting_Data

import plotly.graph_objects as go

fig = go.Figure()

fig.add_trace(go.Indicator(
    mode = "number",
    value = Resulting_Data['plant_count'][0],
    title = {"text": "Plants with retrofitted turbines<br /><span style='font-size:0.8em;color:gray'></span>"},
    domain = {'x': [0, 0.5], 'y': [0.5,1]},
))

fig.add_trace(go.Indicator(
    mode = "number",
    value = Resulting_Data['retrofitted_turbines'][0],
    title = {"text": "Turbines retrofitted since installation<br /><span style='font-size:0.8em;color:gray'></span>"},
    domain = {'x': [0.5, 1], 'y': [0.5, 1]}))

fig.add_trace(go.Indicator(
    mode = "number",
    value = Resulting_Data['Turbines_not_retrofitted'][0],
    title = {"text": "Turbines not retrofitted since installation<br /><span style='font-size:0.8em;color:gray'></span>"},
    domain = {'x': [0, 1], 'y': [0,0.5]}))


fig.update_layout(paper_bgcolor = "lightgray", )


fig.show()