0
\$\begingroup\$

I wrote code which calculates:

  1. Decile threshold for every decile group
  2. Total income in the decile group
  3. Number of persons
  4. Share of decile in total income (%)
  5. Tax
  6. Share of tax (%)

But unfortunately I didn't wrote with function like e.g apply, lapply, aggregate or similar function, so my code had around 150 lines. Can anybody help me to make this code simpler with some function like apply or something similar?

Output of my code you can see in this picture:

enter image description here

     [![`
  library(dplyr)

  set.seed(1444)
  data1<-data.frame(sample(1000))
  data2<-mutate(data1,TAX=sample.1000.*0.15)
  colnames(data2)<-c("NET_INCOME","TAX")

 # CALCULATION....
  decili_total_income_neto<-data.frame(quantile(data2$NET_INCOME, c(.10, .20, .30, .40, .50, .60, .70, .80, .90, 1)))
 ZBIR_TOTAL_NET_INCOME<-sum(data2$NET_INCOME)
  ZBIR_TOTAL_TAX<-sum(data2$TAX)


  #DECILE 1

  t_prag_top_total_income_10<-decili_total_income_neto\[1,1\]
  t_prag_top_total_income_filter_10<-filter(data2, NET_INCOME>= 0, NET_INCOME<= t_prag_top_total_income_10)
  t_prag_top_total_income_filter_10_tax<-sum(t_prag_top_total_income_filter_10$TAX)     
  t_tax_share_10<-((t_prag_top_total_income_filter_10_tax)/ZBIR_TOTAL_TAX)*100      
  t_prag_top_total_income_filter_10<-sum(t_prag_top_total_income_filter_10$NET_INCOME)
  t_prag_top_total_income_filter_10a<-nrow(filter(data2, NET_INCOME>= 0, NET_INCOME<= t_prag_top_total_income_10))
  t_prag_top_total_income_10b<-((t_prag_top_total_income_filter_10)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_DECILE_TABLE<-data.frame(cbind(t_prag_top_total_income_10,t_prag_top_total_income_filter_10,t_prag_top_total_income_filter_10a,t_prag_top_total_income_10b,t_prag_top_total_income_filter_10_tax    ,   t_tax_share_10))
  colnames(FINAL_DECILE_TABLE)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")


  #DECILE 2

  t_prag_top_total_income_20<-decili_total_income_neto\[2,1\]
  t_prag_top_total_income_filter_20<-filter(data2, NET_INCOME> t_prag_top_total_income_10, NET_INCOME<=t_prag_top_total_income_20)
  t_prag_top_total_income_filter_20_tax<-sum(t_prag_top_total_income_filter_20$TAX)     
  t_tax_share_20<-((t_prag_top_total_income_filter_20_tax)/ZBIR_TOTAL_TAX)*100      
  t_prag_top_total_income_filter_20<-sum(t_prag_top_total_income_filter_20$NET_INCOME)
  t_prag_top_total_income_filter_20a<-nrow(filter(data2, NET_INCOME> t_prag_top_total_income_10, NET_INCOME<=t_prag_top_total_income_20))
  t_prag_top_total_income_20b<-((t_prag_top_total_income_filter_20)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_CENTILE_TABLE20<-data.frame(cbind(t_prag_top_total_income_20,t_prag_top_total_income_filter_20,t_prag_top_total_income_filter_20a,t_prag_top_total_income_20b,t_prag_top_total_income_filter_20_tax ,   t_tax_share_20))
  colnames(FINAL_CENTILE_TABLE20)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")
  FINAL_DECILE_TABLE <- rbind(FINAL_DECILE_TABLE, FINAL_CENTILE_TABLE20) 


  #DECILE 3

  t_prag_top_total_income_30<-decili_total_income_neto\[3,1\]
  t_prag_top_total_income_filter_30<-filter(data2, NET_INCOME> t_prag_top_total_income_20, NET_INCOME<=t_prag_top_total_income_30)
  t_prag_top_total_income_filter_30_tax<-sum(t_prag_top_total_income_filter_30$TAX)     
  t_tax_share_30<-((t_prag_top_total_income_filter_30_tax)/ZBIR_TOTAL_TAX)*100      
  t_prag_top_total_income_filter_30<-sum(t_prag_top_total_income_filter_30$NET_INCOME)
  t_prag_top_total_income_filter_30a<-nrow(filter(data2, NET_INCOME> t_prag_top_total_income_20, NET_INCOME<=t_prag_top_total_income_30))
  t_prag_top_total_income_30b<-((t_prag_top_total_income_filter_30)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_CENTILE_TABLE30<-data.frame(cbind(t_prag_top_total_income_30,t_prag_top_total_income_filter_30,t_prag_top_total_income_filter_30a,t_prag_top_total_income_30b,t_prag_top_total_income_filter_30_tax ,   t_tax_share_30))
  colnames(FINAL_CENTILE_TABLE30)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")
  FINAL_DECILE_TABLE <- rbind(FINAL_DECILE_TABLE, FINAL_CENTILE_TABLE30) 



  #DECILE 4

  t_prag_top_total_income_40<-decili_total_income_neto\[4,1\]
  t_prag_top_total_income_filter_40<-filter(data2, NET_INCOME> t_prag_top_total_income_30, NET_INCOME<=t_prag_top_total_income_40)
  t_prag_top_total_income_filter_40_tax<-sum(t_prag_top_total_income_filter_40$TAX)     
  t_tax_share_40<-((t_prag_top_total_income_filter_40_tax)/ZBIR_TOTAL_TAX)*100      
  t_prag_top_total_income_filter_40<-sum(t_prag_top_total_income_filter_40$NET_INCOME)
  t_prag_top_total_income_filter_40a<-nrow(filter(data2, NET_INCOME> t_prag_top_total_income_30, NET_INCOME<=t_prag_top_total_income_40))
  t_prag_top_total_income_40b<-((t_prag_top_total_income_filter_40)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_CENTILE_TABLE40<-data.frame(cbind(t_prag_top_total_income_40,t_prag_top_total_income_filter_40,t_prag_top_total_income_filter_40a,t_prag_top_total_income_40b,t_prag_top_total_income_filter_40_tax ,   t_tax_share_40))
  colnames(FINAL_CENTILE_TABLE40)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")
  FINAL_DECILE_TABLE <- rbind(FINAL_DECILE_TABLE, FINAL_CENTILE_TABLE40) 


  #DECILE 5

  t_prag_top_total_income_50<-decili_total_income_neto\[5,1\]
  t_prag_top_total_income_filter_50<-filter(data2, NET_INCOME> t_prag_top_total_income_40, NET_INCOME<=t_prag_top_total_income_50)
  t_prag_top_total_income_filter_50_tax<-sum(t_prag_top_total_income_filter_50$TAX)     
  t_tax_share_50<-((t_prag_top_total_income_filter_50_tax)/ZBIR_TOTAL_TAX)*100      
  t_prag_top_total_income_filter_50<-sum(t_prag_top_total_income_filter_50$NET_INCOME)
  t_prag_top_total_income_filter_50a<-nrow(filter(data2, NET_INCOME> t_prag_top_total_income_40, NET_INCOME<=t_prag_top_total_income_50))
  t_prag_top_total_income_50b<-((t_prag_top_total_income_filter_50)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_CENTILE_TABLE50<-data.frame(cbind(t_prag_top_total_income_50,t_prag_top_total_income_filter_50,t_prag_top_total_income_filter_50a,t_prag_top_total_income_50b,t_prag_top_total_income_filter_50_tax ,   t_tax_share_50))
  colnames(FINAL_CENTILE_TABLE50)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")
  FINAL_DECILE_TABLE <- rbind(FINAL_DECILE_TABLE, FINAL_CENTILE_TABLE50) 



  #DECILE 6

  t_prag_top_total_income_60<-decili_total_income_neto\[6,1\]
  t_prag_top_total_income_filter_60<-filter(data2, NET_INCOME> t_prag_top_total_income_50, NET_INCOME<=t_prag_top_total_income_60)
  t_prag_top_total_income_filter_60_tax<-sum(t_prag_top_total_income_filter_60$TAX)     
  t_tax_share_60<-((t_prag_top_total_income_filter_60_tax)/ZBIR_TOTAL_TAX)*100      
  t_prag_top_total_income_filter_60<-sum(t_prag_top_total_income_filter_60$NET_INCOME)
  t_prag_top_total_income_filter_60a<-nrow(filter(data2, NET_INCOME> t_prag_top_total_income_50, NET_INCOME<=t_prag_top_total_income_60))
  t_prag_top_total_income_60b<-((t_prag_top_total_income_filter_60)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_CENTILE_TABLE60<-data.frame(cbind(t_prag_top_total_income_60,t_prag_top_total_income_filter_60,t_prag_top_total_income_filter_60a,t_prag_top_total_income_60b,t_prag_top_total_income_filter_60_tax ,   t_tax_share_60))
  colnames(FINAL_CENTILE_TABLE60)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")
  FINAL_DECILE_TABLE <- rbind(FINAL_DECILE_TABLE, FINAL_CENTILE_TABLE60) 


  #DECILE 7

  t_prag_top_total_income_70<-decili_total_income_neto\[7,1\]
  t_prag_top_total_income_filter_70<-filter(data2, NET_INCOME> t_prag_top_total_income_60, NET_INCOME<=t_prag_top_total_income_70)
  t_prag_top_total_income_filter_70_tax<-sum(t_prag_top_total_income_filter_70$TAX)     
  t_tax_share_70<-((t_prag_top_total_income_filter_70_tax)/ZBIR_TOTAL_TAX)*100      
  t_prag_top_total_income_filter_70<-sum(t_prag_top_total_income_filter_70$NET_INCOME)
  t_prag_top_total_income_filter_70a<-nrow(filter(data2, NET_INCOME> t_prag_top_total_income_60, NET_INCOME<=t_prag_top_total_income_70))
  t_prag_top_total_income_70b<-((t_prag_top_total_income_filter_70)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_CENTILE_TABLE70<-data.frame(cbind(t_prag_top_total_income_70,t_prag_top_total_income_filter_70,t_prag_top_total_income_filter_70a,t_prag_top_total_income_70b,t_prag_top_total_income_filter_70_tax ,   t_tax_share_70))
  colnames(FINAL_CENTILE_TABLE70)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")
  FINAL_DECILE_TABLE <- rbind(FINAL_DECILE_TABLE, FINAL_CENTILE_TABLE70) 



  #DECILE 8

  t_prag_top_total_income_80<-decili_total_income_neto\[8,1\]
  t_prag_top_total_income_filter_80<-filter(data2, NET_INCOME> t_prag_top_total_income_70, NET_INCOME<=t_prag_top_total_income_80)
  t_prag_top_total_income_filter_80_tax<-sum(t_prag_top_total_income_filter_80$TAX)     
  t_tax_share_80<-((t_prag_top_total_income_filter_80_tax)/ZBIR_TOTAL_TAX)*100      
  t_prag_top_total_income_filter_80<-sum(t_prag_top_total_income_filter_80$NET_INCOME)
  t_prag_top_total_income_filter_80a<-nrow(filter(data2, NET_INCOME> t_prag_top_total_income_70, NET_INCOME<=t_prag_top_total_income_80))
  t_prag_top_total_income_80b<-((t_prag_top_total_income_filter_80)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_CENTILE_TABLE80<-data.frame(cbind(t_prag_top_total_income_80,t_prag_top_total_income_filter_80,t_prag_top_total_income_filter_80a,t_prag_top_total_income_80b,t_prag_top_total_income_filter_80_tax ,   t_tax_share_80))
  colnames(FINAL_CENTILE_TABLE80)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")
  FINAL_DECILE_TABLE <- rbind(FINAL_DECILE_TABLE, FINAL_CENTILE_TABLE80) 



  #DECILE 9

  t_prag_top_total_income_90<-decili_total_income_neto\[9,1\]
  t_prag_top_total_income_filter_90<-filter(data2, NET_INCOME> t_prag_top_total_income_80, NET_INCOME<=t_prag_top_total_income_90)
  t_prag_top_total_income_filter_90_tax<-sum(t_prag_top_total_income_filter_90$TAX)     
  t_tax_share_90<-((t_prag_top_total_income_filter_90_tax)/ZBIR_TOTAL_TAX)*100      
  t_prag_top_total_income_filter_90<-sum(t_prag_top_total_income_filter_90$NET_INCOME)
  t_prag_top_total_income_filter_90a<-nrow(filter(data2, NET_INCOME> t_prag_top_total_income_80, NET_INCOME<=t_prag_top_total_income_90))
  t_prag_top_total_income_90b<-((t_prag_top_total_income_filter_90)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_CENTILE_TABLE90<-data.frame(cbind(t_prag_top_total_income_90,t_prag_top_total_income_filter_90,t_prag_top_total_income_filter_90a,t_prag_top_total_income_90b,t_prag_top_total_income_filter_90_tax ,   t_tax_share_90))
  colnames(FINAL_CENTILE_TABLE90)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")
  FINAL_DECILE_TABLE <- rbind(FINAL_DECILE_TABLE, FINAL_CENTILE_TABLE90) 


  #DECILE 10

  t_prag_top_total_income_100<-decili_total_income_neto\[10,1\]
  t_prag_top_total_income_filter_100<-filter(data2, NET_INCOME> t_prag_top_total_income_90, NET_INCOME<=t_prag_top_total_income_100)
  t_prag_top_total_income_filter_100_tax<-sum(t_prag_top_total_income_filter_100$TAX)       
  t_tax_share_100<-((t_prag_top_total_income_filter_100_tax)/ZBIR_TOTAL_TAX)*100        
  t_prag_top_total_income_filter_100<-sum(t_prag_top_total_income_filter_100$NET_INCOME)
  t_prag_top_total_income_filter_100a<-nrow(filter(data2, NET_INCOME> t_prag_top_total_income_90, NET_INCOME<=t_prag_top_total_income_100))
  t_prag_top_total_income_100b<-((t_prag_top_total_income_filter_100)/ZBIR_TOTAL_NET_INCOME)*100
  FINAL_CENTILE_TABLE100<-data.frame(cbind(t_prag_top_total_income_100,t_prag_top_total_income_filter_100,t_prag_top_total_income_filter_100a,t_prag_top_total_income_100b,t_prag_top_total_income_filter_100_tax,t_tax_share_100))
  colnames(FINAL_CENTILE_TABLE100)<-c("Decile threshold","Total income in the decile","Number of persons in the centile","Share of the decile in total income (%)","Tax","Share tax(%)")
  FINAL_DECILE_TABLE <- rbind(FINAL_DECILE_TABLE, FINAL_CENTILE_TABLE100) 


  View(FINAL_DECILE_TABLE)][1]][1]
\$\endgroup\$

1 Answer 1

Reset to default
0
\$\begingroup\$

The dplyr package you are using here is perfect for this kind of aggregation work. Of particular interest here will be the functions

  1. ntile() for creating a DECILE (1 through 10) vector added to your data via mutate()
  2. group_by() for doing aggregation work per the newly created DECILE column
  3. summarize for aggregating data within each group

In action, it gives:

data <- data.frame(NET_INCOME = sample(1000)) %>%
  mutate(TAX = 0.15 * NET_INCOME)

report <- data %>%
  mutate(DECILE = ntile(NET_INCOME, 10)) %>%
  group_by(DECILE) %>%
  summarize(
    MAX_INCOME = max(NET_INCOME),
    NET_INCOME = sum(NET_INCOME),
    TAX        = sum(TAX),
    COUNT      = n(),
  ) %>%
  mutate(
    PCT_INCOME = 100 * NET_INCOME / sum(NET_INCOME),
    PCT_TAX    = 100 * TAX / sum(TAX)
  ) %>% print

#    DECILE MAX_INCOME NET_INCOME     TAX COUNT PCT_INCOME   PCT_TAX
#     <int>      <dbl>      <int>   <dbl> <int>      <dbl>     <dbl>
#  1      1        100       5050   757.5   100   1.008991  1.008991
#  2      2        200      15050  2257.5   100   3.006993  3.006993
#  3      3        300      25050  3757.5   100   5.004995  5.004995
#  4      4        400      35050  5257.5   100   7.002997  7.002997
#  5      5        500      45050  6757.5   100   9.000999  9.000999
#  6      6        600      55050  8257.5   100  10.999001 10.999001
#  7      7        700      65050  9757.5   100  12.997003 12.997003
#  8      8        800      75050 11257.5   100  14.995005 14.995005
#  9      9        900      85050 12757.5   100  16.993007 16.993007
# 10     10       1000      95050 14257.5   100  18.991009 18.991009

For comparison, this is how we could do with basic R functions. Using

  1. quantile and findInterval (an alternative is cut) for building a vector of deciles (1 through 10)
  2. aggregate to compute the sums per decile

See for yourself:

set.seed(1444)
net_income <- sample(1000)
deciles <- quantile(net_income, seq(1, 10) / 10)

data <- data.frame(
  NET_INCOME = net_income,
  TAX        = 0.15 * net_income,
  DECILE     = findInterval(net_income, c(-Inf, deciles), rightmost.closed = TRUE),
  COUNT      = 1 
)

per_decile <- aggregate(. ~ DECILE, data, FUN = sum)
per_total  <- aggregate(. ~ 1,      data, FUN = sum)

data.frame(
  INCOME_THRESHOLD  = deciles,
  DECILE            = per_decile$DECILE,
  NET_INCOME        = per_decile$NET_INCOME,
  COUNT             = per_decile$COUNT,
  PCT_INCOME        = 100 * per_decile$NET_INCOME / per_total$NET_INCOME,
  TAX               = per_decile$TAX,
  PCT_TAX           = 100 * per_decile$TAX / per_total$TAX
)
\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.