Assign variables in groups based on fractions and several conditions-CodePudding

I've tried for several days on something I think should be rather simple, with no luck. Hope someone can help me!

I have a data frame called "test" with the following variables: "Firm", "Year", "Firm_size" and "Expenditures".

I want to assign firms to size groups by year and then display the mean, median, std.dev and N of expenditures for these groups in a table (e.g. stargazer). So the first size group (top 10% largest firms) should show the mean, median of expenditures for the 10% largest firms each year.

The size groups should be,

The 10% largest firms
The firms that are between 10-25% largest
The firms that are between 25-50% largest
The firms that are between 50-75% largest
The firms that are between 75-90% largest
The 10% smallest firms

This is what I have tried:

test<-arrange(test, -Firm_size)
test$Variable = 0
test[1:min(5715, nrow(test)),]$Variable <- "Expenditures, 0% size <10%"
test[5715:min(14288, nrow(test)),]$Variable <- "Expenditures, 10% size <25%"
test[14288:min(28577, nrow(test)),]$Variable <- "Expenditures, 25% size <50%"
--> And so on


library(dplyr)
testtest = test%>%
 group_by(Variable)%>%
  dplyr::summarise(
    Mean=mean(Expenditures),
    Median=median(Expenditures),
    Std.dev=sd(Expenditures),
    N=n()
  )

stargazer(testtest, type = "text", title = "Expenditures firms", digits = 1, summary = FALSE)

As shown over, I dont know how I could use fractions/group by percentage. I have therefore tried to assign firms in groups based on their rows after having arranged Firm_size to descending. The problem with doing so is that I dont take year in to consideration which I need to, and it is a lot of work to do this for each year (20 in total).
My intention was to make a new variable which gives each size group a name. E.g. top 10% largest firms each year should get a variable with the name "Expenditures, 0% size <10%"

Further I make a new dataframe "testtest" where I calculate the different measures, before using the stargazer to present it. This works.

CodePudding user response：

You can create the quantiles as a nested variable (size_groups), and then use cut() to create the group sizes (gs). Then group by Year and gs to summarize the indicators you want.

test %>% 
  group_by(Year) %>% 
  mutate(size_groups = list(quantile(Firm_size, probs=c(.1,.25,.5,.75,.9)))) %>% 
  rowwise() %>% 
  mutate(gs = cut(
    Firm_size,c(-Inf, size_groups, Inf),
    labels = c("Lowest 10%","10%-25%","25%-50%","50%-75%","75%-90%","Highest 10%"))) %>% 
  group_by(Year, gs) %>% 
  summarize(across(Expenditures,.fns = list(mean,median,sd,length)), .groups="drop") %>% 
  rename_all(~c("Year", "Group_Size", "Mean_Exp", "Med_Exp", "SD_Exp","N_Firms"))

Output:

# A tibble: 126 x 6
    Year Group_Size  Mean_Exp Med_Exp SD_Exp N_Firms
   <int> <fct>          <dbl>   <dbl>  <dbl>   <int>
 1  2000 Lowest 10%    20885.  21363.  3710.       3
 2  2000 10%-25%       68127.  69497. 19045.       4
 3  2000 25%-50%       42035.  35371. 30335.       6
 4  2000 50%-75%       36089.  29802. 17724.       6
 5  2000 75%-90%       53319.  54914. 19865.       4
 6  2000 Highest 10%   57756.  49941. 34162.       3
 7  2001 Lowest 10%    55945.  47359. 28283.       3
 8  2001 10%-25%       61825.  70067. 21777.       4
 9  2001 25%-50%       65088.  76340. 29960.       6
10  2001 50%-75%       57444.  53495. 32458.       6
# ... with 116 more rows

If you wanted to have an additional column with the yearly mean, you can remove the .groups="drop" from the summarize(across()) line, and then add this final line to the pipeline:

mutate(YrMean = sum(Mean_Exp*N_Firms/sum(N_Firms)))

Note that this is correctly weighted by the number of Firms in each Group_size, and thus returns the equivalent of doing this with the original data

test %>% group_by(Year) %>% summarize(mean(Expenditures))

Input Data:

set.seed(123)
test = data.frame(
  Firm = replicate(2000, sample(letters,1)),
  Year = sample(2000:2020, 2000, replace=T),
  Firm_size= ceiling(runif(2000,2000,5000)),
  Expenditures = runif(2000, 10000,100000)
) %>% group_by(Firm,Year) %>% slice_head(n=1)