global_affairs_quicktest 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 project_budget.csv to table",
    "start_policy": "manual",
    "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
5192768312199147756325615484.0699999928470{"R-AFRICA": 325615484.07}2014031329663m013512001{"14021": 16280774.2, "14022": 16280774.2, "14031": 16280774.2, "14032": 16280774.2, "14081": 651230 ... total length 34220110208
57031256699310008842800000000{"R-AFRICA": 140000000.0, "R-ASIA": 140000000.0}2025073129820p005253001{"12240": 224000000.0, "13020": 56000000.0}20190514
286578574445610227250000000.0099999904630{"R-WESTINDIES": 50000000.0, "R-NORTHCENTRALAMERICA": 80000000.0, "R-SOUTHAMERICA": 120000000.0}2037033129636m013705001{"14015": 25000000.0, "23210": 25000000.0, "23230": 25000000.0, "23240": 25000000.0, "23260": 250000 ... total length 18920120330
543670670682573116410000000000{"IN": 210000000.0, "PH": 210000000.0, "ID": 210000000.0, "ZA": 210000000.0, "DO": 80000000.0, "MK": ... total length 1122047033129578p010807001{"23110": 100000000.0, "23210": 900000000.0}20220331
27553329221339756013000000000{"R-SOUTHSAHARA": 75000000.0, "R-AFRICA": 75000000.0, "R-AMERICA": 75000000.0, "R-ASIA": 75000000.0}2035063030488p008157001{"15170": 300000000.0}20190829
10802745672398160605000000000{"R-AFRICA": 359000000.0, "R-AMERICA": 28000000.0, "R-ASIA": 113000000.0}2021123129981d002243001{"12250": 500000000.0}20150917
34089549255772748712096000000{"R-SOUTHSAHARA": 846720000.0, "R-SOUTHAMERICA": 36288000.0, "R-MIDDLEEAST": 84672000.0, "R-ASIA": 2 ... total length 1112025123129979p012277001{"12262": 362880000.0, "12263": 217728000.0, "13040": 628992000.0}20231214
41721323690129846124000000000{"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
13663558828951411707850000000{"R-AFRICA": 266900000.0, "R-AMERICA": 259050000.0, "R-ASIA": 259050000.0}2020123129979d003827001{"12262": 235500000.0, "12263": 133450000.0, "13040": 416050000.0}20170731
87508985015556833743024000000{"R-AFRICA": 302400000.0}2010051729663m012672001{"11420": 21168000.0, "14021": 30240000.0, "14022": 30240000.0, "15110": 60480000.0, "21020": 604800 ... total length 14720071101

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",
    "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_budget",
      "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_budget:

rowidbatch_idxcountry_budget_jsonpartner_budget_jsonpartner_idproject_idsector_budget_json
47783557263511015723{"R-AFRICA": {"2019-Q2": 2959048.8771466315, "2019-Q3": 5671510.347864377, "2019-Q4": 5671510.347864 ... total length 1590{"2019-Q2": 5918097.754293263, "2019-Q3": 11343020.695728755, "2019-Q4": 11343020.695728755, "2020-Q ... total length 80529820p005253001{"12240": {"2019-Q2": 4734478.203434611, "2019-Q3": 9074416.556583002, "2019-Q4": 9074416.556583002, ... total length 1599
14262697020838670214{"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
72471318083316707220{"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
44230086821446232275{"R-AFRICA": {"2015-Q3": 2187119.234116623, "2015-Q4": 14372497.824194953, "2016-Q1": 14216275.02175 ... total length 2428{"2015-Q3": 3046127.0670147957, "2015-Q4": 20017406.440382943, "2016-Q1": 19799825.93559617, "2016-Q ... total length 79029981d002243001{"12250": {"2015-Q3": 3046127.0670147957, "2015-Q4": 20017406.440382943, "2016-Q1": 19799825.9355961 ... total length 801
21105916589641511410{"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
10814941160570484961{"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
75434667077443399098{"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
51732829605312368191{"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
26624179249810448488{"R-AFRICA": {"2017-Q3": 13238240.0, "2017-Q4": 19643840.0, "2018-Q1": 19216800.0, "2018-Q2": 194303 ... total length 1007{"2017-Q3": 38936000.0, "2017-Q4": 57776000.0, "2018-Q1": 56520000.0, "2018-Q2": 57148000.0, "2018-Q ... total length 32229979d003827001{"12262": {"2017-Q3": 11680800.0, "2017-Q4": 17332800.0, "2018-Q1": 16956000.0, "2018-Q2": 17144400. ... total length 985
78291505510728159517{"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

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",
    "r": {
      "table": "quarterly_project_budget",
      "expected_batches_total": 10
    },
    "p": {
      "tag_field_name": "country_tag",
      "tag_criteria_url": "{dir_cfg}/tag_criteria_country.json"
    },
    "w": {
      "name": "quarterly_project_budget_tagged_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_budget_tagged_by_country:

rowidbatch_idxcountrycountry_budget_jsonproject_id
33157430915436359794R-AMERICA{"R-AFRICA": {"2011-Q4": 4624664.879356569, "2012-Q1": 18297587.131367292, "2012-Q2": 18297587.13136 ... total length 1626m013354001
2326571945117319824R-ASIA{"R-SOUTHSAHARA": {"2023-Q4": 20348411.21495327, "2024-Q1": 102872523.36448598, "2024-Q2": 102872523 ... total length 1158p012277001
73058463140032063949R-AMERICA{"R-AFRICA": {"2021-Q1": 301969.36542669585, "2021-Q2": 13739606.126914661, "2021-Q3": 13890590.8096 ... total length 1961p006992001
70348830041749723557DO{"IN": {"2022-Q1": 22996.057818659658, "2022-Q2": 2092641.2614980289, "2022-Q3": 2115637.3193166885, ... total length 18632p010807001
76117194871530268973R-SOUTHSAHARA{"R-SOUTHSAHARA": {"2020-Q4": 12970723.860589812, "2021-Q1": 72960321.71581769, "2021-Q2": 73770991. ... total length 1446p005247001
10363307991435060772R-AFRICA{"R-AFRICA": {"2023-Q1": 11405.109489051094, "2023-Q2": 518932.4817518248, "2023-Q3": 524635.0364963 ... total length 11700p010532001
16524645335118676722R-AMERICA{"R-AFRICA": {"2017-Q3": 13238240.0, "2017-Q4": 19643840.0, "2018-Q1": 19216800.0, "2018-Q2": 194303 ... total length 1007d003827001
24759103248471531889R-AFRICA{"R-AFRICA": {"2021-Q1": 301969.36542669585, "2021-Q2": 13739606.126914661, "2021-Q3": 13890590.8096 ... total length 1961p006992001
90763194798526623077IN{"IN": {"2022-Q1": 22996.057818659658, "2022-Q2": 2092641.2614980289, "2022-Q3": 2115637.3193166885, ... total length 18632p010807001
71506711198621209511R-AMERICA{"R-AFRICA": {"2022-Q1": 102382.48551191243, "2022-Q2": 9316806.18158403, "2022-Q3": 9419188.6670959 ... total length 2226p006692001

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",
    "r": {
      "table": "quarterly_project_budget",
      "expected_batches_total": 10
    },
    "p": {
      "tag_field_name": "sector_tag",
      "tag_criteria_url": "{dir_cfg}/tag_criteria_sector.json"
    },
    "w": {
      "name": "quarterly_project_budget_tagged_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_budget_tagged_by_sector:

rowidbatch_idxproject_idsectorsector_budget_json
4295586930578162501p00669200111110{"11110": {"2022-Q1": 17063.74758531874, "2022-Q2": 1552801.0302640053, "2022-Q3": 1569864.777849324 ... total length 2225
57719217969308993381p01280100131150{"14021": {"2024-Q1": 81525.69515213277, "2024-Q2": 370941.9129422041, "2024-Q3": 375018.1976998107, ... total length 18663
31237908055824861731p01280100114021{"14021": {"2024-Q1": 81525.69515213277, "2024-Q2": 370941.9129422041, "2024-Q3": 375018.1976998107, ... total length 18663
23756532622752976097m01267200111420{"11420": {"2007-Q4": 1389933.2615715822, "2008-Q1": 2073506.9967707212, "2008-Q2": 2073506.99677072 ... total length 2429
8521505636971812337p01080700123110{"23110": {"2022-Q1": 10950.503723171267, "2022-Q2": 996495.8388085853, "2022-Q3": 1007446.342531756 ... total length 6063
29626642797944132949m01351200114031{"14021": {"2011-Q1": 749203.7684955753, "2011-Q2": 1311106.5948672567, "2011-Q3": 1325514.359646017 ... total length 6564
47793720727656309267p01218300123630{"21012": {"2024-Q1": 71225.07122507122, "2024-Q2": 270061.7283950617, "2024-Q3": 273029.43969610636 ... total length 31222
25324283086155231992p01053200114021{"14021": {"2023-Q1": 5702.554744525547, "2023-Q2": 259466.2408759124, "2023-Q3": 262317.51824817515 ... total length 11924
68701966701684365924m01370500123270{"14015": {"2012-Q1": 5474.652359575167, "2012-Q2": 249096.6823606701, "2012-Q3": 251834.00854045767 ... total length 27991
52051164716353377934p01227700112263{"12262": {"2023-Q4": 8720747.663551401, "2024-Q1": 44088224.29906542, "2024-Q2": 44088224.29906542, ... total length 855

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",
    "r": {
      "table": "quarterly_project_budget_tagged_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_budget_tagged_by_country_quarter",
      "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_budget_tagged_by_country_quarter:

rowidbatch_idxcountrycountry_budget_jsonproject_idquarter
32289553131711499176R-AFRICA{"R-AFRICA": {"2020-Q1": 19661.582459485224, "2020-Q2": 596401.3346043852, "2020-Q3": 602955.1954242 ... total length 8472p0061670012028-Q1
65078267183847161188R-OCEANIA{"R-ASIA": {"2024-Q1": 917164.0704614937, "2024-Q2": 4173096.5205997964, "2024-Q3": 4218954.72412287 ... total length 4700p0128010012033-Q4
74070864381995403647DO{"IN": {"2022-Q1": 22996.057818659658, "2022-Q2": 2092641.2614980289, "2022-Q3": 2115637.3193166885, ... total length 18632p0108070012040-Q1
33172616378013387764R-CENTRALASIA{"R-SOUTHSAHARA": {"2020-Q4": 12970723.860589812, "2021-Q1": 72960321.71581769, "2021-Q2": 73770991. ... total length 1446p0052470012022-Q3
82367480267694283916R-AMERICA{"R-AFRICA": {"2015-Q3": 2187119.234116623, "2015-Q4": 14372497.824194953, "2016-Q1": 14216275.02175 ... total length 2428d0022430012017-Q4
27784443210733733713R-WESTINDIES{"R-WESTINDIES": {"2024-Q1": 142450.14245014245, "2024-Q2": 540123.4567901234, "2024-Q3": 546058.879 ... total length 8602p0121830012031-Q2
27560320427826799036R-SOUTHAMERICA{"R-WESTINDIES": {"2012-Q1": 10949.304719150334, "2012-Q2": 498193.3647213402, "2012-Q3": 503668.017 ... total length 9306m0137050012027-Q1
32504843890198582063R-AFRICA{"R-SOUTHSAHARA": {"2019-Q3": 427830.59636992216, "2019-Q4": 1192739.8444252377, "2020-Q1": 1179775. ... total length 7948p0081570012032-Q3
4192806501819617091IN{"IN": {"2022-Q1": 22996.057818659658, "2022-Q2": 2092641.2614980289, "2022-Q3": 2115637.3193166885, ... total length 18632p0108070012044-Q4
67963912364362646138R-OCEANIA{"R-ASIA": {"2024-Q1": 917164.0704614937, "2024-Q2": 4173096.5205997964, "2024-Q3": 4218954.72412287 ... total length 4700p0128010012028-Q4

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",
    "r": {
      "table": "quarterly_project_budget_tagged_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_budget_tagged_by_sector_quarter",
      "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_budget_tagged_by_sector_quarter:

rowidbatch_idxproject_idquartersectorsector_budget_json
68716103422436565490p0128010012036-Q423630{"14021": {"2024-Q1": 81525.69515213277, "2024-Q2": 370941.9129422041, "2024-Q3": 375018.1976998107, ... total length 18663
38493985867191961293m0135120012013-Q122020{"14021": {"2011-Q1": 749203.7684955753, "2011-Q2": 1311106.5948672567, "2011-Q3": 1325514.359646017 ... total length 6564
39417382233544995416p0108070012039-Q423110{"23110": {"2022-Q1": 10950.503723171267, "2022-Q2": 996495.8388085853, "2022-Q3": 1007446.342531756 ... total length 6063
49019881335045736430p0105320012044-Q224010{"14021": {"2023-Q1": 5702.554744525547, "2023-Q2": 259466.2408759124, "2023-Q3": 262317.51824817515 ... total length 11924
15676712696850327129m0137050012015-Q323210{"14015": {"2012-Q1": 5474.652359575167, "2012-Q2": 249096.6823606701, "2012-Q3": 251834.00854045767 ... total length 27991
26528795786603030141m0137050012013-Q314015{"14015": {"2012-Q1": 5474.652359575167, "2012-Q2": 249096.6823606701, "2012-Q3": 251834.00854045767 ... total length 27991
89503538749739310188p0108070012039-Q423210{"23110": {"2022-Q1": 10950.503723171267, "2022-Q2": 996495.8388085853, "2022-Q3": 1007446.342531756 ... total length 6063
49862031161011213567p0128010012027-Q324030{"14021": {"2024-Q1": 81525.69515213277, "2024-Q2": 370941.9129422041, "2024-Q3": 375018.1976998107, ... total length 18663
41526625100345107226p0121830012038-Q123630{"21012": {"2024-Q1": 71225.07122507122, "2024-Q2": 270061.7283950617, "2024-Q3": 273029.43969610636 ... total length 31222
4933798953195122793p0121830012041-Q423270{"21012": {"2024-Q1": 71225.07122507122, "2024-Q2": 270061.7283950617, "2024-Q3": 273029.43969610636 ... total length 31222

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",
    "r": {
      "table": "quarterly_project_budget",
      "expected_batches_total": 10
    },
    "p": {
      "tag_field_name": "quarter_tag",
      "tag_criteria_url": "{dir_cfg}/tag_criteria_partner_quarter.json"
    },
    "w": {
      "name": "quarterly_project_budget_tagged_by_partner_quarter",
      "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_budget_tagged_by_partner_quarter:

rowidbatch_idxpartner_budget_jsonpartner_idproject_idquarter
38309013478322612104{"2011-Q4": 9249329.758713137, "2012-Q1": 36595174.262734585, "2012-Q2": 36595174.262734585, "2012-Q ... total length 39429979m0133540012012-Q4
75925015857178446244{"2012-Q1": 54746.52359794153, "2012-Q2": 2490966.8237063396, "2012-Q3": 2518340.0855053104, "2012-Q ... total length 313029636m0137050012032-Q4
18346135743058499324{"2019-Q2": 5918097.754293263, "2019-Q3": 11343020.695728755, "2019-Q4": 11343020.695728755, "2020-Q ... total length 80529820p0052530012025-Q1
11755178064818050227{"2007-Q4": 19856189.451022606, "2008-Q1": 29621528.525296018, "2008-Q2": 29621528.525296018, "2008- ... total length 34029663m0126720012009-Q3
59273194428207578775{"2020-Q1": 98307.91229742613, "2020-Q2": 2982006.6730219256, "2020-Q3": 3014775.9771210677, "2020-Q ... total length 286529578p0061670012040-Q1
56959358629261827124{"2012-Q1": 54746.52359794153, "2012-Q2": 2490966.8237063396, "2012-Q3": 2518340.0855053104, "2012-Q ... total length 313029636m0137050012012-Q2
70649877204182452642{"2008-Q1": 5279783.393501805, "2008-Q2": 36958483.75451264, "2008-Q3": 37364620.93862816, "2008-Q4" ... total length 39029979m0127610012010-Q4
59655967520464214827{"2024-Q1": 1424501.4245014247, "2024-Q2": 5401234.567901235, "2024-Q3": 5460588.793922127, "2024-Q4 ... total length 279129636p0121830012046-Q1
46131922720312812643{"2015-Q3": 3046127.0670147957, "2015-Q4": 20017406.440382943, "2016-Q1": 19799825.93559617, "2016-Q ... total length 79029981d0022430012016-Q3
10364623102389501361{"2024-Q1": 1019071.1894016596, "2024-Q2": 4636773.911777551, "2024-Q3": 4687727.471247634, "2024-Q4 ... total length 228129588p0128010012037-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",
    "r": {
      "table": "quarterly_project_budget_tagged_by_country_quarter",
      "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
8751222469725115782786952.5547445254630R-ASIAp0105320012026-Q3
8063360272440380976805957.0740254052229DOp0108070012033-Q4
51953728773388377101491003.3365109628076R-ASIAp0061670012036-Q1
3732709378391311811179775.2808988762558R-ASIAp0081570012024-Q2
1829940362690899251540123.4567901233681R-WESTINDIESp0121830012034-Q2
7432803228807627116805957.0740254052224DOp0108070012045-Q3
152607732623798964904432.793136320314R-AMERICAp0061670012041-Q3
8126167813690408930524635.0364963503097R-AFRICAp0105320012044-Q4
4769882013062139717805957.0740254052228DOp0108070012046-Q4
41801655311383426761192739.8444252377384R-ASIAp0081570012024-Q4

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",
    "r": {
      "table": "quarterly_project_budget_tagged_by_sector_quarter",
      "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
24217546432495242141183619.5508586526850p0052530012019-Q213020
1542337383023709030246359.3561808825188m0137050012017-Q123240
2979239234287857034262317.5182481751549p0105320012046-Q441010
1502322140030697280101907.1189401659656p0128010012024-Q123642
83974045890495215328968462.5492772664874p0108070012040-Q123210
30461461716099807699067017.0827858094136p0108070012036-Q423210
75571118579225582571816263.6861313867858p0105320012040-Q123210
44707782284901510841365147.1984805318067p0121830012025-Q323230
14266869712392248532622213.1905398229144m0135120012011-Q223310
1267431488412970970819088.3190883190842p0121830012032-Q341030

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",
    "r": {
      "table": "quarterly_project_budget_tagged_by_partner_quarter",
      "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
11787488116757452554589640.7506702411926R-SOUTHAMERICAp0052470012022-Q4
36015580979133650592115637.3193166884594ZAp0108070012027-Q3
1807772087307360778398.722627737210R-ASIAp0105320012042-Q2
65106571188827025014218954.7241228707134R-ASIAp0128010012029-Q3
39152510339274407681638176.6381766381676R-NORTHCENTRALAMERICAp0121830012030-Q3
6404756265710794645463677.391177755145R-OCEANIAp0128010012036-Q1
5834564234551792766786952.5547445254632R-ASIAp0105320012025-Q3
6669450010550111776797196.6710468681993DOp0108070012030-Q2
27024325186051905034425587.4673629235486R-ASIAd0022430012019-Q1
55168195349433236413105602.0605280105963R-ASIAp0066920012024-Q1

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