# Imposing a distribution on a dependent variable to increase variation in the dependent variable - disaggregating data

The question I have essentially has to do with dis-aggregating data, based on a known distribution of the data with respect to a certain variable. It has some resemblances with post-stratification, perhaps data augmentation. I am trying to find if I can do this, what the consequences would be and whether there is any literature on it.

NOTE: All code discussed in the post, can be found together at the bottom of the post.

NOTE: If anything about my example is not clear, please let me know in the comments and I will try to improve on the issue.

I have a dependent variable, tax, provided by the government, which is aggregated at the provincial/state level per year (example in R):

library(data.table)
tax_aggregated <- structure(list(Province = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2,
3), Tax = c(2000, 3000, 1500, 3200, 2000, 1500, 4000, 2000, 2000,
1000, 2000, 1500), year = c(2000, 2000, 2000, 2001, 2001, 2001,
2002, 2002, 2002, 2003, 2003, 2003)), row.names = c(NA, -12L), class = c("tbl_df",
"tbl", "data.frame"))

# A tibble: 12 x 3
Province   Tax  year
<dbl> <dbl> <dbl>
1        1  2000  2000
2        2  3000  2000
3        3  1500  2000
4        1  3200  2001
5        2  2000  2001
6        3  1500  2001
7        1  4000  2002
8        2  2000  2002
9        3  2000  2002
10        1  1000  2003
11        2  2000  2003
12        3  1500  2003


My independent variables are as follows:

# A tibble: 48 x 6
ID Province  year Age    Educ Income
<dbl>    <dbl> <dbl> <chr> <dbl>  <dbl>
1     1        1  2000 15-30     2   17.5
2     2        2  2000 30-40    11   11.0
3     3        3  2000 50-65     6   60
4     4        1  2000 50-65     9   43.3
5     5        2  2000 15-30    10   39
6     6        3  2000 30-40     2   60.8
7     7        1  2000 40-50    10   52.4
8     8        2  2000 15-30     2   10.1
9     9        3  2000 40-50     7   15.5
10    10        1  2000 30-40     2   43.4
# ... with 38 more rows

Individual_data <- structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
12,
13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,
45, 46, 47, 48), Province = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2,
3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2,
3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3), year = c(2000,
2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003,
2003, 2003, 2003), Age = c("15-30", "30-40", "50-65", "50-65",
"15-30", "30-40", "40-50", "15-30", "40-50", "30-40", "15-30",
"50-65", "40-50", "15-30", "50-65", "50-65", "40-50", "50-65",
"40-50", "50-65", "15-30", "50-65", "40-50", "50-65", "15-30",
"30-40", "50-65", "15-30", "15-30", "40-50", "50-65", "50-65",
"15-30", "50-65", "40-50", "40-50", "50-65", "15-30", "15-30",
"50-65", "40-50", "30-40", "40-50", "50-65", "15-30", "30-40",
"40-50", "50-65"), Educ = c(2, 11, 6, 9, 10, 2, 10, 2, 7, 2,
7, 11, 9, 0, 7, 2, 9, 0, 2, 9, 9, 9, 0, 5, 3, 5, 7, 9, 10, 8,
9, 5, 11, 3, 10, 11, 1, 2, 9, 6, 7, 3, 6, 3, 0, 2, 5, 5), Income = c(15,
10.99, 50, 43.31, 39, 60.85, 52.4, 10.12, 15.5, 43.42, 1.72,
87.45, 78.04, 83.16, 67.19, 19.05, 89.42, 41.44, 45.23, 17.89,
75.85, 85.94, 40.84, 13.7, 35.48, 10.87, 51.89, 67.54, 34.85,
24.65, 55.25, 41.12, 36.56, 75.85, 22.98, 53.22, 22.07, 73.64,
83.01, 68.29, 12.63, 54.84, 94.84, 79.49, 14.61, 44.24, 56.53,
20.27)), row.names = c(NA, -48L), class = c("tbl_df", "tbl",
"data.frame"))



Merge:

setDT(Individual_data)
setDT(tax_aggregated)
DT <- merge(Individual_data, tax_aggregated, by=c("year", "Province"))


ASSUMPTION FOR THE FAKE DATA:

Let's assume that Educ is an exogenous shock, resulting from an education grant received by every citizen.

My formula of interest is:

$$Tax_i=Educ_i + Province_i + Income_i + u$$

in R:

summary(lm(Tax ~ Educ + Income + as.factor(Province), data=DT))

Call:
lm(formula = Tax ~ Educ + Income + as.factor(Province), data = DT)

Residuals:
Min       1Q   Median       3Q      Max
-1539.91  -311.74   -33.21   374.20  1622.87

Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept)          2461.301    316.688   7.772    1e-09 ***
Educ                   46.801     30.888   1.515 0.137048
Income                 -2.961      4.268  -0.694 0.491571
as.factor(Province)2 -373.479    268.566  -1.391 0.171490
as.factor(Province)3 -990.903    263.822  -3.756 0.000514 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 736.2 on 43 degrees of freedom
Multiple R-squared:  0.267, Adjusted R-squared:  0.1988
F-statistic: 3.916 on 4 and 43 DF,  p-value: 0.008474


