Min and Max across multiple columns with NAs
You can use hablar
's min_
and max_
function which returns NA
if all values are NA
.
library(dplyr)
library(hablar)
dat %>%
rowwise() %>%
mutate(min = min_(c_across(-ID)),
max = max_(c_across(-ID)))
You can also use this with apply
-
cbind(dat, t(apply(dat[-1], 1, function(x) c(min = min_(x), max = max_(x)))))
# ID PM TP2 Sigma min max
#1 1 1 2 3 1 3
#2 2 0 NA 1 0 1
#3 3 2 1 NA 1 2
#4 4 1 0 2 0 2
#5 NA NA NA NA NA NA
#6 5 2 0 7 0 7
How to calculate max and min of multiple columns (row wise) using awk
You may use this awk
:
awk 'BEGIN{FS=OFS=","} NR==1 {print $0, "New_col"; next} {print $0, ($2 > $4 ? $2 : $4) - ($3 < $5 ? $3 : $5)}' df.csv
col1,col2,col3,col4,col5,New_col
A,2,5,7,9,2
B,6,10,2,3,3
C,3,4,6,8,2
A more readable version:
awk '
BEGIN { FS = OFS = "," }
NR == 1 {
print $0, "New_col"
next
}
{
print $0, ($2 > $4 ? $2 : $4) - ($3 < $5 ? $3 : $5)
}' df.csv
Filter maximum and minimum values' of multiple columns in R
You can get the data in long format, convert factor values to numeric using parse_number
and for each column name select max
and min
rows.
library(dplyr)
df %>%
tidyr::pivot_longer(cols = c(month_pct, year_pct)) %>%
mutate(value = readr::parse_number(as.character(value))) %>%
group_by(name) %>%
slice(which.min(value), which.max(value)) %>%
mutate(max_min = c('min', 'max'), .before = 'id')
# max_min id price name value
# <chr> <int> <dbl> <chr> <dbl>
#1 min 10 1.77 month_pct -19.9
#2 max 1 40.6 month_pct 8.53
#3 min 1 40.6 year_pct -35.3
#4 max 7 54.8 year_pct 1.54
SQL MAX of multiple columns?
This is an old answer and broken in many way.
See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.
Original but problematic answer:
Well, you can use the CASE statement:
SELECT
CASE
WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
ELSE Date1
END AS MostRecentDate
PostgreSQL: find minimum value across multiple columns but return column name
You can do:
select *,
case when mp = alpha then 'col: alpha'
when mp = bravo then 'col: bravo'
when mp = charlie then 'col: charlie'
when mp = delta then 'col: delta'
end as lower_positive
from (
select *,
least(
case when alpha > 0 then alpha end,
case when bravo > 0 then bravo end,
case when charlie > 0 then charlie end,
case when delta > 0 then delta end
) as mp
from t
) x
However, this solution doesn't account for multiple minimums; the first one (from left ro right) wins.
pandas get the row-wise minimum value of two or more columns
If you are trying to get the row-wise mininum
of two or more columns, use pandas.DataFrame.min
and specify axis=1
.
data['min_c_h'] = data[['flow_h','flow_c']].min(axis=1)
# display(data)
flow_c flow_d flow_h min_c_h
0 82 36 43 43
1 52 48 12 12
2 33 28 77 33
3 91 99 11 11
4 44 95 27 27
5 5 94 64 5
6 98 3 88 88
7 73 39 92 73
8 26 39 62 26
9 56 74 50 50
Calculate Min Mean Max For Rows Where Multiple Columns Have Same Values
We can use
library(tidyverse)
data %>%
group_by(U, T) %>%
mutate(Mean = mean(Mean), Min = min(Min), Max = max(Max))%>%
slice(1)
Second and third largest values within multiple columns in Pandas
To find the second largest values of each row, you can use nlargest
; apply a function to each row:
df['2nd_largest'] = df[["A1", "B1", "C1", "D1", "E1", "F1"]].apply(lambda row: row.nlargest(2).iat[-1], axis=1)
Consolidate rows and take minimum and maximum values
This works by pivoting your two numeric columns into one column. This way you can take the min
and max
on one column, which is value
.
library(tidyr)
library(dplyr)
df %>%
tidyr::pivot_longer(cols = c(B, C)) %>%
dplyr::group_by(A) %>%
dplyr::summarize(min = min(value),
max = max(value))
A min max
<chr> <int> <int>
1 A 10 350
2 B 5 260
3 C 1 100
4 D 20 50
5 E 1 10
6 F 11 350
Data
lines <- "
A,B,C
A,10,200
A,250,350
B,5,220
B,230,260
C,1,100
D,20,50
E,1,10
F,11,90
F,100,200
F,210,350"
df <- read.table(text = lines, header = T, sep = ",")
Related Topics
R - Test If a String Vector Contains Any Element of Another List
How to Change the Default Colors in Plotly Chart
How to Change the Spacing Between Legend Items in Ggplot2
Add Row to a Data Frame With Total Sum for Each Column
Replacing Nas With Latest Non-Na Value
How to Prevent Ifelse() from Turning Date Objects into Numeric Objects
Select/Assign to Data.Table When Variable Names Are Stored in a Character Vector
Filtering a Data Frame by Values in a Column
Create Counter With Multiple Variables
Global and Local Variables in R
Concatenating Two Text Columns in Dplyr
Filter a Data Frame According to Minimum and Maximum Values
Remove Total Value for One Column in Powerbi
How to Generate a Histogram for Each Column of My Table
Subset Rows Corresponding to Max Value by Group Using Data.Table
Does Ifelse Really Calculate Both of Its Vectors Every Time? Is It Slow