Cell 1 · markdown

Setting up a Graph Database for the US Government IT Portfolios

Cell 2 · code

import pandas as pd
import numpy as np
import dabl
%matplotlib inline
import os
from dabl import plot
import matplotlib.pyplot as plt
import datetime as dt
import dtale

Cell 3 · code

directory = '/Users/lawrence/Documents/GitHub/data_files_for_access/ProjectHack3data_Challenge9_from_Said_school/2013/'

Cell 4 · markdown

Bring in Projects file

Cell 5 · code

Projects=pd.read_csv(directory+'Projects.csv', encoding= 'unicode_escape')

Cell 6 · code

d = dtale.show(Projects, ignore_duplicate=True)

Cell 7 · code

d

Cell 8 · code

d.kill()

Executing shutdown...

2020-08-18 15:47:48,908 - INFO     - Executing shutdown...

Cell 9 · code

Projects=Projects.drop(['Agency Project ID','Project Description','Lifecycle Cost', 'Cost Variance (%)','Projected/Actual Cost ($ M)','Updated Time','Unique Project ID'],axis=1)

Cell 10 · code

Projects['Completion Date (B1)']=Projects['Completion Date (B1)'].astype('datetime64')
Projects['Planned Project Completion Date (B2)']=Projects['Planned Project Completion Date (B2)'].astype('datetime64')
Projects['Start Date']=Projects['Start Date'].astype('datetime64')
Projects['Projected/Actual Project Completion Date (B2)']=Projects['Projected/Actual Project Completion Date (B2)'].astype('datetime64')
Projects['Updated Date']=Projects['Updated Date'].astype('datetime64')
Projects['Business Case ID']=Projects['Business Case ID'].astype('category')
Projects['Agency Code']=Projects['Agency Code'].astype('category')
Projects['Project ID']=Projects['Project ID'].astype('category')

Cell 11 · markdown

In nearly all cases, Completion Date (B1) > Planned Project Completion Date (B2)

In some cases ‘Projected/Actual Project Completion Date (B2) < Planned Project Completion Date (B2) But in most cases, it is the other way around , so that Planned < Project /Actual i,e is earlier

And in most cases, where not equal: Projected/Actual Project Completion Date (B2)’ < ‘Completion Date (B1)’

therefore, Planning progresses in this order

1. Planned Project Completion Date (B2)
2. Projected/Actual Project Completion Date (B2)
3. Completion Date (B1)

So I will create:

• Planned Duration
• Project Delay, which is the worst Project Delay I can find Schedule variance appears to be Planned Project Completion Date (B2) MINUS ‘Projected/Actual Project Completion Date (B2) and Hence is often listed as Negative. Which is okay, so Negative just means that Progress was underwhelming as normal. So I can drop schedule variance and schedule variance % because we have calculated it better as Project_Delay

Cell 12 · code

# worked this out by a variety of boolean mixing such as:
# constraint=(Projects['Projected/Actual Project Completion Date (B2)']<Projects['Completion Date (B1)'])&(pd.notna(Projects['Projected/Actual Project Completion Date (B2)']))&(pd.notna(Projects['Completion Date (B1)']))
# Projects [constraint]

Cell 13 · markdown

Projected/Actual more or less the same as Planned cost.

• sometimes a little more which makes sense
• a few with 0s which is okay
• so maybe best to drop ‘Projected/Actual’

Lifecycle cost is:

• often zero
• mostly identical to Planned
• so best to drop this too. Looks like mostly Cost Variance +Planned Cost = Project/Actual Cost i.e. just keep Planned cost and Cost Variance

Cell 14 · code

# worked this out from variants of: 
# plt.scatter ((Projects['Planned Cost ($ M)'],Projects['Cost Variance ($ M)']))

Cell 15 · code

Projects['Planned_Duration']=(Projects['Planned Project Completion Date (B2)']-Projects['Start Date']).dt.days
Projects['Project_delay']=(np.maximum(Projects['Projected/Actual Project Completion Date (B2)']-Projects['Planned Project Completion Date (B2)'],Projects['Completion Date (B1)']-Projects['Planned Project Completion Date (B2)'])).dt.days

Cell 16 · code

Projects=Projects.drop(['Planned Project Completion Date (B2)','Completion Date (B1)','Projected/Actual Project Completion Date (B2)'],axis=1)

