global_affairs_quicktest_local_fs_one script and data

Input files

Global Affairs projects financials

project_idpartner_idamtstart_dateend_datecountry_amt_jsonsector_amt_json
p012277001299791209600000.02023121420251231{"R-SOUTHSAHARA": 846720000.0, "R-SOUTHAMERICA": 36288000.0, "R-MIDDLEEAST": 84672000.0, "R-ASIA": 2 ... total length 111{"12262": 362880000.0, "12263": 217728000.0, "13040": 628992000.0}
p010807001295781000000000.02022033120470331{"IN": 210000000.0, "PH": 210000000.0, "ID": 210000000.0, "ZA": 210000000.0, "DO": 80000000.0, "MK": ... total length 112{"23110": 100000000.0, "23210": 900000000.0}
p00524700129979930400000.02020121620221231{"R-SOUTHSAHARA": 604760000.0, "R-SOUTHAMERICA": 37216000.0, "R-MIDDLEEAST": 74432000.0, "R-CENTRALA ... total length 140{"12262": 279120000.0, "12263": 158168000.0, "13040": 493112000.0}
d00382700129979785000000.02017073120201231{"R-AFRICA": 266900000.0, "R-AMERICA": 259050000.0, "R-ASIA": 259050000.0}{"12262": 235500000.0, "12263": 133450000.0, "13040": 416050000.0}
p01218300129636500000000.02024030820470331{"R-WESTINDIES": 50000000.0, "R-NORTHCENTRALAMERICA": 150000000.0, "R-SOUTHAMERICA": 300000000.0}{"21012": 25000000.0, "23183": 50000000.0, "23210": 70000000.0, "23230": 125000000.0, "23240": 10000 ... total length 229
d00224300129981500000000.02015091720211231{"R-AFRICA": 359000000.0, "R-AMERICA": 28000000.0, "R-ASIA": 113000000.0}{"12250": 500000000.0}
d00051400129979450000000.012015032720170331{"R-AFRICA": 283500000.01, "R-AMERICA": 36000000.0, "R-ASIA": 103500000.0, "R-EUROPE": 27000000.0}{"12262": 144000000.0, "12263": 81000000.0, "13040": 225000000.0}
m01335400129979450000000.02011120920141231{"R-AFRICA": 225000000.0, "R-AMERICA": 49500000.0, "R-ASIA": 126000000.0, "R-EUROPE": 49500000.0}{"12262": 99000000.0, "12263": 72000000.0, "13040": 279000000.0}
m01276100129979450000000.02008031920110331{"R-AFRICA": 283500000.0, "R-AMERICA": 31500000.0, "R-ASIA": 103500000.0, "R-EUROPE": 31500000.0}{"12262": 99000000.0, "12263": 72000000.0, "13040": 279000000.0}
p00699200129981400000000.02021033020260331{"R-AFRICA": 276000000.0, "R-AMERICA": 4000000.0, "R-ASIA": 120000000.0}{"12250": 336000000.0, "12350": 20000000.0, "13030": 24000000.0, "13040": 20000000.0}

Total 19 rows

1_read_project_financials

Script node 1_read_project_financials:

{
  "1_read_project_financials": {
    "type": "file_table",
    "desc": "Read file harvested_project_financials_*.csv to table",
    "r": {
      "urls": [
        "{dir_in}/{harvested_project_financials_file}"
      ],
      "csv": {
        "hdr_line_idx": 0,
        "first_data_line_idx": 1,
        "separator": ","
      },
      "columns": {
        "project_id": {
          "col_type": "string",
          "csv": {
            "col_hdr": "project_id"
          }
        },
        "partner_id": {
          "col_type": "int",
          "csv": {
            "col_hdr": "partner_id",
            "col_format": "%d"
          }
        },
        "amt": {
          "col_type": "float",
          "csv": {
            "col_hdr": "amt",
            "col_format": "%f"
          }
        },
        "start_date": {
          "col_type": "int",
          "csv": {
            "col_hdr": "start_date",
            "col_format": "%d"
          }
        },
        "end_date": {
          "col_type": "int",
          "csv": {
            "col_hdr": "end_date",
            "col_format": "%d"
          }
        },
        "country_amt_json": {
          "col_type": "string",
          "csv": {
            "col_hdr": "country_amt_json"
          }
        },
        "sector_amt_json": {
          "col_type": "string",
          "csv": {
            "col_hdr": "sector_amt_json"
          }
        }
      }
    },
    "w": {
      "name": "project_financials",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "partner_id": {
          "expression": "r.partner_id",
          "type": "int"
        },
        "amt": {
          "expression": "r.amt",
          "type": "float"
        },
        "start_date": {
          "expression": "r.start_date",
          "type": "int"
        },
        "end_date": {
          "expression": "r.end_date",
          "type": "int"
        },
        "country_amt_json": {
          "expression": "r.country_amt_json",
          "type": "string"
        },
        "sector_amt_json": {
          "expression": "r.sector_amt_json",
          "type": "string"
        }
      }
    }
  }
}

Script node 1_read_project_financials produces Cassandra table project_financials:

rowidamtbatch_idxcountry_amt_jsonend_datepartner_idproject_idsector_amt_jsonstart_date
89114129586476107542750000000{"R-AFRICA": 55000000.0, "R-AMERICA": 82500000.0, "R-ASIA": 137500000.0}2043032029578p006167001{"23210": 225500000.0, "31220": 49500000.0}20200329
12861376484389356883024000000{"R-AFRICA": 302400000.0}2010051729663m012672001{"11420": 21168000.0, "14021": 30240000.0, "14022": 30240000.0, "15110": 60480000.0, "21020": 604800 ... total length 14720071101
3700612225506749757850000000{"R-AFRICA": 266900000.0, "R-AMERICA": 259050000.0, "R-ASIA": 259050000.0}2020123129979d003827001{"12262": 235500000.0, "12263": 133450000.0, "13040": 416050000.0}20170731
49764703870843328254000000000{"R-AFRICA": 276000000.0, "R-AMERICA": 4000000.0, "R-ASIA": 120000000.0}2026033129981p006992001{"12250": 336000000.0, "12350": 20000000.0, "13030": 24000000.0, "13040": 20000000.0}20210330
30668506846294485434500000000{"R-AFRICA": 225000000.0, "R-AMERICA": 49500000.0, "R-ASIA": 126000000.0, "R-EUROPE": 49500000.0}2014123129979m013354001{"12262": 99000000.0, "12263": 72000000.0, "13040": 279000000.0}20111209
79473247350765983733500000000{"R-ASIA": 315000000.0, "R-OCEANIA": 35000000.0}2042123129588p012801001{"14021": 28000000.0, "23210": 150500000.0, "23630": 38500000.0, "23642": 35000000.0, "24030": 28000 ... total length 16920240312
1412909315914209915000000000{"R-AFRICA": 359000000.0, "R-AMERICA": 28000000.0, "R-ASIA": 113000000.0}2021123129981d002243001{"12250": 500000000.0}20150917
26522680692976473653000000000{"R-SOUTHSAHARA": 75000000.0, "R-AFRICA": 75000000.0, "R-AMERICA": 75000000.0, "R-ASIA": 75000000.0}2035063030488p008157001{"15170": 300000000.0}20190829
44053659352030157539304000000{"R-SOUTHSAHARA": 604760000.0, "R-SOUTHAMERICA": 37216000.0, "R-MIDDLEEAST": 74432000.0, "R-CENTRALA ... total length 1402022123129979p005247001{"12262": 279120000.0, "12263": 158168000.0, "13040": 493112000.0}20201216
23086750517459055814500000000{"R-AFRICA": 283500000.0, "R-AMERICA": 31500000.0, "R-ASIA": 103500000.0, "R-EUROPE": 31500000.0}2011033129979m012761001{"12262": 99000000.0, "12263": 72000000.0, "13040": 279000000.0}20080319

Total 19 rows

2_calc_quarterly_budget

Script node 2_calc_quarterly_budget:

{
  "2_calc_quarterly_budget": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "py_calc",
    "desc": "Calculate quarterly project budget for countries and sectors",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "project_financials",
      "expected_batches_total": 10
    },
    "p": {
      "python_code_urls": [
        "{dir_py}/calc_quarterly_budget.py"
      ],
      "calculated_fields": {
        "country_budget_json": {
          "expression": "map_to_quarterly_budget_json(r.start_date, r.end_date, r.country_amt_json)",
          "type": "string"
        },
        "sector_budget_json": {
          "expression": "map_to_quarterly_budget_json(r.start_date, r.end_date, r.sector_amt_json)",
          "type": "string"
        },
        "partner_budget_json": {
          "expression": "amt_to_quarterly_budget_json(r.start_date, r.end_date, r.amt)",
          "type": "string"
        }
      }
    },
    "w": {
      "name": "quarterly_project_bdgt",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "partner_id": {
          "expression": "r.partner_id",
          "type": "int"
        },
        "country_budget_json": {
          "expression": "p.country_budget_json",
          "type": "string"
        },
        "sector_budget_json": {
          "expression": "p.sector_budget_json",
          "type": "string"
        },
        "partner_budget_json": {
          "expression": "p.partner_budget_json",
          "type": "string"
        }
      }
    }
  }
}

Script node 2_calc_quarterly_budget produces Cassandra table quarterly_project_bdgt:

rowidbatch_idxcountry_budget_jsonpartner_budget_jsonpartner_idproject_idsector_budget_json
11107744274832323364{"R-AFRICA": {"2007-Q4": 19856189.451022606, "2008-Q1": 29621528.525296018, "2008-Q2": 29621528.5252 ... total length 354{"2007-Q4": 19856189.451022606, "2008-Q1": 29621528.525296018, "2008-Q2": 29621528.525296018, "2008- ... total length 34029663m012672001{"11420": {"2007-Q4": 1389933.2615715822, "2008-Q1": 2073506.9967707212, "2008-Q2": 2073506.99677072 ... total length 2429
11435860659726775940{"IN": {"2022-Q1": 22996.057818659658, "2022-Q2": 2092641.2614980289, "2022-Q3": 2115637.3193166885, ... total length 18632{"2022-Q1": 109505.03723171266, "2022-Q2": 9964958.388085851, "2022-Q3": 10074463.425317565, "2022-Q ... total length 306229578p010807001{"23110": {"2022-Q1": 10950.503723171267, "2022-Q2": 996495.8388085853, "2022-Q3": 1007446.342531756 ... total length 6063
88214771991052338028{"R-AFRICA": {"2023-Q1": 11405.109489051094, "2023-Q2": 518932.4817518248, "2023-Q3": 524635.0364963 ... total length 11700{"2023-Q1": 57025.54744525548, "2023-Q2": 2594662.408759124, "2023-Q3": 2623175.182481752, "2023-Q4" ... total length 292833915p010532001{"14021": {"2023-Q1": 5702.554744525547, "2023-Q2": 259466.2408759124, "2023-Q3": 262317.51824817515 ... total length 11924
54068275338583927673{"R-AFRICA": {"2020-Q1": 19661.582459485224, "2020-Q2": 596401.3346043852, "2020-Q3": 602955.1954242 ... total length 8472{"2020-Q1": 98307.91229742613, "2020-Q2": 2982006.6730219256, "2020-Q3": 3014775.9771210677, "2020-Q ... total length 286529578p006167001{"23210": {"2020-Q1": 80612.48808388942, "2020-Q2": 2445245.471877979, "2020-Q3": 2472116.3012392754 ... total length 5649
36490020519051475728{"R-WESTINDIES": {"2012-Q1": 10949.304719150334, "2012-Q2": 498193.3647213402, "2012-Q3": 503668.017 ... total length 9306{"2012-Q1": 54746.52359794153, "2012-Q2": 2490966.8237063396, "2012-Q3": 2518340.0855053104, "2012-Q ... total length 313029636m013705001{"14015": {"2012-Q1": 5474.652359575167, "2012-Q2": 249096.6823606701, "2012-Q3": 251834.00854045767 ... total length 27991
67995739530477132681{"R-AFRICA": {"2008-Q1": 3326263.5379061373, "2008-Q2": 23283844.765342962, "2008-Q3": 23539711.1913 ... total length 1626{"2008-Q1": 5279783.393501805, "2008-Q2": 36958483.75451264, "2008-Q3": 37364620.93862816, "2008-Q4" ... total length 39029979m012761001{"12262": {"2008-Q1": 1161552.346570397, "2008-Q2": 8130866.425992779, "2008-Q3": 8220216.606498195, ... total length 1206
43459664795897109881{"R-ASIA": {"2024-Q1": 917164.0704614937, "2024-Q2": 4173096.5205997964, "2024-Q3": 4218954.72412287 ... total length 4700{"2024-Q1": 1019071.1894016596, "2024-Q2": 4636773.911777551, "2024-Q3": 4687727.471247634, "2024-Q4 ... total length 228129588p012801001{"14021": {"2024-Q1": 81525.69515213277, "2024-Q2": 370941.9129422041, "2024-Q3": 375018.1976998107, ... total length 18663
72493632905074676855{"R-WESTINDIES": {"2024-Q1": 142450.14245014245, "2024-Q2": 540123.4567901234, "2024-Q3": 546058.879 ... total length 8602{"2024-Q1": 1424501.4245014247, "2024-Q2": 5401234.567901235, "2024-Q3": 5460588.793922127, "2024-Q4 ... total length 279129636p012183001{"21012": {"2024-Q1": 71225.07122507122, "2024-Q2": 270061.7283950617, "2024-Q3": 273029.43969610636 ... total length 31222
55945651947640020531{"R-SOUTHSAHARA": {"2019-Q3": 427830.59636992216, "2019-Q4": 1192739.8444252377, "2020-Q1": 1179775. ... total length 7948{"2019-Q3": 1711322.3854796886, "2019-Q4": 4770959.377700951, "2020-Q1": 4719101.123595505, "2020-Q2 ... total length 190930488p008157001{"15170": {"2019-Q3": 1711322.3854796886, "2019-Q4": 4770959.377700951, "2020-Q1": 4719101.123595505 ... total length 1920
25854666560262073538{"R-SOUTHSAHARA": {"2020-Q4": 12970723.860589812, "2021-Q1": 72960321.71581769, "2021-Q2": 73770991. ... total length 1446{"2020-Q4": 19954959.78552279, "2021-Q1": 112246648.79356569, "2021-Q2": 113493833.78016086, "2021-Q ... total length 27829979p005247001{"12262": {"2020-Q4": 5986487.935656836, "2021-Q1": 33673994.638069704, "2021-Q2": 34048150.13404825 ... total length 850

Total 19 rows

3_tag_countries

Script node 3_tag_countries:

{
  "3_tag_countries": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "tag_and_denormalize",
    "desc": "Tag by country",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "quarterly_project_bdgt",
      "expected_batches_total": 10
    },
    "p": {
      "tag_field_name": "country_tag",
      "tag_criteria_url": "{dir_cfg}/tag_criteria_country.json"
    },
    "w": {
      "name": "quarterly_project_bdgt_tggd_by_country",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "country": {
          "expression": "p.country_tag",
          "type": "string"
        },
        "country_budget_json": {
          "expression": "r.country_budget_json",
          "type": "string"
        }
      }
    }
  }
}

Script node 3_tag_countries produces Cassandra table quarterly_project_bdgt_tggd_by_country:

rowidbatch_idxcountrycountry_budget_jsonproject_id
12484130693632401547R-EUROPE{"R-AFRICA": {"2023-Q1": 11405.109489051094, "2023-Q2": 518932.4817518248, "2023-Q3": 524635.0364963 ... total length 11700p010532001
1618140382783484083R-ASIA{"R-AFRICA": {"2019-Q2": 2959048.8771466315, "2019-Q3": 5671510.347864377, "2019-Q4": 5671510.347864 ... total length 1590p005253001
89583688328738353078R-SOUTHSAHARA{"R-SOUTHSAHARA": {"2023-Q4": 20348411.21495327, "2024-Q1": 102872523.36448598, "2024-Q2": 102872523 ... total length 1158p012277001
24030013354426464167R-AFRICA{"R-AFRICA": {"2011-Q4": 4624664.879356569, "2012-Q1": 18297587.131367292, "2012-Q2": 18297587.13136 ... total length 1626m013354001
75238880778058311520IN{"IN": {"2022-Q1": 22996.057818659658, "2022-Q2": 2092641.2614980289, "2022-Q3": 2115637.3193166885, ... total length 18632p010807001
6155041732057919145R-AMERICA{"R-AFRICA": {"2020-Q1": 19661.582459485224, "2020-Q2": 596401.3346043852, "2020-Q3": 602955.1954242 ... total length 8472p006167001
16101191179342164915R-NORTHCENTRALAMERICA{"R-WESTINDIES": {"2012-Q1": 10949.304719150334, "2012-Q2": 498193.3647213402, "2012-Q3": 503668.017 ... total length 9306m013705001
67576515770253087307R-EUROPE{"R-AFRICA": {"2011-Q4": 4624664.879356569, "2012-Q1": 18297587.131367292, "2012-Q2": 18297587.13136 ... total length 1626m013354001
68618698277058683367R-EUROPE{"R-AFRICA": {"2015-Q1": 1925951.0870244564, "2015-Q2": 35052309.783845104, "2015-Q3": 35437500.0012 ... total length 1040d000514001
46289653198312634010ID{"IN": {"2022-Q1": 22996.057818659658, "2022-Q2": 2092641.2614980289, "2022-Q3": 2115637.3193166885, ... total length 18632p010807001

Total 63 rows

3_tag_sectors

Script node 3_tag_sectors:

{
  "3_tag_sectors": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "tag_and_denormalize",
    "desc": "Tag by sector",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "quarterly_project_bdgt",
      "expected_batches_total": 10
    },
    "p": {
      "tag_field_name": "sector_tag",
      "tag_criteria_url": "{dir_cfg}/tag_criteria_sector.json"
    },
    "w": {
      "name": "quarterly_project_bdgt_tggd_by_sector",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "sector": {
          "expression": "int(p.sector_tag)",
          "type": "int"
        },
        "sector_budget_json": {
          "expression": "r.sector_budget_json",
          "type": "string"
        }
      }
    }
  }
}

Script node 3_tag_sectors produces Cassandra table quarterly_project_bdgt_tggd_by_sector:

rowidbatch_idxproject_idsectorsector_budget_json
62653691246083496450p00524700113040{"12262": {"2020-Q4": 5986487.935656836, "2021-Q1": 33673994.638069704, "2021-Q2": 34048150.13404825 ... total length 850
22098963426244573870p00524700112262{"12262": {"2020-Q4": 5986487.935656836, "2021-Q1": 33673994.638069704, "2021-Q2": 34048150.13404825 ... total length 850
4112359989135707953p01218300141030{"21012": {"2024-Q1": 71225.07122507122, "2024-Q2": 270061.7283950617, "2024-Q3": 273029.43969610636 ... total length 31222
17950156524461920405m01370500123210{"14015": {"2012-Q1": 5474.652359575167, "2012-Q2": 249096.6823606701, "2012-Q3": 251834.00854045767 ... total length 27991
24577378698316482435m01370500114015{"14015": {"2012-Q1": 5474.652359575167, "2012-Q2": 249096.6823606701, "2012-Q3": 251834.00854045767 ... total length 27991
56551353331528045701m01351200114022{"14021": {"2011-Q1": 749203.7684955753, "2011-Q2": 1311106.5948672567, "2011-Q3": 1325514.359646017 ... total length 6564
46793817277451549345m01276100112263{"12262": {"2008-Q1": 1161552.346570397, "2008-Q2": 8130866.425992779, "2008-Q3": 8220216.606498195, ... total length 1206
60924135768090875529p00815700115170{"15170": {"2019-Q3": 1711322.3854796886, "2019-Q4": 4770959.377700951, "2020-Q1": 4719101.123595505 ... total length 1920
22197146174613103828p01227700112262{"12262": {"2023-Q4": 8720747.663551401, "2024-Q1": 44088224.29906542, "2024-Q2": 44088224.29906542, ... total length 855
84711733690516977567d00051400112262{"12262": {"2015-Q1": 978260.8695652174, "2015-Q2": 17804347.826086957, "2015-Q3": 18000000.0, "2015 ... total length 766

Total 89 rows

4_tag_countries_quarter

Script node 4_tag_countries_quarter:

{
  "4_tag_countries_quarter": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "tag_and_denormalize",
    "desc": "Tag by country and quarter",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "quarterly_project_bdgt_tggd_by_country",
      "expected_batches_total": 10
    },
    "p": {
      "tag_field_name": "quarter_tag",
      "tag_criteria_url": "{dir_cfg}/tag_criteria_country_quarter.json"
    },
    "w": {
      "name": "quarterly_project_bdgt_tggd_by_country_qtr",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "country": {
          "expression": "r.country",
          "type": "string"
        },
        "quarter": {
          "expression": "p.quarter_tag",
          "type": "string"
        },
        "country_budget_json": {
          "expression": "r.country_budget_json",
          "type": "string"
        }
      }
    }
  }
}

Script node 4_tag_countries_quarter produces Cassandra table quarterly_project_bdgt_tggd_by_country_qtr:

rowidbatch_idxcountrycountry_budget_jsonproject_idquarter
64929538024242979295R-ASIA{"R-AFRICA": {"2023-Q1": 11405.109489051094, "2023-Q2": 518932.4817518248, "2023-Q3": 524635.0364963 ... total length 11700p0105320012039-Q2
83692229968450372657R-AFRICA{"R-SOUTHSAHARA": {"2019-Q3": 427830.59636992216, "2019-Q4": 1192739.8444252377, "2020-Q1": 1179775. ... total length 7948p0081570012030-Q2
39914281772738715271R-AFRICA{"R-AFRICA": {"2011-Q4": 4624664.879356569, "2012-Q1": 18297587.131367292, "2012-Q2": 18297587.13136 ... total length 1626m0133540012013-Q4
73425050523669613520R-EUROPE{"R-AFRICA": {"2023-Q1": 11405.109489051094, "2023-Q2": 518932.4817518248, "2023-Q3": 524635.0364963 ... total length 11700p0105320012031-Q2
3294139845846478433R-SOUTHAMERICA{"R-SOUTHSAHARA": {"2023-Q4": 20348411.21495327, "2024-Q1": 102872523.36448598, "2024-Q2": 102872523 ... total length 1158p0122770012024-Q4
4448962473917673000R-AFRICA{"R-AFRICA": {"2020-Q1": 19661.582459485224, "2020-Q2": 596401.3346043852, "2020-Q3": 602955.1954242 ... total length 8472p0061670012032-Q4
22663303962452696721R-AFRICA{"R-AFRICA": {"2011-Q4": 4624664.879356569, "2012-Q1": 18297587.131367292, "2012-Q2": 18297587.13136 ... total length 1626m0133540012012-Q1
20011173376291653557R-AFRICA{"R-SOUTHSAHARA": {"2019-Q3": 427830.59636992216, "2019-Q4": 1192739.8444252377, "2020-Q1": 1179775. ... total length 7948p0081570012034-Q1
4246623187044522813R-ASIA{"R-ASIA": {"2024-Q1": 917164.0704614937, "2024-Q2": 4173096.5205997964, "2024-Q3": 4218954.72412287 ... total length 4700p0128010012036-Q2
56925775050116491793R-ASIA{"R-AFRICA": {"2015-Q1": 1925951.0870244564, "2015-Q2": 35052309.783845104, "2015-Q3": 35437500.0012 ... total length 1040d0005140012015-Q1

Total 2815 rows

4_tag_sectors_quarter

Script node 4_tag_sectors_quarter:

{
  "4_tag_sectors_quarter": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "tag_and_denormalize",
    "desc": "Tag by sector and quarter",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "quarterly_project_bdgt_tggd_by_sector",
      "expected_batches_total": 10
    },
    "p": {
      "tag_field_name": "quarter_tag",
      "tag_criteria_url": "{dir_cfg}/tag_criteria_sector_quarter.json"
    },
    "w": {
      "name": "quarterly_project_bdgt_tggd_by_sector_qtr",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "sector": {
          "expression": "r.sector",
          "type": "int"
        },
        "quarter": {
          "expression": "p.quarter_tag",
          "type": "string"
        },
        "sector_budget_json": {
          "expression": "r.sector_budget_json",
          "type": "string"
        }
      }
    }
  }
}

Script node 4_tag_sectors_quarter produces Cassandra table quarterly_project_bdgt_tggd_by_sector_qtr:

rowidbatch_idxproject_idquartersectorsector_budget_json
34171143613441864781p0108070012023-Q323210{"23110": {"2022-Q1": 10950.503723171267, "2022-Q2": 996495.8388085853, "2022-Q3": 1007446.342531756 ... total length 6063
2782147160769554333m0135120012012-Q122020{"14021": {"2011-Q1": 749203.7684955753, "2011-Q2": 1311106.5948672567, "2011-Q3": 1325514.359646017 ... total length 6564
62170066965539292903p0121830012025-Q423230{"21012": {"2024-Q1": 71225.07122507122, "2024-Q2": 270061.7283950617, "2024-Q3": 273029.43969610636 ... total length 31222
63940772117081042928p0128010012028-Q431320{"14021": {"2024-Q1": 81525.69515213277, "2024-Q2": 370941.9129422041, "2024-Q3": 375018.1976998107, ... total length 18663
62950860447006248491p0108070012036-Q123210{"23110": {"2022-Q1": 10950.503723171267, "2022-Q2": 996495.8388085853, "2022-Q3": 1007446.342531756 ... total length 6063
28270341389827702717m0137050012017-Q441020{"14015": {"2012-Q1": 5474.652359575167, "2012-Q2": 249096.6823606701, "2012-Q3": 251834.00854045767 ... total length 27991
39248125682940159952p0061670012030-Q131220{"23210": {"2020-Q1": 80612.48808388942, "2020-Q2": 2445245.471877979, "2020-Q3": 2472116.3012392754 ... total length 5649
79844738615372714689p0105320012023-Q224010{"14021": {"2023-Q1": 5702.554744525547, "2023-Q2": 259466.2408759124, "2023-Q3": 262317.51824817515 ... total length 11924
22785296920647380608p0128010012031-Q414021{"14021": {"2024-Q1": 81525.69515213277, "2024-Q2": 370941.9129422041, "2024-Q3": 375018.1976998107, ... total length 18663
31127523769456730751p0105320012046-Q123210{"14021": {"2023-Q1": 5702.554744525547, "2023-Q2": 259466.2408759124, "2023-Q3": 262317.51824817515 ... total length 11924

Total 4100 rows

4_tag_partners_quarter

Script node 4_tag_partners_quarter:

{
  "4_tag_partners_quarter": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "tag_and_denormalize",
    "desc": "Tag by partner and quarter",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "quarterly_project_bdgt",
      "expected_batches_total": 10
    },
    "p": {
      "tag_field_name": "quarter_tag",
      "tag_criteria_url": "{dir_cfg}/tag_criteria_partner_quarter.json"
    },
    "w": {
      "name": "quarterly_project_bdgt_tggd_by_partner_qtr",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "partner_id": {
          "expression": "r.partner_id",
          "type": "int"
        },
        "quarter": {
          "expression": "p.quarter_tag",
          "type": "string"
        },
        "partner_budget_json": {
          "expression": "r.partner_budget_json",
          "type": "string"
        }
      }
    }
  }
}

Script node 4_tag_partners_quarter produces Cassandra table quarterly_project_bdgt_tggd_by_partner_qtr:

rowidbatch_idxpartner_budget_jsonpartner_idproject_idquarter
73429407429547493085{"2008-Q1": 5279783.393501805, "2008-Q2": 36958483.75451264, "2008-Q3": 37364620.93862816, "2008-Q4" ... total length 39029979m0127610012008-Q1
33942360846491778993{"2024-Q1": 1019071.1894016596, "2024-Q2": 4636773.911777551, "2024-Q3": 4687727.471247634, "2024-Q4 ... total length 228129588p0128010012031-Q2
50717886417547497705{"2012-Q1": 54746.52359794153, "2012-Q2": 2490966.8237063396, "2012-Q3": 2518340.0855053104, "2012-Q ... total length 313029636m0137050012024-Q2
66261611884844449565{"2020-Q1": 98307.91229742613, "2020-Q2": 2982006.6730219256, "2020-Q3": 3014775.9771210677, "2020-Q ... total length 286529578p0061670012040-Q1
21341304784028442797{"2023-Q1": 57025.54744525548, "2023-Q2": 2594662.408759124, "2023-Q3": 2623175.182481752, "2023-Q4" ... total length 292833915p0105320012031-Q1
16940807184457606475{"2012-Q1": 54746.52359794153, "2012-Q2": 2490966.8237063396, "2012-Q3": 2518340.0855053104, "2012-Q ... total length 313029636m0137050012019-Q2
84010889627497154987{"2023-Q1": 57025.54744525548, "2023-Q2": 2594662.408759124, "2023-Q3": 2623175.182481752, "2023-Q4" ... total length 292833915p0105320012041-Q4
11413181204581572595{"2012-Q1": 54746.52359794153, "2012-Q2": 2490966.8237063396, "2012-Q3": 2518340.0855053104, "2012-Q ... total length 313029636m0137050012034-Q3
21947637286487546501{"2015-Q3": 3046127.0670147957, "2015-Q4": 20017406.440382943, "2016-Q1": 19799825.93559617, "2016-Q ... total length 79029981d0022430012021-Q1
85303536200236208035{"2020-Q1": 98307.91229742613, "2020-Q2": 2982006.6730219256, "2020-Q3": 3014775.9771210677, "2020-Q ... total length 286529578p0061670012022-Q2

Total 807 rows

5_project_country_quarter_amt

Script node 5_project_country_quarter_amt:

{
  "5_project_country_quarter_amt": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "py_calc",
    "desc": "Get country quarter amount",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "quarterly_project_bdgt_tggd_by_country_qtr",
      "expected_batches_total": "{get_amt_from_json_batches|number}"
    },
    "p": {
      "python_code_urls": [
        "{dir_py}/get_amt_from_json.py"
      ],
      "calculated_fields": {
        "amt": {
          "expression": "get_amt_by_key_and_quarter(r.country, r.quarter, r.country_budget_json)",
          "type": "float"
        }
      }
    },
    "w": {
      "name": "project_country_quarter_amt",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "country": {
          "expression": "r.country",
          "type": "string"
        },
        "quarter": {
          "expression": "r.quarter",
          "type": "string"
        },
        "amt": {
          "expression": "p.amt",
          "type": "float"
        }
      }
    }
  }
}

Script node 5_project_country_quarter_amt produces Cassandra table project_country_quarter_amt:

rowidamtbatch_idxcountryproject_idquarter
83524373649853778928979731.9034852553165R-MIDDLEEASTp0052470012022-Q1
14674718739477416811208803.2409941968516R-SOUTHAMERICAm0137050012019-Q3
11464706140117408011179775.2808988762557R-AFRICAp0081570012032-Q2
59474810054559738101192739.8444252377389R-AFRICAp0081570012034-Q3
74024029447269612281096605.7441253263979R-AMERICAd0022430012019-Q1
3123951200987607634518932.4817518247871R-EUROPEp0105320012035-Q2
6067614188642658611797109.3835541442978R-NORTHCENTRALAMERICAm0137050012026-Q2
35867066612763379288500451.263537906115R-ASIAm0127610012010-Q2
3316991650243725571805868.8273294646066R-NORTHCENTRALAMERICAm0137050012032-Q3
3076965167097940652092641.2614980288783PHp0108070012029-Q2

Total 2815 rows

5_project_sector_quarter_amt

Script node 5_project_sector_quarter_amt:

{
  "5_project_sector_quarter_amt": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "py_calc",
    "desc": "Get sector quarter amount",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "quarterly_project_bdgt_tggd_by_sector_qtr",
      "expected_batches_total": "{get_amt_from_json_batches|number}"
    },
    "p": {
      "python_code_urls": [
        "{dir_py}/get_amt_from_json.py"
      ],
      "calculated_fields": {
        "amt": {
          "expression": "get_amt_by_key_and_quarter(r.sector, r.quarter, r.sector_budget_json)",
          "type": "float"
        }
      }
    },
    "w": {
      "name": "project_sector_quarter_amt",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "sector": {
          "expression": "r.sector",
          "type": "int"
        },
        "quarter": {
          "expression": "r.quarter",
          "type": "string"
        },
        "amt": {
          "expression": "p.amt",
          "type": "float"
        }
      }
    }
  }
}

Script node 5_project_sector_quarter_amt produces Cassandra table project_sector_quarter_amt:

rowidamtbatch_idxproject_idquartersector
177932378808384070498219201d0038270012019-Q412263
324294066856394489254605.8879392212770p0121830012040-Q323630
3196811808504227128249096.6823606700937m0137050012025-Q223240
4134373396957659457249096.6823606700936m0137050012030-Q223270
4898646005207659992562527.2965497161492p0128010012033-Q431150
4655671061090342577262317.5182481751545p0105320012028-Q441010
7308084502650371569251834.0085404576685m0137050012036-Q341030
88634931225666268482219286.6578599736092p0052530012025-Q113020
205140700124374795054605.8879392212776p0121830012026-Q423270
4457792887679449827251834.0085404576688m0137050012034-Q414015

Total 4100 rows

5_project_partner_quarter_amt

Script node 5_project_partner_quarter_amt:

{
  "5_project_partner_quarter_amt": {
    "type": "table_custom_tfm_table",
    "custom_proc_type": "py_calc",
    "desc": "Get partner quarter amount",
    "start_policy": "{manual_if_multi}",
    "r": {
      "table": "quarterly_project_bdgt_tggd_by_partner_qtr",
      "expected_batches_total": "{get_amt_from_json_batches|number}"
    },
    "p": {
      "python_code_urls": [
        "{dir_py}/get_amt_from_json.py"
      ],
      "calculated_fields": {
        "amt": {
          "expression": "get_amt_by_quarter(r.quarter, r.partner_budget_json)",
          "type": "float"
        }
      }
    },
    "w": {
      "name": "project_partner_quarter_amt",
      "fields": {
        "project_id": {
          "expression": "r.project_id",
          "type": "string"
        },
        "partner_id": {
          "expression": "r.partner_id",
          "type": "int"
        },
        "quarter": {
          "expression": "r.quarter",
          "type": "string"
        },
        "amt": {
          "expression": "p.amt",
          "type": "float"
        }
      }
    }
  }
}

Script node 5_project_partner_quarter_amt produces Cassandra table project_country_quarter_amt:

rowidamtbatch_idxcountryproject_idquarter
5728672404326026007788436.2680683311770MKp0108070012045-Q1
83836363302852098874218954.7241228707131R-ASIAp0128010012041-Q4
45547464740913865222069645.2036793692973INp0108070012027-Q1
3593219858001012360524635.0364963503098R-EUROPEp0105320012041-Q4
29275632766262454852092641.2614980288786ZAp0108070012023-Q2
85156756987069821322092641.2614980288784INp0108070012045-Q2
68539734198237181002115637.3193166884595PHp0108070012033-Q3
6898433930390248265498193.3647213401852R-WESTINDIESm0137050012030-Q2
6898697046540229926797196.6710468681993MKp0108070012036-Q1
1721444317230417779894602.0019065777313R-AMERICAp0061670012031-Q2

Total 2815 rows

6_file_project_country_quarter_amt

Script node 6_file_project_country_quarter_amt:

{
  "6_file_project_country_quarter_amt": {
    "type": "table_file",
    "desc": "Write project_country_quarter_amt.csv",
    "r": {
      "table": "project_country_quarter_amt"
    },
    "w": {
      "top": {
        "order": "quarter,project_id,country"
      },
      "url_template": "{dir_out}/project_country_quarter_amt.csv",
      "columns": [
        {
          "csv": {
            "header": "project_id",
            "format": "%s"
          },
          "name": "project_id",
          "expression": "r.project_id",
          "type": "string"
        },
        {
          "csv": {
            "header": "country_code",
            "format": "%s"
          },
          "name": "country",
          "expression": "r.country",
          "type": "string"
        },
        {
          "csv": {
            "header": "quarter",
            "format": "%s"
          },
          "name": "quarter",
          "expression": "r.quarter",
          "type": "string"
        },
        {
          "csv": {
            "header": "amt",
            "format": "%.2f"
          },
          "name": "amt",
          "expression": "r.amt",
          "type": "float"
        }
      ]
    }
  }
}

Script node 6_file_project_country_quarter_amt produces data file:

project_idcountry_codequarteramt
m012672001R-AFRICA2007-Q419856189.45
m012672001R-AFRICA2008-Q129621528.53
m012761001R-AFRICA2008-Q13326263.54
m012761001R-AMERICA2008-Q1369584.84
m012761001R-ASIA2008-Q11214350.18
m012761001R-EUROPE2008-Q1369584.84
m012672001R-AFRICA2008-Q229621528.53
m012761001R-AFRICA2008-Q223283844.77
m012761001R-AMERICA2008-Q22587093.86
m012761001R-ASIA2008-Q28500451.26

Total 2815 rows

6_file_project_sector_quarter_amt

Script node 6_file_project_sector_quarter_amt:

{
  "6_file_project_sector_quarter_amt": {
    "type": "table_file",
    "desc": "Write project_sector_quarter_amt.csv",
    "r": {
      "table": "project_sector_quarter_amt"
    },
    "w": {
      "top": {
        "order": "quarter,project_id,sector"
      },
      "url_template": "{dir_out}/project_sector_quarter_amt.csv",
      "columns": [
        {
          "csv": {
            "header": "project_id",
            "format": "%s"
          },
          "name": "project_id",
          "expression": "r.project_id",
          "type": "string"
        },
        {
          "csv": {
            "header": "sector_id",
            "format": "%d"
          },
          "name": "sector",
          "expression": "r.sector",
          "type": "int"
        },
        {
          "csv": {
            "header": "quarter",
            "format": "%s"
          },
          "name": "quarter",
          "expression": "r.quarter",
          "type": "string"
        },
        {
          "csv": {
            "header": "amt",
            "format": "%.2f"
          },
          "name": "amt",
          "expression": "r.amt",
          "type": "float"
        }
      ]
    }
  }
}

Script node 6_file_project_sector_quarter_amt produces data file:

project_idsector_idquarteramt
m012672001114202007-Q41389933.26
m012672001140212007-Q41985618.95
m012672001140222007-Q41985618.95
m012672001151102007-Q43971237.89
m012672001210202007-Q43971237.89
m012672001236302007-Q43971237.89
m012672001311202007-Q42581304.63
m012672001114202008-Q12073507.00
m012672001140212008-Q12962152.85
m012672001140222008-Q12962152.85

Total 4100 rows

6_file_project_partner_quarter_amt

Script node 6_file_project_partner_quarter_amt:

{
  "6_file_project_partner_quarter_amt": {
    "type": "table_file",
    "desc": "Write project_partner_quarter_amt.csv",
    "r": {
      "table": "project_partner_quarter_amt"
    },
    "w": {
      "top": {
        "order": "quarter,project_id,partner_id"
      },
      "url_template": "{dir_out}/project_partner_quarter_amt.csv",
      "columns": [
        {
          "csv": {
            "header": "project_id",
            "format": "%s"
          },
          "name": "project_id",
          "expression": "r.project_id",
          "type": "string"
        },
        {
          "csv": {
            "header": "partner_id",
            "format": "%d"
          },
          "name": "partner_id",
          "expression": "r.partner_id",
          "type": "int"
        },
        {
          "csv": {
            "header": "quarter",
            "format": "%s"
          },
          "name": "quarter",
          "expression": "r.quarter",
          "type": "string"
        },
        {
          "csv": {
            "header": "amt",
            "format": "%.2f"
          },
          "name": "amt",
          "expression": "r.amt",
          "type": "float"
        }
      ]
    }
  }
}

Script node 6_file_project_partner_quarter_amt produces data file:

project_idpartner_idquarteramt
m012672001296632007-Q419856189.45
m012672001296632008-Q129621528.53
m012761001299792008-Q15279783.39
m012672001296632008-Q229621528.53
m012761001299792008-Q236958483.75
m012672001296632008-Q329947039.83
m012761001299792008-Q337364620.94
m012672001296632008-Q429947039.83
m012761001299792008-Q437364620.94
m012672001296632009-Q129296017.22

Total 807 rows