State uses of state commodities using the consumption vector exceed state commodity output (for select commodities)

[WesIngwersen]

expanding on cornerstone-data/USEEIO-State#1

I reviewed the consumption demand vector preparation in useeior and compared it to production demand. For domestic models in the production demand, the international trade adjustment (ita) and export residual (er)columns are added, but this is not the case for consumption demand which just follows the national model formula.
I tested adding these into the consumption demand to see if this prevents the over consumption of state output problem, and it does resolve it. I'm not sure this is valid without further review but this definitely deserves attention

Here are the results for 3 v1.1 state models for 2020, CA, NY and ME.

q_est_>q is a check if the existing consumption demand + intermediate uses is greater than commodity output
q_est_w_e_r>_q is a check if the modified consumption demand with ita and er + intermediate uses is greater than commodity output

There are no cases where the latter is TRUE in these three models

	q	q_est	q_est_w_e_r	q_est_>_q	q_est_w_e_r>_q
111CA/US-CA	8.05E+10	3.69E+10	3.69E+10	FALSE	FALSE
113FF/US-CA	1.83E+10	8.06E+09	8.06E+09	FALSE	FALSE
211/US-CA	6.82E+09	4.93E+09	4.93E+09	FALSE	FALSE
212/US-CA	3.77E+09	3.39E+09	3.66E+09	FALSE	FALSE
213/US-CA	2.04E+09	2.01E+09	2.01E+09	FALSE	FALSE
22/US-CA	7.98E+10	7.73E+10	7.73E+10	FALSE	FALSE
23/US-CA	2.51E+11	2.47E+11	2.51E+11	FALSE	FALSE
321/US-CA	7.84E+09	7.62E+09	7.62E+09	FALSE	FALSE
327/US-CA	1.08E+10	1E+10	1E+10	FALSE	FALSE
331/US-CA	7.76E+09	6.52E+09	6.52E+09	FALSE	FALSE
332/US-CA	3.08E+10	2.35E+10	2.35E+10	FALSE	FALSE
333/US-CA	3.11E+10	1.47E+10	1.47E+10	FALSE	FALSE
334/US-CA	1.18E+11	3.95E+10	3.95E+10	FALSE	FALSE
335/US-CA	1.25E+10	6.84E+09	6.84E+09	FALSE	FALSE
3361MV/US-CA	3.75E+10	2.48E+10	2.48E+10	FALSE	FALSE
3364OT/US-CA	2.58E+10	7.8E+09	7.8E+09	FALSE	FALSE
337/US-CA	6.47E+09	5.58E+09	5.58E+09	FALSE	FALSE
339/US-CA	2.97E+10	1.53E+10	1.53E+10	FALSE	FALSE
311FT/US-CA	1E+11	8.46E+10	8.46E+10	FALSE	FALSE
313TT/US-CA	3.54E+09	2.48E+09	2.48E+09	FALSE	FALSE
315AL/US-CA	6.09E+09	1.92E+09	1.92E+09	FALSE	FALSE
322/US-CA	9.75E+09	8.74E+09	8.74E+09	FALSE	FALSE
323/US-CA	5.98E+09	5.82E+09	5.82E+09	FALSE	FALSE
324/US-CA	6.64E+10	3.65E+10	3.65E+10	FALSE	FALSE
325/US-CA	1.51E+11	5.87E+10	5.87E+10	FALSE	FALSE
326/US-CA	1.44E+10	1.19E+10	1.19E+10	FALSE	FALSE
42/US-CA	2.69E+11	6.62E+10	7.52E+10	FALSE	FALSE
441/US-CA	2.01E+10	2.25E+10	2.01E+10	TRUE	FALSE
445/US-CA	4.85E+10	3.19E+10	4.85E+10	FALSE	FALSE
452/US-CA	3.61E+10	3.35E+10	3.61E+10	FALSE	FALSE
4A0/US-CA	1.41E+11	1.27E+11	1.27E+11	FALSE	FALSE
481/US-CA	1.47E+10	8.04E+09	8.13E+09	FALSE	FALSE
482/US-CA	3.22E+09	2.67E+09	2.69E+09	FALSE	FALSE
483/US-CA	4.14E+09	2.38E+09	3.59E+09	FALSE	FALSE
484/US-CA	4.26E+10	3.38E+10	3.45E+10	FALSE	FALSE
485/US-CA	2.06E+10	1.24E+10	2.06E+10	FALSE	FALSE
486/US-CA	1.17E+09	9.58E+08	9.58E+08	FALSE	FALSE
487OS/US-CA	4.28E+10	3.73E+10	4.11E+10	FALSE	FALSE
493/US-CA	2.34E+10	1.52E+10	1.52E+10	FALSE	FALSE
511/US-CA	7.88E+10	3.54E+10	3.54E+10	FALSE	FALSE
512/US-CA	6.43E+10	8.06E+09	8.06E+09	FALSE	FALSE
513/US-CA	1.23E+11	6.9E+10	6.9E+10	FALSE	FALSE
514/US-CA	1.34E+11	4.91E+10	4.91E+10	FALSE	FALSE
521CI/US-CA	1.03E+11	2.36E+10	2.36E+10	FALSE	FALSE
523/US-CA	8.21E+10	6.12E+10	6.12E+10	FALSE	FALSE
524/US-CA	9.1E+10	2.68E+10	2.69E+10	FALSE	FALSE
525/US-CA	2.17E+10	1.94E+10	1.94E+10	FALSE	FALSE
HS/US-CA	4.29E+11	3.26E+11	4.29E+11	FALSE	FALSE
ORE/US-CA	1.11E+11	1.83E+11	1.11E+11	TRUE	FALSE
532RL/US-CA	5.75E+10	2.29E+10	2.29E+10	FALSE	FALSE
5411/US-CA	5.16E+10	3.09E+10	3.09E+10	FALSE	FALSE
5415/US-CA	1.26E+11	8.6E+10	8.6E+10	FALSE	FALSE
5412OP/US-CA	4.41E+11	9.35E+10	9.35E+10	FALSE	FALSE
55/US-CA	7.89E+10	7.35E+10	7.35E+10	FALSE	FALSE
561/US-CA	1.45E+11	8.58E+10	8.58E+10	FALSE	FALSE
562/US-CA	1.63E+10	7.7E+09	7.7E+09	FALSE	FALSE
61/US-CA	5.66E+10	1.74E+10	1.74E+10	FALSE	FALSE
621/US-CA	1.53E+11	1.55E+11	1.53E+11	TRUE	FALSE
622/US-CA	1.29E+11	1.57E+11	1.29E+11	TRUE	FALSE
623/US-CA	2.78E+10	3.55E+10	2.78E+10	TRUE	FALSE
624/US-CA	4.45E+10	3.28E+10	4.45E+10	FALSE	FALSE
711AS/US-CA	2.78E+10	2.01E+10	2.01E+10	FALSE	FALSE
713/US-CA	1.9E+10	7.9E+09	7.9E+09	FALSE	FALSE
721/US-CA	2.04E+10	1.7E+10	1.7E+10	FALSE	FALSE
722/US-CA	1.25E+11	1.25E+11	1.25E+11	TRUE	FALSE
81/US-CA	1.05E+11	9.95E+10	9.95E+10	FALSE	FALSE
GFGD/US-CA	5.71E+10	3.07E+10	5.71E+10	FALSE	FALSE
GFGN/US-CA	4.49E+10	2.95E+10	4.49E+10	FALSE	FALSE
GFE/US-CA	7.25E+09	4.08E+09	4.08E+09	FALSE	FALSE
GSLG/US-CA	3.05E+11	3.07E+11	3.05E+11	TRUE	FALSE
GSLE/US-CA	9.67E+09	1.53E+10	9.67E+09	TRUE	FALSE
Used/US-CA	9.32E+08	-4.7E+08	-4.3E+08	FALSE	FALSE
Other/US-CA	3.17E+08	-8.9E+09	-7.4E+09	FALSE	FALSE

	q	q_est	q_est_w_e_r	q_est_>_q	q_est_w_e_r>_q