Cell 17 · code

Projects['Start_days_after_2000']=(Projects['Start Date']-pd.Timestamp('2000-01-01')).dt.days

Cell 18 · code

Projects=Projects.drop(['Schedule Variance (%)','Schedule Variance (in days)'],axis=1)

Cell 19 · code

Projects['Updated Date']=(Projects['Updated Date']-Projects['Start Date']).dt.days # where this is now the no of days the update happened after start date

Cell 20 · code

Projects=Projects.dropna()

Cell 21 · code

Projects

Cell 22 · code

d = dtale.show(Projects, ignore_duplicate=True)

Cell 23 · markdown

Work out Data Structure as you go.

Agency » Investment (w Business Case ) » Project ( Start, Completion, Cost, Variances)

Create a basic example in Neo4j inline

CREATE (m:Agency {name:’Dept Agriculture’,Code:5}) CREATE (n:Investment {name:’AMS Infrastructure WAN and DMZ (AMSWAN)’, Identifier:’005-000001723’}) CREATE (o:Project {name:’Virtualization’, Id:657,Cost_Variance:0,Planned_Cost:0.179,Updated_Date:60,Planned_Duration:182, Project_delay:0,Start_days_after_2000:4291}) CREATE (m)-[:invests]->(n) CREATE (n)-[:pays_for]->(o)

After testing this, I can bring in CSV: see further below

Cell 24 · markdown

Data exploration

Cell 25 · code

constraint=Projects['Updated Date']>Projects['Planned_Duration'] #find only those rows where Update date is after Project is meant to have finished

Cell 26 · code

Projects_Training=Projects[constraint]

Cell 27 · code

Stripped_for_delay_forecast=Projects_Training.drop(['Cost Variance ($ M)','Unique Investment Identifier','Business Case ID','Agency Name','Investment Title','Project ID','Project Name','Start Date'],axis=1)

Cell 28 · code

dabl.plot(Stripped_for_delay_forecast,'Project_delay')

Saved output

/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/supervised.py:528: FutureWarning: The second positional argument of plot is a Series 'y'. If passing a column name, use a keyword.
  FutureWarning)

Target looks like regression

/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:621: UserWarning: Dropped 4 outliers in column Updated Date.
  int(dropped), series.name), UserWarning)
/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:621: UserWarning: Dropped 6 outliers in column Planned_Duration.
  int(dropped), series.name), UserWarning)
/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:621: UserWarning: Dropped 4 outliers in column Start_days_after_2000.
  int(dropped), series.name), UserWarning)
/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:621: UserWarning: Dropped 3 outliers in column Planned Cost ($ M).
  int(dropped), series.name), UserWarning)
/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:364: UserWarning: FixedFormatter should only be used together with FixedLocator
  for t in ax.get_xticklabels()]

Cell 29 · code

Stripped_for_cost_forecast=Projects_Training.drop(['Project_delay','Unique Investment Identifier','Business Case ID','Agency Name','Investment Title','Project ID','Project Name','Start Date'],axis=1)

Cell 30 · code

dabl.plot(Stripped_for_cost_forecast,'Cost Variance ($ M)')

Saved output

/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/supervised.py:528: FutureWarning: The second positional argument of plot is a Series 'y'. If passing a column name, use a keyword.
  FutureWarning)

Target looks like regression

/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:621: UserWarning: Dropped 3 outliers in column Planned Cost ($ M).
  int(dropped), series.name), UserWarning)
/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:621: UserWarning: Dropped 6 outliers in column Planned_Duration.
  int(dropped), series.name), UserWarning)
/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:621: UserWarning: Dropped 4 outliers in column Updated Date.
  int(dropped), series.name), UserWarning)
/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:621: UserWarning: Dropped 4 outliers in column Start_days_after_2000.
  int(dropped), series.name), UserWarning)
/Users/lawrence/DABL_Env/lib/python3.7/site-packages/dabl/plot/utils.py:364: UserWarning: FixedFormatter should only be used together with FixedLocator
  for t in ax.get_xticklabels()]

Cell 31 · code

# Could do a similar strip down for forecasting delay

Cell 32 · code

# did not bother with a DABL clean

Cell 33 · code

