My yard survey has turned up a few Leps not seen in my state before, which I’d like to submit to Bugguide, which serves as a gateway to Moth Photographers Group. But I can’t remember which ones were rare and don’t want to slog through all my obs one by one.
Can I use a URL search to filter my observatons for:
Leps observed by me, but no one else, in West Virginia.
Leps observed by me, but fewer than 10 others, in West Virginia.
For pure curiosity:
3. Any species that I have submitted, that are rare in the location observed.
the way i would handle #1 & #2 is to download a list of Lepidoptera observations in West Virginia. then take the results into something (ex. Excel, a database, etc.) that you can use to aggregate observations by species.
for the first item, return just the species where the max observer (based on alpha order of observer login or id) = min observer = you.
for the second item, you would find the species that you have observed, then do a distinct count of observers for each of those species, returning only the ones that have a distinct count of observers less than 11 (10 + you).
for the final item, i don’t think “rare in the location observed” is something that is well-defined across all potential species. so unless you have some sort source that defines this to bump up against your list of species, there’s not really a good way to do this. you could assume that count of observations is a proxy for rarity, but then you’ll need to establish what the count threshold for that definition of rarity would be.
I had already created a project “Moths of West Virginia,” so I scrolled to the bottom of the species list and am looking for ones I recall seeing. Easiest solution at this point, hoping the memory holds. :-)
Thanks
actually, if you do #2, you can get #1, too, just by looking for cases where # of distinct observers = 1 vs distinct observers > 1.
if you were to load, say, verifiable WV Lep observations into a database (or spreadsheet that you can run some SQL against), you could run some SQL like this:
SELECT A.taxon_species_name
, MAX(taxon_id) AS taxon_id
, COUNT(DISTINCT user_login) AS observer_count
, COUNT(id) AS observation_count
, MIN(id) AS first_submitted_obs
FROM (
SELECT DISTINCT taxon_species_name
FROM observations
WHERE user_login = 'becksnyc'
) A
JOIN observations B
ON A.taxon_species_name = B.taxon_species_name
GROUP BY A.taxon_species_name
HAVING COUNT(DISTINCT user_id) < 11
ORDER BY COUNT(DISTINCT user_login) ASC
, A.taxon_species_name ASC
and that would return results that look something like this:
taxon name
taxon id
observer count
observation count
first submitted obs
Acronicta noctivaga
212445
1
1
61831014
Aethes angulatana
151807
1
1
61818257
Aethes rana
417063
1
5
61825714
Aglossa disciferalis
325918
1
1
61836448
Aglossa pinguinalis
212729
1
5
61857405
Apantesis arge
606563
1
1
72551644
Aphomia terrenella
213951
1
3
61808067
Argyrotaenia alisellana
214153
1
1
61882001
Argyrotaenia pinatubana
214161
1
3
61971429
Arugisa lutea
214368
1
3
61808075
Aterpia approximana
214542
1
1
61835450
Bactra furfurana
173472
1
2
61830998
Bedellia somnulentella
214958
1
1
68456366
Bibarrambla allenella
215194
1
2
61971418
Cabera variolaria
215615
1
1
61831037
Caenurgia chloropha
69669
1
1
61808066
Caloptilia negundella
318528
1
1
61838758
Caloptilia stigmatella
215900
1
1
61824277
Chloridea virescens
509353
1
1
59241772
Chytonix palliatricula
52906
1
1
61824290
Coleophora mayrella
321675
1
8
61838746
Condica sutor
217495
1
2
61913049
Crambodes talidiformis
217671
1
1
61857407
Cycnia oregonensis
143117
1
1
61880585
Diastictis argyralis
342848
1
3
61824272
Dichomeris inversella
218377
1
2
61836443
Dichomeris juncidella
218378
1
1
61971419
Dichomeris ligulella
179029
1
1
71940375
Dichomeris offula
218383
1
1
61978327
Eoreuma densellus
700601
1
2
61856501
Ephestia kuehniella
324640
1
1
61836450
Ephestiodes infimella
219287
1
1
68965961
Epiblema abruptana
417008
1
1
61838786
Epiblema glenni
324291
1
1
61838783
Epiblema minutana
1109554
1
1
68967782
Episimus argutana
357353
1
1
68643804
Eugnosta bimaculana
475049
1
1
61888880
Frumenta nundinella
367331
1
1
61838788
Garella nilotica
220292
1
1
61836458
Gerdana caritella
220372
1
1
61914339
Hedya chionosema
220995
1
1
61824289
Hedya separatana
220997
1
1
61838767
Holcocera chalcofrontella
221398
1
2
61836439
Homochlodes fritillaria
221438
1
1
61836464
Homorthodes lindseyi
357969
1
1
61808103
Homostinea curviliniella
325313
1
3
61818262
Hyperstrotia nana
504475
1
2
61836466
Hypomecis umbrosaria
122470
1
1
61838753
Leptostales ferruminaria
177479
1
4
61888872
Leucania incognita
223548
1
1
68502790
Lipocosmodes fuliginosalis
177342
1
5
61838789
Lygropia rivulalis
205236
1
2
61880609
Macaria aequiferaria
224250
1
1
61808072
Macrochilo louisiana
177214
1
1
61913046
Meganola phylla
332250
1
2
61825736
Metalectra richardsi
224971
1
1
61835451
Mompha eloisella
322378
1
1
61808091
Moodna ostrinella
225241
1
1
61824265
Nascia acutellus
176853
1
1
61808082
Nemapogon auropulvella
326306
1
2
61856512
Nemapogon clematella
416837
1
1
61907890
Neodactria luteolellus
143098
1
1
61836447
Niditinea orleansella
199878
1
1
61808077
Nola cereella
225986
1
1
61808062
Nycteola metaspilella
226047
1
1
61818256
Oenoe hybromella
416841
1
1
68647133
Olethreutes concinnana
148025
1
1
61882002
Olethreutes monetiferanum
636793
1
1
61882008
Omphalocera cariosa
417127
1
2
61838743
Ozarba aeria
226803
1
1
61856520
Palpita freemanalis
324620
1
1
61907884
Papaipema nebris
154228
1
3
59251211
Parapediasia decorellus
417112
1
2
61824267
Peoria longipalpella
325507
1
2
61913064
Phycitodes reliquellum
568906
1
1
61913042
Psaphida resumens
209329
1
1
72566226
Psara obscuralis
324627
1
1
61882020
Pseudogalleria inimicella
417038
1
1
61838755
Schinia arcigera
143196
1
1
61913053
Sonia canadana
231147
1
1
61838742
Sonia constrictana
417010
1
1
61825725
Spoladea recurvalis
52043
1
2
62131961
Taygete attributella
232337
1
1
61838766
Thyraylia bunteana
320966
1
2
61808101
Tinea pellionella
193928
1
1
61818280
Varneria postremella
363228
1
1
61831032
Wockia asperipunctella
453470
1
1
61971431
Acleris flavivittana
212333
2
4
2403549
Acleris placidana
823767
2
2
6910767
Acrolophus plumifrontella
212400
2
3
6910992
Agonopterix argillacea
212736
2
3
61880591
Agonopterix pulvipennella
210604
2
8
2826722
Anacampsis levipedella
416945
2
2
50595092
Anicla illapsa
213630
2
4
2612460
Anicla infecta
213631
2
5
6891797
Antaeotricha schlaegeri
213740
2
3
3247481
Caloptilia blandella
342492
2
3
54410901
Caloptilia rhoifoliella
215898
2
5
6495715
Celiptera frustulum
216354
2
2
2434693
Chionodes discoocellella
216736
2
4
61913061
Diastictis ventralis
143284
2
3
2672532
Dichomeris flavocostella
205206
2
4
6207798
Dichomeris punctipennella
218385
2
2
7328590
Dichrorampha bittana
218394
2
2
6390309
Dicymolomia julianalis
218424
2
5
4153309
Epiblema otiosana
142999
2
3
6628888
Eucosma parmatana
511560
2
5
4022240
Eupsilia vinulenta
143010
2
8
4195795
Euzophera semifuneralis
220023
2
3
3262353
Feltia subterranea
152979
2
2
56381887
Geina periscelidactylus
144071
2
2
4015092
Glaphyria fulminalis
220389
2
2
2429304
Glyphidocera juniperella
333290
2
5
6654872
Herpetogramma sphingealis
324298
2
2
2429487
Homoeosoma deceptorium
221445
2
2
2459327
Homophoberia apicosa
143613
2
4
4227015
Hypena sordidula
221970
2
2
7024716
Hypenodes fractilinea
221972
2
5
17309501
Hyperstrotia secta
222005
2
2
6892210
Idaea scintillularia
222167
2
2
4259587
Isochaetes beutenmuelleri
222331
2
2
6910902
Leucania linda
223552
2
4
13734718
Leuconycta lepidula
223564
2
2
56280394
Limnaecia phragmitella
223680
2
6
52329094
Lithophane baileyi
179726
2
3
9689532
Macrochilo absorptalis
177209
2
2
54301177
Macronoctua onusta
224312
2
2
33490406
Metalectra discalis
153304
2
3
53443430
Nigetia formosalis
225958
2
7
1870368
Oegoconia novimundi
321374
2
3
7175152
Oligia modica
147941
2
4
61857406
Papaipema cataphracta
82381
2
5
2445223
Paralobesia viteana
334338
2
3
7171588
Pelochrista derelicta
511586
2
2
61971405
Perispasta caeculalis
227476
2
2
4015477
Petrophila canadensis
201345
2
2
52456207
Phalaenostola eumelusalis
143735
2
6
50417853
Phtheochroa riscana
345629
2
2
61913052
Ponometia erastrioides
228634
2
7
3263140
Proteoteras moffatiana
228846
2
2
2408756
Pseudorthodes vecors
143094
2
2
6891532
Saucrobotys futilalis
146889
2
2
31681653
Sigela brauneata
338318
2
2
45843501
Sparganothis distincta
324666
2
2
7171443
Zanclognatha marcidilinea
311260
2
2
463759
Acleris robinsoniana
212349
3
5
2818648
Acrobasis angusella
212369
3
4
2404483
Adoxophyes furcatana
417052
3
3
16710204
Agnorisma badinodis
154478
3
7
4294433
Agnorisma bollii
155298
3
4
4389402
Allagrapha aerea
81670
3
10
4026978
Azenia obtusa
202013
3
4
6891661
Balsa tristrigella
214842
3
4
3343976
Cabera erythemaria
205194
3
3
31702046
Caloptilia violacella
324207
3
3
4061439
Catocala residua
216310
3
3
2459365
Celypha cespitana
324008
3
9
4027962
Chionodes mediofuscella
216738
3
9
2914926
Chloropteryx tepperaria
216785
3
4
15715489
Clostera inclusa
196518
3
3
7021713
Condylolomia participalis
217498
3
3
50594705
Crocidophora serratissimalis
217766
3
6
6370927
Cucullia asteroides
146625
3
4
2427495
Elophila gyralis
332646
3
4
13968589
Elophila obliteralis
335071
3
4
3951542
Endothenia hebesana
219169
3
8
8399550
Epiblema strenuana
219298
3
6
6632231
Eupsilia morrisoni
204089
3
5
2631620
Evergestis pallidata
81685
3
4
4154285
Glena cribrataria
220395
3
3
1358878
Haimbachia placidella
470638
3
4
50594758
Hulda impudens
366507
3
4
6485904
Hymenia perspectalis
142995
3
5
2350557
Hypsoropha hormos
222116
3
3
2698509
Leucania adjuta
223541
3
3
31525638
Leucania ursula
207995
3
5
1842414
Lochmaeus bilineata
224089
3
3
2709786
Macaria bisignata
146886
3
5
57753535
Macrochilo litophora
177213
3
5
14780251
Monopis longella
978666
3
8
4027473
Nedra ramosula
147093
3
4
1420033
Nematocampa resistaria
81656
3
3
6474733
Norape ovina
225994
3
4
14157614
Ogdoconta cinereola
143849
3
6
6390785
Olethreutes fasciatana
143659
3
3
5045112
Oruza albocostaliata
176174
3
4
7155995
Paectes oculatrix
84000
3
11
2873735
Pelochrista dorsisignatana
511557
3
6
4044355
Pelochrista similiana
511559
3
3
61907888
Phlogophora periculosa
124183
3
3
7720088
Plusiodonta compressipalpis
125475
3
4
4199135
Promalactis suzukiella
123611
3
6
3358031
Proteoteras aesculana
228844
3
9
10839313
Schinia rivulosa
143382
3
7
2757812
Sciota subcaesiella
230622
3
3
57176188
Selenia kentaria
129426
3
4
2921728
Tetanolita mynesalis
154333
3
12
15475579
Xylesthia pruniramiella
233841
3
3
52641887
Acronicta increta
212434
4
5
6890580
Aglossa cuprina
143608
4
5
3811183
Anageshna primordialis
213510
4
4
6405871
Apoda biguttata
214013
4
5
6910911
Arogalea cristifasciella
214195
4
4
7228105
Arta statalis
214211
4
6
14684000
Calledapteryx dryopterata
143476
4
6
2875158
Chimoptesis pennsylvaniana
417026
4
5
2634759
Chrysendeton medicinalis
363819
4
5
2429328
Chrysodeixis includens
148908
4
4
4144589
Cisthene plumbea
217046
4
7
1870581
Colocasia propinquilinea
160791
4
4
2439510
Crambidia pallida
143520
4
7
21943744
Cyclophora packardi
217969
4
5
2662044
Dargida diffusa
218139
4
6
2410829
Elaphria grata
146713
4
19
2430354
Epicallima argenticinctella
324766
4
8
2688256
Epipaschia superatalis
219354
4
9
2429416
Eudonia strigalis
146946
4
5
4022700
Eudryas unio
219719
4
4
30179811
Euzophera ostricolorella
244605
4
4
32281650
Hypena bijugalis
130010
4
5
7024614
Hypsopygia costalis
129149
4
10
4171646
Idia lubricalis
119066
4
5
7024662
Lithacodia musta
223965
4
7
7208766
Machimia tentoriferella
81689
4
7
4026530
Martyringa latipennis
224466
4
7
6207684
Natada nasoni
225460
4
4
61628321
Ochropleura implecta
143121
4
9
2719724
Orthosia hibisci
226646
4
12
2775394
Palpita magniferalis
208112
4
9
3344003
Papaipema baptisiae
82382
4
5
4170047
Pasiphila rectangulata
129223
4
5
7021588
Peridroma saucia
179339
4
6
6417440
Phaecasiophora niveiguttana
325858
4
7
4045031
Plutella xylostella
126765
4
8
2826730
Pococera asperatella
321469
4
13
7020382
Polygrammodes flavidalis
143017
4
11
2429495
Pyrausta bicoloralis
205301
4
8
2751830
Rusicada privata
554232
4
12
54603789
Timandra amaturaria
142994
4
4
4199239
Vitula edmandsii
233590
4
5
2461209
Zale galbanata
207983
4
7
4044554
Zanclognatha lituralis
143027
4
4
26750061
Agrotis ipsilon
126276
5
17
2426515
Athetis tarda
214552
5
7
4022103
Baileya ophthalmica
143525
5
6
3977967
Caenurgina erechtea
126640
5
11
463741
Cerastis tenebrifera
216431
5
9
2775225
Choristoneura parallela
216808
5
12
6642141
Clepsis peritana
132789
5
13
4009507
Coryphista meadii
143201
5
18
1367173
Crocidophora tuberculalis
217767
5
9
2429502
Cydia latiferreana
148907
5
8
2664535
Dasychira obliquata
218152
5
7
1551644
Glenoides texanaria
220398
5
14
59214927
Hypena deceptalis
143654
5
6
2410909
Hypena manalis
221967
5
8
4021666
Idaea bonifata
323057
5
16
2389218
Lophosis labeculata
177241
5
5
6684942
Mocis texana
225188
5
17
2411524
Morrisonia latex
176927
5
6
4044448
Orthodes majuscula
194598
5
9
2422262
Perigea xanthioides
227470
5
12
2732180
Phalaenostola larentioides
227683
5
13
2734543
Phosphila miselioides
227864
5
9
4043107
Platynota idaeusalis
146797
5
12
6096215
Pseudothyatira cymatophoroides
204699
5
7
2434334
Schizura leptinoides
230608
5
5
2403379
Spargaloma sexpunctata
231254
5
6
2430411
Spodoptera frugiperda
132468
5
7
4358256
Tetanolita floridana
232390
5
13
463753
Tosale oviplagalis
145534
5
8
463754
Trigrammia quadrinotaria
232960
5
6
6497483
Xanthorhoe ferrugata
143485
5
8
2771611
Zale horrida
233933
5
9
3035932
Acronicta interrupta
212436
6
8
2455069
Adoneta spinuloides
212510
6
11
3799685
Agriphila ruricolella
146968
6
19
4038370
Apantesis parthenice
606575
6
6
4006593
Apoda y-inversum
214015
6
8
361291
Argyrotaenia velutinana
208118
6
15
2616832
Choristoneura rosaceana
143728
6
10
4029755
Galgula partita
143119
6
18
2442581
Haploa lecontei
84038
6
12
5166826
Lacinipolia explicata
321467
6
12
2700420
Nerice bidentata
201280
6
6
7021694
Pangrapta decoralis
226889
6
8
2726655
Pero ancetaria
227512
6
6
6892391
Phigalia strigataria
227741
6
12
2704842
Plagodis phlogosaria
205241
6
9
3035935
Platynota flavedana
208090
6
14
2738418
Pleuroprucha insulsaria
143105
6
13
2740108
Plodia interpunctella
124184
6
13
7020542
Pseudohermonassa bicarnea
205065
6
7
2750396
Renia adspergillus
126992
6
10
6914644
Spragueia leo
143592
6
6
2765916
Tortricidia flexuosa
128785
6
9
3810926
Tortricidia testacea
232759
6
6
6452338
Urola nivalis
132736
6
14
2429504
Baileya australis
215225
7
10
2436493
Callopistria mollissima
199999
7
8
2625251
Catocala piatrix
143751
7
8
14887831
Dasylophia anguina
218158
7
7
2402728
Elaphria versicolor
218931
7
13
7099291
Gluphisia septentrionis
143274
7
7
6938534
Misogada unicolor
225178
7
9
3077208
Nephelodes minians
153076
7
17
4069124
Orthonama obstipata
143002
7
18
3810681
Papaipema cerussata
147320
7
10
4204468
Pseudeustrotia carneola
143118
7
18
2405340
Schizura unicornis
126207
7
7
361082
Sparganothis sulfureana
81683
7
16
2403302
Xestia dolosa
143123
7
7
31743658
Agriphila vulgivagellus
947590
8
18
2429509
Chlorochlamys chloroleucaria
149017
8
14
1870414
Chytolita morbidalis
129226
8
10
291859
Clemensia albata
81672
8
15
2426548
Emmelina monodactyla
127431
8
16
2432163
Evergestis rimosalis
359686
8
12
2429406
Fissicrambus mutabilis
143730
8
14
4009534
Herpetogramma pertextalis
143008
8
15
2429336
Horisme intestinata
205228
8
11
2696554
Idia aemula
127460
8
23
2447690
Patalene olyzonaria
227307
8
9
356052
Amorbia humerosana
213414
9
13
4048317
Anagrapha falcifera
59300
9
11
2604877
Condica vecors
217496
9
22
3247446
Heterocampa guttivitta
203462
9
10
7021773
Idia americalis
127457
9
12
2445573
Lochmaeus manteo
153443
9
11
7021758
Marimatha nigrofimbria
143181
9
21
1848847
Microcrambus biguttellus
225105
9
16
2395909
Nomophila nearctica
58750
9
17
2429498
Pantographa limata
205240
9
13
2429313
Pyrausta acrionalis
142998
9
21
2407757
Scoparia biplagialis
146963
9
13
2429417
Anavitrinella pampinaria
63750
10
15
2449925
Autographa precationis
142990
10
28
4375138
Caenurgina crassiuscula
52900
10
20
1693003
Catocala ultronia
132881
10
16
7024817
Digrammia ocellinata
218446
10
18
4032983
Euchlaena amoenaria
219627
10
11
463756
Hydrelia inornata
221596
10
13
6880045
Hypena madefactalis
143125
10
18
2410884
Iridopsis defectaria
222284
10
33
2427127
Lascoria ambigualis
146750
10
16
2434401
Palthis angulalis
128242
10
21
3059327
Xanthorhoe lacustrata
205036
10
19
1870428
(if anyone is interested in learning how to load data into a simple database, let me know.)
I really appreciate your reply. But I feel like I’m reading a foreign language. I don’t know how to “run SQL” against a database. I would need detailed explanation, step by step.
I do have Excel.
Thanks
(tip: if you don’t actually need to save the database, you can create a new in-memory database rather than creating and saving a new database.)
you can’t do a distinct count in SQL queries in Excel’s version of SQL (or at least not without some convolutions, i think). so i’m not going to explain how to do that.
it’s possible to do something similar in basic Excel + Power Pivot, but it’s more manual work than just importing data and running a query. i’ll explain the key steps below:
export data from iNaturalist. here are the settings i’m using in my example:
add a column to identify when a row has user_login=becksnyc. (below, I added a “becksnyc” header in cell I1, added a formula =IF(C2=I$1,1,0) in cell I2, and then copied the formula down the column for all rows in the dataset.)
save your data as an .xlsx file at this point. (you need it saved to properly initiate a Power Pivot.)
now select your data, and go to Insert > Pivot Table
when prompted, make sure you check the “Add this data to the Data Model” box at the bottom, and click OK. (checking the box creates adds the data as a table in Power Query and creates a Power Pivot against that data. otherwise, you would get just a plain Pivot Table, which doesn’t handle Distinct Count aggregations.)
in your Pivot Table field thing, drop taxon_species_name into Rows, and drop becksnyc, id, and user_id into Values. then change the Value Field settings for id to summarize by Count instead of Sum. for user_id, summarize by Distinct Count instead of Sum
at this point, the data that shows up in the Pivot Table is basically what you’ll need to get the 2 things you were looking for in your original post. if sum(becksnyc)=count(id), then that means the species was observed only by you. distinct count(user_id) tells you how many users (including you) observed the species. so just perform additional manipulation of the data to get your desired result.
EDIT: actually, now that i think about it again, you don’t need count(id) in the Pivot Table. if sum(becksnyc)>0 and distinct count(user_id)=1, then that means you’re the only one to have observed the species.
You could also use the “compare” tool - at least to get to your first search. You have to choose taxa that will return less than 500 species (so families of moths instead of all of Lepidoptera). Here’s all the Owlet moths & allies observed in West Virginia vs. your Owlet moths & allies observed in West Virginia. If you look at the tab for the ones in common, then anything with a “1” in both columns means that you are the only observer of that species in West Virginia.
you’re welcome. i wasn’t not in the mood to help, just not to explain SQL.
i’ve been thinking about the next tutorial that i want to make to help folks work with iNaturalist data better. i had been thinking about putting something together for SQL, but just based on what i’ve seen in this forum, i get the impression that very few people would be interested or wouldn’t really know what to do with it. so i’m not sure it’s worth my time to go down that path.
it’s kind of odd to me because SQL tends to be very fundamental for working with structured data and is very well-adopted in the world of business and technology, but then SQL hasn’t really seemed to make much of a dent in the natural sciences for whatever reason… it seems that in the science world, maybe folks are more interested in statistical analysis and so have learned R, and R provides just enough other functionality that it’s not worth it to learn other things that can do certain things better.
it’s funny because R is widely used in AI stuff, which is one of those hot career paths these days. so a lot of folks who know only SQL are learning R, but then i don’t think a lot of people who know only R are learning SQL, even though i think knowing SQL is still more likely to lead to a high-paying job, i think, since it’s just more broadly applicable.
Thanks for the ride along on your tangent. I always enjoy learning how much I don’t know. If iNat would merge in bugguide data, the point would be moot for me. I find bugguide volunteers very helpful, top of the line, but the site itself feels quite antiquated.