1 Versions

1.0: 27/2/2019: Worked against excel and csv files on disk
1.1: July - September: Moved data storage to Zeus and revised figures to harmonize with AEA and PEFA
2.0: December 2019
- Transferred the collection of data from the store database to another project, as this data is used in projects other than the MFA project.
- Processing projection tables from TLSK categories to MFA categories is also something that is not necessary to do often. Moved this process into a “special project” that is not run here. Here we only look at a few views of the database to see if the TLSK categories are somewhat unrelated.
2.01: September 2020.
- Fixed summarize commands -> summarize(… , .groups = ‘drop’) to not get an error message out of R:dplyr v4.01.
- Also removed class(df) <- c(class(df), ‘Name of function’) as this is against arbitrary tidyr.
- Also had to move pointers from G: drive to V: drive as G is disappearing.
- The definition of BRIDGE_ items in AEA accounting has been corrected. This means that I cannot use the data from the AEA directly to find out how much fuel companies themselves buy abroad if data is missing from the customs data. Had to revise the accounts on this in the document MFA-Table_B.biti.4
2.1: December 2020 - January 2021.
- Changed the processing for ship fuel quite a bit
- Aligned the calculations on the MF4232 so that they are similar to what is done in the AEA process
2.3: February 2023 - May 2023.
- Reviewed the run and looked at the projections… probably need to redo when new customs file comes in
- SQL runs to update the database are now all run automatically
- Could revise and keep fuel_cd, or lind_text in the mfa.sub_gogn tables to be able to better track what data I’m working with
- Could revise and move all the mfa.sub_gogn tables into one structure and get rid of the different Table_B.proc functions that take care of the processing. I have found errors in the preparation which I then have to carefully transfer to the Table_B.proc functions. It is therefore more efficient to do the processing in just one place (in the text)
- Text could be shortened and revised
2.3.1: July 2023.
- Rebuilt the mapping table between tlsk and mfa classes (after destroying this table)
- Received comments regarding the difference in oil consumption from the previous return as it seemed to have moved to the oil level (72 kt utb) from the MF421.
- Got empty errors in the mapping between customs classes and mfa classes, which I traced back to the view sub_gogn_store. Ended up using the mapping table I produced in “Other - Projecting TLSK to MFA categories. This is a sanity check process that is good to run regularly when updating the store database. In this mapping process, the way customs categories are mapped to MFA changed categories. This caused the quantities in Table B to change and be projected quite differently. However, I put quite a bit of work into examining whether this difference was obviously false. The total quantities in the customs figures increased somewhat.
- I’m still struggling with how best to work out the numbers for the MF4231, MF4232 and MF4233 correctly. This is a very fragile process.
- Switching between EU and non-eu is no longer necessary or part of returns. So I decided to throw this part out in order to simplify the processing (this is still in the GITLAB system, but the maintenance of this part is annoying).
3.0.0: February 2024: Re-coding
- Split the processing into independently executable sub-processes (Same method as in EPEA and EGSS)
- Added data-quailty score into the processing.
- Check the calulated data against last submitted data

2 What is the project?

EW-MFA Tables B, C, D and E contain information on how much goods are imported and exported. Initial questionnaires had separation between trade from outside the EU or from within the EU. This part was phased out in 2022.

These data for this table comes first and foremost from the trade database. Eurostat provides annex information that assists in mapping customs numbers against weight. Part of the preparation for the processing here is therefore to review tha mapping from the toll numbers to MFA classes.

The following processes are available/run

Sub processes in compilation of Table B-E
sub_proc	skjal_nafn	skjal_fannst
table_be_biti_1	MFA-Table_B.biti.1.rmd	TRUE
table_be_biti_2	MFA-Table_B.biti.2.rmd	TRUE
table_be_biti_3	MFA-Table_B.biti.3.rmd	TRUE
table_be_biti_4	MFA-Table_B.biti.4.rmd	TRUE
table_be_biti_5	MFA-Table_B.biti.5.rmd	TRUE
table_be_store	MFA-Table_B.db_storage.rmd	TRUE

3 Processing and design

This project will therefore be solved by:

Fetching data from the trade database
Map Tollnumbers to MFA categories
Adjust the figures to follow the residence principle in the EW-MFA
Run a merge of the data and calculate annual figures

The basic collection for Table B and Table D are in a database view named mfa.sub_gogn_verslun. The query in this view is:


select 
    g.land_cd,
    g.ar,
    g.tlsk,
    g.eink, 
    g.magn_kg/1000 gildi,
    'TONN' eining,
    tx.mfa_cd,
    tx.ms_id,
    tx.titill
from vrsl.v_gogn_verslun_ar g
left join mfa.verslun_tlsk_mfa_varpanir_fra tx
    on tx.tlsk = g.tlsk and g.ar between year(tx.gildir_fra) AND year(tx.gildir_til)

where the view vrsl.v_gogn_verslun_ar is a summary view of the vrsl.gogn_verslun data and mfa.verslun_tlsk_mfa_varpanir_fra is a table that contains the mapping between tlsk and mfa_cd.

The trade database (verslun) is inconveniently structured to run timeseries queries, since each year trade is stored in separate tables. The trade data is also somewhat cumbersome to work with, since products are imported and exported under a toll-number (tlsk), which is close to (but not quite) harmonized with the common nomenclature (CN) system. The table vrsl.gogn_verslun is therefore processed snapshot from the trade database, where I have aggregated lines to months (from days) and unified the tables from 1990 to the current year into a single table. The snapshot is updated sporatically (every year), except for the newest year, which is updated each month. The annual update updates any changes and changes to previous years that the trade-db team inserts into the database. This can change the outcome in the EW-MFA compilation.

The mapping between tlsk numbers to mfa categories is also reviewed each year. This review sometimes exposes mistakes, which can change the outcome in the EW-MFA compilation. The data is therefore somewhat “volatile”. A comprehensive view of the TLSK-MFA mapping is also done when a new toll-number registry is issued.

4 Data collection from the trade database

This process - Collects data from the database - Maps tlsk to mfa_cd and ms_id. This step can be fragile, since the tlsk values can change and some ar re-used for different material between years. The most common change is, however, spelling and punctuation corrections, which should not change the mfa_cd mapping - 09-2021: New toll numbers introduced - Joins the data into a single table. - Does some rudimentary data-checks

4.1 Orphans check of TLSK-MFA mapping

Here I check if the quantities in the vrsl.gogn_verslun data are identical to the values in mfa.sub_gogn_verslun. If I have “orphan” toll numbers, viz toll numbers that have no mapping to mfa classes (e.g. when I get new tlsk numbers into the database), the quantity in the mfa.sub_gogn_verslun will be lower than in the vrsl.gogn_verslun.

## Quantities are identical, all toll numbers are mapped

If this error comes up I need to review each year where the error appears and find what toll-numbers are orphaned.

if the difference is > 0 I most likely have a active_from/active_to date overlap for the TLSK->MFA mapping, or that the same tollnumber has two mapping routes.
if the difference is < 0 I wil most likely need to add a line to the TLSK->MFA mapping table

4.1.1 Exploration of the sub_gogn_verslun table

The data from the mfa.sub_gogn_verslun view is stored in a parquet file on the local drive in order to speed up processing. The file is not automatically deleted if it is outdated and may need to be manually removed if there are changes to the mapping.

Number of lines: 278683
First year: 1990
Newest year: 2024