# Not bothering w classiifcation model for now

Cell 34 · markdown

Train a regression

Cell 35 · code

Delay_regression_model=dabl.SimpleRegressor(random_state=0)

Cell 36 · code

X=Projects_Training.drop(['Project_delay','Cost Variance ($ M)','Unique Investment Identifier','Business Case ID','Agency Name','Investment Title','Project ID','Project Name','Start Date'],axis=1)

Cell 37 · code

y=Projects_Training['Project_delay']

Cell 38 · code

Delay_regression_model.fit(X,y)

Running DummyRegressor()
r2: -0.009 neg_mean_squared_error: -215755.852
=== new best DummyRegressor() (using r2):
r2: -0.009 neg_mean_squared_error: -215755.852

Running DecisionTreeRegressor(max_depth=1)
r2: -0.086 neg_mean_squared_error: -226917.448
Running DecisionTreeRegressor(max_depth=5)
r2: -0.425 neg_mean_squared_error: -276607.244
Running Ridge(alpha=10)
r2: 0.050 neg_mean_squared_error: -205111.219
=== new best Ridge(alpha=10) (using r2):
r2: 0.050 neg_mean_squared_error: -205111.219

Running Lasso(alpha=10)
r2: 0.058 neg_mean_squared_error: -205306.111
=== new best Lasso(alpha=10) (using r2):
r2: 0.058 neg_mean_squared_error: -205306.111


Best model:
Lasso(alpha=10)
Best Scores:
r2: 0.058 neg_mean_squared_error: -205306.111

SimpleRegressor(random_state=0)

Cell 39 · code

# not bothered w Cost regression because would get just as rubbish results

Cell 40 · markdown

Export to CSV en route to Neo4j

Cell 41 · code

Projects=Projects.rename(columns={"Agency Name": "Agency_Name", "Agency Code":"Agency_Code",'Investment Title':'Investment_Title','Unique Investment Identifier':'Unique_Investment_Identifier','Project Name':'Project_Name','Project ID':'Project_ID','Start Date':'Start_Date', 'Cost Variance ($ M)':'Cost_Variance','Planned Cost ($ M)':'PlannedCost','Updated Date':'Updated_Date','Business Case ID':'Business_Case_ID'})  

Cell 42 · code

Projects.to_csv(directory+'Oxford_Projects_Out.csv', index=False)

Cell 43 · markdown

#Cypher code for Neo4j LOAD CSV WITH HEADERS FROM ‘file:///Oxford_Projects_Out.csv’ AS row MERGE (m:Agency {name:row.Agency_Name,Code:row.Agency_Code}) MERGE (n:Investment {name:row.Investment_Title,Identifier:row.Unique_Investment_Identifier,Business_Case:row.Business_Case_ID}) MERGE (o:Project {name:row.Project_Name,Id:row.Project_ID,Cost_Variance:toFloat(row.Cost_Variance),Planned_Cost:toFloat(row.PlannedCost),Updated_Date:toInteger(row.Updated_Date),Planned_Duration:toInteger(row.Planned_Duration),Project_delay:toInteger(row.Project_delay),Start_days_after_2000:toInteger(row.Start_days_after_2000)}) MERGE (m)-[:invests]->(n) MERGE (n)-[:pays_for]->(o)

Cell 44 · markdown

Pull in strategic investment information

Cell 45 · code

Strategy=pd.read_csv(directory+'Exhibit300A.csv', encoding= 'unicode_escape')

Cell 46 · code

Strategy=Strategy.drop(['Investment Title (Exhibit 53)','Investment Title (Exhibit 300)','Date of Last Change to Activities','Date of Last Update to Activities','Date of Last Update to Activities','Date of Last Update to Activities','Date of Last Change to Contracts','Date of Last Change to Performance Metrics','Date of Last Tech Stat','Budget Year','Date of Last Investment Detail Update','Investment Auto Submission Date'],axis=1)

Cell 47 · code

Strategy=Strategy.drop(['Data Freshness','Date of Last Change to CIO Evaluation','Date of Last Update to CIO Evaluation'],axis=1)

Cell 48 · code

Strategy=Strategy.drop(['IPT Charter Date','Date Investment First Submitted'],axis=1)

Cell 49 · code

