Extracting columns values based on values in a list

Question

I need to extract values from a column in a dataframe based on the values of another column which I have extracted in a list.

import pandas as pd
data = [[1, 'john', 'kelly'], [2, 'john', 'raj'], [2, 'john', 'leonard'], [3, 'penny', 'stuart'], [3, 'penny', 'halley'], [3, 'penny', 'amy'], [4, 'sheldon', 'will'], [4, 'sheldon', 'richard']]
school = pd.DataFrame(data, columns=['teacher_id', 'teacher_name', 'student_name'])
print(school)

This is my dataframe.

   teacher_id teacher_name student_name
0           1         john        kelly
1           2         john          raj
2           2         john      leonard
3           3        penny       stuart
4           3        penny       halley
5           3        penny          amy
6           4      sheldon         will
7           4      sheldon      richard

From this data frame I have extracted the teacher_id occuring most number of times.

school.teacher_id.value_counts().head()
> 3    3
  2    2
  4    2
  1    1

Now using the above values(teacher_id) how can i get the teacher name?

Try `s=pd.DataFrame(school.teacher_id.value_counts().head()) s.merge(school.iloc[:,:2], how='left', on='teacher_id').drop_duplicates(keep='first')#get dataframe` or `s.merge(school.iloc[:,:2], how='left', on='teacher_id').drop_duplicates(keep='first')['teacher_name'].to_list()#put to list` — wwnde, Mar 25 '21 at 05:53

score 1 · Accepted Answer · answered Mar 25 '21 at 05:39

1

Instead of using:-

school.teacher_id.value_counts().head()

Use this:-

school[['teacher_id','teacher_name']].value_counts().head()

Or

you can make use of groupby() method:-

school.groupby('teacher_id')['teacher_name'].value_counts().head()

answered Mar 25 '21 at 05:39

Anurag Dabas

23,866
9
21
41

1

Nice one Anurag. +1. – Mayank Porwal Mar 25 '21 at 05:51
1

That was quite simple. Thank you! – vikingd Mar 25 '21 at 06:27

Extracting columns values based on values in a list

1 Answers1