Tail of the data after summarization
ar	tlsk	eink	mfa_cd	ms_id	eining	gildi
2024	97040000	X	MF5	SM_FIN	TONN	0.048
2024	97051010	M	MF5	SM_FIN	TONN	0.005
2024	97052910	M	MF5	SM_FIN	TONN	0.008
2024	97052910	X	MF5	SM_FIN	TONN	0.142
2024	97053900	M	MF5	SM_FIN	TONN	0.019
2024	97069040	M	MF5	SM_FIN	TONN	0.037

Key dimensions in the data

mfa_cd = MFA category of the material
ms_id = State of manufacturinig of the product
eink = import (M) or export(X) identification

All values shouild be in tonnes. The data is a left-join between tlsk and mapping. Some mfa_cd may therefore be NULL. This is acceptable for some tollnumbers, mainly those that start on 999, since this is an unspecified or unknown product. Tollstjóri has been notified that using this number is not an acceptable practice, but some values remain

4.1.2 Total import/export by year

4.1.3 Import in key categories

Some quantities are imported and exported in large quantities. Errors in values here can affect the EW-MFA outcome. Here I choose MFA classes that exceed 100 kt import or export per year

The largest value is for MF227, which is aluminum. The imported material is actually aluminum oxide, where the exported material is mostly pure aluminum (Al) or aluminum alloys. The weight fraction of aluminum in pure aluminum oxide is 52.926%, which means that if Al2O3 were the only material in MF227 import and pure Al is the only material in MF227, the ratio of export to import should be close to this value.

pure exported aluminum has the toll-number 76011000, whereas aluminum oxide has the toll-number 28182000. This allows me to calculate slightly “cleaner” ratio.

Yield values > 100 can arise when the factories have stocked up manufactured goods (when the market is slow) and export larger quantity than would be expected from the imported raw materials. The yield has been decreasing in the recent years, as the manufacturers have started producing aluminum alloys and shaped aluminum products (rods, platec etc.) rathern than just pure Aluminum blocks.

Other material classes that are noticable in import are the fuel classes MF4231, MF4232 og MF4233. The other major export (MF141) is fish.

4.1.4 Quantity of toll numbers that have NULL mapping to MFA:

I am not too concerned if there are some unmapped toll numbers, as long as the quantity does not go much over 1000 tonnes. The key orphan tollnumbers are 988xxx and 999xx.

4.2 Data for the assembly

The structure of the data in the trade database is slightly different than the structure of corrections and additions that are produced in later stages of the assembly, although the values are eventually all aggregated down to the same form. Tables B and D also have sub-parts which consider the state of manufacturing. This changes the form needed for the assembly data.

##                 Length Class  Mode
## table_b_verslun 10     tbl_df list
## table_d_verslun 10     tbl_df list

## MFA-Table_B.biti.1.rmd completed

5 Residence principle adjustments for the trade data and fuel used for transport

Some corrections need to be made to trade figures, where import and export are not directly mapped to material consumption, or when more detailed information is needed for the material category. There are some minor adjustments that may be nescessary for the biomatter category (not everything that is labeled as grain is grain etc.). The largest adjustments are, however for fuels, or

MF 4.2.3 Fuels bunkered (Imports: by resident units abroad; exports: by non-resident units domestically)
- MF 4.2.3.1 Fuel for land transport
- MF 4.2.3.2 Fuel for water transport
- MF 4.2.3.3 Fuel for air transport

Here, each category needs to be examined individually in order to assess whether figures from the Energy Authority (Orkustofnun; OS) or from the Air Emission Account (AEA) accounting can be used to retrieve more accurate information. The method here is to:

Take in ALL fuels from the trade databse and put into a single category; MF421: Crude oil, condensate and natural gas liquids.
Find fuel values from the AEA and OS assembly and put these values into the proper MFA categories
Subtract this quantity from the MF421 quantity

The processing uses the following datasources and steps:

Fuel quantity that is recorded in the AEA reporting. This includes fuel purchased abroad and fuel purchased by non-nationals on the territory
Fuel quantity that is recorded in the UN-NIR reporting in order to reach back to 1990. These figures do not include fuel purchased abroad
Make up the subtraction from the import/export values
Verify that the subtracted quantity does not exceed the value originally assigned to MF421.

5.1 Road transport

Diesel and gasoline are used in road transport. Gasoline is not used for any purpose other than for cars, while diesel oil is imported for ships, for heating, for energy production and for road transport.

Here are three things I can do:

Put all Diesel and gasoline in MF421 and then subtract any amount that I allocate to MF4321 from this quantity
Put all Diesel and gasoline in MF42 category (not used) and then subtract allocated quantity from this volume. If there is any surplus left when the fuel for road transport and ships is exported (possibly that which goes to smelters and fishing), I will transfer the remainder into MF421. This means that the remainder has to be calculated separately at the end of the process, which is a big drawback.
Put all gasoline in MF4231 and Diesel in MF421 or MF42 and transfer known quantities between classes. In this method, care must be taken that only diesel oil is deducted, but gasoline sold to foreign tourists must also be transferred to category 4231-X

Results from method 1: I had a hard time in ballancing the correct amount between classes without getting a minus count in class MF421 for a few years, especially when looking at the amount exported. This happens since the fuel type in OS and AEA data is not directly identifiable from the TLSK number.

Method 2: Here I first try to put all the fuel that is obviously for ships and road transport into MF42, which is a category that should not be used otherwise. This keeps the MF421 “clean” until I put the ball from the MF42 back in there. This can be done in one final step after road transport and shipping are clear. Vinnslan er því efirfarandi:

Definition of fuel categories that are used in road transport and are in the MF42 category. Put on that candidate MFA class
Retrieve information from AEA and OS data regarding road transport in order to obtain a comparable amount of fuel (AEA+OS set)
Use the quantities in the MF42 fuel to calculate the percentage coming from the EU28 countries (f.eu28)

January 2021 Went back to recording all fuel to MF421 and set up the processing as a debit account from MF421 to MF4231

In the AEA accounting, I have made calculations on the fuel consumption of foreign tourists since 1995. These figures are based on the OS figures This means that I basically just need to

Add total consumption from 1990 to 1995
Determine the share of foreign tourists (small) for this time. The tourism boom in Iceland didn’t start until after 2010 (the Eyjafjallajökull eruption).

June 2023 Updated the mapping table for the customs files, then something went wrong and this run stopped working. I was also just starting to see some suspicious numbers in the MFA accounts for the MF.4.3 class that I got comments on.

July 2023 The method I was using ended up deducting too much from the MF421 category at the end, so the total for 2021 and the previous few years was negative. This was an error I missed in the return documents in Table_D:MF421!! ANNOYING

5.1.1 Retrieve data from class MF421 that could belong to road-transport

The following toll categories are taken as potentially used in road transport:

Toll numbers and mapping for road transport
tlsk	kandidat.mf	vörpun	fuel_cd
27100019	MF4231	annað bensín -> samgöngur	BENSIN
27101129	MF4231	annað bensín -> samgöngur	BENSIN
27101229	MF4231	annað bensín -> samgöngur	BENSIN
27102029	MF4231	annað bensín -> samgöngur	BENSIN
27101121	MF4231	Blýlaust bensín -> samgöngur	BENSIN
27101221	MF4231	Blýlaust bensín -> samgöngur	BENSIN
27100012	MF4231	Blýlaust bensín -> samgöngur	BENSIN
27102021	MF4231	Blýlaust bensín með <30% lífdíselolíu-> samgöngur	BENSIN
27100060	MF4231	Gasolíur -> samgöngur	DIESEL
27101930	MF4231	Gasolíur -> samgöngur	DIESEL
27102065	MF4231	Gasolíur með <30% af lífdíselolíu -> samgöngur	DIESEL

