Update PCE data to more detailed version

[bl-young]

For State PCE, currently we use SAPCE1, which has about ~20 categories.

stateior/data-raw/BEAData.R

Line 113 in 7844559

FileName <- list.files(dir, pattern = "SAPCE1__ALL_AREAS")
SAPCE3 has a higher resolution (~100). Would require an updated mapping file.

[jvendries]

@bl-young @WesIngwersen I made an initial mapping of the BEA summary level IO codes to the more detailed PCE line items (SAPCE 3). I uploaded the mapping in an excel file in the cornerstone drive. Please review as you are able.

The new mapping is in the "CW_SAPCE 1 and 3" tab under columns J through K. This tab contains the original mapping as well as a reference, as well as notes on the mapping line by line. Some lines are highlighted orange which indicates that I assessed that mapping as uncertain.

[WesIngwersen]

Instead of a manual mapping why not use the PCE Bridge table. The items here in SAPCE are derived from or at least correspond well with the NIPA items. Ratios can be derived to BEA commodities based on producer value that are even year specific.

https://apps.bea.gov/industry/release/xlsx/PCEBridge_Summary.xlsx

[jvendries]

Instead of a manual mapping why not use the PCE Bridge table. The items here in SAPCE are derived from or at least correspond well with the NIPA items. Ratios can be derived to BEA commodities based on producer value that are even year specific.

https://apps.bea.gov/industry/release/xlsx/PCEBridge_Summary.xlsx

Thanks for the suggestion, Wes. I made a quick rmd script based on the PCE Bridge Summary file. Please take a look at the output and let me know if this is what you had in mind.

If it is, the next stesp would be to replace the current mapping in the repo with this one, and then confirm the that the state models are built properly (or make the necessary edits in the repo so that they do).

Script overview

The script does the following:

1. Download the summary bridge file;
2. Verify that the line codes in this file match the line codes in the SAPCE3 files (they do), and
3. Create the ratio you suggested using producers prices for 2017.

I didn't push the script to the repo as I am working from an untracked /work folder in my local stateior repository, but you should be able to copy the file in the sharepoint to your local repository and run it there. You need to be in the "5-PCE" stateior cornerstone branch. The output of the script is printed to inst/extdata as a csv file.

[bl-young]

This chunk in calculateStateUSPCERatio is where we will really need to determine the implementation. The prior mapping had only a single mapping per BEA code so the ratios will need to be applied here.

Also - it looks like the Summary PCE file has data for every year - wouldn't we want the ratios to reflect the year being modeled? As such that will need to be calculated on the fly and not embedded in the mapping file itself.

[jvendries]

This chunk in calculateStateUSPCERatio is where we will really need to determine the implementation. The prior mapping had only a single mapping per BEA code so the ratios will need to be applied here.

Also - it looks like the Summary PCE file has data for every year - wouldn't we want the ratios to reflect the year being modeled? As such that will need to be calculated on the fly and not embedded in the mapping file itself.

To be clear, we are moving away from a single mapping file then?
If so we can apply something similar to the script I made in the chunk you linked to calculate the ratios on the fly.

[bl-young]

I assume the mapping between sectors doesn't change year to year, probably just the ratios. But I want to make sure we are considering the directionality of the mapping correctly here - the old mapping does not have any duplicate BEA sectors. We are basically assigning the correct line code to use for each sector (IIRC)

[WesIngwersen]

The ratios here across a PCE item need to add to 1 and that's not what's happening here. For instance, the ratios in this block shold add to 1. These ratios would be static because they are only available in the PCE Bridge.

