I have two dataframes. The first one called 'sw_flows_final' contains data of flows between sectors from 1952-2019. The second one is called 'sw_stocks' and contains data of stocks of what each sector owes the other from 2011-2019. I want to backwards extend the 'sw_stocks' data frame till 1952. The connection between the two is the following
stock_(t)+flow_(t)=stock_(t+1).
To take an example, I have the level for some sector in 2011. By subtracting the corresponding flow in 2011 of the same sector, I will get the level in 2010, and so on. There are 10 such sectors. I want R to generate levels from 2011 back until 1952 for every sector correctly. Can someone recommend how to do this efficiently?
Here are two images of how the dataframes look like sw_flows_final sw_stocks
Here is the sample data using dput for sw_flows_final
structure(list(sector = structure(c(2L, 3L, 4L, 5L, 6L, 7L, 8L,
9L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 1L, 2L, 3L), .Label = c("Source-Use",
"Banking", "Other financial corporations", "Private nonfinancial corporations",
"Government", "Rest of the World", "Households and NIPISHs",
"Other resident sectors", "Total"), class = "factor"), bank_s = structure(c(NA,
4, -1, 15, 23, -131, -6, -96, 19, NA, -3, -7, -43, -38, 13, 2,
-76, -48, NA, -3), label = "Banking Sources", format.stata = "%10.0g"),
bank_u = structure(c(NA, -5, 33, -25, -150, 32, NA, -115,
NA, NA, 6, -32, 10, 1, -13, NA, -28, NA, NA, -2), label = "Banking Uses", format.stata = "%10.0g"),
ofi_s = structure(c(NA, NA, NA, NA, 2, 38, 2, 42, -2, NA,
NA, NA, NA, 2, 53, NA, 55, -4, 4, NA), label = "Other Fin. Institutions Sources", format.stata = "%10.0g"),
ofi_u = structure(c(4, NA, 5, 25, 1, 9, NA, 44, NA, 2, NA,
5, 44, 2, 6, NA, 59, NA, 1, NA), label = "Other Fin. Institutions Uses", format.stata = "%10.0g"),
pcs_s = structure(c(54, 5, NA, 2, -1, 68, 6, 134, 118, -22,
5, NA, 3, 1, 58, 2, 47, 31, -9, 12), label = "Pvt. Corp. Sector Sources", format.stata = "%10.0g"),
pcs_u = structure(c(9, NA, NA, -2, NA, 2, 7, 16, NA, 3, NA,
NA, 5, NA, 3, 5, 16, NA, 15, NA), label = "Pvt. Corp. Sector Uses", format.stata = "%10.0g"),
govt_s = structure(c(-54, 15, -2, NA, 62, 102, -3, 120, 94,
75, 26, 5, NA, 28, 1, -41, 94, 120, -20, 36), label = "Government Sources", format.stata = "%10.0g"),
govt_u = structure(c(-3, NA, 2, NA, -2, 22, 7, 26, NA, -52,
NA, 3, NA, 1, 18, 4, -26, NA, -36, NA), label = "Government Uses", format.stata = "%10.0g"),
rotw_s = structure(c(-153, NA, -3, -14, NA, NA, -3, -173,
-207, 2, NA, NA, 9, NA, NA, -1, 10, 34, 26, NA), label = "Rest of the World Sources", format.stata = "%10.0g"),
rotw_u = structure(c(9, NA, 2, 42, NA, NA, -19, 34, NA, -48,
NA, 8, 20, NA, NA, -4, -24, NA, -25, NA), label = "Rest of the World Uses", format.stata = "%10.0g"),
hh_s = structure(c(32, 9, -5, 22, NA, NA, NA, 58, 1, -12,
7, -18, 18, NA, NA, NA, -5, -88, 24, 5), label = "Households Sources", format.stata = "%10.0g"),
hh_u = structure(c(-130, 11, 54, 122, NA, NA, NA, 57, NA,
16, 15, 23, 29, NA, NA, NA, 83, NA, 70, 16), label = "Households Uses", format.stata = "%10.0g"),
total_s = structure(c(-121, 33, -11, 25, 86, 77, -4, 85,
23, 43, 35, -20, -13, -7, 125, -38, 125, 45, 25, 50), label = "Total Sources", format.stata = "%8.0g"),
total_u = structure(c(-111, 6, 96, 162, -151, 65, -5, 62,
NA, -79, 21, 7, 108, 4, 14, 5, 80, NA, 25, 14), label = "Total Uses", format.stata = "%8.0g"),
year = structure(c(1952, 1952, 1952, 1952, 1952, 1952, 1952,
1952, 1952, 1953, 1953, 1953, 1953, 1953, 1953, 1953, 1953,
1953, 1954, 1954), label = "Year", format.stata = "%9.0g")), row.names = c(NA,
-20L), class = c("tbl_df", "tbl", "data.frame"))
and for sw_stocks
structure(list(sector = structure(c(1L, 2L, 3L, 4L, 5L, 6L, 7L,
9L, 8L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 9L, 8L, 1L, 2L), .Label = c("Banking",
"Government", "Households and NIPISHs", "Other financial corporations",
"Other resident sectors", "Private nonfinancial corporations",
"Rest of the World", "Source-Use", "Total"), class = "factor"),
bank_s_l = c(1350079.49133551, 1899576.67082447, 5062495.73351201,
1151214.12986427, 110.276338621267, 1355504.86599654, 859206.587426226,
11678187.7552976, -178532.519500652, 1505246.38567655, 2155467.21125388,
5855906.75278197, 1381570.68752383, 78.0505047633771, 1456469.98651368,
1066704.4326649, 13421443.5069196, -202944.716738584, 1740628.61553264,
2544347.17898597), bank_u_l = c(1156063.70260075, 3554941.84780303,
2447185.38181841, 616874.110576552, 138320.990278717, 1945789.820585,
1997544.42113584, 11856720.2747983, NA, 1241749.97765978,
4007409.67985746, 3290777.41165653, 612317.31788805, 141423.711476498,
2055249.27763703, 2275460.84748283, 13624388.2236582, NA,
1458813.42489447, 4587951.87769072), ofi_s_l = c(412030.693749423,
187700.316261259, 121865.465242432, 3611043.08926093, 87881.51,
120740.971533392, 175451.98473536, 4716714.03078279, -670782.594882376,
488886.759730145, 194866.871943733, 123364.152903093, 4019909.92650251,
94803.43, 115690.116216912, 253583.626498628, 5291104.88379502,
-846071.342671093, 572720.870848094, 205760.036602362), ofi_u_l = c(1082050.91844983,
1987036.26341659, 43593.1327534439, 1346470.61507149, 3473.61146178588,
891968.551489673, 32903.5330223561, 5387496.62566517, NA,
1080238.25250793, 2240819.70449555, 67741.7919287182, 1730643.94899816,
4370.85592038175, 930827.085634209, 82534.5869811682, 6137176.22646612,
NA, 1072481.25801139, 2658225.53044836), pcs_s_l = c(1940282.83357554,
229076.060527219, 1493897.17610662, 863355.291768346, 0,
5024995.75077571, 2801147.39882306, 12352754.5115765, 3480898.17067805,
2051048.40228274, 254992.855935366, 1509718.51083095, 899187.870098343,
0, 5265806.62852803, 3359680.47293263, 13340434.7406081,
3941569.20740091, 2386853.8881313, 277562.331033185), pcs_u_l = c(1355504.86599654,
471854.794244865, 0, 150821.917117881, 0, 6306138.74353917,
587536.02, 8871856.34089845, NA, 1456469.98651368, 476029.97685104,
0, 151878.215616114, 0, 6616322.85152686, 698164.502699446,
9398865.53320714, NA, 1614687.15738287, 497308.289032772),
govt_s_l = c(3026391.34344152, 1232718.57225668, 899831.786828824,
1758432.78129903, 0, 471854.794244865, 385631.474767484,
7774860.7528384, 7774860.7528384, 3299762.57843369, 1508255.01724503,
878289.365689908, 2114742.39334311, 0, 476029.97685104, 609867.659036133,
8886946.99059891, 8886946.99059891, 3775295.4629249, 1833119.31513762
), govt_u_l = c(1932781.13639086, 1380943.25642814, 20831.3302280885,
273893.850293545, 2077.96462093295, 219943.84762228, 10369.45,
3840840.83558385, 3840840.83558385, 2063225.61511638, 1542429.04485517,
23827.0863095146, 318847.879129413, 1766.43372182738, 246595.428939527,
12529.88, 4209221.36807184, 4209221.36807184, 2274350.44000159,
1705859.60013762), rotw_s_l = c(1451517.27411681, 10373.77,
0, 0, 175620.33, 587536.02, 0, 2225047.39411681, 2225047.39411681,
1548720.84, 12533.923713, 0, 0, 175786.24, 698164.502699446,
0, 2435205.50641245, 2435205.50641245, 1859509.8975799, 14605.2524611378
), rotw_u_l = c(337475.82215636, 409030.0781722, 0, 0, 0,
2814996.33882306, 0, 3561502.23915162, NA, 436497.84863876,
400723.19, 0, 0, 0, 3372956.29281454, 0, 4210177.3314533,
NA, 682427.647230683, 453829.566744612), hh_s_l = c(2963585.38181841,
20831.3302280885, 0, 43374.9585653354, 0, 0, 0, 3027791.67061183,
-7557951.13157221, 3273435.03602629, 23827.0863095146, 0,
67086.1333979951, 0, 0, 0, 3364348.2557338, -8314568.56600038,
3579173.99233299, 26649.23), hh_u_l = c(5147567.73351201,
882725.978434388, 0, 3061551.91413103, -7.27595761418343e-10,
1493897.17610662, 0, 10585742.802184, NA, 5861967.75278197,
881712.095035784, 0, 3425545.46308548, -7.27595761418343e-10,
1509691.51083095, 0, 11678916.8217342, NA, 6628288.36795117,
896978.173784456), total_s_l = c(11143887.0180372, 3580276.72009772,
7578090.16168989, 7427420.25075791, 263612.116338621, 7560632.4025505,
4221437.44575213, 41775356.115224, -2328803.00305744, 12167100.0021494,
4149942.96640053, 8367278.78220592, 8482497.01086579, 270667.720504763,
8012161.21080911, 5289836.19113229, 46739483.8840678, -2519261.62052293,
13914182.7273498, 4902043.34422028), total_u_l = c(11011444.1791064,
8686532.21849921, 2511609.84479994, 5449612.40719049, 143872.566361435,
13672734.4781658, 2628353.4241582, 44104159.1182814, NA,
12140149.4332185, 9549123.691095, 3382346.28989476, 6239232.82471722,
147561.001118706, 14731642.4473831, 3068689.81716345, 49258745.5045908,
NA, 13731048.2954722, 10800153.0378385), year = c(2012, 2012,
2012, 2012, 2012, 2012, 2012, 2012, 2012, 2013, 2013, 2013,
2013, 2013, 2013, 2013, 2013, 2013, 2014, 2014)), class = c("grouped_df",
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -20L), groups = structure(list(
sector = structure(1:9, .Label = c("Banking", "Government",
"Households and NIPISHs", "Other financial corporations",
"Other resident sectors", "Private nonfinancial corporations",
"Rest of the World", "Source-Use", "Total"), class = "factor"),
.rows = structure(list(c(1L, 10L, 19L), c(2L, 11L, 20L),
c(3L, 12L), c(4L, 13L), c(5L, 14L), c(6L, 15L), c(7L,
16L), c(9L, 18L), c(8L, 17L)), ptype = integer(0), class = c("vctrs_list_of",
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -9L), .drop = TRUE))
