---
# System prepended metadata

title: SPC Scotland failing

---

# SPC Scotland failing 

Currently, Wales and England LADs run and produce data. Scotland LADs produce errors and incomplete data 

- https://github.com/alan-turing-institute/spc-hpc-pipeline
- https://github.com/alan-turing-institute/spc-hpc-pipeline/blob/main/scripts/scp/SPENSER_HPC_setup.sh



## Problem 

On step 1 of the simulations: 

```bash
    echo 'Step 1'
    cd microsimulation
    python -W ignore scripts/run_ssm.py -c config/ssm_current.json $var
```

we get an error of 

```python
ValueError                                Traceback (most recent call last)
File ~/microsimulation/scripts/run_ssm.py:48
     45 if __name__ == "__main__":
     47     PARAMS = utils.get_config()
---> 48     main(PARAMS)

File ~/microsimulation/scripts/run_ssm.py:36, in main(params)
     33 print("Static P Microsimulation: ", region, "@", resolution)
     35 # init microsynthesis
---> 36 ssm = Static.SequentialMicrosynthesis(region, resolution, variant, is_custom, cache_dir, output_dir, use_fast_mode)
     37 ssm.run(ref_year, horizon_year)
     39 print(region, "done. Exec time(s): ", time.time() - start_time)

File ~/microsimulation/microsimulation/static.py:57, in SequentialMicrosynthesis.__init__(self, region, resolution, variant, is_custom, cache_dir, output_dir, fast_mode)
     53   raise ValueError("fast mode should be boolean")
     55 # TODO enable 2001 ref year?
     56 # (down)load the census 2011 tables
---> 57 self.__get_census_data()

File ~/microsimulation/microsimulation/static.py:190, in SequentialMicrosynthesis.__get_census_data(self)
    188 def __get_census_data(self):
--> 190   (dc1117, dc2101, dc6206) = self.get_census_data()
    192   # add children to adult-only table
    193   #dc6206ew_adj = self.append_children(dc1117, dc6206)
    194   # For now we drop NS-SEC (not clear if needed)
    195   dc6206_adj = None

File ~/microsimulation/microsimulation/common.py:26, in Base.get_census_data(self)
     24 def get_census_data(self):
     25     if self.region[0] == "S":
---> 26         return self.__get_census_data_sc()
     27     elif self.region[0] == "N":
     28         raise NotImplementedError("NI support not yet implemented")

File ~/microsimulation/microsimulation/common.py:51, in Base.__get_census_data_sc(self)
     49 dc1117seed = utils.unlistify(dc1117lad, ["DC1117SC_0_CODE", "DC1117SC_1_CODE"], [2, 86], "OBS_VALUE").astype(float)
     50 # expand to all geogs within LAD
---> 51 dc1117seed = np.dstack([dc1117seed] * ngeogs).T
     53 ga = utils.unlistify(qs103sc, ["GEOGRAPHY_CODE", "QS103SC_0_CODE"], [ngeogs, nages], "OBS_VALUE")
     54 gs = utils.unlistify(qs104sc, ["GEOGRAPHY_CODE", "QS104SC_0_CODE"], [ngeogs, nsexes], "OBS_VALUE")

File <__array_function__ internals>:180, in dstack(*args, **kwargs)

File ~/miniconda/lib/python3.9/site-packages/numpy/lib/shape_base.py:723, in dstack(tup)
    721 if not isinstance(arrs, list):
    722     arrs = [arrs]
--> 723 return _nx.concatenate(arrs, 2)

File <__array_function__ internals>:180, in concatenate(*args, **kwargs)

ValueError: need at least one array to concatenate

```

This happens because `ngeogs` is 0. 

