Get unique info for gene based on value in second column

[sr320]

How can I get unique LOC, based on retainin lowest up_evalue. Tidyverse would be cool, but bash is ok.

LOC	Cg_acc	unitpro	up_evalue
LOC109621113	lcl|NC_047559.1_prot_XP_034319074.1_1	P24043.4	0.14	
LOC117687066	lcl|NC_047559.1_prot_XP_034319086.1_2	Q2U9B0.1	5.8	
LOC117687066	lcl|NC_047559.1_prot_XP_034319086.1_2	Q2U9A2.1	4.8	
LOC117689737	lcl|NC_047559.1_prot_XP_034327644.1_3	Q9PM37.1	1.8
LOC105333279	lcl|NC_047559.1_prot_XP_034318957.1_5	A1KZ92.3	6.12e-5	
LOC105321471	lcl|NC_047559.1_prot_XP_034319098.1_6	P0CT34.1	1.6	

[kubu4]

Bash solution:

awk 'NR > 1 {print $0}' <filename> | sort -k4,4 -k1,1 | awk '!array[$1]++'

Explanation:

• awk 'NR > 1 {print $0}' <filename>: Uses awk to skip first record (i.e. line) of file.

• sort -k4,4 -k1,1: Sorts on 4th column only, then 1st column only.

• awk '!array[$1]++': Uses an awk array to compare the first field line by line. If the first field in the line doesn't match the previous record, it will keep the line and print it.

[laurahspencer]

Tidyverse option, assuming your table is saved to object df:

df %>% group_by(LOC) %>% slice(which.min(up_evalue))