Fuel in the trade database that may be related to road transport is stored in category MF421. The table above shows mapping from of this fuel into the candidate MF category. These values will then be subtracted from the quantity in MF421.

Summary of
ar	mfa_cd	27100012	27100019	27100060	27101121	27101129	27101930	27101221	27101229	27102021	27102029	27102065
1990	MF421	46134.50	81189.647	335775.7	NA	NA	NA	NA	NA	NA	NA	NA
1991	MF421	78204.33	53827.037	247711.4	NA	NA	NA	NA	NA	NA	NA	NA
1992	MF421	88894.31	52212.869	310076.6	NA	NA	NA	NA	NA	NA	NA	NA
1993	MF421	70688.35	48486.839	283007.3	NA	NA	NA	NA	NA	NA	NA	NA
1994	MF421	121002.62	20189.491	294202.3	NA	NA	NA	NA	NA	NA	NA	NA
1995	MF421	114670.24	16212.763	296041.5	NA	NA	NA	NA	NA	NA	NA	NA
1996	MF421	140630.14	6825.718	355160.2	NA	NA	NA	NA	NA	NA	NA	NA
1997	MF421	132528.29	NA	336953.8	NA	NA	NA	NA	NA	NA	NA	NA
1998	MF421	141033.61	NA	371387.1	NA	NA	NA	NA	NA	NA	NA	NA
1999	MF421	147748.83	NA	402246.4	NA	NA	NA	NA	NA	NA	NA	NA
2000	MF421	152474.93	0.740	443687.1	NA	NA	NA	NA	NA	NA	NA	NA
2001	MF421	137914.92	0.623	375114.3	NA	NA	NA	NA	NA	NA	NA	NA
2002	MF421	NA	NA	NA	145010.5	3.404	402834.07	NA	NA	NA	NA	NA
2003	MF421	NA	NA	NA	139395.2	5.365	386246.32	NA	NA	NA	NA	NA
2004	MF421	NA	NA	NA	151987.5	101.662	479476.76	NA	NA	NA	NA	NA
2005	MF421	NA	NA	NA	163308.5	3.351	423218.28	NA	NA	NA	NA	NA
2006	MF421	NA	NA	NA	165290.4	39.901	450527.08	NA	NA	NA	NA	NA
2007	MF421	NA	NA	NA	156543.8	17.163	420917.29	NA	NA	NA	NA	NA
2008	MF421	NA	NA	NA	151944.5	4.953	390843.35	NA	NA	NA	NA	NA
2009	MF421	NA	NA	NA	156515.8	9.170	375021.19	NA	NA	NA	NA	NA
2010	MF421	NA	NA	NA	143990.5	1.781	321326.61	NA	NA	NA	NA	NA
2011	MF421	NA	NA	NA	144549.6	2.417	333872.09	NA	NA	NA	NA	NA
2012	MF421	NA	NA	NA	NA	NA	322111.21	137746.56	0.418	228.765	NA	NA
2013	MF421	NA	NA	NA	NA	NA	316212.00	132823.19	2.928	NA	2.352	NA
2014	MF421	NA	NA	NA	NA	NA	341791.59	133080.52	2.288	NA	0.034	NA
2015	MF421	NA	NA	NA	NA	NA	404858.88	139315.99	2.734	NA	0.016	NA
2016	MF421	NA	NA	NA	NA	NA	397962.28	136733.37	0.383	0.001	0.090	NA
2017	MF421	NA	NA	NA	NA	NA	396555.05	139090.39	3.605	NA	NA	NA
2018	MF421	NA	NA	NA	NA	NA	441458.24	134806.54	1.888	0.015	0.064	NA
2019	MF421	NA	NA	NA	NA	NA	467563.27	123742.90	1.078	NA	1.222	NA
2020	MF421	NA	NA	NA	NA	NA	448057.34	94514.14	7083.174	NA	0.081	NA
2021	MF421	NA	NA	NA	NA	NA	507799.21	83544.68	20736.121	NA	0.148	53.576
2022	MF421	NA	NA	NA	NA	NA	623066.96	101116.72	14158.969	NA	0.006	NA
2023	MF421	NA	NA	NA	NA	NA	617449.37	109751.29	11953.068	NA	NA	NA
2024	MF421	NA	NA	NA	NA	NA	93213.85	15160.01	645.763	NA	NA	NA

The function Table_BE.kandiat_eldsneyti_til_samgangna_ur_mf421.fGet(df.verslun) is a wrapper that does the following

Collects the fuel from the data and selects numbers for toll numbers marked road transport
Puts fuel_cd on the data along with candidate.mfa class.
Calculates total quantity by fuel_cd (group-by) and calculates f.eu28 value for each category.

This returns the table with the following columns

gildi: Value in tonnes
kandidat.mf: Candidate mf for the fuel

In fact, all exports of gasoline could be ignored here, as this is an insignificant amount. There is some export of diesel oil, but this export is primarily to ships and only a fraction to foreign tourists.

5.1.2 Data from AEA accounting (and OS numbers)

Here we are missing fuel consumption recorded in the AEA accounts and in the data from the Energy Agency concerning fuel from the pump. This processing has two functions.

Table_BE.eldsneyti_af_daelu.fGet(this.connection): retrieves fuel figures from AEA accounting and from OS figures accounting. The function returns data down to year, fuel_cd, characteristics (M or X).
Table_BE.eldsneyti_af_daelu_by_group.fGet(db): takes the data from the fGet function (just that function) and cleans it up.

The outcome here is:

ar: year
eink: identity M=import, X=export
fuel_cd: identity of the fuel,
gildi: tonns of fuels

These numbers from the AEA are the numbers I’m going to put into the MF4231 next. Then I subtract the same amount from the MF421 amount

5.1.3 Quality control and comparison of the numbers

The most important thing here is that the amount of fuel recorded in the AEA/OS numbers is not higher than the amount recorded in the import numbers. Normal behavior is:

Gasoline volume is almost the same in AEA/OS as imported volume.
Considerably more diesel oil is imported than is used in road transport.
Gasoline exports “sales to foreign tourists” should always be higher than the recorded exports in the trade figures. Sales to foreign tourists are not counted in the customs data, but this is calculated separately in the AEA figures
Export of diesel in road transport is negligible compared to export in the trade figures

5.1.3.1 Import of gasoline and use

5000 tons is about 4% of the imported amount. Fluctuations within these limits are therefore acceptable. It just annoys me that the difference in gas can be as much as 16% between what I calculate in AEA and here for the most recent years, but in these years there is always a risk that I overestimate the amount.

5.1.3.2 Export of petrol

Here again it is obvious that the exported quantity is much more than the imported quantity. The exported quantity here is sales to foreign tourists, while gasoline is not exported in the trade data except in exceptional cases.

Here it would be tempting to coldly subtract the gasoline imports from the MF42 category since the AEA/OS data gives the correct figures.

5.1.3.3 Import of disel oil

The use of Diesel is clearly negligible compared to the imported amount

5.1.3.4 Export of diesel oil

Exports here are again primarily to foreign tourists. However, according to trade data, exports are very high to fishing vessels and to some extent to cruise ships and cruise ships.