### Why is `ngeogs` 0?
Because it is collected from an empty dataset `qs103sc`
```python
    def __get_census_data_sc(self):

        print("Synthesising Scottish DC1117/DC2101 tables from LAD-level seeds and univariate data")

        # age only, no gender
        qs103sc = self.data_api_sc.get_data("QS103SC", self.region, self.resolution, category_filters={"QS103SC_0_CODE": range(1,102)})
        qs103sc = utils.cap_value(qs103sc, "QS103SC_0_CODE", 86, "OBS_VALUE")
        # sex only
        qs104sc = self.data_api_sc.get_data("QS104SC", self.region, self.resolution, category_filters={"QS104SC_0_CODE": [1,2]})

        ngeogs = len(qs103sc.GEOGRAPHY_CODE.unique())
        
```

as shown: 

```python
ipdb> qs103sc
Empty DataFrame
Columns: [GEOGRAPHY_CODE, QS103SC_0_CODE, OBS_VALUE]
Index: []

```

because of: 

```python
self.data_api_sc.get_data("QS103SC", self.region, self.resolution, category_filters={"QS103SC_0_CODE": range(1,102)})
```

where: 

```python
ipdb> self.region
'S12000005'
ipdb> self.resolution
'MSOA11'

```

### get_data | NRScotland | UKCensusAPI 

Here the `get_data` function produces the empty dataframe noted above 

A few curiosities are present in this function: 

```python
  def get_data(self, table, coverage, resolution, category_filters={}, r_compat=False):
    """
    Returns a table with categories in columns, filtered by geography and (optionally) category values
    If r_compat==True, instead of returning a pandas dataframe it returns a dict raw value data and column names
    that can be converted into an R data.frame 
    """

    # No data is available for Intermediate zones (~MSOA) so we get Data Zone (LSOA) then aggregate
    msoa_workaround = False
    if resolution == "MSOA11":
      msoa_workaround = True
      resolution = "LSOA11"

```

Notably the `msoa_workaround` which given will result in `resolution="LSOA11"`

Stepping through tis function to the next lines of code 

```python
geography = self.get_geog(coverage, resolution)
meta, raw_data = self.__get_rawdata(table, resolution)
```

Inspecting these we can see that 

```python
ipdb> geography
array(['S01007454', 'S01007453', 'S01007452', 'S01007424', 'S01007433',
       'S01007444', 'S01007447', 'S01007480', 'S01007479', 'S01007478',
       'S01007466', 'S01007465', 'S01007464', 'S01007463', 'S01007462',
       'S01007476', 'S01007474', 'S01007473', 'S01007475', 'S01007472',
       'S01007471', 'S01007477', 'S01007470', 'S01007467', 'S01007468',
       'S01007461', 'S01007460', 'S01007469', 'S01007459', 'S01007432',
       'S01007458', 'S01007455', 'S01007456', 'S01007457', 'S01007413',
       'S01007409', 'S01007412', 'S01007411', 'S01007410', 'S01007419',
       'S01007418', 'S01007420', 'S01007415', 'S01007416', 'S01007449',
       'S01007451', 'S01007450', 'S01007448', 'S01007434', 'S01007436',
       'S01007435', 'S01007439', 'S01007438', 'S01007437', 'S01007440',
       'S01007442', 'S01007441', 'S01007429', 'S01007428', 'S01007425',
       'S01007426', 'S01007427', 'S01007430', 'S01007431', 'S01007421',
       'S01007422', 'S01007423', 'S01007443', 'S01007446', 'S01007445',
       'S01007417', 'S01007414'], dtype=object)

```

and 