CommodityCode	LineCode	PCECategory	CommodityDescription	ProducersValue	Transportation Costs	Wholesale	Retail	PurchasersValue	Year	ProducersValuebyCommodityCode	Mapping_ratio
325	7	Motor vehicles parts and accessories	Chemical products	177	4	22	113	315	2017	255660	0.000692
326	7	Motor vehicles parts and accessories	Plastics and rubber products	13930	310	4619	17119	35978	2017	35009	0.397898
327	7	Motor vehicles parts and accessories	Nonmetallic mineral products	109	15	17	136	277	2017	11570	0.009421
331	7	Motor vehicles parts and accessories	Primary metals	12	0	1	14	28	2017	749	0.016021
332	7	Motor vehicles parts and accessories	Fabricated metal products	653	19	306	811	1789	2017	16629	0.039269
333	7	Motor vehicles parts and accessories	Machinery	0	0	0	0	0	2017	8872	0
334	7	Motor vehicles parts and accessories	Computer and electronic products	6	0	4	8	18	2017	73168	8.20030614476274e-05
335	7	Motor vehicles parts and accessories	Electrical equipment, appliances, and components	1540	15	248	1914	3717	2017	40276	0.038236
3361MV	7	Motor vehicles parts and accessories	Motor vehicles, bodies and trailers, and parts	16279	186	2463	20229	39158	2017	190228	0.085576
339	7	Motor vehicles parts and accessories	Miscellaneous manufacturing	99	13	25	124	261	2017	85969	0.001152
Used	7	Motor vehicles parts and accessories	Scrap, used and secondhand goods	-2376	16	1117	2327	1083	2017	81328	-0.02922

[jvendries]

The ratios here across a PCE item need to add to 1 and that's not what's happening here. For instance, the ratios in this block shold add to 1. These ratios would be static because they are only available in the PCE Bridge.

I edited the script to produce the ratios by LineCode rather than CommodityCode. See updated csv file; I linked to the LineCode you indicated in the block above.

I believe the mapping itself (BEA Summary Code-to-Line Code) remains unchanged regardless of which sets of ratios we calculate.

[WesIngwersen]

We decided to use the State PCE data dynamically along with the respective annual national PCE Bridge at the summary level.

[WesIngwersen]

@jvendries confirmed the US totals in the PCE data, when using the Bridge as described, sum to totals found in the Summary table before redef. Unless the final findings show otherwise, we should use the values rather than ratios, as there is not need to cloud/complicate the procedure.

[jvendries]

@jvendries confirmed the US totals in the PCE data, when using the Bridge as described, sum to totals found in the Summary table before redef. Unless the final findings show otherwise, we should use the values rather than ratios, as there is not need to cloud/complicate the procedure.

While the US totals in the PCE data sum to the totals found in the summary use table F01 column, we still need the PCE ratios as derived in this file to calculate the commodity values by state, don't we?

We can't just use the PCE Bridge Summary values as that file only provides total US values and not individual state values (as far as I'm aware), and the State PCE data at the detailed line level (113 lines) do not have the corresponding Summary Code mapping (see image), so we need the ratios to convert the values between the two files to summary BEA codes.

[jvendries]

@jvendries confirmed the US totals in the PCE data, when using the Bridge as described, sum to totals found in the Summary table before redef. Unless the final findings show otherwise, we should use the values rather than ratios, as there is not need to cloud/complicate the procedure.

@WesIngwersen @bl-young

While working on the the stateior code to update the mapping to SAPCE 3, I took another look at this and realized there are two things we need to compare: 1) Summary Bridge Producer value by commodity vs. the F10 (PCE) column in the Producer Summary Use table (which I quickly did before), and 2) Summary Bridge Purchaser Value by Line Code vs. the SAPCE 3 values by Line Code. The TLDR is that we will still need to use the ratios we have been using in the stateior code as the three datasets don't have quite the same values.

These two comparisons are needed because we cannot compare directly the Use table F10 column vs. the SAPCE 3 Line items due to the different schemas and price types these two datasets use. I created a script for these comparisons which you should be able to run from the PCE-5 branch as-is.

For comparison 1: Bridge producer values by commodity vs. F10 column from producer Use table

Most commodities do in fact have comparable values between these two datasets (within +/- 0.1%). However, I initally missed a few sectors a few sectors that have values that fall outside this range (see below - the image shows all such sectors). In particular, commodities 484 (Truck transportation), 482 (Rail transportation) and 4A0 (Other retail) have values that much higher between the Use table F10 column and the mapped PCE bridge commodity values, with ratios well over 2:1.

For comparison 2: Summary Bridge Purchaser Value by Line Code vs. the SAPCE 3 values by Line Code
All the values here have values within 0.01%. However, this means that if we were to aggregate purchaser values by Commodity Codes, we will have different values between the Use table F10 column and the SPACE 3 values, assuming that the data in the purchaser use table directly relates to the producer use table values.

What does this mean
Since only the SPACE 3 dataset has values by state, and the US totals by commodity between this dataset and the Use table F10 column are different, I believe we will need to use ratios to redistribute the mapping, i.e., follow the same approach we have been using with the SAPCE 1 data.