5.1.4 Final installation of data for road transport

In the previous process, there was a lot of fuss around the division of fuel between EU and NON.EU countries, where I used a number of tourists to divide the fuel. Since this processing is not necessary, the processing is considerably simplified.

So I basically just need to take the amount I’ve settled on from the AEA data and subtract the same amount from the store numbers in the MF421 category. Since the MFA processing does not look at duty categories, I am pretty sure that this amount in the deduction never zeros out the amount in the MF421.

2023/07/14: I have to be careful NOT to blindly withdraw the export of gasoline and Diesel oil from MF421!! I can deduct Gasoline and Diesel from the import figures (M), but I should NOT deduct the Gasoline and Diesel figures from the exports in the customs data as these exports are not customs registered.

5.1.4.1 QUALITY CHECK: Do I have a module error?

I’m going to add an amount to the MF4231 and subtract the same amount from the MF421. If I have the wrong module on the MF4231 data, everything goes to shit. Here it is good to check again whether the amount I have in the supplement, e.g. for petrol, is still similar to what is in the retail figures.

Here, I prefer to check whether the amount I am going to enter is somewhat 1000 times more or less than the amount that is in the store numbers (i.e. more than 1000% difference)

## This error was not found

5.1.4.2 QUALITY CHECK: Do I have a deduction error?

The process here is finding values to subtract from MF421. The sales to tourists is, however, not quantity that is

Addition and subtraction terms
mfa_cd	eink	neg	gildi
MF421	M	TRUE	-7800.1160
MF4231	M	FALSE	7800.1160
MF4231	X	FALSE	325.3631

The values in the eink=‘M’ should cancel out, but the eink=‘X’ should not.

5.1.5 Final run and save to database:

In update 05-2023, I decided to automatically insert the data into the bases regardless of what is already there, since it was otherwise such a long process. The processing here saves all data into its own sub_gogn table. In other parts, I have put all the numbers I generate into the same data table so as not to end up with a myriad of tables in the database. However, this will be an update that I will do later in Table B

Lines: 188
First year: 1990
Newest year: 2022

The following sql processin is then done

truncate table mfa.sub_gogn_vegasamgongur

insert into mfa.sub_gogn_vegasamgongur(
  eu28, ar, mfa_cd, ms_id, 
  eink, fuel_cd, gildi, 
  vinnsla, reiknad_dags)
select 
  eu28, ar, mfa_cd, ms_id, 
  eink, fuel_cd, gildi, 
  vinnsla, reiknad_dags
from tmp_sub_gogn_vegasamgongur

drop table tmp_sub_gogn_vegasamgongur

5.2 Data for the assembly

The same data as is stored in the database is also put into the data assembly for later checks and validations

##                       Length Class  Mode
## table_b_verslun       10     tbl_df list
## table_d_verslun       10     tbl_df list
## table_b_vegasamgongur 10     tbl_df list
## table_d_vegasamgongur 10     tbl_df list

## End of MFA-Table_B.biti.2.rmd

5.3 Fuel for air transport

Aircraft fuel is recorded in the commercial data much more precisely than for other fuels. There is therefore no need to go to the AEA accounting, except in order to find the fuel purchased for the, as the AEA accounting has a longer time series for fuel bought abroad by Icelandic aircraft than is in the commercial base

5.3.1 Data collection from the trade database

In older processing, all fuel was first classified as MF421. Then the “potential” mapping of the fuel was retrieved in the function fGet.fuel_map() and the share of fuel was subtracted from the quantity in MF421. This method is still used for marine fuels and for road transport fuels that are not obviously used for road transport (e.g. Gas oils). Fuel for aviation is, however, quite easily identifiable in the description of the customs category.

Toll numbers marked with MF4322
tlsk	mfa_cd	ms_id	titill
27101110	MF4233	SM_FIN	Flugvélabensín
27100011	MF4233	SM_FIN	Flugvélabensín
27100020	MF4233	SM_FIN	Bensínkennt þotueldsneyti
27100042	MF4233	SM_FIN	Þotueldsneyti
27101912	MF4233	SM_FIN	Þotueldsneyti (jet fuel)
27101918	MF4233	SM_FIN	Eldsneyti keypt erlendis af íslenskum flugvélum
27102010	MF4233	SM_FIN	Flugvélabensín með <30% af lífdíselolíu
27102030	MF4233	SM_FIN	Bensínkennt þotueldsneyti með <30% af lífdíselolíu
27102052	MF4233	SM_FIN	Þotueldsneyti með <30% af lífdíselolíu
27101210	MF4233	SM_FIN	Flugvélabensín
27101230	MF4233	SM_FIN	Bensínkennt þotueldsneyti

These customs categories are already marked in the trade data, so I don’t need to process them further, unless I only need data (older time series)

A review of the quantities of fuel registered in the trade database reveals several interesting aspects

There is a big jump in imported fuel in 2013. This is due to fuel purchases for Icelandic aircraft abroad, which are missing from the figures for previous years.
The customs data does not have any fuel sold to foreign airlines for the year 2005. This is a built-in flaw in the data that we need to fix by retrieving data from the AEA and OS data.

5.3.2 Data collection from data from the Energy Agency and the AEA accounting

The data from the Energy Agency has an older classification for fuel bought abroad and fuel sold to foreign companies. This data is retrieved by the function Table_BE.os_flug.x_eldri().

Nokkrar línur frá orkustofnun fyrir eldsneyti keypt erlendis og selt til erlendra flugfélaga
ar	eink	gildi	vinnsla
2013	M	188059.2	Table_BE.os_flug.x_eldri
2014	M	232123.8	Table_BE.os_flug.x_eldri
2015	M	239036.6	Table_BE.os_flug.x_eldri
2016	M	332951.3	Table_BE.os_flug.x_eldri
2017	M	358592.5	Table_BE.os_flug.x_eldri
2018	M	399038.1	Table_BE.os_flug.x_eldri

In the AEA accounting, the turnover figures of airlines were also examined in order to prepare data for the purchase of errands. This data can be retrieved by running the following sql script:


select 
  f.ar, 
  'M' eink,
  sum(f.eydsla)/1000 gildi_tonn
from aea.gogn_mapped_eldsneyti_flug f
where f.nace_cd in ('BRIDGE_AIR_TRANSPORT', 'BRIDGE_NR_AIR_TRANSPORT')
group by f.ar, f.nace_cd

These numbers are retrieved in the function Table_BE.verslun_gogn.aea_abroad(this.connection)

Nokkrar línur frá AEA fyrir eldsneyti keypt erlendis
ar	eink	gildi	vinnsla
2023	M	71283.743	Table_BE.verslun_gogn.aea_abroad
2023	X	7165.432	Table_BE.verslun_gogn.aea_abroad
2022	M	419937.082	Table_BE.verslun_gogn.aea_abroad
2022	X	29202.423	Table_BE.verslun_gogn.aea_abroad
2021	M	146888.836	Table_BE.verslun_gogn.aea_abroad
2021	X	28433.154	Table_BE.verslun_gogn.aea_abroad

The AEA and OS numbers can be combined to select a full set. This processing is in the function Table_BE.eldsneyti_flug.os_aea(aea, os)

These figures need to be added to the figures already obtained from the trade data. This means that older time series will be slightly confusing, but this can be improved by inserting correction entries in the base to subtract or add fuel.

This function also takes care not to return data that has already entered the database.

