# ---
# 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
# ---

# # Power Transmission PPP projects

import pandas as pd
import re
import numpy as np

# ## 1. Data acquisition and consolidation

# The PPI website provides either a stata database or a xlsx document that can be downloaded further to a query
# For the purpose of the analysis we will be using df_query since it allows to select project segments such as 'Transmission', but first we make sure that the query document is consistent with the STATA file.

df_stata = pd.read_stata('2019 H1 PPI Stata.dta')
df_query = pd.read_excel('PPI_Full_Query.xlsx')

# ## 2. Data preparation and clean-up

# Now, back to df_query for some analysis

# +
# df_query['Subtype of PPI'].value_counts()

# +
# Unlike the PPI website, we elect to remove 'Not Available' from the list of PPP subtypes

PPP_subtypes = ['Build, operate, and transfer',
                'Build, own, and operate',
                'Build, rehabilitate, operate, and transfer',
                'Rehabilitate, operate, and transfer',
                'Management contract',
                'Lease contract',
                'Rehabilitate, lease or rent, and transfer'
                'Rental',
                'Other',
                'Build, lease, and transfer'
               ]

# +
# df_query['Subsector'].value_counts()
# -

# List of sectors that corresponds to Electricity
electricity_sectors = ['Electricity','Electricity, ICT','Electricity, Treatment plant','Electricity, Roads']

# we build a dataframe containing only the chosen electricity sectors
df_electricity = df_query[df_query['Subsector'].isin(electricity_sectors)]

# list of segments that corresponds to transmission
transmission_segments = ['Electricity transmission','Electricity transmission, <br/>Other']

df_electricity_transmission = df_electricity[df_electricity['Segment'].isin(transmission_segments)]

# ## 3. Data analysis

# Now that we've compiled together all electricity transmission projects, it is time for data analysis

# Looking at available PPI subtypes
df_electricity_transmission['Subtype of PPI'].value_counts()

# Looking at available Project status
df_electricity_transmission['Project status'].value_counts()

# Looking at available types of T-Line PPP
df_electricity_transmission['Type of PPI'].value_counts()

# Looking at countries with T-Line PPP experience
df_electricity_transmission['Country'].value_counts()

# We create a few selection criteria based on our findings
selected_type_of_PPI=['Greenfield project','Brownfield']

df_electricity_transmission_new_PPP = df_electricity_transmission[
    df_electricity_transmission['Type of PPI'].isin(selected_type_of_PPI) & 
    df_electricity_transmission['Subtype of PPI'].isin(PPP_subtypes)
]

# +
# replacing some values for better readibility

replacement_dict = {
    'Subtype of PPI':{
        'Build, operate, and transfer' : 'BOT',
        'Build, rehabilitate, operate, and transfer' : 'BROT',
        'Build, own, and operate': 'BOO',
        'Rehabilitate, operate, and transfer': 'ROT'
    }  
}

df_output = df_electricity_transmission_new_PPP.replace(replacement_dict)
# -

tt = df_output['Subtype of PPI'].value_counts()

pie = tt.plot.pie(legend=True,autopct='%1.1f%%',title='T-Line PPP Types')
plt.title = 'T-Line PPP Types'
plt.size = (10,10)
plt.show()

df_output['TotalInvestment'].value_counts()

df_output_for_investment = df_output[['InvestmentYear','TotalInvestment']]

# replaces the 'Not Available values' in the 'TotalInvestment column'
df_output_for_investment.replace({'TotalInvestment':{'Not Available':0}},inplace=True)

df_output_for_investment['TotalInvestment'].value_counts()

df_output_for_investment.applymap(lambda x: isinstance(x, (int, float)))

df_output_for_investment['TotalInvestment'] = df_output_for_investment['TotalInvestment'].apply(lambda x: float(x))

df_output_for_investment['Count'] = 1
df_output_for_investment.set_index('InvestmentYear',inplace=True)

df_output_for_investment

# +
# Investment per year
import matplotlib.pyplot as plt

data = df_output_for_investment.groupby(['InvestmentYear']).sum()
data

# +
ax = data.plot(kind='bar', y =['TotalInvestment'],use_index=True, title='Total Investment in T-line PPP per year (USD million)', label = ['Total Investment'])
ax.set_ylabel("Investment Value (USD million)")
ax.set_xlabel("Years")
ax2 = ax.twinx()
ax2.plot(data[['Count']].values,linestyle='-', marker='o', linewidth=2.0,color ='k', label = 'Count of T-Line PPP Contract')
ax2.set_ylabel('Count of T-Line PPP Contracts')

# ask matplotlib for the plotted objects and their labels
lines, labels = ax.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax2.legend(lines + lines2, labels + labels2, loc=0)

plt.show()
# -

df_contract_period = df_output['ContractPeriod']
df_contract_period.value_counts()

df_contract_period = df_contract_period[~df_contract_period.isin(['Not Available','Not Applicable'])]                             
df_contract_period = df_contract_period.apply(lambda x: float(x))

ax = df_contract_period.plot(kind='hist',xticks=np.arange(0, 100, step=5),title='Distribution of T-Line PPP Contract Periods')
ax.set_xlabel("Duration in years")
ax.set_ylabel("Count of PPP contracts")
plt.show()

StarcoderdataJupyterScriptsDedupFiltered