Ask Your Question
0

Maximum value for each distinct name

asked 2019-11-13 12:25:42 +0100

Aliks gravatar image

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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
4

answered 2019-11-13 13:36:52 +0100

Opaque gravatar image

updated 2019-11-13 14:50:41 +0100

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)

C:\fakepath\PivotTable-of-PhotoRatings.ods

image description

Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more

Comments

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

Aliks gravatar imageAliks ( 2019-11-13 15:23:17 +0100 )edit

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?

Aliks gravatar imageAliks ( 2019-11-13 15:37:32 +0100 )edit

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.

Opaque gravatar imageOpaque ( 2019-11-13 15:52:34 +0100 )edit

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 ...(more)

Aliks gravatar imageAliks ( 2019-11-13 16:06:25 +0100 )edit

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".

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-13 16:34:44 +0100 )edit

Good points - that should work - many thanks for the clear thinking!

Aliks gravatar imageAliks ( 2019-11-13 16:44:15 +0100 )edit
1

answered 2019-11-13 19:56:34 +0100

updated 2019-11-13 20:26:13 +0100

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:

DrillToDetails_1

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:

DrillToDetails_2

DrillToDetails_3

Or

You can create PT without dragging Photo Id field to Row Fields, but with Enable drill to details option activated:

DrillToDetails_4

This will create PT with two columns, but double click on Birdname opens new dialog window:

DrillToDetails_5

Select detail you want to inspect and you get the same result:

DrillToDetails_6

--- 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:

DrillToDetails_7

edit flag offensive delete link more

Comments

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 @Mike Kaganski called that just a fake (as far is I understood his comment).

Opaque gravatar imageOpaque ( 2019-11-14 10:46:47 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-13 12:25:42 +0100

Seen: 240 times

Last updated: Nov 13 '19