2
$\begingroup$

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))
$\endgroup$
2
  • $\begingroup$ How do you define the total_tax for different age groups? $\endgroup$ – Kota Mori Nov 22 '20 at 11:30
  • $\begingroup$ 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. $\endgroup$ – Tom Nov 22 '20 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.