Since in the aggregated data, there is only one data point per year, it is possible that the dependent variable has not enough variation to use in a regression. As a results I want to increase the variation.

Then I thought, what if I know the variation of tax with respect to age groups for these provinces. So for example, I have found on the internet how tax is distributed with respect to age in these provinces. Essentially I know the distribution of Age with respect to taxes.

Age_tax_rate <- structure(list(year = c(2000, 2000, 2000, 2000, 2000, 2000,
2000,
2000, 2000, 2000, 2000, 2000, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2003, 2003, 2003, 2003,
2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003), Province = c(1,
1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3,
3, 3, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 1, 1, 1, 1, 2, 2, 2,
2, 3, 3, 3, 3), Tax_rat = c(0.16, 0.18, 0.3, 0.36, 0.17, 0.25,
0.29, 0.29, 0.15, 0.18, 0.33, 0.34, 0.16, 0.22, 0.29, 0.33, 0.18,
0.22, 0.3, 0.3, 0.12, 0.18, 0.33, 0.37, 0.17, 0.25, 0.33, 0.25,
0.16, 0.18, 0.33, 0.33, 0.18, 0.22, 0.33, 0.27, 0.16, 0.25, 0.3,
0.29, 0.16, 0.22, 0.29, 0.33, 0.15, 0.22, 0.33, 0.3), Age = c("15-30",
"30-40", "40-50", "50-65", "15-30", "30-40", "40-50", "50-65",
"15-30", "30-40", "40-50", "50-65", "15-30", "30-40", "40-50",
"50-65", "15-30", "30-40", "40-50", "50-65", "15-30", "30-40",
"40-50", "50-65", "15-30", "30-40", "40-50", "50-65", "15-30",
"30-40", "40-50", "50-65", "15-30", "30-40", "40-50", "50-65",
"15-30", "30-40", "40-50", "50-65", "15-30", "30-40", "40-50",
"50-65", "15-30", "30-40", "40-50", "50-65")), row.names = c(NA,
-48L), class = c("tbl_df", "tbl", "data.frame"))

year Province Tax_rat   Age
1: 2000        1    0.16 15-30
2: 2000        1    0.18 30-40
3: 2000        1    0.30 40-50
4: 2000        1    0.36 50-65
5: 2000        2    0.17 15-30
6: 2000        2    0.25 30-40
7: 2000        2    0.29 40-50
8: 2000        2    0.29 50-65
9: 2000        3    0.15 15-30
10: 2000        3    0.18 30-40


To merge:

setDT(Age_tax_rate)
tax_aggregated_new <- tax_aggregated[Age_tax_rate, on = .(Province, year), allow.cartesian = TRUE # in order to get a larger output than DT1 + DT2
][, :=(Tax = Tax * Tax_rat)]
DT <- merge(Individual_data, tax_aggregated_new, by=c("Age","year", "Province"))


So now I have a dependent variable which much more variation. If I now run:

summary(lm(Tax ~ Educ + Income + as.factor(Province), data=DT))

Call:
lm(formula = Tax ~ Educ + Income + as.factor(Province), data = DT)

Residuals:
Min      1Q  Median      3Q     Max
-369.91 -133.53   17.21  125.78  442.63

Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept)           587.8997    91.4133   6.431 8.62e-08 ***
Educ                   16.4933     8.9396   1.845   0.0719 .
Income                 -0.3946     1.2346  -0.320   0.7508
as.factor(Province)2 -134.1574    77.6550  -1.728   0.0912 .
as.factor(Province)3 -230.9902    76.3909  -3.024   0.0042 **
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 213 on 43 degrees of freedom
Multiple R-squared:  0.2081,    Adjusted R-squared:  0.1344
F-statistic: 2.825 on 4 and 43 DF,  p-value: 0.0363


Now it is marginally significant at the 10% level (again, please note this is just an example with made up data).

The problem now is that, in contrast to a normal variable, where I am trying to figure out what drives this variable, I here know exactly what drives the variation, namely age, because I created it like that. That obviously does not mean that age also CAUSES the variation, especially after controlling for income (Please note that this question is non-existent example). My argument is for example that something else actually causes the variation, let's say, whether you have a tax adviser or not. So what I want to do is see if those variable explain variation in my new dependent variable.

Coming to the final issue: I am not completely one hundred percent sure if this works, or what the pitfalls, or potential biases are. In addition, I not only have to convince myself, but also other people that this is okay. I would therefore like to read up on this but I cannot really find anything that helps me out in this respect.

Can anyone give me some guidance on this, or provide me with some literature on this?

# ALL CODE IN ONE GO

