Window functions don't work like traditional GROUP BYs. They allow you to partition data and pull any relevant column, whether it's used in the partition or not.
I'm not sure what the syntax is for PySpark, but the standard SQL would be something like this: WITH InputData AS ( SELECT 'USA' Country, 'New York' City, 9000000 Population UNION SELECT 'USA' Country, 'Miami', 6200000 Population UNION SELECT 'Ukraine' Country, 'Kyiv', 3000000 Population UNION SELECT 'Ukraine' Country, 'Kharkiv', 1400000 Population ) SELECT *, ROW_NUMBER() OVER(PARTITION BY Country ORDER BY Population DESC) PopulationRank FROM InputData; Results would be something like this: Country City Population PopulationRank Ukraine Kyiv 3000000 1 Ukraine Kharkiv 1400000 2 USA New York 9000000 1 USA Miami 6200000 2 Which you could further filter in another CTE or subquery where PopulationRank = 1. As I mentioned, I'm not sure how this translates into PySpark, but that's the general concept in SQL. On Mon, Dec 19, 2022 at 12:01 PM Oliver Ruebenacker < oliv...@broadinstitute.org> wrote: > If we only wanted to know the biggest population, max function would > suffice. The problem is I also want the name of the city with the biggest > population. > > On Mon, Dec 19, 2022 at 11:58 AM Sean Owen <sro...@gmail.com> wrote: > >> As Mich says, isn't this just max by population partitioned by country in >> a window function? >> >> On Mon, Dec 19, 2022, 9:45 AM Oliver Ruebenacker < >> oliv...@broadinstitute.org> wrote: >> >>> >>> Hello, >>> >>> Thank you for the response! >>> >>> I can think of two ways to get the largest city by country, but both >>> seem to be inefficient: >>> >>> (1) I could group by country, sort each group by population, add the >>> row number within each group, and then retain only cities with a row number >>> equal to 1. But it seems wasteful to sort everything when I only want the >>> largest of each country >>> >>> (2) I could group by country, get the maximum city population for each >>> country, join that with the original data frame, and then retain only >>> cities with population equal to the maximum population in the country. But >>> that seems also expensive because I need to join. >>> >>> Am I missing something? >>> >>> Thanks! >>> >>> Best, Oliver >>> >>> On Mon, Dec 19, 2022 at 10:59 AM Mich Talebzadeh < >>> mich.talebza...@gmail.com> wrote: >>> >>>> In spark you can use windowing function >>>> <https://sparkbyexamples.com/spark/spark-sql-window-functions/>s to >>>> achieve this >>>> >>>> HTH >>>> >>>> >>>> view my Linkedin profile >>>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/> >>>> >>>> >>>> https://en.everybodywiki.com/Mich_Talebzadeh >>>> >>>> >>>> >>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for >>>> any loss, damage or destruction of data or any other property which may >>>> arise from relying on this email's technical content is explicitly >>>> disclaimed. The author will in no case be liable for any monetary damages >>>> arising from such loss, damage or destruction. >>>> >>>> >>>> >>>> >>>> On Mon, 19 Dec 2022 at 15:28, Oliver Ruebenacker < >>>> oliv...@broadinstitute.org> wrote: >>>> >>>>> >>>>> Hello, >>>>> >>>>> How can I retain from each group only the row for which one value is >>>>> the maximum of the group? For example, imagine a DataFrame containing all >>>>> major cities in the world, with three columns: (1) City name (2) Country >>>>> (3) population. How would I get a DataFrame that only contains the largest >>>>> city in each country? Thanks! >>>>> >>>>> Best, Oliver >>>>> >>>>> -- >>>>> Oliver Ruebenacker, Ph.D. (he) >>>>> Senior Software Engineer, Knowledge Portal Network <http://kp4cd.org/>, >>>>> Flannick Lab <http://www.flannicklab.org/>, Broad Institute >>>>> <http://www.broadinstitute.org/> >>>>> >>>> >>> >>> -- >>> Oliver Ruebenacker, Ph.D. (he) >>> Senior Software Engineer, Knowledge Portal Network <http://kp4cd.org/>, >>> Flannick >>> Lab <http://www.flannicklab.org/>, Broad Institute >>> <http://www.broadinstitute.org/> >>> >> > > -- > Oliver Ruebenacker, Ph.D. (he) > Senior Software Engineer, Knowledge Portal Network <http://kp4cd.org/>, > Flannick > Lab <http://www.flannicklab.org/>, Broad Institute > <http://www.broadinstitute.org/> >
