Skip to content
Advertisement

Filtering out non-zero values unless the value is the only one within its group (SQL or R)

I have a data set of record numbers, with multiple values in a column for each record. I would like to produce a table where within each group it returns the minimum non-negative, non-NA value. But if that negative number or NA corresponds to the only row for that record, then I would like it to return NA.

I would like to get

In R I have tried:

In MySQL I have tried

However, both of these solutions drop record #2 from the table.

How do I include a clause that detects the number of rows per record and doesn’t eliminate single records?

Advertisement

Answer

One option is to replace those ‘value’ less than 0 to NA, grouped by ‘record’, slice the rows where we get the min of ‘value’ or if all are NA, get the first row

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement