Second largest value in a vector of specific object in another vector

Question

I have a very large dataframe with investors, companies and shares. Each company has multiple investors and I want to have a 4th vector with the value of the second biggest shares from the investor of each company. For showing the biggest investor I used:

df$bigInv <- aggregate(df$InvShare ~ df$Company, df, max)

This is an example of my data frame: enter image description here

Hey, thank your for the quick response! The function runs, but R shows me the second smallest shares now — Scrappycoco, Nov 11 '20 at 17:18
In order for us to help you, please provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). For example, to produce a minimal data set, you can use `head()`, `subset()`, or the indices. Then use `dput()` to give us something that can be put in R immediately. Also, please make sure you know what to do [when someone answers your question](https://stackoverflow.com/help/someone-answers). More info can be found at Stack Overflow's [help center](https://stackoverflow.com/help). Thank you! — iamericfletcher, Nov 11 '20 at 17:25
Another guess (without the data i can't test it), use `function(x){x[order(x, decreasing=TRUE)==2]}` — Ricardo Semião e Castro, Nov 11 '20 at 17:29

Álvaro A. Gutiérrez-Vargas · Accepted Answer · 2020-11-11T18:22:50.087

Data for reproducibility

df <- data.frame(id = c(1, 2, 3, 4, 5, 6, 7, 8),
                 Investor.Name =  c("Shigeo Kageyama", 
                                    "Shigeo Kageyama", 
                                    "Arataka Reigen",   
                                    "Arataka Reigen",  
                                    "Ritsu Kageyama",   
                                    "Ritsu Kageyama",
                                    "Ritsu Kageyama",   
                                    "Tenga Onigawara"),
                 Company = c("Body Improvement Club",   
                             "Salt Middle School",   
                             "Spirits Consultation ", 
                             "Pepper Middle School",
                             "Tofu Spirits School", 
                             "Psycho Helmet Cult",
                             "Chess Club",  
                             "Lecture Club"),
                 InvShare = c(
                           0.45,
                           0.12, 
                           0.89, 
                           0.541, 
                           0.15, 
                           0.75, 
                           0.14, 
                           0.1))

df

# id   Investor.Name               Company InvShare
# 1  1 Shigeo Kageyama Body Improvement Club    0.450
# 2  2 Shigeo Kageyama    Salt Middle School    0.120
# 3  3  Arataka Reigen Spirits Consultation     0.890
# 4  4  Arataka Reigen  Pepper Middle School    0.541
# 5  5  Ritsu Kageyama   Tofu Spirits School    0.150
# 6  6  Ritsu Kageyama    Psycho Helmet Cult    0.750
# 7  7  Ritsu Kageyama            Chess Club    0.140
# 8  8 Tenga Onigawara          Lecture Club    0.100

The following code will give to you the name of the second-biggest investor in each company.

#The second of the list of affiliation
second = function(x) {
  if (length(x) == 1)
    return(x)
  return(sort(x, decreasing = TRUE)[2])}

aggregate(x = list( InvShare =  df$InvShare),
          by= list(second_member =df$Investor.Name),
          FUN=second)

# second_member InvShare
# 1  Arataka Reigen    0.541
# 2  Ritsu Kageyama    0.150
# 3 Shigeo Kageyama    0.120
# 4 Tenga Onigawara    0.100

Is that what you were searching for?

[EDITED]

I just realized that you wanted a 4th column on your data.frame. The following lines will do it:

df$second  <- ave(x = df$InvShare,  
                  by = df$Investor.Name, 
                  FUN = second)

df

# id   Investor.Name               Company InvShare second
# 1  1 Shigeo Kageyama Body Improvement Club    0.450  0.120
# 2  2 Shigeo Kageyama    Salt Middle School    0.120  0.120
# 3  3  Arataka Reigen Spirits Consultation     0.890  0.541
# 4  4  Arataka Reigen  Pepper Middle School    0.541  0.541
# 5  5  Ritsu Kageyama   Tofu Spirits School    0.150  0.150
# 6  6  Ritsu Kageyama    Psycho Helmet Cult    0.750  0.150
# 7  7  Ritsu Kageyama            Chess Club    0.140  0.150
# 8  8 Tenga Onigawara          Lecture Club    0.100  0.100

Wow! This is very impressive! Thanks a lot for your effort. I replaced `max` with your `function(x) { if (length(x) == 1) return(x) return(sort(x, decreasing = TRUE)[2])}` and it works perfectly! — Scrappycoco, Nov 11 '20 at 20:26

Second largest value in a vector of specific object in another vector

1 Answers1