Strategy=Strategy.drop(['Business Case ID'],axis=1)

Cell 50 · code

Strategy=Strategy.drop(['CIO Evaluation Color'],axis=1)

Cell 51 · code

Strategy['Date of Last Baseline']=Strategy['Date of Last Baseline'].astype('datetime64')

Cell 52 · code

Strategy=Strategy.dropna()

Cell 53 · code

Strategy=Strategy.rename(columns={'Agency Name':'Agency_Name',"Bureau Name": "Bureau_Name",'Bureau Code':'Bureau_Code','Number of changes to Baseline':'Number_of_changes_to_Baseline','Evaluation (by Agency CIO)':'Evalu)ation_by_CIO"}'})

Cell 54 · code

Strategy=Strategy.rename(columns={'Evalu)ation_by_CIO"}':'Evaluation_by_CIO','Date of Last Baseline':'Date_Last_Baseline'})

Cell 55 · markdown

Run a classification to predict how CIO rates investments

Cell 56 · code

Stripped_for_CIO_forecast=Strategy.drop(['Bureau_Name','Brief Summary','Summary of Performance Gap','Accomplishments PY','Accomplishments CY BY','Explanation of Change to PY or CY Funding','No EVM Explanation','CIO Evaluation Comments'],axis=1)

Cell 57 · code

dabl.plot(Stripped_for_CIO_forecast,'Evaluation_by_CIO')

Cell 58 · code

Investment_classification_model=dabl.SimpleClassifier (random_state=0)

Cell 59 · code

X=Strategy.drop(['Evaluation_by_CIO','Bureau_Name','Brief Summary','Summary of Performance Gap','Accomplishments PY','Accomplishments CY BY','Explanation of Change to PY or CY Funding','No EVM Explanation','CIO Evaluation Comments'],axis=1)

Cell 60 · code

y=Strategy["Evaluation_by_CIO"]

Cell 61 · code

Investment_classification_model.fit(X,y)

Cell 62 · code

Contracts

Cell 63 · markdown

Work out Data Structure as you go.

This results in the following Cypher code to be placed into Neo4j

LOAD CSV WITH HEADERS FROM ‘file:///Oxford_Strategy_Out.csv’ AS row MERGE (m:Agency {Code:row.Agency_Code}) MERGE (o:Bureau {Code:row.Bureau_Code,Name:row.Bureau_Name}) MERGE (n:Investment {Identifier:row.Unique_Investment_Identifier}) CREATE (m)-[:owns]->(o) CREATE (o)-[:responsible_for]->(n) SET n.Summary=row.Brief_Summary SET n.Performance_Gap=row.Summary_of_Performance_Gap SET n.Evaluation_by_CIO=row.Evaluation_by_CIO SET n.Changes_to_Baseline=row.Number_of_changes_to_Baseline SET n.Last_Baseline=row.Date_Last_Baseline

LOAD CSV WITH HEADERS FROM ‘file:///Oxford_Strategy_Out.csv’ AS row MATCH (m:Agency {Code:row.Agency_Code}) SET m.name = row.Agency_Name RETURN m

“MERGE matches on the entire pattern you specify within a single clause…The solution is to MERGE on the unique property and then use SET to update additional properties.

Cell 64 · code

Strategy=Strategy.rename(columns={'Unique Investment Identifier':'Unique_Investment_Identifier','Brief Summary':'Brief_Summary',
'Summary of Performance Gap':'Summary_of_Performance_Gap','Agency Code':'Agency_Code'})

Cell 65 · code

Strategy.to_csv(directory+'Oxford_Strategy_Out.csv', index=False)

Cell 66 · markdown

Bring in Contracts file

Cell 67 · code

Contracts=pd.read_csv(directory+'Contracts.csv', encoding= 'unicode_escape')

Cell 68 · code

Contracts=Contracts.drop(['Business Case ID','Agency Code','Agency Name','Investment Title','Agency Contract ID','Contract Status','Contracting Agency ID','Contract Number (PIID)','Performance Based Contract (USAspending)','Contract Start Date (USAspending)','Contract End Date (USAspending)','Contract Compete (USAspending)','Base Contract ID (USAspending)'
        ,'Identifying Agency ID (USAspending)','Transaction Number (USAspending)','Timestamp (Base Contract)'],axis=1)