library(data.table)
tax_aggregated <- structure(list(Province = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2,
3), Tax = c(2000, 3000, 1500, 3200, 2000, 1500, 4000, 2000, 2000,
1000, 2000, 1500), year = c(2000, 2000, 2000, 2001, 2001, 2001,
2002, 2002, 2002, 2003, 2003, 2003)), row.names = c(NA, -12L), class = c("tbl_df",
"tbl", "data.frame"))

Individual_data <- structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
12,
13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,
45, 46, 47, 48), Province = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2,
3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2,
3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3), year = c(2000,
2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003,
2003, 2003, 2003), Age = c("15-30", "30-40", "50-65", "50-65",
"15-30", "30-40", "40-50", "15-30", "40-50", "30-40", "15-30",
"50-65", "40-50", "15-30", "50-65", "50-65", "40-50", "50-65",
"40-50", "50-65", "15-30", "50-65", "40-50", "50-65", "15-30",
"30-40", "50-65", "15-30", "15-30", "40-50", "50-65", "50-65",
"15-30", "50-65", "40-50", "40-50", "50-65", "15-30", "15-30",
"50-65", "40-50", "30-40", "40-50", "50-65", "15-30", "30-40",
"40-50", "50-65"), Educ = c(2, 11, 6, 9, 10, 2, 10, 2, 7, 2,
7, 11, 9, 0, 7, 2, 9, 0, 2, 9, 9, 9, 0, 5, 3, 5, 7, 9, 10, 8,
9, 5, 11, 3, 10, 11, 1, 2, 9, 6, 7, 3, 6, 3, 0, 2, 5, 5), Income = c(15,
10.99, 50, 43.31, 39, 60.85, 52.4, 10.12, 15.5, 43.42, 1.72,
87.45, 78.04, 83.16, 67.19, 19.05, 89.42, 41.44, 45.23, 17.89,
75.85, 85.94, 40.84, 13.7, 35.48, 10.87, 51.89, 67.54, 34.85,
24.65, 55.25, 41.12, 36.56, 75.85, 22.98, 53.22, 22.07, 73.64,
83.01, 68.29, 12.63, 54.84, 94.84, 79.49, 14.61, 44.24, 56.53,
20.27)), row.names = c(NA, -48L), class = c("tbl_df", "tbl",
"data.frame"))

setDT(Individual_data)
setDT(tax_aggregated)
DT <- merge(Individual_data, tax_aggregated, by=c("year", "Province"))
summary(lm(Tax ~ Educ + Income + as.factor(Province), data=DT))

Age_tax_rate <- structure(list(year = c(2000, 2000, 2000, 2000, 2000, 2000,
2000,
2000, 2000, 2000, 2000, 2000, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2003, 2003, 2003, 2003,
2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003), Province = c(1,
1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3,
3, 3, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 1, 1, 1, 1, 2, 2, 2,
2, 3, 3, 3, 3), Tax_rat = c(0.16, 0.18, 0.3, 0.36, 0.17, 0.25,
0.29, 0.29, 0.15, 0.18, 0.33, 0.34, 0.16, 0.22, 0.29, 0.33, 0.18,
0.22, 0.3, 0.3, 0.12, 0.18, 0.33, 0.37, 0.17, 0.25, 0.33, 0.25,
0.16, 0.18, 0.33, 0.33, 0.18, 0.22, 0.33, 0.27, 0.16, 0.25, 0.3,
0.29, 0.16, 0.22, 0.29, 0.33, 0.15, 0.22, 0.33, 0.3), Age = c("15-30",
"30-40", "40-50", "50-65", "15-30", "30-40", "40-50", "50-65",
"15-30", "30-40", "40-50", "50-65", "15-30", "30-40", "40-50",
"50-65", "15-30", "30-40", "40-50", "50-65", "15-30", "30-40",
"40-50", "50-65", "15-30", "30-40", "40-50", "50-65", "15-30",
"30-40", "40-50", "50-65", "15-30", "30-40", "40-50", "50-65",
"15-30", "30-40", "40-50", "50-65", "15-30", "30-40", "40-50",
"50-65", "15-30", "30-40", "40-50", "50-65")), row.names = c(NA,
-48L), class = c("tbl_df", "tbl", "data.frame"))

setDT(Age_tax_rate)
tax_aggregated_new <- tax_aggregated[Age_tax_rate, on = .(Province, year), allow.cartesian = TRUE # in order to get a larger output than DT1 + DT2
][, :=(Tax = Tax * Tax_rat)]
DT <- merge(Individual_data, tax_aggregated_new, by=c("Age","year", "Province"))
summary(lm(Tax ~ Educ + Income + as.factor(Province), data=DT))

• How do you define the total_tax for different age groups? – Kota Mori Nov 22 '20 at 11:30
• I take the ratio of total_tax for each age group and apply the same ratio at the province/state level. So for example, if people between 20 and 30, account for 25% percent of the total_tax at the national level, I assume that they account for the same percentage at the state level. – Tom Nov 22 '20 at 11:43