filter dataframe by python line

Question

Hello, I own this Dataframe with hundreds of thousands of lines, I want to filter so that selection is only the desired lines, in case more current lines, follow an example of what I want:

the column DT_REFER represents the date that was updated, the column VERSAO the version of the current doc, DT_FIM_EXERC the date that the row represents

I want to filter through the last DT_REFER date along with the latest version of VERSAO, returning this to me:

I can’t start the search, correlate the columns.

Thank you

Answer 1

In the example below I created a small DataFrame containing two columns and four rows.

The date of 3/10/2020 repeats twice and it is she who will be filtered.

Creating the Dataframe

>>> import pandas as pd

>>> df = pd.DataFrame({"DT_REFER": ["1/10/2020", "2/10/2020", "3/10/2020", "3/10/2020"], 
                       "OUTRA": ["banana", "laranja", "abacaxi", "goiaba"]})

>>> df
    DT_REFER    OUTRA
0  1/10/2020   banana
1  2/10/2020  laranja
2  3/10/2020  abacaxi
3  3/10/2020   goiaba

Converting the column DT_REFER of string for datetime

>>> df["DT_REFER"] = pd.to_datetime(df['DT_REFER'], format="%d/%m/%Y")

>>> df
    DT_REFER    OUTRA
0 2020-10-01   banana
1 2020-10-02  laranja
2 2020-10-03  abacaxi
3 2020-10-03   goiaba

Filtering by date and playing the result for a new Dataframe

>>> new_df = df[(df["DT_REFER"] == "2020-10-03")]

>>> new_df
    DT_REFER    OUTRA
2 2020-10-03  abacaxi
3 2020-10-03   goiaba

UPDATE: In time, to find the latest date, just take the maximum amount (max) column DT_REFER

>>> recent_date = df['DT_REFER'].max()

>>> recent_date
Timestamp('2020-10-03 00:00:00')

UPDATE

Based on your comments, you can:

>>> df
    DT_REFER    OUTRA    VERSAO
0 2020-10-01   banana 2020-10-05
1 2020-10-02  laranja 2020-10-06
2 2020-10-03  abacaxi 2020-10-10
3 2020-10-03   goiaba 2020-10-03

>>> new_df = df[(df["DT_REFER"] == df["DT_REFER"].max()) & (df["VERSAO"] == df["VERSAO"].max())]

>>> new_df
    DT_REFER    OUTRA    VERSAO
2 2020-10-03  abacaxi 2020-10-10

Note that the operator & represents AND and the operator | (not used in this query, represents OR

Here has a reference if you want.

I hope it helps

Answer 2

You can use groupby + max

Data types in excel file:

df.info()
Data columns (total 7 columns):
 #   Column        Non-Null Count  Dtype         
---  ------        --------------  -----         
 0   DT_REFER      6 non-null      datetime64[ns]
 1   VERSAO        6 non-null      int64         
 2   DENON_CIA     6 non-null      object        
 3   DT_FIM_EXERC  6 non-null      datetime64[ns]
 4   CD_CONTA      6 non-null      object        
 5   DS_CONTA      6 non-null      object        
 6   VL_CONTA      6 non-null      float64   

Should your column DT_REFER is not as date you can make the conversion(in case the file I used has already made this conversion):

df['DT_REFER'] = pd.to_datetime(df['DT_REFER'], format="%d/%m/%Y")

Data frame:

df
    DT_REFER    VERSAO    DENON_CIA           DT_FIM_EXERC    CD_CONTA    DS_CONTA                                    VL_CONTA
0   2011-12-31    2       BCO BRADESCO S.A    2011-12-31      2.03.05     provisões técnicas de seguros e previdência 99112321.0
1   2012-12-31    2       BCO BRADESCO S.A    2011-12-31      2.03.05     provisões técnicas de seguros e previdência 99112321.0
2   2017-12-31    2       BCO BRADESCO S.A    2017-12-31      2.06        Outros passivos                             97816824.0
3   2018-12-31    3       BCO BRADESCO S.A    2017-12-31      2.06        Outros passivos                             97816824.0
4   2017-12-31    2       BCO BRADESCO S.A    2017-12-31      2.06.01     Outros passivos                             97816824.0
5   2018-12-31    3       BCO BRADESCO S.A    2017-12-31      2.06.01     Outros passivos                             97816824.0

Grouping and showing result:

df.groupby('CD_CONTA').max().reset_index()

Exit:

    CD_CONTA    DT_REFER    VERSAO  DENON_CIA           DT_FIM_EXERC    DS_CONTA                                    VL_CONTA
0   2.03.05    2012-12-31      2    BCO BRADESCO S.A    2011-12-31      provisões técnicas de seguros e previdência 99112321.0
1   2.06       2018-12-31      3    BCO BRADESCO S.A    2017-12-31      Outros passivos                             97816824.0
2   2.06.01    2018-12-31      3    BCO BRADESCO S.A    2017-12-31      Outros passivos                             97816824.0

---

Edit

Grouping and showing the biggest version and date:

df.groupby(['DENON_CIA','DT_FIM_EXERC','CD_CONTA','DS_CONTA','VL_CONTA'])[['VERSAO','DT_REFER']].max().reset_index()