```python
ipdb> meta
{'table': 'QS103SC', 'description': '', 'geography': 'LSOA11', 'fields': {'QS103SC_0_CODE': {0: 'All people', 1: 'Under 1', 2: '1', 3: '2', 4: '3', 5: '4', 6: '5', 7: '6', 8: '7', 9: '8', 10: '9', 11: '10', 12: '11', 13: '12', 14: '13', 15: '14', 16: '15', 17: '16', 18: '17', 19: '18', 20: '19', 21: '20', 22: '21', 23: '22', 24: '23', 25: '24', 26: '25', 27: '26', 28: '27', 29: '28', 30: '29', 31: '30', 32: '31', 33: '32', 34: '33', 35: '34', 36: '35', 37: '36', 38: '37', 39: '38', 40: '39', 41: '40', 42: '41', 43: '42', 44: '43', 45: '44', 46: '45', 47: '46', 48: '47', 49: '48', 50: '49', 51: '50', 52: '51', 53: '52', 54: '53', 55: '54', 56: '55', 57: '56', 58: '57', 59: '58', 60: '59', 61: '60', 62: '61', 63: '62', 64: '63', 65: '64', 66: '65', 67: '66', 68: '67', 69: '68', 70: '69', 71: '70', 72: '71', 73: '72', 74: '73', 75: '74', 76: '75', 77: '76', 78: '77', 79: '78', 80: '79', 81: '80', 82: '81', 83: '82', 84: '83', 85: '84', 86: '85', 87: '86', 88: '87', 89: '88', 90: '89', 91: '90', 92: '91', 93: '92', 94: '93', 95: '94', 96: '95', 97: '96', 98: '97', 99: '98', 100: '99', 101: '100 and over'}}}
ipdb> raw_data
   Unnamed: 0 All people Under 1       1       2       3       4       5       6       7       8       9      10      11      12  ...      86      87      88      89     90     91     92     93     94     95     96     97   98   99 100 and over
0   S92000003  5,295,403  58,715  59,556  58,909  58,764  56,877  55,224  55,236  53,786  52,325  53,046  55,067  56,769  58,656  ...  15,898  14,181  12,024  10,778  9,439  6,975  4,331  3,321  2,840  2,241  1,825  1,174  857  539          854
1   S12000005     51,442     615     561     593     620     538     562     534     512     544     556     571     609     606  ...     119     117     111      79     64     51     35     26     22     16     13      9    3    4            4
2   S12000006    151,324   1,487   1,577   1,495   1,563   1,538   1,540   1,558   1,470   1,463   1,347   1,555   1,570   1,681  ...     601     482     444     387    335    235    168    121    102     77     63     45   30   13           25
3   S12000008    122,767   1,348   1,372   1,387   1,319   1,336   1,292   1,316   1,305   1,202   1,279   1,295   1,371   1,375  ...     343     291     268     219    226    151    112     78     49     48     40     27   13   14           11
4   S12000010     99,717   1,155   1,139   1,223   1,228   1,180   1,162   1,126   1,155   1,023   1,075   1,125   1,164   1,174  ...     328     294     250     217    191    159     77     69     63     37     47     32   18    8           23
5   S12000011     90,574     944     957     988   1,041   1,054   1,044   1,163   1,100   1,109   1,142   1,150   1,169   1,250  ...     307     286     251     219    198    142     81     73     55     45     35     26   23   14           19
6   S12000013     27,684     235     243     277     287     312     277     268     302     301     265     304     279     325  ...     108     114      94      91     69     52     27     21     17     13     14     15    8    4            6
7   S12000014    155,990   1,786   1,961   1,903   1,863   1,826   1,802   1,740   1,697   1,602   1,570   1,673   1,696   1,818  ...     437     396     290     292    249    184    108     78     71     55     43     19   24   12           20
8   S12000015    365,198   4,116   4,311   4,213   4,122   4,013   3,957   3,991   3,788   3,649   3,771   3,814   3,874   4,135  ...   1,149   1,041     858     715    725    493    326    250    183    189    133     88   61   36           63
9   S12000017    232,132   2,457   2,424   2,645   2,652   2,527   2,442   2,534   2,481   2,456   2,495   2,589   2,498   2,819  ...     743     666     599     490    458    302    214    175    137    100     96     48   28   33           47
10  S12000018     81,485     811     833     846     885     829     830     853     800     790     739     845     877     920  ...     231     242     211     172    164    108    100     56     50     38     25     17   17   13           10
11  S12000019     83,187     951     995     961   1,032     939     982     971     924     872     932     983   1,014   1,045  ...     225     205     170     145    131     99     58     44     39     32     23     14   17    6            9
12  S12000020     93,295     987   1,057   1,073   1,125   1,004     986     995     926     997     983     981   1,107   1,121  ...     306     239     222     187    190    127     89     74     48     39     39     20   17    8           13
13  S12000021    138,146   1,462   1,476   1,480   1,499   1,503   1,473   1,453   1,410   1,444   1,450   1,489   1,502   1,597  ...     409     362     350     283    282    197    108     86     81     57     50     35   43   12           24
14  S12000023     21,349     209     205     231     220     242     213     199     194     202     196     218     227     246  ...      70      60      54      52     44     21     22     23     18     15      5      5   10    4            7
15  S12000024    146,652   1,468   1,456   1,451   1,533   1,409   1,448   1,461   1,428   1,487   1,483   1,649   1,691   1,666  ...     556     520     439     403    300    239    146    131     96     80     84     46   30   22           36
16  S12000026    113,870   1,104   1,099   1,153   1,188   1,168   1,124   1,216   1,182   1,154   1,229   1,188   1,259   1,247  ...     476     408     328     306    285    207    127     81     70     45     54     37   22   13           24
17  S12000027     23,167     257     305     279     239     309     266     267     269     262     262     270     311     286  ...      71      60      51      50     44     31     15     22     12      9      9     10    3    2            3
18  S12000028    112,799   1,117   1,057   1,087   1,088   1,163   1,060   1,094   1,096   1,056   1,106   1,157   1,180   1,286  ...     465     408     348     317    269    201    128     93     92     63     52     41   32    9           18
19  S12000029    313,830   3,415   3,461   3,484   3,537   3,504   3,386   3,308   3,314   3,209   3,269   3,393   3,566   3,568  ...     846     774     662     596    515    378    228    187    142    127    101     58   40   30           45
20  S12000030     90,247     828     899     875     868     945     942     973   1,010     937     928   1,010   1,114   1,142  ...     290     233     199     165    162    126     75     72     57     39     29     26   17   12           13
21  S12000033    222,793   2,469   2,489   2,238   2,218   2,098   1,936   1,898   1,799   1,790   1,749   1,782   1,914   1,841  ...     605     548     475     401    392    274    141    124    110     82     59     39   35   22           39
22  S12000034    252,973   2,880   3,182   3,085   2,979   3,012   2,843   2,854   2,778   2,803   2,730   2,828   3,031   3,064  ...     753     690     543     516    422    341    187    155    133     93     87     59   26   27           36
23  S12000035     88,166     767     872     843     785     840     865     879     879     823     866     906     889   1,035  ...     336     291     262     230    211    164    102     63     58     57     49     31   16   15           32
24  S12000036    476,626   5,753   5,440   5,330   5,035   4,605   4,407   4,085   4,057   3,935   4,010   4,044   4,101   4,231  ...   1,421   1,321   1,129   1,032    904    627    438    293    279    227    182    114   85   57           93
25  S12000038    174,908   1,971   1,936   1,924   1,917   1,888   1,856   1,927   1,783   1,694   1,818   1,904   1,971   1,909  ...     467     422     356     360    305    231    126    108    116     79     52     42   28   19           15
26  S12000039     90,720   1,017   1,069   1,044   1,096   1,010     942   1,009     920     918     871     892   1,040     991  ...     257     222     179     163    164    127     78     65     49     47     31     19    6   11           11
27  S12000040    175,118   2,258   2,286   2,332   2,390   2,299   2,208   2,213   2,117   2,061   2,123   2,086   2,074   2,214  ...     308     325     228     224    170    131     89     78     49     40     30     20   16   13           20
28  S12000041    115,978   1,130   1,179   1,222   1,263   1,173   1,203   1,214   1,260   1,187   1,232   1,266   1,306   1,397  ...     441     395     347     290    232    204    118     93     80     72     51     29   23   15           25
29  S12000042    147,268   1,599   1,622   1,558   1,557   1,455   1,416   1,383   1,406   1,306   1,344   1,359   1,444   1,453  ...     516     486     392     309    282    209    121     69     86     67     58     25   28   13           19
30  S12000044    337,727   3,892   4,211   4,159   4,146   4,141   3,936   4,067   3,917   3,772   3,782   3,863   3,961   4,190  ...     753     658     503     515    404    302    186    130    122     88     76     39   49   28           26
31  S12000045    105,026     990     974   1,063   1,073   1,052   1,154   1,115   1,173   1,062   1,176   1,172   1,249   1,304  ...     369     319     237     248    180    144     84     71     63     40     27     20   18    8           17
32  S12000046    593,245   7,237   6,908   6,467   6,396   5,965   5,670   5,572   5,334   5,215   5,268   5,706   5,711   5,720  ...   1,592   1,306   1,174   1,105    872    718    417    312    291    225    168    119   71   42          101

[33 rows x 103 columns]

```