Cell 69 · code

Contracts=Contracts.drop(['IDV Agency ID','Match found in USAspending','Solicitation ID (USAspending)'],axis=1)

Cell 70 · code

Contracts=Contracts.rename(columns={'Unique Investment Identifier':'Unique_Investment_Identifier','Contract ID':'Contract_ID','IDV PIID':'IDV_PIID','Vendor Name (USAspending)':'Vendor_name','Action Obligation Amount (In $ million) (USAspending)':'Contract_size','Contract Description (USAspending)':'Contract_Description'})

Cell 71 · markdown

Start of training a model with data across two dataframes:

Do Suppliers predict CIO Evaluation success? Work in progress

Cell 72 · code

Contracts1=Contracts.drop(['Contract_ID','Contract_size','Contract_Description','Type of Contract (USAspending)','Timestamp (Contract)'],axis=1)

Cell 73 · code

# Use merge or join to add CIO Evaluation in from Strategy DF, matching w Unique Investment Identifier. Then fit

Cell 74 · markdown

Work out Data Structure as you go.

This results in the following Cypher code to be placed into Neo4j

Cell 75 · code

Contracts=Contracts.dropna()

Cell 76 · code

Contracts

Cell 77 · code

Contracts.to_csv(directory+'Oxford_Contracts_Out.csv', index=False)

Cell 78 · code

LOAD CSV WITH HEADERS FROM 'file:///Oxford_Contracts_Out.csv' AS row 
MERGE (m:Contract {ID:row.Contract_ID})
MERGE (n:Investment {Identifier:row.Unique_Investment_Identifier})
MERGE (o:Supplier {ID:row.IDV_PIID})
SET m.Description=row.Contract_Description
SET m.Contract_size=row.Contract_size
SET o.name=row.Vendor_name
CREATE (n)-[:lets_contract]->(m)
CREATE (n)-[:uses_Supplier]->(o)
CREATE (o)-[:delivers]->(m)

Cell 79 · markdown

Bring in Metrics file

Cell 80 · code

Metrics=pd.read_csv(directory+'Performance_Metrics.csv', encoding= 'unicode_escape')

Cell 81 · code

Metrics=Metrics.drop(['Business Case ID','Agency Name','Agency Performance Metric ID','Comment','Updated Date','Updated Time','Reporting Frequency'],axis=1)

Cell 82 · code

Metrics=Metrics.drop(['Target for PY','Actual for PY','Most Recent Actual Results','Measurement Condition'],axis=1)

Cell 83 · code

Metrics=Metrics.rename(columns={'Unique Investment Identifier':'Unique_Investment_Identifier','Performance Metric ID':'Performance_Metric_ID','Metric Description':'Metric_Description','Unit of Measure':'Unit_of_Measure','FEA Performance Measurement Category Mapping':'Measurement_category','Actuals have Met/Not Met Target':'Metric_results'})

Cell 84 · code

Metrics=Metrics.dropna()

Cell 85 · code

Metrics['Agency Code']=Metrics['Agency Code'].astype('category')

Cell 86 · code

Metrics

Cell 87 · markdown

Run a classification to see if one can predict KPI success

Cell 88 · code

Stripped_for_KPI_forecast=Metrics.drop(['Performance_Metric_ID','Investment Title','Baseline','Target for CY'],axis=1)

Cell 89 · code

dabl.plot(Stripped_for_KPI_forecast,'Metric_results')

Cell 90 · code

Metric_classification_model=dabl.SimpleClassifier (random_state=0)

Cell 91 · code

X=Metrics.drop(['Performance_Metric_ID','Investment Title','Baseline','Target for CY','Metric_results'],axis=1)

Cell 92 · code

y=Metrics['Metric_results']

Cell 93 · code

Metric_classification_model.fit(X,y)

Cell 94 · code

Metrics.to_csv(directory+'Oxford_Metrics_Out.csv', index=False)

Cell 95 · markdown

from this, we know that Metric ID is unique constraint=(Metrics.duplicated(subset=’Performance_Metric_ID’, keep=False))==True Metrics[constraint]

Cell 96 · markdown

Work out Data Structure as you go.

This results in the following Cypher code to be placed into Neo4j

