Combine specific data from a pandas dataframe

I am working with a dataframe that has the following structure:

   ID     SCORE        DATE
0   123    98.5         20210520
1   123    97.3         20210518
2   123    95.7         20210430
3   456    87.6         20210312
4   789    92.1         20210228
... ... ... ...
1000   23456   84.3     20210801

My goal is to extract the ID with the highest DATE and a SCORE greater than 90. I've attempted various groupby methods but haven't had any success.

Any assistance would be greatly appreciated!

pythonpandasjupyter-notebookgroupingjupyter

Answer №1

To filter out positive values, you can use boolean indexing with pandas and the Series.gt method. Then, you can retrieve rows with the highest COD_DAY per CARD by using DataFrameGroupBy.idxmax on the groupby object. Finally, you can select these specific rows using DataFrame.loc:

df = df.loc[df[df['CALL'].gt(0)].groupby('CARD')['COD_DAY'].idxmax()]
print(df)
             CARD  CALL   COD_DAY
1            5713   1.0  20200811
2135284  73306055  12.0  20200930
2135285  73306479   9.0  20200930
2135286  73306656   3.0  20200930
2135287  73306676   1.0  20200930

If you only need one card, regardless of groups:

card = df.loc[df.loc[df['CALL'].gt(0), 'COD_DAY'].idxmax(), 'CARD']
print(card)
73306055

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Is it advisable to utilize smart pointers in C++ when working with low-level data structures such as Linked Lists? An example might be during an interview scenario

Recently, I built a templated Linked List that utilizes unique pointers internally. The process involved creating a ListNode struct to store the next node pointer and data, along with a function to convert it to a string for later printing: template < ...

pythonc++listlinked-listsmart-pointers

Getting a NoSuchElementException error while using Selenium in an iframe

I am facing a challenge with my streamlit app. I am attempting to utilize selenium within an iframe component in order to interact with a search box, but I keep encountering a "NoSuchElementException" error. Although the iframe successfully load ...

pythonseleniumselenium-webdriveriframestreamlit

Does Python have a built-in function for calculating combinations (nCr)?

Is there an existing nCr (n choose r) function incorporated in the Python math library similar to the one displayed here? https://i.stack.imgur.com/KmC1g.jpg While I know the calculation can be coded manually, I wanted to confirm its availability as a bu ...

pythonfunctionmath

What is the best way to determine if an image has been gamma encoded upon importing into numpy?

I have some confusion about the gamma encoding/decoding of images and when it is necessary to apply a gamma function. Let's take an example of an image 'boat.jpg' with color representation labeled as 'sRGB'. I assume that the pixe ...

pythonnumpyscikit-image

Python's Selenium WebDriver seems to be malfunctioning when using `driver.find_element(By.XPATH, '')` syntax

Hoping to retrieve the Full Name "Aamer Jamal" using Selenium WebDriver from the provided URL above. However, encountering an issue where a NoSuchElementException is thrown. `Check out the code snippet below: from selenium import webdriver from selenium.we ...

pythonseleniumselenium-webdriverweb-scrapingxpath

Essential Ubuntu packages required for building Python 2.7

While attempting to compile Python 2.7 on Ubuntu 10.4, I encountered an error message upon executing make: Python build completed, however the required components to build these modules were not located: _bsddb bsddb185 sunaudiodev ...

pythonubuntupackageubuntu-10.04

Transforming a string to a date while iterating through a loop

In my code, I am attempting to compare a date from a CSV file with today's date. If today's date is greater, then the output should be "no." The specific scenario involves checking if the expiry date of a card has passed today's date: # Spe ...

pythondatetime

How can a custom format structure be established for the json export feature in Scrapy? If it is possible, what is the process for doing so

As a beginner in the world of Python and Scrapy, I am struggling with the complexities of Scrapy documentation. Despite successfully creating a spider for my school project to scrape data, I am facing issues with the formatting in JSON export. Here is a sn ...

pythonjsonweb-scrapingscrapy

Selenium struggles to update dropdown menu choice

Here is the HTML code snippet in isolation: <span style="position: relative; width: 100%; display: inline-block;"> <select id="0ac88542d16d6200fb983d094f655c76_select" class="form-control"> <option value="display_value">Numbe ...

pythonhtmlseleniumselenium-webdriverdrop-down-menu

Enhance your Google Drive file management by adding download, print, and copy functionalities using Python Django Storage

Looking to modify the options for downloading, printing, and copying Google Drive files using django.core.files.storage. When adding a file to the drive with a body parameter, I can update these options using the specified parameters below. body = { ...

pythondjangogoogle-drive-api

Python code to locate images within HTML source code

Searching for images in an html source code can be tricky. I prefer using regex over the html.parser method, but I'm open to learning if you can explain it simply like you would to a child. I wish I could use beautifulsoup, but mastering the hard way ...

pythonhtmlregexpython-3.x

Exploring SQL Alchemy Query Processing with Python

I am trying to achieve dynamic Query processing with SQLAlchemy. Let's say we have a table with three fields - name, age, and city. I want to retrieve cities with names containing the characters (s, r, t). The SQLAlchemy Query I have come up with i ...

pythonsqlalchemy

Executing one python script from another python script and waiting for the execution to finish

Let me illustrate my issue with an example: Imagine I have a script called script1.py, which looks like this: script1.py for (i in range(0,10)) generate script2.py execute(script3.py) script3.py is dependent on script2.py. My goal is to create ...

pythonpython-3.3

Locate multiple elements in Selenium using a tag selector (either xpath or css)

As a beginner in web scraping, I am trying to extract prices using Selenium (Python) from a website. However, I have noticed that the price is not consistently located within the same HTML tags. Here are the different tag options where the price may be sto ...

pythonseleniumxpathweb-crawler

Python - Break a string into an array of two characters

str = "zyx wvut srqp onml opq pno lmnk" What is the best way to achieve the following output? array = ['zyx wvut', 'wvut srqp' , 'srqp onml' ,'onml opq', 'opq pno', 'pno lmnk'] ...

pythonpython-3.x

Guide on generating a tertiary column using the first two columns in a pandas dataframe

I have a table that looks like this col-x col-y abc 123 def 456 ghi 789 Given a string str = "https://{val1}.{val2}" I want to add a new column col-z as shown below col-x col-y col-z abc 123 https://abc.123 def 456 https://def.456 ...

pythonpandas

Unraveling JSON string containing additional double quotes in Python

Does anyone know how to handle parsing a poorly formatted JSON String in python? Take a look at this example: "{""key1"":""value1"",""key2"":{""subkey1"":null,"&qu ...

pythonjsonparsing

Running Python script in Node.js and pausing until completion

I am currently working on an API-Rest project using Node.js, where I need to run a python script that will create a .json file and store it in a specific directory. My goal is to wait for the python script to finish executing so that Node.js can access the ...

javascriptpythonnode.jspromise

Python loop within a loop resetting nested iterators

Currently in the process of developing a sudoku solver, and one key aspect involves extracting the values from the 3x3 sub-box. The snippet of code I have written for this is as follows: def numbers_taken_in_box(row, col, board): col = col - (col % 3) ...

pythonloopsfor-loop

Navigating through API replies

I am attempting to iterate through an API response in order to display unique, non-repeating ip-srcs and their corresponding values. I have successfully made the API call in Python and formatted the output as JSON, but I am struggling with filtering the di ...

pythonjsonapidictionary