In [1]:
# pandas, geopandas, numpy for working with dataframes and arrays
import pandas as pd
import geopandas as gpd
import numpy as np
# matplotlib for basic plots
import matplotlib.pyplot as plt
%matplotlib inline
# plotly for interactive plots
import plotly.offline as py
from plotly.graph_objs import *
from plotly import tools
py.init_notebook_mode(connected=True)
In [2]:
# set pandas float display to have 2 decimal points and a comma separating every 1000X
pd.options.display.float_format = '{:,.2f}'.format
In [3]:
# original data from https://data.go.th/DatasetDetail.aspx?id=de82938b-361e-412c-bf30-4a4f2c5e6c3a
# note: the original data was re-encoded to utf8 to work with pandas
govSpendCntrct = pd.read_csv('GovSpending_25601002_contract_utf8.csv',
usecols=['proj_no','proj_name','subdep_name','corp_name',
'mthd_name', 'typ_name','contrct_price','contrct_date'])
subdep_provnc = pd.read_csv('GovSpending_25601002_department_utf8.csv', usecols=['subdep_name','org_name','provnc'])
govSpendCntrct = govSpendCntrct.merge(subdep_provnc, 'left', on='subdep_name')
govSpendCntrct = govSpendCntrct[['proj_no', 'proj_name', 'subdep_name', 'org_name', 'provnc', 'mthd_name', 'typ_name', 'corp_name', 'contrct_price','contrct_date']]
In [4]:
govSpendCntrct['contrct_date'] = pd.to_datetime(govSpendCntrct['contrct_date'], dayfirst=True)
In [5]:
print('Data from:', govSpendCntrct['contrct_date'].min())
print('Data to:', govSpendCntrct['contrct_date'].max())
print('Total government spending (THB):', govSpendCntrct['contrct_price'].sum())
print('Total orders:', govSpendCntrct.shape[0])
In [6]:
govSpendCntrct.sample(5)
Out[6]:
In [7]:
get_col = ['proj_name','subdep_name','org_name','provnc','mthd_name','typ_name','corp_name','contrct_price']
govSpendCntrct.sort_values('contrct_price', ascending=False)[get_col].head(20)
Out[7]:
In [8]:
list_to_group = ['corp_name']
target = ['contrct_price']
sum_contrct = govSpendCntrct[list_to_group+target].groupby(list_to_group).sum().sort_values(target,ascending=False)
count_contrct = govSpendCntrct[list_to_group+target].groupby(list_to_group).count().sort_values(target,ascending=False)
costPerContrct = (sum_contrct/count_contrct).sort_values(target,ascending=False)
In [9]:
start_date = govSpendCntrct['contrct_date'].min().strftime('%Y/%m/%d')
end_date = govSpendCntrct['contrct_date'].max().strftime('%Y/%m/%d')
count_total = count_contrct.sum()
sum_total = sum_contrct.sum()
count_contrct = count_contrct[:15]
sum_contrct = sum_contrct[:15]
costPerContrct = costPerContrct[:15]
marker = dict(color='rgb(158,202,225)',
line=dict(color='rgb(8,48,107)',width=1.5),
)
# if would like rainbow color
barcolors = ['hsl('+str(h)+',50%'+',50%)' for h in range(0, 360, int(360/count_contrct.shape[0]))]
edgescolors = ['hsl('+str(h)+',100%'+',20%)' for h in range(0, 360, int(360/count_contrct.shape[0]))]
marker = dict(color=barcolors,
line=dict(color=edgescolors,width=1.5),
)
trace_count = Bar(
x=count_contrct.index,
y=count_contrct['contrct_price'],
text=['%.2f%% of total' % percent[0] for percent in (count_contrct/count_total*100).values],
marker=marker,
opacity=0.7,
name='count'
)
trace_sum = Bar(
x=sum_contrct.index,
y=sum_contrct['contrct_price'],
text=['%.2f%% of total' % percent[0] for percent in (sum_contrct/sum_total*100).values],
marker=marker,
opacity=0.7,
name='sum value'
)
trace_avg = Bar(
x=costPerContrct.index,
y=costPerContrct['contrct_price'],
marker=marker,
opacity=0.7,
name='per contract'
)
fig = tools.make_subplots(rows=2, cols=2, specs=[[{}, {}], [{'colspan': 2}, None]],
vertical_spacing=0.2,
subplot_titles=('15 บริษัทที่ได้จำนวนสัญญาจัดซื้อรวมสูงสุด',
'15 บริษัทที่ได้รับมูลค่าสัญญารวมสูงสุด',
'15 บริษัทที่มีมูลค่าจัดซื้อต่อสัญญาสูงสุด'))
fig.append_trace(trace_count, 1, 1)
fig.append_trace(trace_sum, 1, 2)
fig.append_trace(trace_avg, 2, 1)
fig['layout'].update(height=460, width=700,
title='สรุปคำสั่งและงบประมาณการจัดซื้อของหน่วยงานรัฐบาล ({} ถึง {})'.format(start_date, end_date),
showlegend=False, plot_bgcolor='rgb(256,256,256)',
font=dict(size=11)
)
fig['layout']['annotations'][0]['font'].update(size=13)
fig['layout']['annotations'][1]['font'].update(size=13)
fig['layout']['annotations'][2]['font'].update(size=14)
fig['layout']['yaxis1'].update(title='จำนวนรวม (สัญญา)')
fig['layout']['yaxis2'].update(title='มูลค่ารวม (บาท)')
fig['layout']['yaxis3'].update(title='มูลค่าต่อสัญญา (บาท/สัญญา)')
fig['layout']['xaxis1'].update(showticklabels=True,tickfont=dict(size=6),tickangle=20)
fig['layout']['xaxis2'].update(showticklabels=True,tickfont=dict(size=6),tickangle=20)
fig['layout']['xaxis3'].update(showticklabels=True,tickfont=dict(size=10),tickangle=20)
fig['layout']['annotations'][2]['y'] = 0.35
py.iplot(fig)
# py.plot(fig, filename='summaryByCompany.html')
In [10]:
list_to_group = ['org_name']
target = ['contrct_price']
sum_contrct = govSpendCntrct[list_to_group+target].groupby(list_to_group).sum().sort_values(target,ascending=False)
count_contrct = govSpendCntrct[list_to_group+target].groupby(list_to_group).count().sort_values(target,ascending=False)
costPerContrct = (sum_contrct/count_contrct).sort_values(target,ascending=False)
In [11]:
start_date = govSpendCntrct['contrct_date'].min().strftime('%Y/%m/%d')
end_date = govSpendCntrct['contrct_date'].max().strftime('%Y/%m/%d')
count_total = count_contrct.sum()
sum_total = sum_contrct.sum()
marker = dict(color='rgb(158,202,225)',
line=dict(color='rgb(8,48,107)',width=1.5),
)
# if would like rainbow color
barcolors = ['hsl('+str(h)+',50%'+',50%)' for h in range(0, 360, int(360/count_contrct.shape[0]))]
edgescolors = ['hsl('+str(h)+',100%'+',20%)' for h in range(0, 360, int(360/count_contrct.shape[0]))]
marker = dict(color=barcolors,
line=dict(color=edgescolors,width=1.5),
)
trace_count = Bar(
x=count_contrct.index,
y=count_contrct['contrct_price'],
text=['%.2f%% of total' % percent[0] for percent in (count_contrct/count_total*100).values],
marker=marker,
opacity=0.7,
name='count'
)
trace_sum = Bar(
x=sum_contrct.index,
y=sum_contrct['contrct_price'],
text=['%.2f%% of total' % percent[0] for percent in (sum_contrct/sum_total*100).values],
marker=marker,
opacity=0.7,
name='sum value'
)
trace_avg = Bar(
x=costPerContrct.index,
y=costPerContrct['contrct_price'],
marker=marker,
opacity=0.7,
name='per contract'
)
fig = tools.make_subplots(rows=2, cols=2, specs=[[{}, {}], [{'colspan': 2}, None]],
vertical_spacing=0.25,
subplot_titles=('จำนวนสัญญาจัดซื้อรวมเรียงตามหน่วยงาน',
'มูลค่าสัญญาจัดซื้อรวมเรียงตามหน่วยงาน',
'มูลค่าสัญญาจัดซื้อต่อสัญญาเฉลี่ยเรียงตามหน่วยงาน')
)
fig.append_trace(trace_count, 1, 1)
fig.append_trace(trace_sum, 1, 2)
fig.append_trace(trace_avg, 2, 1)
fig['layout'].update(height=460, width=700,
title='สรุปคำสั่งและงบประมาณการจัดซื้อของหน่วยงานรัฐบาล ({} ถึง {})'.format(start_date, end_date),
showlegend=False, plot_bgcolor='rgb(256,256,256)',
font=dict(size=11)
)
fig['layout']['annotations'][0]['font'].update(size=13)
fig['layout']['annotations'][1]['font'].update(size=13)
fig['layout']['annotations'][2]['font'].update(size=14)
fig['layout']['yaxis1'].update(title='จำนวนรวม (สัญญา)')
fig['layout']['yaxis2'].update(title='มูลค่ารวม (บาท)')
fig['layout']['yaxis3'].update(title='มูลค่าต่อสัญญา (บาท/สัญญา)')
fig['layout']['xaxis1'].update(showticklabels=True,tickfont=dict(size=6),tickangle=20)
fig['layout']['xaxis2'].update(showticklabels=True,tickfont=dict(size=6),tickangle=20)
fig['layout']['xaxis3'].update(showticklabels=True,tickfont=dict(size=10),tickangle=20)
fig['layout']['annotations'][2]['y'] = 0.35
py.iplot(fig)
# py.plot(fig, filename='summaryByOrg.html')
In [12]:
n_sampling = 100
x_data = costPerContrct.index
# if would like rainbow color
barcolors = ['hsl('+str(h)+',50%'+',50%)' for h in range(0, 360, int(360/count_contrct.shape[0]))]
edgescolors = ['hsl('+str(h)+',100%'+',20%)' for h in range(0, 360, int(360/count_contrct.shape[0]))]
marker = dict(color=barcolors,
line=dict(color=edgescolors,width=1.5),
)
traces = []
hover_text = []
for xd, cls in zip(x_data, barcolors):
temp_df = govSpendCntrct.loc[govSpendCntrct['org_name']==xd,'contrct_price']
q1_mark = temp_df.quantile(0.25)
q3_mark = temp_df.quantile(0.75)
hover_text.append('{}:<br>Mean={:.0f}k<br>Q3={:.0f}k<br>median={:.0f}k<br>Q1={:.0f}k'.format(
xd,temp_df.mean()/1000,q3_mark/1000,temp_df.median()/1000,q1_mark/1000))
hover_text_temp = '{}:<br>Mean={:.0f}k<br>Q3={:.0f}k<br>median={:.0f}k<br>Q1={:.0f}k'.format(
xd,temp_df.mean()/1000,q3_mark/1000,temp_df.median()/1000,q1_mark/1000)
traces.append(Box(
y=temp_df.sample(n_sampling),
name=xd,
boxpoints='all',
pointpos=0,
jitter=1,
whiskerwidth=0.2,
marker=dict(color=cls, size=4),
opacity=0.5,
fillcolor='rgba(0,0,0,0)',
line=dict(color='rgba(0,0,0,0)'),
text=hover_text_temp,
hoverinfo='text'
)
)
temp_df.loc[temp_df<=q1_mark] = q1_mark
temp_df.loc[temp_df>=q3_mark] = q3_mark
# use this to make the box plot instead of using the whole temp_df (with 8xx,xxx data points)
# since a box plot keeps all data points even when the points are hidden --> large file size
temp_box = pd.Series([q1_mark]*5+[temp_df.mean()]+[q3_mark]*5)
traces.append(Box(
# y=temp_df,
y=temp_box,
name=xd,
boxpoints=False,
whiskerwidth=0.2,
opacity=0.7,
line=dict(color=cls),
hoverinfo='none'
)
)
traces.append(Bar(
x=costPerContrct.index,
y=costPerContrct['contrct_price'],
marker=marker,
opacity=0.2,
name='average',
text=hover_text,
hoverinfo='none'
)
)
layout = Layout(
title='มูลค่าสัญญาจัดซื้อเฉลี่ยและการกระจายตัว (บาท/สัญญา)',
showlegend=False,
width=600,
height=400,
font=dict(size=11),
yaxis=dict(
range=[0, 11e6]
)
)
fig = Figure(data=traces, layout=layout)
py.iplot(fig)
# py.plot(fig, filename='spendingDistributionByOrg.html')
In [13]:
# original data from https://github.com/apisit/thailand.json
gdf = gpd.read_file('thailand.json')
gdf.columns = ['Province','geometry']
gdf.plot()
plt.show()
In [14]:
# original data from https://data.go.th/DatasetDetail.aspx?id=8d13d593-aea4-40b9-ad78-884da8a49e35
# note: the original data was re-encoded to utf8 to work with pandas
df = pd.read_csv('ThailandReferendum2016_regular.csv', skiprows=3, header=[0,1])
# This file only maps Thai to English version of provinces
province_pair = pd.read_csv('thaiProvinces.csv', header=None, names=['จังหวัด','Province'])
arrays = [['จังหวัด', 'ภาค', 'ผู้มีสิทธิออกเสียง', 'ผู้มาใช้สิทธิออกเสียง', 'มาใช้สิทธิ์ร้อยละ',
'ประเด็นที่ 1 ร่างรัฐธรรมนูญ', 'ประเด็นที่ 1 ร่างรัฐธรรมนูญ', 'ประเด็นที่ 1 ร่างรัฐธรรมนูญ', 'ประเด็นที่ 1 ร่างรัฐธรรมนูญ',
'ประเด็นที่ 2 คำถามเพิ่มเติม', 'ประเด็นที่ 2 คำถามเพิ่มเติม', 'ประเด็นที่ 2 คำถามเพิ่มเติม', 'ประเด็นที่ 2 คำถามเพิ่มเติม',
'บัตรเสีย', 'บัตรเสียร้อยละ'],
['', '', '', '', '',
'เห็นชอบ', 'เห็นชอบร้อยละ', 'ไม่เห็นชอบ', 'ไม่เห็นชอบร้อยละ',
'เห็นชอบ', 'เห็นชอบร้อยละ', 'ไม่เห็นชอบ', 'ไม่เห็นชอบร้อยละ',
'', '']]
tuples = list(zip(*arrays))
columns = pd.MultiIndex.from_tuples(tuples)
df.columns = columns
df.columns = [' '.join(col).strip() for col in df.columns.values]
columns = np.insert(df.columns.values, 1, 'Province')
df = pd.merge(df, province_pair, 'inner', 'จังหวัด')
df = df[columns]
In [15]:
gdf = pd.merge(gdf, df, 'inner', on='Province')
In [16]:
temp = govSpendCntrct[['provnc','contrct_price']].groupby('provnc').sum()
gdf = pd.merge(gdf, temp, 'inner', left_on='จังหวัด', right_index=True)
map_df = pd.merge(gdf[['Province','geometry','จังหวัด','ผู้มีสิทธิออกเสียง']], temp, 'inner', left_on='จังหวัด', right_index=True)
In [17]:
map_df.head(5)
Out[17]:
In [18]:
# import a custom made functions to plot plotly maps
from plotly_map_gen import *
In [19]:
# for Medium
plot_data1 = []
plot_data2 = []
# column of interest
dataCol = 'contrct_price'
# polygon simplification factor
simplify_factor=0.03
# set location of the colorbar
cbar1_x = 0.33
cbar1_y = 0.25
cbar2_x = 0.74
cbar2_y = 0.25
#plot type -- 'linear' or 'log'
cbar1_type = 'linear'
cbar2_type = 'log'
# set tick locations and texts on the colorbar
cbar1_ticktext = ['1B','5B','10B','50B'] if cbar1_type=='log' else None
cbar1_tickvals = np.log10(np.array([1,2,10,50])*1e9) if cbar1_type=='log' else None
cbar2_ticktext = ['1B','5B','10B','50B'] if cbar2_type=='log' else None
cbar2_tickvals = np.log10(np.array([1,2,10,50])*1e9) if cbar2_type=='log' else None
gen_map(plot_data1, map_df, dataCol, simplify_factor, cbar1_x, cbar1_y, cbar1_type,
cbar_tickvals=cbar1_tickvals, cbar_ticktext=cbar1_ticktext, colorbarname='Thai Baht')
gen_map(plot_data2, map_df, dataCol, simplify_factor, cbar2_x, cbar2_y, cbar2_type,
cbar_tickvals=cbar2_tickvals, cbar_ticktext=cbar2_ticktext, colorbarname='Thai Baht')
##################
fig = tools.make_subplots(rows=1, cols=2, specs=[[{}, {}]],
subplot_titles=('Linear-scale', 'Log-scale'))
for e1, e2 in zip(plot_data1,plot_data2):
fig.append_trace(e1, 1, 1)
fig.append_trace(e2, 1, 2)
fig['layout'].update(hovermode = 'closest', height=466, width=700,
title='Sum government spending by province <br>' +
'({} to {})'.format(start_date, end_date),
xaxis1 = {'anchor': 'y1', 'domain': [0.15, 0.44]},
xaxis2 = {'anchor': 'y2', 'domain': [0.56, 0.85]}
)
fig['layout']['annotations'][0]['x'] = 0.29
fig['layout']['annotations'][1]['x'] = 0.72
py.iplot(fig)
In [20]:
map_df['contrct_price_per_eligible_voter'] = map_df['contrct_price']/map_df['ผู้มีสิทธิออกเสียง']
In [21]:
map_df.head(5)
Out[21]:
In [22]:
# for Medium
plot_data3 = []
plot_data4 = []
# column of interest
dataCol = 'contrct_price_per_eligible_voter'
# polygon simplification factor
simplify_factor=0.03
# set location of the colorbar
cbar3_x = 0.33
cbar3_y = 0.25
cbar4_x = 0.74
cbar4_y = 0.25
#plot type -- 'linear' or 'log'
cbar3_type = 'linear'
cbar4_type = 'log'
# set tick locations and texts on the colorbar
cbar3_ticktext = ['2k','5k','10k'] if cbar1_type=='log' else None
cbar3_tickvals = np.log10(np.array([2,5,10])*1e3) if cbar3_type=='log' else None
cbar4_ticktext = ['2k','5k','10k'] if cbar2_type=='log' else None
cbar4_tickvals = np.log10(np.array([2,5,10])*1e3) if cbar4_type=='log' else None
gen_map(plot_data3, map_df, dataCol, simplify_factor, cbar3_x, cbar3_y, cbar3_type,
cbar_tickvals=cbar3_tickvals, cbar_ticktext=cbar3_ticktext, colorbarname='Thai Baht')
gen_map(plot_data4, map_df, dataCol, simplify_factor, cbar4_x, cbar4_y, cbar4_type,
cbar_tickvals=cbar4_tickvals, cbar_ticktext=cbar4_ticktext, colorbarname='Thai Baht')
##################
fig = tools.make_subplots(rows=1, cols=2, specs=[[{}, {}]],
subplot_titles=('Linear-scale', 'Log-scale'))
for e1, e2 in zip(plot_data3,plot_data4):
fig.append_trace(e1, 1, 1)
fig.append_trace(e2, 1, 2)
fig['layout'].update(hovermode = 'closest', height=466, width=700,
title='Sum government spending per eligible voter by province <br>' +
'({} to {})'.format(start_date, end_date),
xaxis1 = {'anchor': 'y1', 'domain': [0.15, 0.44]},
xaxis2 = {'anchor': 'y2', 'domain': [0.56, 0.85]}
)
fig['layout']['annotations'][0]['x'] = 0.29
fig['layout']['annotations'][1]['x'] = 0.72
py.iplot(fig)
In [23]:
# re-create this placeholder plot with correct locations of text, labels, colorbar, etc.
plot_data2 = []
# column of interest
dataCol = 'contrct_price'
# polygon simplification factor
simplify_factor=0.03
# set location of the colorbar
cbar2_x = 0.9
cbar2_y = 0.25
#plot type -- 'linear' or 'log'
cbar1_type = 'linear'
cbar2_type = 'log'
# set tick locations and texts on the colorbar
cbar2_ticktext = ['1B','5B','10B','50B'] if cbar2_type=='log' else None
cbar2_tickvals = np.log10(np.array([1,2,10,50])*1e9) if cbar2_type=='log' else None
gen_map(plot_data2, map_df, dataCol, simplify_factor, cbar2_x, cbar2_y, cbar2_type,
cbar_tickvals=cbar2_tickvals, cbar_ticktext=cbar2_ticktext, colorbarname='Thai Baht')
In [24]:
fig = tools.make_subplots(rows=1, cols=2, specs=[[{}, {}]],
subplot_titles=('การกระจายตัว (บาท/สัญญา)',
'มูลค่ารวมตามจังหวัด'))
# traces were created when plotting the distribution of spending
for e1 in traces:
fig.append_trace(e1, 1, 1)
# spending by province
for e1 in plot_data2:
fig.append_trace(e1, 1, 2)
fig['layout'].update(hovermode = 'closest',
height=460, width=700, showlegend=False,
title='ข้อมูลคำสั่งจัดซื้อจัดจ้างโดยหน่วยงานรัฐบาล <br>' +
'({} to {})'.format(start_date, end_date),
xaxis1 = {'anchor': 'y1', 'domain': [0.0, 0.7]},
yaxis1 = {'range': [0, 11e6]},
xaxis2 = {'anchor': 'y2', 'domain': [0.73, 1]}
)
fig['layout']['annotations'][0]['x'] = 0.35
fig['layout']['annotations'][1]['x'] = 0.86
fig['layout']['annotations'][0]['font'].update({'size': 13})
fig['layout']['annotations'][1]['font'].update({'size': 13})
fig['layout']['xaxis1'].update(showticklabels=True,tickfont=dict(size=9),tickangle=25)
py.iplot(fig)
# py.plot(fig, filename='openningPlot.html')
In [ ]: