R - Find first non zero elements per groups in data.table

Question

I have a data table in R that looks like this:

    State  City Maturing  Soil 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
 1:    PR CityA    Early SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 40 40 40 40
 2:    PR CityA    Early  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 20 20 20 20 20 20
 3:    PR CityA    Early  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 40 30 30 20 20 20 20 20 20
 4:    PR CityA   Medium SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 40 40 40 40 40
 5:    PR CityA   Medium  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 30 20 20 20 20 20 20 30
 6:    PR CityA   Medium  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 30 30 20 20 20 20 20 20 20
 7:    PR CityA     Late SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 30 30 30 30 40 40  0
 8:    PR CityA     Late  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 30 30 20 20 20 20 20 30 30
 9:    PR CityA     Late  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 30 20 20 20 20 20 20 20 20 20
10:    PR CityB    Early SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 30 30 30 30 30 30
11:    PR CityB    Early  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 30 30 20 20 20 20 20 20 20
12:    PR CityB    Early  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 30 20 20 20 20 20 20 20 20 20
13:    PR CityB   Medium SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 30 30 30 20 20 30 30 30
14:    PR CityB   Medium  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 30 30 20 20 20 20 20 20 20 20
15:    PR CityB   Medium  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 20 20 20 20 20 20 20 20 20 20
16:    PR CityB     Late SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 30 30 20 20 20 20 20 30 40
17:    PR CityB     Late  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 20 20 20 20 20 20 20 20 20 20
18:    PR CityB     Late  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 20 20 20 20 20 20 20 20 20 20 20
19:    RS CityC    Early SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 40 40  0
20:    RS CityC    Early  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 30 30 30 30 30 40
21:    RS CityC    Early  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 30 30 30 20 30 20 30 30
22:    RS CityC   Medium SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 40 40 40  0  0
23:    RS CityC   Medium  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 30 30 30 30 30 30  0
24:    RS CityC   Medium  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 30 30 30 20 20 20 30 40
25:    RS CityC     Late SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 40 30 30 30 40  0  0
26:    RS CityC     Late  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 30 30 30 20 30 30 40  0
27:    RS CityC     Late  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 20 20 20 20 20 20 30 40
28:    RS CityD    Early SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 30 30 30 30 30 30 30 40
29:    RS CityD    Early  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 20 20 20 20 20 20 20 20 30
30:    RS CityD    Early  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 20 20 20 20 20 20 20 20 20 20
31:    RS CityD   Medium SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 30 30 30 30 20 20 30 30  0
32:    RS CityD   Medium  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 20 20 20 20 20 20 20 20 40
33:    RS CityD   Medium  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 20 20 20 20 20 20 20 20 20 20
34:    RS CityD     Late SANDY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 40 30 20 20 20 20 20 30 40  0
35:    RS CityD     Late  SILT 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 30 20 20 20 20 20 20 20 30  0
36:    RS CityD     Late  CLAY 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 20 20 20 20 20 20 20 20 20 30
    State  City Maturing  Soil 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

Columns named 1 to 36 represent ten-day periods in the year where planting is recommended. I want to extract the earliest recommended planting date for each State, Maturing and Soil. In other words, I want to extract the column name for the first ten-day period which is not 0 for the aforementioned groups.

For the above example, the expected outcome would be:

State  Maturing  Soil   Earliest
PR     Early     SANDY  30
PR     Early     SILT   26
PR     Early     CLAY   26
PR     Medium    SANDY  27
PR     Medium    SILT   26
PR     Medium    CLAY   26
PR     Late      SANDY  26
PR     Late      SILT   26
PR     Late      CLAY   26
RS     Early     SANDY  28
RS     Early     SILT   27
RS     Early     CLAY   27
RS     Medium    SANDY  27
RS     Medium    SILT   27
RS     Medium    CLAY   27
RS     Late      SANDY  27
RS     Late      SILT   27
RS     Late      CLAY   27

How can I achieve this?

Data can be reproduced with the following code:

library(data.table)

dat <- data.table(structure(list(State = rep(c("PR","RS"), each=18),
City = rep(c("CityA","CityB","CityC","CityD"), each=9),
Maturing = rep(rep(c("Early","Medium","Late"), each=3), 4),
Soil = rep(c("SANDY","SILT","CLAY"), 12), `1` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `2` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `3` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `4` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `5` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `6` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `7` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `8` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `9` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `10` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `11` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `12` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `13` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `14` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `15` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `16` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `17` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `18` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `19` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `20` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `21` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `22` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `23` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `24` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `25` = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `26` = c(0L, 0L, 40L, 0L, 0L, 
40L, 0L, 40L, 30L, 0L, 40L, 30L, 0L, 30L, 30L, 40L, 30L, 20L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), `27` = c(0L, 0L, 
40L, 0L, 0L, 40L, 0L, 40L, 30L, 0L, 40L, 30L, 40L, 30L, 20L, 
40L, 20L, 20L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 30L, 20L, 
40L, 30L, 20L, 40L, 30L, 20L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L), `28` = c(0L, 0L, 40L, 0L, 40L, 30L, 0L, 30L, 20L, 0L, 30L, 
20L, 40L, 30L, 20L, 30L, 20L, 20L, 0L, 0L, 40L, 0L, 0L, 40L, 
0L, 40L, 40L, 30L, 20L, 20L, 30L, 20L, 20L, 30L, 20L, 20L, 20L, 
20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L), `29` = c(0L, 0L, 30L, 
0L, 30L, 30L, 40L, 30L, 20L, 0L, 30L, 20L, 30L, 20L, 20L, 30L, 
20L, 20L, 0L, 40L, 30L, 0L, 30L, 30L, 40L, 30L, 20L, 30L, 20L, 
20L, 30L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 
20L, 20L, 20L), `30` = c(0L, 30L, 30L, 40L, 20L, 20L, 30L, 20L, 
20L, 40L, 20L, 20L, 30L, 20L, 20L, 20L, 20L, 20L, 0L, 40L, 30L, 
40L, 30L, 30L, 40L, 30L, 20L, 30L, 20L, 20L, 30L, 20L, 20L, 20L, 
20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L), `31` = c(40L, 
20L, 20L, 40L, 20L, 20L, 30L, 20L, 20L, 30L, 20L, 20L, 30L, 20L, 
20L, 20L, 20L, 20L, 0L, 30L, 30L, 40L, 30L, 30L, 30L, 30L, 20L, 
30L, 20L, 20L, 30L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 
20L, 20L, 20L, 20L, 20L), `32` = c(40L, 20L, 20L, 40L, 20L, 20L, 
30L, 20L, 20L, 30L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 40L, 
30L, 20L, 40L, 30L, 20L, 30L, 20L, 20L, 30L, 20L, 20L, 20L, 20L, 
20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L
), `33` = c(40L, 20L, 20L, 40L, 20L, 20L, 30L, 20L, 20L, 30L, 
20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 40L, 30L, 30L, 40L, 30L, 
20L, 30L, 30L, 20L, 30L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 
20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L), `34` = c(40L, 20L, 
20L, 40L, 20L, 20L, 40L, 20L, 20L, 30L, 20L, 20L, 30L, 20L, 20L, 
20L, 20L, 20L, 40L, 30L, 20L, 40L, 30L, 20L, 40L, 30L, 20L, 30L, 
20L, 20L, 30L, 20L, 20L, 30L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 
20L, 30L, 20L, 20L), `35` = c(40L, 20L, 20L, 40L, 20L, 20L, 40L, 
30L, 20L, 30L, 20L, 20L, 30L, 20L, 20L, 30L, 20L, 20L, 40L, 30L, 
30L, 0L, 30L, 30L, 0L, 40L, 30L, 30L, 20L, 20L, 30L, 20L, 20L, 
40L, 30L, 20L, 20L, 20L, 20L, 30L, 20L, 20L, 30L, 20L, 20L), 
    `36` = c(40L, 20L, 20L, 40L, 30L, 20L, 0L, 30L, 20L, 30L, 
    20L, 20L, 30L, 20L, 20L, 40L, 20L, 20L, 0L, 40L, 30L, 0L, 
    0L, 40L, 0L, 0L, 40L, 40L, 30L, 20L, 0L, 40L, 20L, 0L, 0L, 
    30L, 30L, 20L, 20L, 40L, 30L, 20L, 0L, 30L, 20L)), row.names = c(NA, 
-45L), class = "data.frame"))

user_14314520 · Answer

Updated

I have modified my code to have your desired result, however this is not your preferred data.table solution. The point we didn't get is that the combinations of grouping variables are not always unique so this was not basically a row-wise operation and needed grouping while considering the earliest date in each group. The only problem with my output is the orders of levels for Maturing and Soil variables which differ from the order obs in your output. It can be fixed.

library(dplyr)
library(tidyr)
library(purrr)

dat %>%
  mutate(Earliest = pmap(dat %>% 
                           select(`1`:`36`), ~ names(c(...))[c(...) != 0][1])) %>%
  select(-c(`1`:`36`)) %>%
  unnest(cols = c(Earliest)) %>% 
  group_by(State, Maturing, Soil) %>% 
  mutate(Earliest = as.numeric(Earliest)) %>%
  summarise(across(Earliest, ~ min(.x))) %>%
  ungroup()


# A tibble: 18 x 4
   State Maturing Soil  Earliest
            
 1 PR    Early    CLAY        26
 2 PR    Early    SANDY       30
 3 PR    Early    SILT        26
 4 PR    Late     CLAY        26
 5 PR    Late     SANDY       26
 6 PR    Late     SILT        26
 7 PR    Medium   CLAY        26
 8 PR    Medium   SANDY       27
 9 PR    Medium   SILT        26
10 RS    Early    CLAY        27
11 RS    Early    SANDY       28
12 RS    Early    SILT        27
13 RS    Late     CLAY        27
14 RS    Late     SANDY       27
15 RS    Late     SILT        27
16 RS    Medium   CLAY        27
17 RS    Medium   SANDY       27
18 RS    Medium   SILT        27

user_13321647 · Answer

Inspired by the solution of Anoushiravan (very well done) I tried a solution with dyplr and tidyr alone and keep the desired order of the OP.

Here is my solution (with comments):

library(dplyr)
library(tidyr)

# relevel Soil for same Output as desired
order_Soil <- c("SANDY", "SILT", "CLAY")

dat1 <- dat %>%
  select(where(~ any(. != 0)), -City) %>%           #remove all 0 columns
  pivot_longer(                                     #bring in longformat
    cols = c(`26`:`36`), 
    names_to = "Names",
    values_to = "Values"
  ) %>% 
  mutate(Soil = factor(Soil,                        #to keep the desired order
                       levels = order_Soil)) %>% 
  filter(Values != 0) %>%                           #remove rows with any 0
  group_by(State, Maturing, Soil) %>%
  summarise(Earliest = min(Names))                  #Summarize the Earliest

Output:

   State Maturing Soil  Earliest
            
 1 PR    Early    SANDY 30      
 2 PR    Early    SILT  26      
 3 PR    Early    CLAY  26      
 4 PR    Late     SANDY 26      
 5 PR    Late     SILT  26      
 6 PR    Late     CLAY  26      
 7 PR    Medium   SANDY 27      
 8 PR    Medium   SILT  26      
 9 PR    Medium   CLAY  26      
10 RS    Early    SANDY 28      
11 RS    Early    SILT  27      
12 RS    Early    CLAY  27      
13 RS    Late     SANDY 27      
14 RS    Late     SILT  27      
15 RS    Late     CLAY  27      
16 RS    Medium   SANDY 27      
17 RS    Medium   SILT  27      
18 RS    Medium   CLAY  27

user_8380272 · Answer

You could do:

is.na(dat) <- dat == 0
dat[, cbind(.SD[,1:4], Earliest = dplyr::coalesce(!!!.SD[,-(1:4)]))]

   State  City Maturing  Soil Earliest
 1:    PR CityA    Early SANDY       40
 2:    PR CityA    Early  SILT       30
 3:    PR CityA    Early  CLAY       40
 4:    PR CityA   Medium SANDY       40
 5:    PR CityA   Medium  SILT       40
 6:    PR CityA   Medium  CLAY       40
 7:    PR CityA     Late SANDY       40
 8:    PR CityA     Late  SILT       40
 9:    PR CityA     Late  CLAY       30
10:    PR CityB    Early SANDY       40
11:    PR CityB    Early  SILT       40
12:    PR CityB    Early  CLAY       30

EDIT:

if you need the column numbers: then you could do,

dat[, cbind(.SD[,1:4], Earliest = max.col(.SD[,-(1:4)]>0,ties.method = "first"))]
    State  City Maturing  Soil Earliest
 1:    PR CityA    Early SANDY       31
 2:    PR CityA    Early  SILT       30
 3:    PR CityA    Early  CLAY       26
 4:    PR CityA   Medium SANDY       30
 5:    PR CityA   Medium  SILT       28
 6:    PR CityA   Medium  CLAY       26
 7:    PR CityA     Late SANDY       29
 8:    PR CityA     Late  SILT       26
 9:    PR CityA     Late  CLAY       26
10:    PR CityB    Early SANDY       30

user_7522764 · Answer

Solution:

select the name of first non-zero element of each row
group by State, Maturing, Soil
set the min of each group as Earliest

dat[, Earliest := apply(
    .SD[, -(1:4)], 1, function(x) as.numeric(names(which(x != 0)[1]))
    )][,.(Earliest = min(Earliest)),by = .(State, Maturing, Soil)]