Here you need to look specifically at

Export (X) does not have an overlap between store and addition
That the volume numbers on (M) for 2013 with addition are roughly comparable to the jump in volume around 2013

This data has a certain irregularity in it, which is normal, but must be corrected at the end of the processing. The final step is to select data from this collection that has not entered the database.

Additions and subtractions
eink	mfa_cd	neg	gildi
M	MF4233	FALSE	4332311
X	MF4233	FALSE	437871

5.3.3 Final storage of the data

In update 05-2023, I decided to automatically insert numbers into the base. This just speeds up the processing of the entire run and I no longer have to go in and run the base sql scripts manually.

Health check of the data that was entered. Only data that is not already in the store numbers is being entered here. This means that the data does not cover the current year, this is normal.

Yfirlit yfir gögn sem voru sett inn
ar	dalkar	radir	M	X
1990	10	1	155.7545	37.654
1991	10	1	141.3288	NA
1992	10	1	152.3553	31.061
1993	10	1	127.4286	30.058
1994	10	1	146.1094	32.066
1995	10	1	104.9237	37.968
1996	10	1	151.1956	40.081
1997	10	1	169.1282	41.134
1998	10	1	178.7968	41.543
1999	10	1	216.3527	43.632
2000	10	1	245.8884	44.237
2001	10	1	201.2684	30.023
2002	10	1	180.4010	28.414
2003	10	1	216.1555	NA
2004	10	1	222.9716	NA
2005	10	1	242.4705	NA
2006	10	1	249.0053	NA
2007	10	1	234.3578	NA
2008	10	1	217.4630	NA
2009	10	1	158.9764	NA
2010	10	1	192.4320	NA
2011	10	1	197.3506	NA
2012	10	1	230.1966	NA

Data is expected to be NULL for X after 2003. After this the following sql processes are executed

truncate table mfa.sub_gogn_flugsamgongur

insert into mfa.sub_gogn_flugsamgongur(
  eu28, ar, mfa_cd, ms_id, 
  eink, gildi, eining, vinnsla, 
  reiknad_dags)
select 
  eu28, ar, mfa_cd, ms_id, 
  eink, gildi, eining, vinnsla, 
  reiknad_dags
from tmp_sub_gogn_flugsamgongur

drop table tmp_sub_gogn_flugsamgongur

5.3.4 Data for the assembly

The same data as is stored in the database is also put into the data assembly for later checks and validations

##                       Length Class  Mode
## table_b_verslun       10     tbl_df list
## table_d_verslun       10     tbl_df list
## table_b_vegasamgongur 10     tbl_df list
## table_d_vegasamgongur 10     tbl_df list
## table_b_flug          10     tbl_df list
## table_d_flug          10     tbl_df list

## MFA-Table_B.biti.3.rmd completed

5.4 Fuel for ships and marine transport

Here we find ourselves in a bit of a gap between the fuel sold to ships and the fuel imported to Iceland. Some of the imported black oil is used for domestic heating, and a large part of the imported diesel oil is used for road transport. There is also a big discrepancy in what the Norwegian Energy Agency says about the use of materials by “Other ships”. After 2019, almost no black oil has been used by Icelandic ships, and little or nothing has been imported. The 2022 tariff redefinition could even eliminate “fuel oil” from the list.

After a lot of trouble I decided to use data from aea processing to find these numbers and not have a lot of design in the EW-MFA processing in this

5.4.1 Fuel mapping for ships and marine transports

The trading data is set up so that all oil content (even fuel bought abroad) is put into MF421. Customs categories that are used in maritime transport are marked separately in this process.

Product categories in trade data in category MF421 marked as marine fuel, including fuel purchased abroad (new data)
fuel_cd	tlsk	kandidat.mf	vörpun
27101948	27101948	MF4232	Eldsneyti keypt erlendis af íslenskum skipum -> sjósamgöngur
RFO	27101940	MF4232	Brennsluolíur -> Sjósamgöngur
RFO	27100070	MF4232	Brennsluolíur -> Sjósamgöngur
RFO	27102070	MF4232	Brennsluolíur með <30% af lífdíselolíu-> Sjósamgöngur
DIESEL	27100060	MF4232	Gasolíur -> sjósamgöngur
DIESEL	27101930	MF4232	Gasolíur -> sjósamgöngur
DIESEL	27102065	MF4232	Gasolíur -> sjósamgöngur

This mapping is used to pick up fuels from the trade data.

It is worth noting here that exports only go back to 1998 or so. Exports are also insignificant until 2003. However, I know that foreign vessels have been taking some oil back until 1995. This means that I cannot guarantee that the use in MF4232 is not greater than the exports recorded in MF421. I can be pretty sure that imports into MF421 are always greater than usage into MF4232, as long as I leave out tariff class 27101948 and don’t deduct foreign bought fuel coming into MF4232 from the amount in MF421

5.4.2 The processing

Here I would like to use a similar idea as I did in road transport, i.e. Use AEA, NIR and OS to find the amount of fuel based on emission/use. This is less accurate, but easier to process than what I’ve done using oil numbers directly. With this I can use the design work I do in AEA and coordinate the result. The processing is therefore:

Retrieve data from aea.gogn_mapped_eldsneyti_skip_lind. This table has the uses I have identified on ships by fuel. This model is an important design point in AEA and other environmental calculations that would be good to be able to ensure is correct.
Retrieve data from aea.v_skilaskjol_gogn_full. This view has data from the returns table in AEA. In some cases, I have a rougher approach for the latest year
Retrieve data from umhverfisstofnun.v_unfccc_sector_data. This view has data going back to 1990

The process is then:

Use the oil numbers directly that come out aea.gogn_mapped_eldsneyti_skip_lind and subtract this amount from MF421.
Calculate a factor for tons of oil per CO2 emission in AEA to get an estimate of tons of oil for the most recent year
Calculate a factor for tons of CO2 in AEA per ton of CO2 in NIR to get an estimate of tons of oil for the years 1990-1994

I then compare these numbers with imports in the trade data (which is automatically recorded in MF421). Then I need to prepare a subtractor for the chart to move the oil from MF421 to MF4232. This is quite obvious for tariff class 27101948, but is not obvious for other classes. Here I have to be careful not to end up in the red.

5.4.2.1 Step 1: Retrieve numbers from aea.gogn_mapped_eldsneyti_skip_lind

Class MF4232 is only for water transport, so I can get rid of “BRIDGE_FISHING” and “A03” from the data. I can also get rid of “BIO” fuel. NR_WATER_TRANSPORT is fuel exported. So I only have fuel that is for H50 and BRIDGE_NR_WATER_TRANSPORT