111CA/US-NY	7.19E+09	6.95E+09	6.95E+09	FALSE	FALSE
113FF/US-NY	6.89E+08	4.78E+08	4.78E+08	FALSE	FALSE
211/US-NY	1.27E+08	92102313	92102313	FALSE	FALSE
212/US-NY	1.17E+09	9.92E+08	1.08E+09	FALSE	FALSE
213/US-NY	1.42E+08	1.3E+08	1.3E+08	FALSE	FALSE
22/US-NY	4.75E+10	1.14E+10	1.14E+10	FALSE	FALSE
23/US-NY	1.09E+11	1.31E+11	1.09E+11	TRUE	FALSE
321/US-NY	1.62E+09	1.48E+09	1.48E+09	FALSE	FALSE
327/US-NY	6.76E+09	4.18E+09	4.18E+09	FALSE	FALSE
331/US-NY	4.91E+09	2.78E+08	2.78E+08	FALSE	FALSE
332/US-NY	1.11E+10	6.71E+09	6.71E+09	FALSE	FALSE
333/US-NY	1.19E+10	4.21E+09	4.21E+09	FALSE	FALSE
334/US-NY	1.03E+10	3.54E+09	3.54E+09	FALSE	FALSE
335/US-NY	3.66E+09	2.02E+09	2.02E+09	FALSE	FALSE
3361MV/US-NY	5.68E+09	4.87E+09	4.87E+09	FALSE	FALSE
3364OT/US-NY	1.59E+09	9.98E+08	9.98E+08	FALSE	FALSE
337/US-NY	1.94E+09	1.83E+09	1.83E+09	FALSE	FALSE
339/US-NY	3.55E+09	2.92E+09	2.92E+09	FALSE	FALSE
311FT/US-NY	3.93E+10	3.01E+10	3.01E+10	FALSE	FALSE
313TT/US-NY	1.67E+09	7.2E+08	7.2E+08	FALSE	FALSE
315AL/US-NY	1.97E+09	4.99E+08	4.99E+08	FALSE	FALSE
322/US-NY	6.91E+09	4.2E+09	4.2E+09	FALSE	FALSE
323/US-NY	3.33E+09	3.21E+09	3.21E+09	FALSE	FALSE
324/US-NY	2.13E+09	1.5E+09	1.5E+09	FALSE	FALSE
325/US-NY	1.32E+10	8.7E+09	8.7E+09	FALSE	FALSE
326/US-NY	1.13E+10	5.76E+09	5.76E+09	FALSE	FALSE
42/US-NY	1.18E+11	2.69E+10	3.08E+10	FALSE	FALSE
441/US-NY	7.65E+09	9.81E+09	7.65E+09	TRUE	FALSE
445/US-NY	2.58E+10	1.53E+10	2.58E+10	FALSE	FALSE
452/US-NY	1.56E+10	1.63E+10	1.56E+10	TRUE	FALSE
4A0/US-NY	6.66E+10	6.28E+10	6.28E+10	FALSE	FALSE
481/US-NY	9.26E+09	4.35E+09	4.41E+09	FALSE	FALSE
482/US-NY	1.52E+09	1.27E+09	1.28E+09	FALSE	FALSE
483/US-NY	2.24E+09	1.24E+09	1.9E+09	FALSE	FALSE
484/US-NY	9.43E+09	8.31E+09	8.44E+09	FALSE	FALSE
485/US-NY	1.36E+10	7.04E+09	1.36E+10	FALSE	FALSE
486/US-NY	8.39E+08	6.87E+08	6.87E+08	FALSE	FALSE
487OS/US-NY	1.3E+10	1.61E+10	1.25E+10	TRUE	FALSE
493/US-NY	4.37E+09	3.86E+09	3.86E+09	FALSE	FALSE
511/US-NY	3.68E+10	1.58E+10	1.58E+10	FALSE	FALSE
512/US-NY	2.74E+10	5.4E+09	5.4E+09	FALSE	FALSE
513/US-NY	1.13E+11	4.72E+10	4.72E+10	FALSE	FALSE
514/US-NY	4.26E+10	2.4E+10	2.4E+10	FALSE	FALSE
521CI/US-NY	2.22E+11	2.24E+10	2.24E+10	FALSE	FALSE
523/US-NY	2.32E+11	6.59E+10	6.59E+10	FALSE	FALSE
524/US-NY	1.58E+11	2.87E+10	2.88E+10	FALSE	FALSE
525/US-NY	2.02E+10	1.36E+10	1.36E+10	FALSE	FALSE
HS/US-NY	2.49E+11	1.57E+11	2.49E+11	FALSE	FALSE
ORE/US-NY	6.43E+10	1.15E+11	6.41E+10	TRUE	FALSE
532RL/US-NY	4.42E+10	1.34E+10	1.34E+10	FALSE	FALSE
5411/US-NY	5.79E+10	2.09E+10	2.09E+10	FALSE	FALSE
5415/US-NY	3.88E+10	3.05E+10	3.05E+10	FALSE	FALSE
5412OP/US-NY	2.13E+11	3.94E+10	3.94E+10	FALSE	FALSE
55/US-NY	4.57E+10	3.6E+10	3.6E+10	FALSE	FALSE
561/US-NY	7.42E+10	4.76E+10	4.76E+10	FALSE	FALSE
562/US-NY	6.95E+09	3.64E+09	3.64E+09	FALSE	FALSE
61/US-NY	5.01E+10	1.06E+10	1.06E+10	FALSE	FALSE
621/US-NY	7.68E+10	8.5E+10	7.68E+10	TRUE	FALSE
622/US-NY	1.04E+11	8.61E+10	1.04E+11	FALSE	FALSE
623/US-NY	2.12E+10	1.94E+10	2.12E+10	FALSE	FALSE
624/US-NY	2.55E+10	1.79E+10	2.55E+10	FALSE	FALSE
711AS/US-NY	1.88E+10	1.13E+10	1.13E+10	FALSE	FALSE
713/US-NY	8.55E+09	3.35E+09	3.35E+09	FALSE	FALSE
721/US-NY	1.18E+10	9.45E+09	9.45E+09	FALSE	FALSE
722/US-NY	5.49E+10	5.68E+10	5.48E+10	TRUE	FALSE
81/US-NY	5.77E+10	5.59E+10	5.59E+10	FALSE	FALSE
GFGD/US-NY	1.11E+10	6.55E+09	1.11E+10	FALSE	FALSE
GFGN/US-NY	1.9E+10	1.22E+10	1.9E+10	FALSE	FALSE
GFE/US-NY	3.06E+09	1.58E+09	1.58E+09	FALSE	FALSE
GSLG/US-NY	1.73E+11	1.73E+11	1.73E+11	FALSE	FALSE
GSLE/US-NY	5.5E+09	9.68E+09	5.5E+09	TRUE	FALSE
Used/US-NY	4.84E+08	1.17E+08	-2.3E+08	FALSE	FALSE
Other/US-NY	1.34E+08	-4.7E+09	-3.1E+09	FALSE	FALSE

	q	q_est	q_est_w_e_r	q_est_>_q	q_est_w_e_r>_q