Seemingly fine so far, as there is data! 

### Misc data wrangling 

```python
    raw_data.replace("-", 0, inplace=True)
    raw_data.replace(",", "", inplace=True, regex=True)
    # assumes the first n are (unnamed) columns we don't want to melt, geography coming first: n = geog + num categories - 1 (the one to melt)
    lookup = raw_data.columns.tolist()[len(meta["fields"]):]

    id_vars = ["GEOGRAPHY_CODE"]
    for i in range(1,len(meta["fields"])):
      id_vars.append(table + "_" + str(i) + "_CODE")
    cols = id_vars.copy()
    cols.extend(list(range(0,len(lookup))))

    raw_data.columns = cols
    raw_data = raw_data.melt(id_vars=id_vars)
    id_vars.extend([table + "_0_CODE", "OBS_VALUE"])
    raw_data.columns = id_vars

    # ensure OBS_VALUE is numeric
    raw_data["OBS_VALUE"] = pd.to_numeric(raw_data["OBS_VALUE"])

    # convert categories to numeric values
    for i in range(1,len(meta["fields"])):
      category_name = raw_data.columns[i]
      category_values = meta["fields"][category_name]
      # make sure metadata has same no. of categories
      assert len(category_values) == len(raw_data[category_name].unique())
      category_map = { k: v for v, k in enumerate(category_values)}
      raw_data[category_name] = raw_data[category_name].map(category_map)

    # geography (and category_filter) must be lists
    if isinstance(geography, str):
      geography = [geography]

```