Gögn fyrir 2020 úr aea.gogn_mapped_eldsneyti_skip_lind
ar	fuel_cd	nace_cd	is_bio	magn	eining	lind
2020	DIESEL	BRIDGE_NR_WATER_TRANSPORT	FALSE	1105.0000	tonn	os_fuel.skip(Dísilolía, erlend fiskiskip)
2020	DIESEL	H50	FALSE	2962.0000	tonn	os_fuel.skip(Dísilolía, önnur skip, innlend notkun)
2020	MGO	BRIDGE_NR_WATER_TRANSPORT	FALSE	7512.6870	tonn	os_fuel.skip(Flotadísilolía, erlend fiskiskip)
2020	MGO	BRIDGE_NR_WATER_TRANSPORT	FALSE	15437.0000	tonn	os_fuel.skip(Skipagasolía, erlend fiskiskip)
2020	MGO	BRIDGE_NR_WATER_TRANSPORT	FALSE	22519.7350	tonn	tollagogn.eldsneyti_utflutt.brige_nr
2020	MGO	BRIDGE_WATER_TRANSPORT	FALSE	17320.3360	tonn	os_fuel.skip.eftir_ad_leidretta(Flotadísilolía, önnur skip, millilandanotkun)
2020	MGO	BRIDGE_WATER_TRANSPORT	FALSE	6955.0000	tonn	os_fuel.skip.eftir_ad_leidretta(Skipagasolía, önnur skip, millilandanotkun)
2020	MGO	BRIDGE_WATER_TRANSPORT	FALSE	63336.9735	tonn	tollagogn.eldsneyti_keypt_erlendis.bridge_mgo(DK)
2020	MGO	BRIDGE_WATER_TRANSPORT	FALSE	906.0705	tonn	tollagogn.eldsneyti_keypt_erlendis.bridge_mgo(NL)
2020	MGO	BRIDGE_WATER_TRANSPORT	FALSE	-22519.7350	tonn	tollagogn.eldsneyti_utflutt.bridge_leidretting
2020	MGO	H50	FALSE	113.6990	tonn	os_fuel.skip(Flotadísilolía, önnur skip, innlend notkun)
2020	MGO	H50	FALSE	4758.0000	tonn	os_fuel.skip(Skipagasolía, önnur skip, innlend notkun)
2020	MGO	H50	FALSE	17320.3360	tonn	os_fuel.skip.eftir_ad_leidretta(Flotadísilolía, önnur skip, millilandanotkun)
2020	MGO	H50	FALSE	6955.0000	tonn	os_fuel.skip.eftir_ad_leidretta(Skipagasolía, önnur skip, millilandanotkun)
2020	MGO	H50	FALSE	63336.9735	tonn	tollagogn.eldsneyti_keypt_erlendis.nace_mgo(DK)
2020	MGO	H50	FALSE	906.0705	tonn	tollagogn.eldsneyti_keypt_erlendis.nace_mgo(NL)
2020	MGO	H50	FALSE	-22519.7350	tonn	tollagogn.eldsneyti_utflutt.h50_leidretting
2020	RFO	BRIDGE_WATER_TRANSPORT	FALSE	54.2970	tonn	os_fuel.skip.eftir_ad_leidretta(Likan label= Áælað m.v. innflutning)
2020	RFO	BRIDGE_WATER_TRANSPORT	FALSE	63336.9735	tonn	tollagogn.eldsneyti_keypt_erlendis.bridge_mgo(DK)
2020	RFO	BRIDGE_WATER_TRANSPORT	FALSE	906.0705	tonn	tollagogn.eldsneyti_keypt_erlendis.bridge_mgo(NL)
2020	RFO	H50	FALSE	54.2970	tonn	os_fuel.skip.eftir_ad_leidretta(Likan label= Áælað m.v. innflutning)
2020	RFO	H50	FALSE	63336.9735	tonn	tollagogn.eldsneyti_keypt_erlendis.nace_rfo(DK)
2020	RFO	H50	FALSE	906.0705	tonn	tollagogn.eldsneyti_keypt_erlendis.nace_rfo(NL)

Inspection shows that BRIDGE_WATER_TRANSPORT are mostly the same numbers as in the H50 category (e.g. for RFO). I can therefore get rid of this NACE category. I can also very easily set up import and export labels on the data. However, I do not have a good description of which customs categories are behind, but I can assume that all this fuel is already marked with MF category MF421 and with candidate category MF4323 (according to collection above)

5.4.2.2 Step 2: Retrieve figures from AEA return data

Here I also select NACE categories H50 and BRIDGE_NR_WATER_TRANSPORT, as these should be the same or similar numbers.

AEA losunartölur fyrir CO2 árið 2020
ar	efni_cd	nace_cd	titill_is	gildi
2020	CO2	H50	Flutningar á sjó og vatnaleiðum	444.8979
2020	CO2	BRIDGE_NR_WATER_TRANSPORT	Þar fyrir utan: Sjóflutningur erlendra aðila hérlendis	149.5390

The ratio between emissions and material consumption should be fairly constant, since almost all of the fuel for these companies is in shipping. Here I need to calculate the total amount of oil regardless of type. I know that the normal value for CO2 emissions per tonne of oil is around 3.2-3.4 tonnes of CO2 per tonne of oil.

Emission coefficients kt-CO2 per tonn ship fuels
ar	BRIDGE_NR_WATER_TRANSPORT	H50
2016	0.0031762	0.0032714
2017	0.0031674	0.0032363
2018	0.0031742	0.0032328
2019	0.0031772	0.0032462
2020	0.0032108	0.0032209
2021	0.0032108	0.0032269
2022	0.0032108	0.0032345

Here, it is natural that the H50 has a higher emission factor, as part of the emission comes from the burning of fuel on land. These figures allow me to get a total of tons of oil for the year beyond what is in the quantity figures.

5.4.2.3 Step 3: Retrieve numbers from NIR return data

NACE categories H50 and BRIDGE_NR_WATER_TRANSPORT, where these should be the same or similar numbers.

NIR losunartölur fyrir CO2 árið 2020
sector	sector_title	efni_cd	ar	losun	eining	ID_unfccc_mengunarefni	ID_unfccc_sheet_key	ar_skrad
1.A.3.d	1.A.3.d - Domestic Navigation	CO2	2020	24.90962	kt	4	321	2023
1.Memo.1.b	1.Memo.1.b - Navigation	CO2	2020	77.19078	kt	4	347	2023

These numbers do not differentiate between imports and exports, so it is never as accurate as the AEA calculation. However, I can prepare a model of what the emissions in these items are compared to emissions in NACE categories

2023-07-11: Here’s a pretty obvious flaw I’ve been struggling with in the data. The oil figures for the H50 from 1995-2003 are beyond insane. This needs to be fixed in the next submission

2023-07-11: The same gap exists between NIR and recorded oil consumption in the AEA system. This usage is therefore wrong, especially for H50. The usage is not identical from map for BRIDGE_NR_WATER_TRANSPORT

Here I have two options:

Submit wrong numbers and correct next year
Calculate oil consumption based on NIR data for the period 1990-2003 for the current return

2023-07-11: Decided to use method 2 for returns in 2023. Method 1 will be the default in the next returns

5.4.2.4 Step 4: Arrange the data in a data frame. Here, the attempt is to make sure that I am not going over the subtraction terms at every step

The steps are as follows:

Take all the fuel in aea.gogn_mapped_eldsneyti_skip_lind and signal in MF4232

Comments here.

It’s annoying how “checkered” the data is for MF4232 in import (M)
It’s interesting how close I am to the migration in the MF421 being exactly the same numbers as the MF4232 numbers. Here I can basically “wipe out” the MF421 numbers from the store data (put this in a deduction)

Set export figures in MF421 to deduction category. This means that the fuel I have marked as likely for shipping in the trade figures will be deducted in MF421, but the amount I find in aea is instead put in MF4232. As a backup (and since this is math) I don’t deduct 100% of the fuel, but only about 95%
Deduct fuel in the customs category “fuel purchased abroad by Icelandic ships” from MF421. This fuel is included in the MF4232 numbers from the AEA, but only in customs data from 2013

The result after these two steps:

Calculate the amount of oil based on NIR data and enter the numbers for 1990-1994 Here I can add quantity in MF4232 for years 1990-1994.
For the 2023 return, I seem to have too little oil recorded in the aea.gogn_mapped_eldsneyti_skip_lind table for the years 1995-2002. I can prepare a supplement according to a schedule from the NIR and add in for these years. This correction is only made for selected years (2023) but is otherwise not included

Mismunur á magni sem reiknast úr NIR og því sem ég er með í aea.gogn_mapped_eldsneyti_skip_lind
ar	eink	nir_gildi	mapped_tonn	mismunur
1995	M	54348.58	12831	41517.580
1995	X	42610.75	44406	-1795.250
1996	M	84340.19	14062	70278.186
1996	X	66124.98	39000	27124.976
1997	M	86957.98	8532	78425.975
1997	X	68177.39	46920	21257.394
1998	M	96614.41	7871	88743.414
1998	X	75748.30	54503	21245.302
1999	M	76421.66	6419	70002.659
1999	X	59916.64	50927	8989.638
2000	M	89112.45	5941	83171.448
2000	X	69866.56	67069	2797.559
2001	M	106626.34	7126	99500.342
2001	X	83597.92	46337	37260.923
2002	M	138882.95	6403	132479.954
2002	X	108887.98	64806	44081.975

5.4.3 Result of this mess?

Skref	Fjöldi raða	Samtals magn fyrir öll ár
aea_oliutolur	467	3965306.6
utflutningur.verslun421.fradrattur	53	-1483923.9
eldsneyti_keypt_erlendis_ut	12	-897253.1
nir_vidbot1990_1994	10	634513.1

Here I was hoping that the net sum of everything would be zero, but the data has a mis-long timeline. This data is otherwise plus numbers that should all go to the MF4232 class and minus numbers that should all go to the MF421 class.

5.4.3.1 Am I getting negative numbers?

I think this result is quite acceptable. I can always try to revise the projection in aea to fuel, but this is not an out of the box result (and much easier than the previous processing).

5.4.4 Final storage of data

In update 05-2023, I decided to automatically insert all data generated in this process into the database instead of doing this manually. This only simplifies the overall run. The data entered here comes from the following list.

Following sql queries are then done

truncate table mfa.sub_gogn_skipasamgongur

insert into mfa.sub_gogn_skipasamgongur(
  eu28, ar, mfa_cd, ms_id, 
  eink, gildi, eining, vinnsla, 
  reiknad_dags)
select 
  eu28, ar, mfa_cd, ms_id, 
  eink, gildi, eining, vinnsla, 
  reiknad_dags
from tmp_sub_gogn_skipasamgongur

drop table tmp_sub_gogn_skipasamgongur

5.4.5 Data for the assembly

The same data as is stored in the database is also put into the data assembly for later checks and validations

##                       Length Class  Mode
## table_b_verslun       10     tbl_df list
## table_d_verslun       10     tbl_df list
## table_b_vegasamgongur 10     tbl_df list
## table_d_vegasamgongur 10     tbl_df list
## table_b_flug          10     tbl_df list
## table_d_flug          10     tbl_df list
## table_b_skip          10     tbl_df list
## table_d_skip          10     tbl_df list

## MFA-Table_B.biti.4.rmd finished

5.5 MF421 class fixes

In the older processing of the trade figures, I put some fuels (for road transport and shipping) into category MF42. In the correction figures, I calculated a subtraction term based on the OS and AEA data. In some cases, the total deduction is more than the amount entered from the store data. In other cases, some fuel remains. Here I assume that the difference is going to heating, melting or on a fishing vessel. This fuel is about to go into the MF421.

2023-07-12 I think this work process unnecessary and it is therefore omitted

6 Storing data on database

Althought most of the data is based on a view that is automatically updated, I still find it useful to still store the processed data, which can be some 290 thousand lines in full breakdown, in a table in the database. This the data is automatically purged and updated when the script is run, but this method matches the current design of the calculation. The data is stored in the table mfa.gogn_tafla_be (this used to store the eu28/non-eu data as well). The stored data does not have a set of unique columns, although the columns ‘table_cd’, ‘mfa_cd’, ‘ms_id’, ‘ar’, ‘vara_cd’ could become unique in later designs.

After this the following sql processes are executed

truncate table mfa.gogn_tafla_be

insert into mfa.gogn_tafla_be(
  table_cd, mfa_cd, ms_id, ar, gildi, eining_inn, vara_cd, lind_texti, vinnsla, reiknad_dags)
select 
 table_cd, mfa_cd, ms_id, ar, gildi, eining_inn, vara_cd, lind_texti, vinnsla, reiknad_dags
from tmp_gogn_tafla_be

drop table tmp_gogn_tafla_be

7 Comparison with previous submissions

The stored data is then checked for consistency and basic quality.

7.1 Table B

7.1.1 Import values

The top part of the table contains the import of material in kilotonnes. This total is summed up at the bottom of the table. There are a lot of values in this table, which means things can easily change without me noticing it.

Here I calculate the difference between the newest submission of the MFA and the data as it is here in the compilation. Some values may need to be tweaked by hand, but most of the table is prepared in this processing. The matrix is then collored by:

Is there a new value in the compilation? (New value)
Is there a value in last submission, but no value in the new compilation (New value missing)
Is the old value greater than the new value (OLD>NEW) - the difference is shown
Is the old value smaller than the new value (OLD<NEW) - the difference is shown
Is the old value repeated (REP)

Major changes in the outcome result from

Review of the toll-number/MFA mapping table
Rebuilding of the extraction from the trade database to the environmental group database (the trade database is poorly structured, so an snapshot of the tables is extracted to the environmetal group database)

7.1.1.1 MF1_ outcome

## Warning: Removed 309 rows containing missing values (`geom_text()`).

7.1.1.2 MF2_ outcome

## Warning: Removed 278 rows containing missing values (`geom_text()`).

7.1.1.3 MF3_ outcome

## Warning: Removed 276 rows containing missing values (`geom_text()`).

7.1.1.4 MF4_ outcome

## Warning: Removed 228 rows containing missing values (`geom_text()`).

7.1.1.5 MF5, MF6 outcome

## Warning: Removed 4 rows containing missing values (`geom_text()`).

7.1.1.6 Total for all categories

## Warning: Removed 2 rows containing missing values (`geom_text()`).

7.1.2 State of manufacturing

The state of manufacturing is a fragile part of the account. I have rarely managed to get the sum of all states of manufacturing to match upp with the total of all products, probably since I am doing some adjusting with fuels that may have different SM designation in imports than what I assign in the processing.

7.1.2.1 Per state

## Warning: Removed 6 rows containing missing values (`geom_text()`).

7.1.2.2 Sum over states from previous years

## Warning: Removed 2 rows containing missing values (`geom_text()`).

7.1.2.3 Sum over states and sum over products for the current assembly

One problem has commonly been that the sum of all products by state of manufacturing has been different than the sum over the import quantity. This shouldn’t happen, but can happen if there is a mismatch in the SM designation for the import products and from the products that I use in residental principle adjustments. Example of this is if I record gasoline as SM_FIN into MF421, but then allocate crude oil (residual oils) for the shipping industry (MF4233). This means i will be adding X amoung of SM_SFIN to the shipping industry and then subtracting the same amount from the gasoline value (SM_FIN). This crude-oil was, however, never in the MF421 category, which means I need to retroactively fix the value. This can cause a small mismatch.