LOAD CSV WITH HEADERS FROM ‘file:///Oxford_Metrics_Out.csv’ AS row MERGE (m:Metric {ID:row.Performance_Metric_ID}) MERGE (n:Investment {Identifier:row.Unique_Investment_Identifier}) SET m.Description=row.Metric_Description SET m.Measurement_category=row.Measurement_category SET m.Metric_results=row.Metric_results MERGE (n)-[:judged_by]->(m)

Cell 97 · markdown

Bring in Business Mapping

Cell 98 · code

Mapping=pd.read_csv(directory+'Exhibit53.csv', encoding= 'unicode_escape')

Cell 99 · code

Mapping=Mapping.drop(['Agency Code','Agency Name','Previous UPI','Investment Category','Bureau Code','Bureau Name','Part of Exhibit 53'],axis=1)

Cell 100 · code

Mapping=Mapping.drop(['Mission Delivery And Management Support Area','Line Item Descriptor','Investment Title','Investment Description','Updated Date'],axis=1)

Cell 101 · code

Mapping=Mapping.drop(['Budget Year','XML Request ID','Updated Time','O&M BY Contributions ($ M)','O&M BY Agency Funding ($ M)','O&M CY Contributions ($ M)'],axis=1)

Cell 102 · code

Mapping=Mapping.drop(['O&M CY Agency Funding ($ M)','O&M PY Contributions ($ M)','O&M PY Agency Funding ($ M)','DME BY Contributions ($ M)'],axis=1)

Cell 103 · code

Mapping=Mapping.drop(['Segment Architecture - Agency Segment','Total IT Spending FY2011 (PY) ($ M)','Total IT Spending FY2012 (CY) ($ M)'],axis=1)

Cell 104 · code

Mapping=Mapping.drop(['DME PY Contributions ($ M)','DME CY Contributions ($ M)','DME BY Agency Funding ($ M)'],axis=1)

Cell 105 · code

Mapping=Mapping.rename(columns={'Unique Investment Identifier':'Unique_Investment_Identifier','Type of Investment':'Investment_type'})

Cell 106 · code

Mapping=Mapping.rename(columns={'FEA BRM Mapping - Sub-Function':'Sub-Function','FEA BRM Mapping - Primary Function':'Function','FEA BRM Mapping - Business Area':'Business_Area'})

Cell 107 · code

Mapping=Mapping.rename(columns={'Service Code Mapping - Component':'Service_Component','Service Code Mapping - Primary Function':'Service','Function':'Business Function'})

Cell 108 · code

Mapping=Mapping.rename(columns={'Service Code Mapping - Business Area':'Service Area','Segment Architecture - Federal Standard Segment':'Architecture_code'})

Cell 109 · code

Mapping=Mapping.drop(['Total IT Spending FY2013 (BY) ($ M)','DME PY Agency Funding ($ M)'],axis=1)

Cell 110 · code

Mapping=Mapping.rename(columns={'DME CY Agency Funding ($ M)':'Enhancement_spend_$m'})

Cell 111 · code

Mapping=Mapping.rename(columns={'Enhancement_spend_$M)':'Enhancement_spend_$m'})

Cell 112 · code

Mapping=Mapping.rename(columns={'Service Area':'Service_Area'})

Cell 113 · code

Mapping=Mapping.rename(columns={'Business Function':'Business_Function'})

Cell 114 · code

Mapping=Mapping.dropna()

Cell 115 · code

Mapping.to_csv(directory+'Oxford_Mapping_Out.csv', index=False)

Cell 116 · markdown

Code for Neo4j

LOAD CSV WITH HEADERS FROM ‘file:///Oxford_Mapping_Out.csv’ AS row MERGE (n:Investment {Identifier:row.Unique_Investment_Identifier}) SET n.Enhancement_spend_$m=toFloat(row.Enhancement_spend_$m) MERGE (o:Service {name:row.Service}) MERGE (p:Business_Area {name:row.Business_Area}) MERGE (q:Business_Function {name:row.Business_Function}) SET n.Investment_type=row.Investment_type MERGE (n)-[:has_business_function]->(q) MERGE (q)-[:serves]->(p) MERGE (n)-[:is_a_type_of]->(o)

Cell 117 · markdown

Bring in activities

Cell 118 · code

