I have a spreadsheet with info about 10,000 bird photos. Each row gives the bird name, with a rating from 0 to 5 for the quality of the photo.
I want to extract a list of unique bird names, and for each bird, I want the highest quality rating among photos of that bird. Armed with this list, I can go on a bird photography trip, prioritising which birds I am likely to see, that need a better photo.
I don’t think I can do this with filters, and no-duplicates - does anyone have a solution?
Hello,
I’d create a pivot table from your data using Max function for Rating in **Data Fields. See the following layout of the pivot table in the following sample file (the result is in second sheet named Pivot Table_Sheet1_1)
PivotTable-of-PhotoRatings.ods
Hope that helps.
If the answer is correct or helped you to solve your problem, please click the check mark (
) next to the answer.
Great - I hadn’t seen that function inside Pivot before. And it took me a few minutes to work out how to set Max as the function on Rating.
Double click on Rating and set User Defined to Max button THEN and only THEN drag the Rating into the Data Fields
However, one extra challenge - for any bird, the pivot table gives me the max Photo rating, but it doesnt tell me which photo has the max rating. Ideally I would l like to see a output table with 3 columns:
Bird Name, Max-Rating, Name of Photo that has that Max-Rating
Is this possible?
Hmm - I don’t understand the purpose of that requirement according to bird photography trip, prioritising which birds I am likely to see, that need a better photo. In other words. You can’t take the same photo a second time to get a better quality, so what will that additional info provide? And another issue: Is there a guarantee that highest rating for a given bird is only arwarded to a single photo?
Anyway - I don’t see a method to get the name of the photo(s) having the max rating within the same pivot. Name / No of a photo would introduce a new category, having its own Max - Rating and you’ll end up with your orignal table reformatted / categorized). I assume you’d need to some lookup of the max rating in your original data range.
Instead of “bird” I should say “species of bird”. The 10,000+ photos I have taken come from many trips to many countries and locations, so the same bird species can be seen and photographed on many occasions. I might have 5 pictures of “Senegal Lapwing” , some rubbish, some distant or in poor light. Or I might have several top quality shots all rated “5”.
When planning a trip, I will look at the list of bird species I might see there. Top priority is anything I haven’t seen before, next are birds I haven’t photographed at all, and then birds where I only have a poor shot on record.
When going to a location where I might see another Senegal Lapwing, its good to know the best rated photo I have taken so far and where I took it. If its only rated 2 then I will try extra hard to get a better shot. Given there are likely to be several hundred birds possible in a good location like Oaxaca, Mexico I want as much as possible info in a spreadsheet table before I go.
I hope that helps.
Having a PT with a species and correspinding max rating of its photos, you have enough information to start. You can sort the PT data to find the poorest max values; then you may sort your original data by rating, and filter it to only show you the chosen poorest species - giving you 100% available information about all its available photos… Having some single photo reference next to max rating (out of possibly many with that max rating, and one of many in total) is just a fake instead of “as much as possible info in a spreadsheet table before I go”.
Good points - that should work - many thanks for the clear thinking!
Just a note to this comment:
However, one extra challenge - for any
bird, the pivot table gives me the max
Photo rating, but it doesnt tell me
which photo has the max rating.
Ideally I would l like to see a output
table with 3 columns: Bird Name,
Max-Rating, Name of Photo that has
that Max-Rating Is this possible?
Yes, you can activate Enable drill to details option while creating Pivot table (PT) and drag also Photo Id field to Row Fields:
This gives you PT with three columns, double click on Birdname field hides/expands details about specific bird’s photo ID’s and their ratings:
Or
You can create PT without dragging Photo Id field to Row Fields, but with Enable drill to details option activated:
This will create PT with two columns, but double click on Birdname opens new dialog window:
Select detail you want to inspect and you get the same result:
— Edit 2019-11-13 —
And, of course, you can have several columns/layers to drill to, so the detailed information is accessible in a few clicks:
As meant in my comment to my own answer: Introducing “Photo No / Photo ID” ends up in a pivot table containing the same information than the original data, since each "Photo ID* creates its own category and why @mikekaganski called that just a fake (as far is I understood his comment).