ar	TOTAL.TABLE_B	TOTAL.TABLE_B.sm
1990	1943.1267	1943.1267
1991	1798.1044	1798.1044
1992	1890.8251	1890.8251
1993	1842.2589	1842.2589
1994	1964.1670	1964.1670
1995	1927.4939	1927.4939
1996	2120.9677	2120.9677
1997	2103.4291	2103.4291
1998	2706.0535	2706.0535
1999	2768.3411	2768.3411
2000	3058.3448	3058.3448
2001	2940.6340	2940.6340
2002	2980.7924	2980.7924
2003	3137.4955	3137.4955
2004	3498.3458	3498.3458
2005	3735.3203	3735.3203
2006	4187.6693	4187.6693
2007	4313.8796	4313.8796
2008	4892.0926	4892.0926
2009	4212.6692	4212.6692
2010	4221.5150	4221.5150
2011	4253.2343	4253.2343
2012	4384.1653	4384.1653
2013	4457.0634	4457.0634
2014	4527.1802	4527.1802
2015	5061.7416	5061.7416
2016	5331.7062	5331.7062
2017	5814.6164	5814.6164
2018	6069.7840	6069.7840
2019	5376.3967	5376.3967
2020	4655.0319	4655.0319
2021	5115.1077	5115.1077
2022	5727.8255	5727.8255
2023	6485.8153	6485.8153
2024	840.3645	840.3645

7.2 Table D

7.2.1 Export values

The structure of this table has the same structure as Table A

Is there a new value in the compilation? (New value)
Is there a value in last submission, but no value in the new compilation (New value missing)
Is the old value greater than the new value (OLD>NEW) - the difference is shown
Is the old value smaller than the new value (OLD<NEW) - the difference is shown
Is the old value repeated (REP)

Major changes in the outcome result from

Review of the toll-number/MFA mapping table
Rebuilding of the extraction from the trade database to the environmental group database (the trade database is poorly structured, so an snapshot of the tables is extracted to the environmetal group database)

7.2.1.1 MF1_ outcome

## Warning: Removed 511 rows containing missing values (`geom_text()`).

7.2.1.2 MF2_ outcome

## Warning: Removed 145 rows containing missing values (`geom_text()`).

7.2.1.3 MF3_ outcome

## Warning: Removed 220 rows containing missing values (`geom_text()`).

7.2.1.4 MF4_ outcome

## Warning: Removed 55 rows containing missing values (`geom_text()`).

7.2.1.5 MF5, MF6 outcome

## Warning: Removed 5 rows containing missing values (`geom_text()`).

7.2.1.6 Total for all categories

## Warning: Removed 2 rows containing missing values (`geom_text()`).

7.2.2 State of manufacturing

7.2.2.1 Per state

## Warning: Removed 6 rows containing missing values (`geom_text()`).

7.2.2.2 Sum over states from previous years

## Warning: Removed 2 rows containing missing values (`geom_text()`).

7.2.2.3 Sum over states and sum over products for the current assembly

ar	TOTAL.TABLE_D	TOTAL.TABLE_D.sm
1990	967.3083	967.3083
1991	754.1475	754.1475
1992	912.6352	912.6352
1993	1021.2472	1021.2472
1994	1265.9715	1265.9715
1995	1283.4495	1283.4495
1996	1318.3040	1318.3040
1997	1351.2389	1351.2389
1998	1291.0746	1291.0746
1999	1299.5669	1299.5669
2000	1436.7444	1436.7444
2001	1490.3184	1490.3184
2002	1524.4889	1524.4889
2003	1490.5871	1490.5871
2004	1570.2374	1570.2374
2005	1522.9484	1522.9484
2006	1437.3386	1437.3386
2007	1710.3944	1710.3944
2008	2076.2354	2076.2354
2009	2027.2363	2027.2363
2010	2056.3612	2056.3612
2011	2123.6319	2123.6319
2012	2208.0015	2208.0015
2013	2234.0759	2234.0759
2014	2180.3119	2180.3119
2015	2186.4681	2186.4681
2016	2191.4453	2191.4453
2017	2230.3521	2230.3521
2018	2512.0543	2512.0543
2019	2283.8188	2283.8188
2020	2290.5784	2290.5784
2021	2333.0278	2333.0278
2022	2528.2879	2528.2879
2023	2398.4725	2398.4725
2024	412.1943	412.1943

## MFA-Table_B.rmd completed

EW-MFA Table_B-E: Processing script

Þorsteinn Aðalsteinsson

2024-03-19

1 Versions

2 What is the project?

3 Processing and design

4 Data collection from the trade database

4.1 Orphans check of TLSK-MFA mapping

4.1.1 Exploration of the sub_gogn_verslun table

4.1.2 Total import/export by year

4.1.3 Import in key categories

4.1.4 Quantity of toll numbers that have NULL mapping to MFA:

4.2 Data for the assembly

5 Residence principle adjustments for the trade data and fuel used for transport

5.1 Road transport

5.1.1 Retrieve data from class MF421 that could belong to road-transport

5.1.2 Data from AEA accounting (and OS numbers)

5.1.3 Quality control and comparison of the numbers

5.1.3.1 Import of gasoline and use

5.1.3.2 Export of petrol

5.1.3.3 Import of disel oil

5.1.3.4 Export of diesel oil

5.1.4 Final installation of data for road transport

5.1.4.1 QUALITY CHECK: Do I have a module error?

5.1.4.2 QUALITY CHECK: Do I have a deduction error?

5.1.5 Final run and save to database:

5.2 Data for the assembly

5.3 Fuel for air transport

5.3.1 Data collection from the trade database

5.3.2 Data collection from data from the Energy Agency and the AEA accounting

5.3.3 Final storage of the data

5.3.4 Data for the assembly

5.4 Fuel for ships and marine transport

5.4.1 Fuel mapping for ships and marine transports

5.4.2 The processing

5.4.2.1 Step 1: Retrieve numbers from aea.gogn_mapped_eldsneyti_skip_lind

5.4.2.2 Step 2: Retrieve figures from AEA return data

5.4.2.3 Step 3: Retrieve numbers from NIR return data

5.4.2.4 Step 4: Arrange the data in a data frame. Here, the attempt is to make sure that I am not going over the subtraction terms at every step

5.4.3 Result of this mess?

5.4.3.1 Am I getting negative numbers?

5.4.4 Final storage of data

5.4.5 Data for the assembly

5.5 MF421 class fixes

6 Storing data on database

7 Comparison with previous submissions

7.1 Table B

7.1.1 Import values

7.1.1.1 MF1_ outcome

7.1.1.2 MF2_ outcome

7.1.1.3 MF3_ outcome

7.1.1.4 MF4_ outcome

7.1.1.5 MF5, MF6 outcome

7.1.1.6 Total for all categories

7.1.2 State of manufacturing

7.1.2.1 Per state

7.1.2.2 Sum over states from previous years

7.1.2.3 Sum over states and sum over products for the current assembly

7.2 Table D

7.2.1 Export values

7.2.1.1 MF1_ outcome

7.2.1.2 MF2_ outcome

7.2.1.3 MF3_ outcome

7.2.1.4 MF4_ outcome

7.2.1.5 MF5, MF6 outcome

7.2.1.6 Total for all categories

7.2.2 State of manufacturing

7.2.2.1 Per state

7.2.2.2 Sum over states from previous years

7.2.2.3 Sum over states and sum over products for the current assembly