Find Value Corresponding to Maximum in Other Column

Find value corresponding to maximum in other column

Using dplyr:

# install.packages(dplyr)
library(dplyr)

df %>% 
    filter(x == max(y)) %>% # filter the data.frame to keep row where x is maximum
    select(x) # select column y

Alternatively to return a vector

df %>% 
    filter(x == max(y)) %>% 
    pull(x) # pull the variable y

using base R:

df[df$x == max(df$y), "x"]

Using group by to get the value corresponding to the max value of another column

Up front, "never" (okay, almost never) use df$ within a dplyr pipe. In this case, df$value[which.max(df$age)] is referencing the original data each time, not the grouped data. Inside each group in this dataset, value is length 3 whereas df$value is length 9.

The only times I feel it is appropriate to use df$ (referencing the original value of the current dataset) inside a pipe is when it is required to look at pre-pipeline data, in absence of any grouping, reordering, or new variables created outside of the currently-saved (pre-pipeline) version of df.

dplyr

library(dplyr)
df %>%
  group_by(groups) %>%
  mutate(new_value = value[which.max(age)]) %>%
  ungroup()
# # A tibble: 9 x 4
#   groups   age value new_value
#    <dbl> <dbl> <dbl>     <dbl>
# 1      1    12     1         3
# 2      1    23     2         3
# 3      1    34     3         3
# 4      2    13     4         6
# 5      2    24     5         6
# 6      2    35     6         6
# 7      3    13     7         9
# 8      3    25     8         9
# 9      3    36     9         9

data.table

library(data.table)
DT <- as.data.table(df)
DT[, new_value := value[which.max(age)], by = .(groups)]

base R

df$new_value <- ave(seq_len(nrow(df)), df$groups,
                    FUN = function(i) df$value[i][which.max(df$age[i])])
df
#   groups age value new_value
# 1      1  12     1         3
# 2      1  23     2         3
# 3      1  34     3         3
# 4      2  13     4         6
# 5      2  24     5         6
# 6      2  35     6         6
# 7      3  13     7         9
# 8      3  25     8         9
# 9      3  36     9         9

The base R approach seems to be the least-elegant-looking solution. I believe that ave is the best approach, but it has many limitations, first being that it only works on one value-object (value) in the absence of others (we need to know age).

SQL Query to get column values that correspond with MAX value of another column?

I would try something like this:

SELECT
   s.video_id
   ,s.video_category
   ,s.video_url
   ,s.video_date
   ,s.video_title
   ,short_description
FROM videos s
   JOIN (SELECT MAX(video_id) AS id FROM videos GROUP BY video_category) max
      ON s.video_id = max.id

which is quite faster that your own solution

Get values in one column that correspond with max value of other columns in a matrix (R)?

Your code isn't working because if requires a single Boolean, and the left side of your Boolean statement has length > 1, resulting in a Boolean vector with length > 1. You could do something with ifelse, which will take length > 1 inputs, but in this case which.max is much simpler.

df <- data.frame(time=seq(0,9,1), matrix(sample(1:30,30), ncol=6))
df$time[apply(df,2,which.max)]

This will take only the first occurrence of the maximum value, so if there are multiple time points that have the max, you might want to do something else. In your sample data, the first five rows are always the same as the last five rows, so you always have two occurrences. More generally, though, there won't always be the same number, so you'll need a list to store these results, so you could lapply to loop over the columns of the data frame and which to find all the indices that correspond to the max.

lapply(df, function(x) df$time[which(x==max(x))])

Obtain corresponding value to max value of another column

You want to use ROW_NUMBER here:

SELECT group, subgroup_2, value_a AS max_value_a, value_b, date
FROM
(
    SELECT group, subgroup_2, value_a, value_b, date,
           ROW_NUMBER() OVER (PARTITION BY group, subgroup_2 ORDER BY value_a DESC) rn
    FROM table_1
) t
WHERE rn = 1;

How do I take max value of a pandas dataframe column and find the corresponding value in another column?

You can use

df.loc[df.GPA == df.GPA.max(), 'ID']

You get

5    Conor2

If you don't want the index but just the value, try

df.loc[df.GPA == df.GPA.max(), 'ID'].values[0]

You get

'Conor2'

Replace column with corresponding value of max value from another column

A possible solution:

library(tidyverse)

df <- data.frame(CATEGORY = c("A","A","A","B","B"), SALES = c(10,20,30,40,50))

df %>% 
  mutate(CATEGORY = CATEGORY[which.max(SALES)])

#>   CATEGORY SALES
#> 1        B    10
#> 2        B    20
#> 3        B    30
#> 4        B    40
#> 5        B    50

Pandas Dataframe: groupby id to find max column value and return corresponding value of another column

Try this:

def food_for_nutrient(lookup_nutrient):
    try:
        return df[df['nutrient'] == lookup_nutrient].set_index('food')['value'].astype(float).idxmax()
    except ValueError:
        return f'Sorry, {lookup_nutrient} not found.'

Output:

>>> food_for_nutrient('A')
'banana'

>>> food_for_nutrient('B')
'cheese'

>>> food_for_nutrient('C')
'bread'

>>> food_for_nutrient('D')
'apple'

>>> food_for_nutrient('E')
'Sorry, E not found.'

How can I get the max value from one column where values match another column?

IIUC, you can use

df['Wins'].update(df['Name'].map(df.groupby('Owner')['Wins'].max()))

---

Related Topics