Activities=pd.read_csv(directory+'Activities.csv', encoding= 'unicode_escape')

Cell 119 · code

Activities=Activities.drop(['Agency Name','Business Case ID','Investment Title','Date of Last Change','Baseline ID'],axis=1)

Cell 120 · code

Activities=Activities.drop(['Cost Variance','Cost Variance Percent'],axis=1)

Cell 121 · code

# Cost Variance and Cost % are unreliable: they give different signs, so dropped

Cell 122 · code

Activities=Activities.drop(['Structure ID','Schedule Variance (in days)','Schedule Variance Percent','Schedule Duration (in days)'],axis=1)

Cell 123 · code

Activities=Activities.dropna(subset=['Unique Investment Identifier','Agency Code','Agency Activity ID','Project ID','Activity Name','Start Date Planned','Completion Date Planned',
                                     'Total Costs Planned','Activity Status','Has No Child Activity','Date of Last Update','Unique Activity ID'])

Cell 124 · code

Activities=Activities.drop(['Agency Activity ID'],axis=1)

Cell 125 · code

Activities['Start Date Planned'] = pd.to_datetime(Activities['Start Date Planned'])
Activities['Start Date Projected'] = pd.to_datetime(Activities['Start Date Projected'])
Activities['Start Date Actual'] = pd.to_datetime(Activities['Start Date Actual'])
Activities['Completion Date Planned'] = pd.to_datetime(Activities['Completion Date Planned'])
Activities['Completion Date Projected'] = pd.to_datetime(Activities['Completion Date Projected'])
Activities['Completion Date Actual'] = pd.to_datetime(Activities['Completion Date Actual'])

Cell 126 · code

Activities['Planned_Duration']=np.subtract(Activities['Completion Date Planned'],Activities['Start Date Planned'])

Cell 127 · code

Activities=Activities.drop('Completion Date Planned',axis=1)

Cell 128 · code

Activities['Actual_duration']=np.subtract(Activities['Completion Date Actual'],Activities['Start Date Actual'])
Activities['Actual_start_delay']=np.subtract(Activities['Start Date Actual'],Activities['Start Date Planned'])

Cell 129 · code

Activities=Activities.drop(['Start Date Actual','Completion Date Actual'],axis=1)

Cell 130 · code

# This shows that a project may well start early or late
plt.scatter (Activities['Start Date Planned'],Activities['Start Date Projected'])

Saved output

<matplotlib.collections.PathCollection at 0x12addd690>

Cell 131 · code

# similar holds for these
# plt.scatter (Activities['Start Date Planned'],Activities['Start Date Actual'])
# plt.scatter (Activities['Start Date Projected'],Activities['Start Date Actual'])

Cell 132 · code

Training_set=Activities[Activities['Activity Status']=='Completed - Closed']
Run_time_set=Activities[Activities['Activity Status']=='In-progress']

Cell 133 · code

Training_set['Cost_increase_%']=np.divide(np.subtract(Training_set['Total Costs Actual'],Training_set['Total Costs Planned']),Training_set['Total Costs Planned'])
Training_set['Schedule_increase_%']=np.divide(np.subtract(Training_set['Actual_duration'],Training_set['Planned_Duration']),Training_set['Planned_Duration'])

Cell 134 · code

Activities=Activities.drop(['Total Costs Actual'],axis=1)
Training_set=Training_set.drop(['Total Costs Actual'],axis=1)

Cell 135 · code

Training_set=Training_set.dropna()

Cell 136 · code

# Switched these off as they arent recognised as float. but below makes it look like good percentage
# Training_set['Cost_increase_%'] = pd.Series(["{0:.2f}%".format(val * 100) for val in Training_set['Cost_increase_%']], index = Training_set.index)
# Training_set['Schedule_increase_%'] = pd.Series(["{0:.2f}%".format(val * 100) for val in Training_set['Schedule_increase_%']], index = Training_set.index)

Cell 137 · code

# to run this again, need to run the % format box just above
plt.scatter(Training_set['Schedule_increase_%'],Training_set['Cost_increase_%'])

Cell 138 · code

Training_set['Planned_Duration']=Training_set['Planned_Duration'].dt.days
Training_set['Actual_duration']=Training_set['Actual_duration'].dt.days
Training_set['Actual_start_delay']=Training_set['Actual_start_delay'].dt.days