111CA/US-ME	9.79E+08	5.47E+08	5.47E+08	FALSE	FALSE
113FF/US-ME	9.48E+08	3.53E+08	3.53E+08	FALSE	FALSE
211/US-ME	397007.5	286997.1	286997.1	FALSE	FALSE
212/US-ME	98793012	85015755	92037615	FALSE	FALSE
213/US-ME	6208643	6054207	6054207	FALSE	FALSE
22/US-ME	1.93E+09	1.92E+09	1.92E+09	FALSE	FALSE
23/US-ME	5.04E+09	6.43E+09	5.04E+09	TRUE	FALSE
321/US-ME	1.1E+09	3.18E+08	3.18E+08	FALSE	FALSE
327/US-ME	2.95E+08	2.11E+08	2.11E+08	FALSE	FALSE
331/US-ME	99647026	39607418	39607418	FALSE	FALSE
332/US-ME	1.1E+09	2.76E+08	2.76E+08	FALSE	FALSE
333/US-ME	6.51E+08	1.61E+08	1.61E+08	FALSE	FALSE
334/US-ME	5.53E+08	1.3E+08	1.3E+08	FALSE	FALSE
335/US-ME	1.09E+08	81292503	81292503	FALSE	FALSE
3361MV/US-ME	4.38E+08	3.55E+08	3.55E+08	FALSE	FALSE
3364OT/US-ME	1.19E+09	2.35E+08	2.35E+08	FALSE	FALSE
337/US-ME	1.86E+08	1.24E+08	1.24E+08	FALSE	FALSE
339/US-ME	3.69E+08	1.49E+08	1.49E+08	FALSE	FALSE
311FT/US-ME	3.7E+09	1.86E+09	1.86E+09	FALSE	FALSE
313TT/US-ME	2.94E+08	31655029	31655029	FALSE	FALSE
315AL/US-ME	1.73E+08	11688685	11688685	FALSE	FALSE
322/US-ME	2.23E+09	5.54E+08	5.54E+08	FALSE	FALSE
323/US-ME	1.73E+08	1.68E+08	1.68E+08	FALSE	FALSE
324/US-ME	2.82E+08	2.78E+08	2.78E+08	FALSE	FALSE
325/US-ME	2.05E+09	4.08E+08	4.08E+08	FALSE	FALSE
326/US-ME	7.58E+08	2.42E+08	2.42E+08	FALSE	FALSE
42/US-ME	5.95E+09	1.59E+09	1.79E+09	FALSE	FALSE
441/US-ME	8.33E+08	1.06E+09	8.33E+08	TRUE	FALSE
445/US-ME	1.84E+09	1.3E+09	1.84E+09	FALSE	FALSE
452/US-ME	1.17E+09	8.49E+08	1.17E+09	FALSE	FALSE
4A0/US-ME	4.82E+09	3.41E+09	3.41E+09	FALSE	FALSE
481/US-ME	44475311	37922655	38209558	FALSE	FALSE
482/US-ME	1.34E+08	1.11E+08	1.11E+08	FALSE	FALSE
483/US-ME	35497119	77703305	30798709	TRUE	FALSE
484/US-ME	1.24E+09	5.63E+08	5.81E+08	FALSE	FALSE
485/US-ME	1.94E+08	2.81E+08	1.94E+08	TRUE	FALSE
486/US-ME	43508518	30155987	30155987	FALSE	FALSE
487OS/US-ME	6.67E+08	7.98E+08	6.4E+08	TRUE	FALSE
493/US-ME	4.37E+08	2.59E+08	2.59E+08	FALSE	FALSE
511/US-ME	5.71E+08	4.98E+08	4.98E+08	FALSE	FALSE
512/US-ME	83693421	25193093	25193093	FALSE	FALSE
513/US-ME	1.04E+09	1.02E+09	1.02E+09	FALSE	FALSE
514/US-ME	1.94E+08	1.91E+08	1.91E+08	FALSE	FALSE
521CI/US-ME	2.55E+09	4.75E+08	4.75E+08	FALSE	FALSE
523/US-ME	8.66E+08	7.72E+08	7.72E+08	FALSE	FALSE
524/US-ME	5.65E+09	9.24E+08	9.27E+08	FALSE	FALSE
525/US-ME	2.13E+08	2.13E+08	2.13E+08	FALSE	FALSE
HS/US-ME	1.26E+10	1E+10	1.26E+10	FALSE	FALSE
ORE/US-ME	3.24E+09	5.12E+09	3.23E+09	TRUE	FALSE
532RL/US-ME	1.04E+09	4.86E+08	4.86E+08	FALSE	FALSE
5411/US-ME	8.92E+08	8.04E+08	8.04E+08	FALSE	FALSE
5415/US-ME	1.31E+09	1.03E+09	1.03E+09	FALSE	FALSE
5412OP/US-ME	5.11E+09	9.52E+08	9.52E+08	FALSE	FALSE
55/US-ME	2.42E+09	1.67E+09	1.67E+09	FALSE	FALSE
561/US-ME	3.22E+09	1.77E+09	1.77E+09	FALSE	FALSE
562/US-ME	5.11E+08	442518.6	442518.6	FALSE	FALSE
61/US-ME	1.58E+09	3.97E+08	3.97E+08	FALSE	FALSE
621/US-ME	4.38E+09	4.21E+09	4.38E+09	FALSE	FALSE
622/US-ME	6.81E+09	4.24E+09	6.79E+09	FALSE	FALSE
623/US-ME	1.78E+09	9.53E+08	1.78E+09	FALSE	FALSE
624/US-ME	1.31E+09	8.78E+08	1.31E+09	FALSE	FALSE
711AS/US-ME	2.6E+08	2.57E+08	2.57E+08	FALSE	FALSE
713/US-ME	6.4E+08	2.21E+08	2.21E+08	FALSE	FALSE
721/US-ME	7.01E+08	4.3E+08	4.3E+08	FALSE	FALSE
722/US-ME	3.84E+09	3.51E+09	3.83E+09	FALSE	FALSE
81/US-ME	2.94E+09	2.94E+09	2.94E+09	FALSE	FALSE
GFGD/US-ME	8.77E+08	5.6E+08	8.77E+08	FALSE	FALSE
GFGN/US-ME	2.11E+09	2.99E+09	2.11E+09	TRUE	FALSE
GFE/US-ME	3.41E+08	16525388	16525388	FALSE	FALSE
GSLG/US-ME	7.07E+09	7.41E+09	7.07E+09	TRUE	FALSE
GSLE/US-ME	2.26E+08	6.08E+08	2.26E+08	TRUE	FALSE
Used/US-ME	33919360	-6.1E+07	-1.6E+07	FALSE	FALSE
Other/US-ME	14907264	-3.5E+08	-3.5E+08	FALSE	FALSE