Output:

    State Maturing  Soil Earliest
 1:    PR    Early SANDY       30
 2:    PR    Early  SILT       26
 3:    PR    Early  CLAY       26
 4:    PR   Medium SANDY       27
 5:    PR   Medium  SILT       26
 6:    PR   Medium  CLAY       26
 7:    PR     Late SANDY       26
 8:    PR     Late  SILT       26
 9:    PR     Late  CLAY       26
10:    RS    Early SANDY       28
11:    RS    Early  SILT       27
12:    RS    Early  CLAY       27
13:    RS   Medium SANDY       27
14:    RS   Medium  SILT       27
15:    RS   Medium  CLAY       27
16:    RS     Late SANDY       27
17:    RS     Late  SILT       27
18:    RS     Late  CLAY       27

user_3521006 · Answer

Here's a data.table approach:

dat_long = melt(
  data = dat, 
  measure.vars = as.character(1:36), # column names to be melted
  variable.name = 'period', 
  variable.factor = FALSE
)

res = dat_long[
  value > 0,                              # we're looking for non-zero periods
  .(Earliest = min(as.integer(period))),  # extract the minimum (first) period
  by = .(State, Maturing, Soil)           # grouping variables
]

res
#    State Maturing  Soil Earliest
# 1:    PR    Early  CLAY       26
# 2:    PR   Medium  CLAY       26
# 3:    PR     Late  SILT       26
# 4:    PR     Late  CLAY       26
# 5:    PR    Early  SILT       26
# 6:    PR   Medium  SILT       26
# 7:    PR     Late SANDY       26
# 8:    PR   Medium SANDY       27
# 9:    RS    Early  SILT       27
# 10:    RS    Early  CLAY       27
# 11:    RS   Medium SANDY       27
# 12:    RS   Medium  SILT       27
# 13:    RS   Medium  CLAY       27
# 14:    RS     Late SANDY       27
# 15:    RS     Late  SILT       27
# 16:    RS     Late  CLAY       27
# 17:    RS    Early SANDY       28
# 18:    PR    Early SANDY       30

Bottom line: convert your data to long format and the computation becomes very easy (and will most likely be more efficient in long format).

user_2884859 · Answer

Though many of the above approaches are very good, I found that this can simply be done, by using max.col. Here's one using dplyr only

library(dplyr)
dat %>%
  mutate(Earliest = max.col(.[, -c(1:4)] > 0, ties.method = "first")) %>%
  group_by(State, Maturing, Soil) %>%
  summarise(Earliest = min(Earliest), .groups = 'drop')

# A tibble: 18 x 4
   State Maturing Soil  Earliest
            
 1 PR    Early    CLAY        26
 2 PR    Early    SANDY       30
 3 PR    Early    SILT        26
 4 PR    Late     CLAY        26
 5 PR    Late     SANDY       26
 6 PR    Late     SILT        26
 7 PR    Medium   CLAY        26
 8 PR    Medium   SANDY       27
 9 PR    Medium   SILT        26
10 RS    Early    CLAY        27
11 RS    Early    SANDY       28
12 RS    Early    SILT        27
13 RS    Late     CLAY        27
14 RS    Late     SANDY       27
15 RS    Late     SILT        27
16 RS    Medium   CLAY        27
17 RS    Medium   SANDY       27
18 RS    Medium   SILT        27

Moreover, if both values and index are required, that can also be done in dplyr only, with the following syntax

dat %>%
  mutate(Earliest = names(.[, -(1:4)])[max.col(.[, -c(1:4)] > 0, ties.method = "first")]) %>%
  rowwise() %>%
  mutate(E_val = get(Earliest)) %>%
  group_by(State, Maturing, Soil) %>%
  summarise(E_val = first(E_val[Earliest == min(Earliest)]), 
            Earliest = min(Earliest), 
            .groups = 'drop')

# A tibble: 18 x 5
   State Maturing Soil  E_val Earliest
             
 1 PR    Early    CLAY     40 26      
 2 PR    Early    SANDY    40 30      
 3 PR    Early    SILT     40 26      
 4 PR    Late     CLAY     30 26      
 5 PR    Late     SANDY    40 26      
 6 PR    Late     SILT     40 26      
 7 PR    Medium   CLAY     40 26      
 8 PR    Medium   SANDY    40 27      
 9 PR    Medium   SILT     30 26      
10 RS    Early    CLAY     20 27      
11 RS    Early    SANDY    30 28      
12 RS    Early    SILT     30 27      
13 RS    Late     CLAY     20 27      
14 RS    Late     SANDY    40 27      
15 RS    Late     SILT     30 27      
16 RS    Medium   CLAY     20 27      
17 RS    Medium   SANDY    40 27      
18 RS    Medium   SILT     30 27

R - Find first non zero elements per groups in data.table

6 Answers