Cell 139 · code

Training_set=Training_set[Training_set['Cost_increase_%']<100]

Cell 140 · code

Training_set=Training_set[Training_set['Schedule_increase_%']<100]       

Cell 141 · code

Training_set1=Training_set.drop(['Activity Name','Activity Description','Total Costs Projected','Start Date Projected','Date of Last Update','Unique Activity ID'],axis=1)

Cell 142 · code

Training_set1=Training_set1.drop(['Completion Date Projected'],axis=1)

Cell 143 · code

Training_set1['Project ID']=Training_set1['Project ID'].astype('category')

Cell 144 · code

Training_set1['Start Date Planned']=(Training_set1['Start Date Planned']-pd.to_datetime('1/1/2000')).dt.days #now days after 2000

Cell 145 · code

Training_set1=Training_set1.drop('Actual_duration',axis=1)

Cell 146 · code

#dabl.plot(Training_set1.drop(['Cost_increase_%','Schedule_increase_%'],axis=1),Training_set1['Cost_increase_%'])

Cell 147 · code

#dabl.plot(Training_set1.drop(['Cost_increase_%','Schedule_increase_%'],axis=1),Training_set1['Schedule_increase_%'])

Cell 148 · code

Schedule_Activities_regression_model=dabl.SimpleRegressor (random_state=0)
X=Training_set1.drop(['Cost_increase_%','Schedule_increase_%'],axis=1)

Cell 149 · code

y=Training_set1['Schedule_increase_%']
Schedule_Activities_regression_model.fit(X,y)

Cell 150 · code

Cost_Activities_regression_model=dabl.SimpleRegressor (random_state=0)
X=Training_set1.drop(['Cost_increase_%','Schedule_increase_%'],axis=1)

Cell 151 · code

y=Training_set1['Cost_increase_%']
Cost_Activities_regression_model.fit(X,y)

Cell 152 · code

Activities=Activities.rename(columns={'Unique Investment Identifier':'Unique_Investment_Identifier','Project ID':'Project_ID','Activity Name':'Activity_Name','Activity Description':'Activity Description','Key Deliverable / Usable Functionality':'Output_type','Start Date Planned':'Start Date Planned','Total Costs Planned':'Planned_Costs','Activity Status':'Status','Has No Child Activity':'No_Child_Activity','Unique Activity ID':'ID'})

Cell 153 · code

Activities=Activities.drop(['Unique_Investment_Identifier','Agency Code','Start Date Projected','Start Date Projected'],axis=1)

Cell 154 · code

Activities=Activities.rename(columns={'Activity Description':'Description'})

Cell 155 · code

Activities=Activities.rename(columns={'Start Date Planned':'Start_Date_Planned'})

Cell 156 · markdown

Code for Neo4j

LOAD CSV WITH HEADERS FROM ‘file:///Oxford_Activities_Out.csv’ AS row MERGE (n:Project {Id:row.Project_ID}) MERGE (o:Activity {Id:row.ID}) SET o.name=row.Activity_Name SET o.description=row.Description SET o.output_type=row.Output_type SET o.planned_Costs=row.Planned_Costs SET o.status=row.Status SET o.No_Child_Activity=row.No_Child_Activity SET o.Planned_Duration=row.Planned_Duration SET o.Actual_duration=row.Actual_duration SET o.Actual_start_delay=row.Actual_start_delay SET o.Start_Date_Planned=row.Start_Date_Planned MERGE (n)-[:has_task]->(o)

This is the additional code to take subgraph, as a query, to export Cypher code, say for populating a Sandbox

CALL apoc.export.cypher.query( “MATCH (n:Bureau)-[a]-(o:Investment)-[b]-(p:Project)-[c]-(q:Activity),(o)-[d]-(r:Service),(o)-[e]-(s:Business_Function)-[f]-(t:Business_Area),(o)-[g]-(u:Metric),(o)-[h]-(v:Agency) WHERE n.Name=’Small Business Administration’ OR RETURN *”,”export.cypher”,{});

Cell 157 · code

Activities.to_csv(directory+'Oxford_Activities_Out.csv', index=False)

Cell 158 · code

Activities

Cell 159 · code