← Return to the example · Supporting files

subgraph OxfordUS Portfolio

Saved notebook · 106 cells. Code and outputs below are retained from the original; nothing was executed for this reading copy. Environment dependencies, missing data and the model limitations remain as described in the linked guide.

Cell 1 · markdown

Setting up a Graph Database for the US Government IT Portfolios

Cell 2 · code

# Privacy edit, 4 October 2026: personal filesystem paths were replaced by neutral example paths.
# Existing calculations and saved results were not rerun; this file is a privacy-edited reading edition.
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

Saved output

Matplotlib is building the font cache; this may take a moment.

Cell 3 · code

directory = 'example-data/2013/'

Cell 4 · markdown

Bring in Projects file

Cell 5 · code

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

Cell 6 · 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 7 · 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 8 · 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

Planned Project Completion Date (B2)
Projected/Actual Project Completion Date (B2)
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 9 · 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 10 · 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 11 · code

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

Cell 12 · 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 13 · code

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

Cell 14 · code

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

Cell 15 · code

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

Cell 16 · 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 17 · code

Constraint=Projects['Agency Code']==9

Cell 18 · code

Projects=Projects[Constraint]

Cell 19 · 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 20 · markdown

Export to CSV en route to Neo4j

Cell 21 · 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 22 · code

Projects=Projects.dropna()

Cell 23 · code

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

Cell 24 · markdown

#Cypher code for Neo4j LOAD CSV WITH HEADERS FROM ‘file:///Health_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 25 · markdown

Pull in strategic investment information

Cell 26 · code

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

Cell 27 · 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 28 · code

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

Cell 29 · code

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

Cell 30 · code

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

Cell 31 · code

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

Cell 32 · code

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

Cell 33 · code

#Strategy=Strategy.dropna()

Cell 34 · 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 35 · code

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

Cell 36 · code

Constraint=Strategy['Agency Code']==9

Cell 37 · code

Strategy=Strategy[Constraint]

Cell 38 · 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:///Health_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:///Health_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 39 · code

Cell 40 · 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 41 · code

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

Cell 42 · markdown

Bring in Contracts file

Cell 43 · code

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

Cell 44 · code

Constraint=Contracts['Agency Code']==9

Cell 45 · code

Contracts=Contracts[Constraint]

Cell 46 · 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 47 · code

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

Cell 48 · 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 49 · markdown

Work out Data Structure as you go.

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

Cell 50 · code

Contracts=Contracts.dropna()

Cell 51 · code

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

Cell 52 · markdown

LOAD CSV WITH HEADERS FROM ‘file:///Health_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 53 · markdown

Bring in Metrics file

Cell 54 · code

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

Cell 55 · code

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

Cell 56 · code

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

Cell 57 · 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 58 · code

#Metrics=Metrics.dropna()

Cell 59 · code

Constraint=Metrics['Agency Code']==9

Cell 60 · code

Metrics=Metrics[Constraint]

Cell 61 · code

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

Cell 62 · markdown

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

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:///Health_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 64 · markdown

Bring in Business Mapping

Cell 65 · code

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

Cell 66 · code

Constraint=Mapping['Agency Code']==9

Cell 67 · code

Mapping=Mapping[Constraint]

Cell 68 · code

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

Cell 69 · code

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

Cell 70 · 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 71 · 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 72 · code

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

Cell 73 · code

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

Cell 74 · code

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

Cell 75 · 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 76 · code

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

Cell 77 · code

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

Cell 78 · code

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

Cell 79 · code

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

Cell 80 · code

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

Cell 81 · code

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

Cell 82 · code

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

Cell 83 · code

#Mapping=Mapping.dropna()

Cell 84 · code

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

Cell 85 · markdown

Code for Neo4j

LOAD CSV WITH HEADERS FROM ‘file:///Health_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 86 · markdown

Bring in activities

Cell 87 · code

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

Cell 88 · code

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

Cell 89 · code

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

Cell 90 · code

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

Cell 91 · code

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

Cell 92 · code

Constraint=Activities['Agency Code']==9

Cell 93 · code

Activities=Activities[Constraint]

Cell 94 · 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 95 · code

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

Cell 96 · 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 97 · code

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

Cell 98 · code

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

Cell 99 · 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 100 · code

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

Cell 101 · 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 102 · code

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

Cell 103 · code

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

Cell 104 · code

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

Cell 105 · markdown

Code for Neo4j

LOAD CSV WITH HEADERS FROM ‘file:///Health_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 106 · code

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