Home / Questions / 487634
Accepted
LopezAi
Asked: 2022-10-01 03:47:59 +0800 CST 2022-10-01 03:47:59 +0800 CST

Get date from a max value by grouping by id - Python

  • 772

I have this dataframe in Python:

enter image description here

I need to group by idCDU and get the dateValue from the maximum value for each idCDU.

This has been my attempt of several:

df_max_fecha = df_max_fecha.groupby(['idCDU', 'fechaValor'])['valor'].max().reset_index()

Any idea is welcome.

Thank you!

python

1 Answers

  1. Best Answer

    I'm pretty sure there has to be a less twisted method, but this is the only thing I could think of:

    • Do a groupby()for the "idCDU" column
    • You make an .apply(funcion)envelope of the result. The function in question will receive as a parameter each of the sub-dataframes in which the main dataframe has been grouped, so within that function you can index by the "dateValue" column and use it .valor.idxmax()to return the index (which will then be the date) of the row that has the maximum value.

    Although the idea is twisted, the implementation is a couple of lines:

    # Leer dataframe
import pandas as pd
df = pd.read_csv("file_name.csv", sep=";", index_col=0)

# Implementación de la idea
def getmax(subdf):
  return subdf.set_index("fechaValor").valor.idxmax()
  
df_max_fecha = df.groupby("idCDU").apply(getmax)

    The result df_max_fechawould come out:

    ATD-987    12/12/2019 15:04
IEC-229    16/12/2019 17:09
    • 1