<jupyter_start><jupyter_text>FAOstat: Food Balances Combined- [_Source data (FBS)_](https://www.fao.org/faostat/en/data/FBS)- [_Source data (FBSH)_](https://www.fao.org/faostat/en/data/FBSH)This notebook integrates two FAOstat datasets (previously imported to _meadow_) into a single _garden_ dataset. This is because a new version of the _Food Balances_ dataset was launched in 2014 with a slightly new methodology ([more info](https://fenixservices.fao.org/faostat/static/documents/FBS/New%20FBS%20methodology.pdf)). The new dataset is named FBSC (Food Balances Combined).  Parameters<jupyter_code>dest_dir = "/tmp/faostat_fbs"<jupyter_output><empty_output><jupyter_text>Imports & PathsImport the required libraries and define paths to load files (including data files and standardisation mappings for item and element names).<jupyter_code>import json
from pathlib import Path
import pandas as pd
from owid import catalog
from etl.paths import DATA_DIR, BASE_DIR
# side-car file containing manual country mapping
COUNTRY_MAPPING = (
    BASE_DIR / "etl/steps/data/garden/faostat/2021-04-09/faostat_fbsc.country_std.json"
)<jupyter_output><empty_output><jupyter_text>Load meadow datasetsIn this step we load the required datasets from Garden: FBS and FBSH<jupyter_code># Read datasets
fbs_meadow = catalog.Dataset(DATA_DIR / "meadow/faostat/2021-04-09/faostat_fbs")
fbsh_meadow = catalog.Dataset(DATA_DIR / "meadow/faostat/2017-12-11/faostat_fbsh")
metadata = catalog.Dataset(DATA_DIR / "meadow/faostat/2022-02-10/faostat_metadata")
# Bulk data and items metadata
fbs_bulk = fbs_meadow["bulk"]
fbsh_bulk = fbsh_meadow["bulk"]
print(fbs_bulk.shape)
fbs_bulk.head()
print(fbsh_bulk.shape)
fbsh_bulk.head()<jupyter_output><empty_output><jupyter_text>Sanity checksAs we are fusing two different datasets, we will be doing some checks to ensure the consistency of the dataset. Specially in the identifying fields (i.e. `Year`, `Area Code`, `Item Code`, `Element Code`, `Flag`)  Check data files  `Year`Check if the time window of both datasets is disjoint, otherwise we could end up with duplicates.<jupyter_code>fbs_year_min, fbs_year_max = (
    fbs_bulk.index.get_level_values("year").min(),
    fbs_bulk.index.get_level_values("year").max(),
)
fbsh_year_min, fbsh_year_max = (
    fbsh_bulk.index.get_level_values("year").min(),
    fbsh_bulk.index.get_level_values("year").max(),
)
# Year disjoints
assert (fbsh_year_min < fbsh_year_max) & (
    fbsh_year_max + 1 == fbs_year_min < fbs_year_max
)<jupyter_output><empty_output><jupyter_text>`Area`Here we check which Areas (i.e. countries/regions) appear in one dataset but not in the other.We observe that former countries only appear in FBSH (USSR, Serbia and Montenegro, Sudan (fromer), Belgium-Luxembourg, Checkoslovakia, Netherland Antilles, Yugoslavia, Ethiopia PDR), which makes sense. There are some special cases where countries stopped or started appearing (Bermuda, Brunei and Papua New Guinea, Seychelles and Comoros).<jupyter_code>fbsh_area = metadata["meta_fbsh_area"]
fbs_area = metadata["meta_fbs_area"]
# Get unique codes
codes_fbs = set(fbs_bulk.index.get_level_values("area_code"))
codes_fbsh = set(fbsh_bulk.index.get_level_values("area_code"))
# Find missing codes
miss_in_fbs = codes_fbsh.difference(codes_fbs)
miss_in_fbsh = codes_fbs.difference(codes_fbsh)
# Print
print("- FBSH but not FBS:", fbsh_area.loc[sorted(miss_in_fbs), "country"].to_dict())
print("- FBS but not FBSH:", fbs_area.loc[sorted(miss_in_fbsh), "country"].to_dict())<jupyter_output><empty_output><jupyter_text>Next, we check that all codes correspond to the same country name in both datasets.<jupyter_code>x = fbs_area.merge(fbsh_area, left_index=True, right_index=True)
assert (x.country_x.astype(str) == x.country_y.astype(str)).all()<jupyter_output><empty_output><jupyter_text>`Item`Here we check which items appear and disappear from dataset to dataset.It seems that some elements were deprecated in favour of others:  `Groundnuts (Shelled Eq) --> Groundnuts` and `Rice (Milled Equivalent) --> Rice and products`<jupyter_code># Load item info
fbsh_item = metadata["meta_fbsh_item"]
fbs_item = metadata["meta_fbs_item"]
def build_item_all_df(df):
    """Flatten item dataframe."""

    def _process_df(df, cols):
        return (
            df.drop_duplicates(cols)[cols]
            .rename(columns=dict(zip(cols, ["code", "name"])))
            .set_index("code")
        )

    df = df.reset_index()
    a = _process_df(df, ["item_group_code", "item_group"])
    b = _process_df(df, ["item_code", "item"])
    df = pd.concat([a, b])
    assert df.index.value_counts().max() == 1
    return df
# Build flattened version (item group, item in same column)
fbsh_item_ = build_item_all_df(fbsh_item)
fbs_item_ = build_item_all_df(fbs_item)
# Get unique codes
codes_fbs = set(fbs_bulk.index.get_level_values("item_code"))
codes_fbsh = set(fbsh_bulk.index.get_level_values("item_code"))
# Find missing codes
miss_in_fbs = codes_fbsh.difference(codes_fbs)
miss_in_fbsh = codes_fbs.difference(codes_fbsh)
# Print
print("- FBSH but not FBS:", fbsh_item_.loc[sorted(miss_in_fbs), "name"].to_dict())
print("- FBS but not FBSH:", fbs_item_.loc[sorted(miss_in_fbsh), "name"].to_dict())
# fbsh_item.reset_index().set_index(["item_code", "item_group_code"]).loc[2805]
# fbs_item.reset_index().set_index(["item_code", "item_group_code"]).loc[2807]<jupyter_output><empty_output><jupyter_text>We check that all codes are mapped to the same names.<jupyter_code>x = fbs_item_.merge(fbsh_item_, left_index=True, right_index=True)
assert (x.name_x.astype(str) == x.name_y.astype(str)).all()
x[x.name_x != x.name_y]<jupyter_output><empty_output><jupyter_text>`Element`We see that two items were introduced in FBS (not present in FBSH): `Residuals` and `Tourist consumption`.<jupyter_code># Load element info
fbsh_element = metadata["meta_fbsh_element"]
fbs_element = metadata["meta_fbs_element"]
# Get unique codes
codes_fbs = set(fbs_bulk.index.get_level_values("element_code"))
codes_fbsh = set(fbsh_bulk.index.get_level_values("element_code"))
# Find missing codes
miss_in_fbs = codes_fbsh.difference(codes_fbs)
miss_in_fbsh = codes_fbs.difference(codes_fbsh)
# Print
print("- FBSH but not FBS:", fbsh_element.loc[miss_in_fbs, "element"].to_dict())
print("- FBS but not FBSH:", fbs_element.loc[miss_in_fbsh, "element"].to_dict())<jupyter_output><empty_output><jupyter_text>First, we check if all element codes just have one unit associated. Next, we verify that in both datasets we have the same mappings `code -> name`, `code -> unit` and `code -> description`.<jupyter_code># Only one unit per element code
assert fbs_bulk.reset_index().groupby("element_code").unit.nunique().max() == 1
assert fbsh_bulk.reset_index().groupby("element_code").unit.nunique().max() == 1
# Given an element code, we have the same element name, unit and description in fbs and fbsh
x = fbs_element.merge(fbsh_element, left_index=True, right_index=True)
assert (x.element_x.astype(str) == x.element_y.astype(str)).all()
assert (x.unit_x.astype(str) == x.unit_y.astype(str)).all()
assert (x.description_x.astype(str) == x.description_y.astype(str)).all()<jupyter_output><empty_output><jupyter_text>`Flag`Next, we compare which flags appear in each dataset. We observe that some flags only appear in one of the datasets. This is fine. In particular:- `Im` (Imputed) ist most common in new dataset, whereas `S` (Standardized data) was in the old one.- `Im` (Imputed) and `*` (Unofficial) appear first in new FBS.- `nan` (Official data), `SD` (Statistical Discrepancy) and `F` (FAO estimate) appear only in old FBSH.<jupyter_code># Get unique codes
codes_fbs = set(fbs_bulk.index.get_level_values("flag"))
codes_fbsh = set(fbsh_bulk.index.get_level_values("flag"))
# Find missing codes
miss_in_fbs = codes_fbsh.difference(codes_fbs)
miss_in_fbsh = codes_fbs.difference(codes_fbsh)
# Print
print("- FBSH but not FBS:", miss_in_fbs)
print("- FBS but not FBSH:", miss_in_fbsh)
pd.value_counts(fbsh_bulk.index.get_level_values("flag").fillna("nan"))
pd.value_counts(fbs_bulk.index.get_level_values("flag").fillna("nan"))<jupyter_output><empty_output><jupyter_text>Merge datasetThe moment has arrived. Now we attempt to merge both FBS and FBSH datasets into one: FBSC dataset. For this, we will be merging several files:- **bulk file**: The data itself.- **item file**: The file containing the mapping from item code to item name.- **element file**: The file containing the mapping from element to element name and unit.In addition, we will transition from `Area Code ---> Country`.  `Area`In this step, we standardise the country names. We first go from `Area Code` to `Area` (country name as per the FAO), and then `Area` to `Country`, using our country standardisation file.<jupyter_code># Load our country standardisation file
with open(COUNTRY_MAPPING) as f:
    country_mapping = json.load(f)
# Merge both datasets Area Code -> Area mapping dataframe
fbsc_area = pd.concat([fbs_area, fbsh_area]).drop_duplicates(subset=["country"])
fbsc_area[fbsc_area.country.apply(lambda x: "sudan" in x.lower())]
# Check which countries will be discarded based on our country standardisation file (those without a mapped standardised name)
msk = fbsc_area.country.isin(country_mapping)
print(fbsc_area.loc[-msk, "country"].tolist())<jupyter_output><empty_output><jupyter_text>Finally, we build the `Area Code ---> Country` mapping dictionary.<jupyter_code>map_area = fbsc_area.loc[msk, "country"].replace(country_mapping).sort_index().to_dict()<jupyter_output><empty_output><jupyter_text>`Item`Merging the item dataframe is straight forward. There are some exceptions, which we accept, due to the renaming of items such as Groundnuts and Rice.<jupyter_code>fbsc_item = pd.concat([fbs_item, fbsh_item]).drop_duplicates(
    subset=["item_group", "item"]
)
# Check differences are as exprected
a = fbs_item.index
b = fbsh_item.index
c = fbsc_item.index

assert not {cc for cc in c if cc not in a}.difference(
    {
        (2905, 2805),
        (2901, 2805),
        (2903, 2805),
        (2901, 2556),
        (2913, 2556),
        (2903, 2556),
        (2960, 2769),
    }
)

assert not {cc for cc in c if cc not in b}.difference(
    {
        (2905, 2807),
        (2901, 2807),
        (2903, 2807),
        (2901, 2552),
        (2913, 2552),
        (2903, 2552),
        (2961, 2769),
    }
)
# fbsh_item.loc[2960, 2769]
# fbs_item.loc[2961, 2769]
fbsc_item = fbsc_item[["item_group", "item"]]<jupyter_output><empty_output><jupyter_text>`Element`We merge element and unit dataframes, in order to obtain all the info in one. Next, we combine both FBS and FBSH datasets.<jupyter_code># Load unit table
fbs_unit = metadata["meta_fbs_unit"]
fbsh_unit = metadata["meta_fbsh_unit"]
# Merge element and unit
fbs_element_unit = fbs_element.merge(
    fbs_unit.rename(columns={"description": "unit_description"}),
    left_on="unit",
    right_index=True,
)
assert fbs_element_unit.shape[0] == fbs_element.shape[0]

fbsh_element_unit = fbsh_element.merge(
    fbsh_unit.rename(columns={"description": "unit_description"}),
    left_on="unit",
    right_index=True,
)
assert fbsh_element_unit.shape[0] == fbsh_element.shape[0]
# Merge
fbsc_element_unit = pd.concat([fbs_element_unit, fbsh_element_unit]).drop_duplicates(
    subset=["element", "unit", "unit_description"]
)
assert fbsc_element_unit.shape == fbsh_element_unit.shape == fbs_element_unit.shape<jupyter_output><empty_output><jupyter_text>`Bulk`Time to merge the core of the dataset, the bulk file! We do this by:- Concatenating both datasets- Renaming `Area Code --> Country`- Drop unused columns (`Unit`, `Area Code`)- Drop data related to population (`2501`) item.- Add `variable_name` column, with some more verbosity about each row info.<jupyter_code>fbsc_bulk = pd.concat([fbs_bulk, fbsh_bulk])
# Filter countries + Area Code -> Country
index_new = [
    (col_map := {"area_code": "country"}).get(x, x) for x in fbsc_bulk.index.names
]
fbsc_bulk = fbsc_bulk.loc[map_area].reset_index()
fbsc_bulk[col_map["area_code"]] = fbsc_bulk["area_code"].replace(map_area).tolist()
fbsc_bulk = fbsc_bulk.set_index(index_new)
# Drop Unit, Area Code
fbsc_bulk = fbsc_bulk.drop(columns=["unit", "area_code"])
# Drop population (2501) item
msk = fbsc_bulk.index.get_level_values("item_code").isin([2501])
fbsc_bulk = fbsc_bulk[~msk]<jupyter_output><empty_output><jupyter_text>Variable nameVariable name is built using the name of the item, element and unit: `item - element - [unit]`<jupyter_code># Get item names
fbsc_item_ = build_item_all_df(fbsc_item)
map_items = fbsc_item_.astype(str)["name"].to_dict()
item_names = [map_items[i] for i in fbsc_bulk.index.get_level_values("item_code")]
# Get Element + Unit names
x = fbsc_element_unit.reset_index()
y = list(x["element"].astype(str) + " [" + x["unit"].astype(str) + "]")
map_elems = dict(zip(x["element_code"], y))
elem_names = [map_elems[el] for el in fbsc_bulk.index.get_level_values(2)]
# Construct variable name
variable_names = [f"{i} - {e}" for i, e in zip(item_names, elem_names)]
# Add variable name to index
fbsc_bulk["variable_name"] = variable_names
fbsc_bulk = fbsc_bulk.reset_index()
fbsc_bulk = fbsc_bulk.set_index(
    ["country", "item_code", "element_code", "variable_name", "year", "flag"]
)
fbsc_bulk.head()<jupyter_output><empty_output><jupyter_text>Create Garden dataset  MetadataFirst, we create the metadata for this new dataset FBSC. Most of its content comes from concatenating FBS and FBSH fields. Checksum field is left to `None`, as it is unclear what we should use here (TODO).<jupyter_code>from owid.catalog.meta import DatasetMeta
# Check description field in FBS and FBSH
assert fbsh_meadow.metadata.description == fbs_meadow.metadata.description

# Define metadata
metadata = DatasetMeta(
    namespace="faostat",
    short_name="faostat_fbsc",
    title="Food Balance: Food Balances (-2013 old methodology and 2014-) - FAO (2017, 2021)",
    description=fbsh_meadow.metadata.description,
    sources=fbsh_meadow.metadata.sources + fbs_meadow.metadata.sources,
    licenses=fbsh_meadow.metadata.licenses + fbs_meadow.metadata.licenses,
)<jupyter_output><empty_output><jupyter_text>Create dataset and add tablesFinally, we add the tables to the dataset.<jupyter_code>fbsc_garden = catalog.Dataset.create_empty(dest_dir)
# Propagate metadata
fbsc_garden.metadata = metadata
fbsc_garden.save()
# Add bulk table
fbsc_bulk.metadata.short_name = "bulk"
fbsc_garden.add(fbsc_bulk)
# Add table items
fbsc_item.metadata.short_name = "meta_item"
fbsc_garden.add(fbsc_item)
# Add table elements
fbsc_element_unit.metadata = fbs_element.metadata
fbsc_element_unit.metadata.description = "List of elements, with their units and the respective descriptions of both. It also includes the element codes."
fbsc_garden.add(fbsc_element_unit)
fbsc_garden.save()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12078105079514191, 'fraction_numerical': 0.014292424344091793, 'mean_word_length': 5.919220055710307, 'pattern_counts': {'":': 3, '<': 79, '<?xml version=': 0, '>': 87, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '302582', 'license': 'MIT', 'n_tokens_mistral': 5436, 'n_tokens_neox': 5133, 'n_words': 1287, 'path': 'etl/steps/data/garden/faostat/2021-04-09/faostat_fbsc.ipynb', 'repo_name': 'c1x1x00xxPentium/etl', 'size': '14903'}