Results in the data table now looking: 

```python
In [2]: raw_data
Out[2]: 
     GEOGRAPHY_CODE QS103SC.csv_0_CODE  OBS_VALUE
0         S92000003                  0    5295403
1         S12000005                  0      51442
2         S12000006                  0     151324
3         S12000008                  0     122767
4         S12000010                  0      99717
...             ...                ...        ...
3361      S12000041                101         25
3362      S12000042                101         19
3363      S12000044                101         26
3364      S12000045                101         17
3365      S12000046                101        101

[3366 rows x 3 columns]

```

### The true problem

```python
data = raw_data[raw_data.GEOGRAPHY_CODE.isin(geography)]
```

this creates the filtered empty `data` becuse `GEOGRAPHY_CODE` and the values in `geography` are not of the same format

## `geography`

This variable is defined by 

```python
  def get_geog(self, coverage, resolution):
    """
    Returns all areas at resolution in coverage
    """
    # assumes all areas in coverage are the same type
    coverage_type = _coverage_type(coverage)
    if coverage_type == "ALL":
      return self.area_lookup[resolution].unique()

    # ensure list
    if isinstance(coverage, str):
      coverage = [coverage]

    return self.area_lookup[self.area_lookup[coverage_type].isin(coverage)][resolution].unique()
```

Where this `area_lookup` table is referenced and is defined in the class constructor 

