Selected code · Data Officer at AAFC
From data to useful information
Most of my programming at AAFC was in R. This Python example shows how I retrieved public data, put it into a consistent shape and carried readable descriptions alongside the values.
Data preparation connects the source information to the analysis that follows. These excerpts show three parts of that work: collecting, reshaping and describing the data.
Collect with purpose
Select the countries, indicators, units and years needed, then retrieve the data through the source’s API.
req = sdmx.Request("OECD")
resp = req.data(
resource_id="MON_REFERENCE_TABLE",
key=KEY4, params=PARAMS
)
df_data = resp.to_pandas()
df_data = df_data.to_frame()KEY4 and PARAMS hold the query selections defined elsewhere in the original analysis.
Make the source and the selection explicit.
Give the data a consistent shape
Turn year columns into rows, retaining the identifying fields so the data can be grouped and compared.
df_process = df_process.melt(
id_vars=lst_id_cols,
var_name='Year',
value_name='Value'
)
df_process.Year = (
df_process.Year.astype('int64')
)The identifying columns are selected before this excerpt.
Use a structure that supports the next calculation.
Keep the meaning with the values
Use the source’s descriptions to make coded fields easier to interpret when preparing an output.
df_data['Country Description'] = (
df_data['Country'].apply(
lambda x: df_desc[
df_desc['Code'] == x
].iloc[0]['Hierarchical Description']
)
)df_desc contains descriptions retrieved from the OECD data structure definition.
Make the output easier for people to understand.
Structure and meaning travel together.
Years in columns
| Country | 2019 | 2020 |
|---|---|---|
| Example A | a | b |
Years in rows
| Country | Year | Value |
|---|---|---|
| Example A | 2019 | a |
| Example A | 2020 | b |
Codes with readable labels
- CAN
- Canada
- AUS
- Australia
- USA
- United States