Pandas - Find all rows with a specific value and keep all rows with matching column value

Question

I have a dataframe with two columns, Date and Name. I want to search for a specific name and get the different Dates on which that name occurs. Then, I only want to keep those rows that have those dates. This is my data:

Meeting Dates	Name
1746-06-02	Sa MajestÃ© (#p32)
1746-06-02	Maupertuis (#p4)
1746-06-02	Jordan (#p31)
1746-06-09	Maupertuis (#p4)
1746-06-09	Voltaire (#p37)
1746-06-09	de la Condamine (#p38)
1746-06-09	SÃ¼ssmilch (#p16)
1746-06-09	Sa MajestÃ© (#p32)
1746-06-09	Formey (#p27)
1746-06-16	Marggraf (#p20)
1746-06-23	Dohna (#p39)
1746-06-23	Euler (#p10)

I have used the following code to find all occurrences of my name:

df["Name"].value_counts()["Sa Majesté (#p32)"]

I know what to store the corresponding dates but I am unsure how. Once I have those dates, I plan to store the values and filter through my original dataframe and keep only those rows with those dates.

I would really appreciate any help/pointers with this.

Naveed · Accepted Answer · 2022-09-15 18:23:02Z

2

IIUC, is this what you're looking for? a function that takes a name and return the filtered dataset?


def find_name(name):
    return df.loc[df['Name'].eq(name)]
find_name('Sa MajestÃ© (#p32)')

OR


def find_name(name):
    return df.loc[df['Name'].str.contains(name, regex=False)] 
find_name('(#p32)')

Meeting Dates   Name
0   1746-06-02  Sa MajestÃ© (#p32)
7   1746-06-09  Sa MajestÃ© (#p32)

OR

if you just need the list of dates


def find_name(name):
    return df.loc[df['Name'].eq(name)]['Meeting Dates'].to_list()

find_name('Sa MajestÃ© (#p32)')

['1746-06-02 ', '1746-06-09 ']

edited Sep 15, 2022 at 18:23

answered Sep 15, 2022 at 15:43

Naveed

11.7k2 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

noahjb Over a year ago

Thank you so much. For added usefulness, is there a way to replace eq to be something that contains the name parameter, not necessarily just equalling it?

Naveed Over a year ago

not following you completely. Did you meant to pass a list of names? if so, then yes, you can pass the list of names and then use isin to find rows., matching the names def find_name(name): return df.loc[df['Name'].isin(name)] find_name(['Sa MajestÃ© (#p32)'])

noahjb Over a year ago

Ah sorry I did not make it quite clear. I mean instead of passing the full value of the cell in find_name(name) we pass a string that might be part of the value. For example: find_name("(#p32)")

Naveed Over a year ago

yes. use contains instead of eq, i updated the solution

ThePyGuy · Accepted Answer · 2022-09-15 15:43:53Z

You can filter on the string value, then get the unique for Meeting Dates column.

>>> df[df['Name'].eq("Sa MajestÃ© (#p32)")]['Meeting Dates'].unique()

array(['1746-06-02', '1746-06-09'], dtype=object)

And if you want to do it for all names, then you can use groupby and get the unique dates:

>>> df.groupby('Name')['Meeting Dates'].agg('unique')

Name
Dohna (#p39)                          [1746-06-23]
Euler (#p10)                          [1746-06-23]
Formey (#p27)                         [1746-06-09]
Jordan (#p31)                         [1746-06-02]
Marggraf (#p20)                       [1746-06-16]
Maupertuis (#p4)          [1746-06-02, 1746-06-09]
Sa MajestÃ© (#p32)        [1746-06-02, 1746-06-09]
SÃ¼ssmilch (#p16)                     [1746-06-09]
Voltaire (#p37)                       [1746-06-09]
de la Condamine (#p38)                [1746-06-09]
Name: Meeting Dates, dtype: object

sigh · Accepted Answer · 2023-01-05 19:25:24Z

My provided solution is not based on your specific dataset, however is valid for your problem. My hope is you are able to see the logic and implement in your own use case.

id	name
1	..
2	..
3	..

According to this general dataset, we are trying to find all rows with a certain value under the columns name "name".. then we take the id of those rows and then find ALL rows with those values.

import pandas as pd

df = pd.read_csv("MOCK_DATA.csv")

# FIND ALL ROWS THAT THE SECOND COLUMNS HAS NAME "Two-toed tree sloth"
tempOne = df[(df == "Two-toed tree sloth").any(axis=1)]

# SUM UP ALL OF THE ID'S THAT THIS IS TRUE
tempTwo = df[(df == "Two-toed tree sloth").any(axis=1)]["id"].to_list()

# OUTPUT ALL ROWS WITH THESE LIST ITEMS
df.loc[df["id"].isin(tempTwo)]

Collectives™ on Stack Overflow

Pandas - Find all rows with a specific value and keep all rows with matching column value

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related