Selecting Top X of Exploded Criteria Python Dataframe

user_16669624 0
Viewed 31

hello!

It's your friendly neighbourhood pest back with another question.

On a previous post, I was shown the use of the explode function (I'm a newbie to Python) and that resolved the issue, however I've been trying to employ it in a different way and I can't seem to get it working.

What I have is this:

[{'Name': 'Andes, The',
  'Year': 2021,
  'Score': '8',
  '2nd Score': 8.8,
  '% of People': '87%',
  'Country': 'The Netherlands',
  'Fruit': 'The Apple',
  'Export Countries': 'United States,United Kingdom',
  'Language': 'English,Japanese,French',
  'Transit Duration': 148.0,
  'Quality': 1.0,
  'Taste': 0.0,
  'Freshness': 0.0,
  'Packaging': 0.0},
 {'Name': 'Phil',
  'Year': 2021,
  'Score': '8.5',
  '2nd Score': 8.8,
  '% of People': '87%',
  'Country': 'Spain',
  'Fruit': 'The Banana',
  'Export Countries': 'United Kingdom, Germany',
  'Language': 'English,German,French,Italian',
  'Transit Duration': 118.0,
  'Quality': 1.0,
  'Taste': 0.0,
  'Freshness': 0.0,
  'Packaging': 0.0},
{'Name': 'Sarah',
  'Year': 2021,
  'Score': '9',
  '2nd Score': 8.8,
  '% of People': '89%',
  'Country': 'Greece',
  'Fruit': 'The Plum',
  'Export Countries': 'Germany,United States',
  'Language': 'English,German,French,Italian',
  'Transit Duration': 165.0,
  'Quality': 1.0,
  'Taste': 0.0,
  'Freshness': 0.0,
  'Packaging': 0.0},
{'Name': 'William',
  'Year': 2021,
  'Score': '6',
  '2nd Score': 8.8,
  '% of People': '65%',
  'Country': 'Brazil',
  'Fruit': 'Strawberries',
  'Export Countries': 'Spain,Greece',
  'Language': 'English,Spanish,French',
  'Transit Duration': 153.0,
  'Quality': 1.0,
  'Taste': 0.0,
  'Freshness': 0.0,
  'Packaging': 0.0},

or, simply, this:

Name | Year | Score | 2nd Score | % of People | Country | Fruit | Export Countries | Language | Transit Duration | Quality | Taste | Freshness | Packaging
Andes, The | 2021 | 8 | 8.8 | 87% | The Netherlands | The Apple | United States,United Kingdom | English,Japanese,French | 148.0 | 1.0 | 0.0 | 0.0 | 0.0
Phil | 2021 | 8.5 | 8.4 | 87% | Spain | The Banana | United Kingdom, Germany | English,German,French,Italian | 165.0 | 1.0 | 0.0 | 0.0 | 0.0
Sarah | 2021 | 9 | 8.3 | 89% | Greece | The Plum | Germany,United States | English,German,French,Italian | 153.0 | 1.0 | 0.0 | 0.0 | 0.0
William | 2021 | 6 | 8.8 | 65% | Brazil | Strawberries | Spain,Greece | English,Spanish,French | 153.0 | 1.0 | 0.0 | 0.0 | 0.0

Now, on the previous I was helped to separate the Languages out, and then group them accordingly while applying a mean:

(df[['Score', 'Language']]
  .assign(Language=lambda x: x.Language.str.split(','))
  .explode('Language')
  .groupby('Language')
  .Score.mean()
  .reset_index())

Which spat out:

   Language     Score
0   English  8.333333
1    French  8.333333
2    German  8.500000
3   Italian  8.500000
4  Japanese  8.000000

I have tried to then use this logic in another way, the Name column, but only select the Top x rows of each language. Just in case it matters, I don't want every language covered in the one syntax. My goal is to run them one at a time for each language.

So, for English, it would select the Top x names based on the Score sorting highest to lowest. The expected output would be something like this:

   Language  Top X
0   English  Phil
1   English  Sarah
2   English  Andes, The
3   English  William

I believe that I could utilise a combo of head(x) and .sort_values(ascending=False) if the criteria weren't concatenated with ',', so the part that I'm getting stumped on is the need for .assign(Language=lambda x: x.Language.str.split(',')), which I believe is required (happy to be wrong though!)

Anyone able to give me an assist?

Cheers

pandas dataframe python
1 Answers

I think in first step is necessary explode splitted values by , and in next step sorting by both columns for also ordering by Language by DataFrame.sort_values with GroupBy.head:

df1 = df.assign(Language=lambda x: x.Language.str.split(',')).explode('Language')

Then your solution is possible simplify:

df0 = df1.groupby('Language', as_index=False).Score.mean()

For another solution use:

N = 5
df2 = (df1.sort_values(['Language', 'Score'], ascending=False)
          .set_index('Language')
          .groupby('Language')['Name']
          .head(N)
          .reset_index(name=f'Top {N}'))
print (df2)
    Language       Top 5
0    Spanish     William
1   Japanese  Andes, The
2    Italian       Sarah
3    Italian        Phil
4     German       Sarah
5     German        Phil
6     French       Sarah
7     French        Phil
8     French  Andes, The
9     French     William
10   English       Sarah
11   English        Phil
12   English  Andes, The
13   English     William
user_29010020
Related