```python
  def __init__(self, cache_dir):
    """Constructor.
    Args:
        cache_dir: cache directory
    Returns:
        an instance.
    """
    # checks exists and is writable, creates if necessary
    self.cache_dir = utils.init_cache_dir(cache_dir)

    self.offline_mode = not utils.check_online(self.URL1)
    if self.offline_mode:
     print("Unable to contact %s, operating in offline mode - pre-cached data only" % self.URL1)

    # download the lookup if not present
    lookup_file = self.cache_dir / "sc_lookup.csv"
    if not os.path.isfile(str(lookup_file)):
      self.make_sc_lookup()

    self.area_lookup = pd.read_csv(str(self.cache_dir / "sc_lookup.csv"))

    # TODO use a map (just in case col order changes)
    #self.area_lookup = self.area_lookup.rename(columns={"OutputArea", "DataZone", "InterZone", "Council":"LSOA11"})
    self.area_lookup.columns = ["OA11", "LSOA11", "MSOA11", "LAD"] # This maybe broken for scotland
```

### `sc_lookup.csv`

Is it possible this CSV and the presumed column order being set to it have shifted? 

```python
["OA11", "LSOA11", "MSOA11", "LAD"]
```

In this csv we have 

```csvpreview
OutputArea,DataZone,InterZone,Council
S00088956,S01006732,S02001275,S12000033
S00088957,S01006732,S02001275,S12000033
S00088958,S01006732,S02001275,S12000033
S00088959,S01006737,S02001275,S12000033
S00088960,S01006518,S02001238,S12000033
```

Which indicates that `Council` or `LAD` columns are matching to `raw_data` and that `LSOA11` or `DataZone` are not present. 

So perhaps `raw_data` is incorrectly made?


### `__get_rawdata`


```python 
  def __get_rawdata(self, table, resolution):
    """
    Gets the raw csv data and metadata
    """

    if not os.path.exists(os.path.join(str(self.cache_dir), table + ".csv")):
      print("Downloading...")
      try:
        zf = str(self.__source_to_zip(self.data_sources[self.GeoCodeLookup[resolution]]))
        print("File saved to: ", zf)
        with zipfile.ZipFile(zf) as zip_ref:
          print("Extracting downloaded zip...")
          zip_ref.extractall(str(self.cache_dir))
      except NotImplementedError as e:
        print("Unsupported zip type... running unzip via shell...")
        subprocess.run(['unzip', zf, '-d', str(self.cache_dir)])
    else:
      print("Data already chached at: " + str(os.path.join(str(self.cache_dir), table + ".csv")))
    raw_data = pd.read_csv(os.path.join(str(self.cache_dir), table + ".csv"))
    # more sophisticate way to check for no data?
    if raw_data.shape == (2,1):
      raise ValueError("Table {}: data not available at {} resolution.".format(table, resolution))
    # assumes:
    # - first column is geography (unnamed)
    # - any subsequent columns are categorical
    # - named columns are categories
    raw_cols = raw_data.columns.tolist()
    fields = {}

    col_index = 1
    while raw_cols[col_index][:8] == "Unnamed:":
      # lists format better than numpy arrays 
      fields[table + "_" + str(col_index) + "_CODE"] = raw_data[raw_cols[col_index]].unique().tolist()
      col_index = col_index + 1

    categories = raw_data.columns.tolist()[col_index:]
    fields[table + "_0_CODE"] = dict(zip(range(len(categories)), categories))

    meta = { "table": table,
             "description": "",
             "geography": resolution,
             "fields": fields
          }
    return (meta, raw_data)
```

Looking into the table downloaded here 

```csvpreview 
,All people,Males,Females
S92000003,"5,295,403","2,567,444","2,727,959"
S12000005,"51,442","25,109","26,333"
S12000006,"151,324","73,405","77,919"
S12000008,"122,767","59,389","63,378"
S12000010,"99,717","47,873","51,844"
S12000011,"90,574","43,165","47,409"
```

it would seem that `LAD` codes are used? 