Originally posted by @WesIngwersen in #1

[WesIngwersen]

Needs:

• Expand on previous analysis to include RoUS uses of state commodities
• Calculate state consumption of state commodities for all 52 regions with state models to check if it equals national consumption from an equivalent national model

[WesIngwersen]

Included RoUS uses of SoI commodities in a modified script and ran select models for select years for all states to compare uses with consumption demand as is and with ita and er added. Results are in logs by year and model type attached.

test_consumption_against_q_2020_pecan.log
test_consumption_against_q_2020_v1.1.log
test_consumption_against_q_2021_pecan.log
test_consumption_against_q_2022_pecan.log

Summary of results:
For pecan 2021 and 2022 models:
q_est_w_ita_er is still greater than q only for 113FF for a handful of states like AL, AZ, AR,IA, MN, MT, NV, TX,
but for those states q_est (existing demand also fails for those)
for pecan 2020 there is a failure for 327 for HI , for 111CA/US-RI,

For v1.1 2020 there are almost no failures
except for 323/US-AL, 321/US-CO
In all cases those same coms fail as well for the current consumption approach

[bl-young]

It appears as though the logic for generating the consumption demand vector needs to be updated here, to then align with the script above, but would not resolve the remaining failures.

[jvendries]

I edited prepare2RDemand() in StateiorFunctions.R to match the consumption final demand calculation in the test script. I did a quick test to make sure that the code runs with the changes for the "StateEEIOv1.1-GHG-20" model spec, and also to make sure that the model$DemandVectors object differ with the changes.

I started trying to identify the reason for the failures in the test script even with the adjusted consumption demand calculation but I didn't get too far yet, so I will continue looking into it.

[WesIngwersen]

I edited prepare2RDemand() in StateiorFunctions.R to match the consumption final demand calculation in the test script. I did a quick test to make sure that the code runs with the changes for the "StateEEIOv1.1-GHG-20" model spec, and also to make sure that the model$DemandVectors object differ with the changes.

I started trying to identify the reason for the failures in the test script even with the adjusted consumption demand calculation but I didn't get too far yet, so I will continue looking into it.

This is resolved/understood in #14

[WesIngwersen]

Completion of this task :

'Calculate state consumption of state commodities for all 52 regions with state models to check if it equals national consumption from an equivalent national model'

requires creating model for DC which is not possible until #16 is merged