Keep the data with latest date from a pandas dataframe

user_4132347 0
Viewed 1759

I have a csv file with company software details along with installed dates. I am reading the csv file using pandas in a dataframe. Below is the sample data containing two different software:

software_id software_name                               installed_date    software_version
8331        Intel(R) Graphics Media Accelerator Driver  2009-05-23 0:00   8.15.10.2008
8331        Intel(R) Graphics Media Accelerator Driver  2010-09-15 0:00   8.15.10.2008
8331        Intel(R) Graphics Media Accelerator Driver  2009-12-27 0:00   8.15.10.2008
8332        Wireless Switch Utility                     2009-12-22 0:00   4.3.1400.0
8332        Wireless Switch Utility                     2010-11-22 0:00   4.3.1400.0
8332        Wireless Switch Utility                     2011-01-25 0:00   4.3.1400.0

So from the above data I just need to keep one row with the latest date from each software code. For example, the output of the above file should be:

software_id     software_name                               installed_date    software_version
    8331        Intel(R) Graphics Media Accelerator Driver  2010-09-15 0:00   8.15.10.2008
    8332        Wireless Switch Utility                     2011-01-25 0:00   4.3.1400.0

How can I choose the set of rows for one software code and remove all rows but the one with the latest date and then move to the next software code until the file only has one entry per software code. I cannot hardcode the software_id to check as there are thousands of them.

My logic is to read and store the first software_id and installed_date in two variables and start reading the file line by line. Next line will check if the software_id matches the one stored in the variable and then compare the date and store the latest one in the variable. When the software_id does not match the stored software id then it means that new software_id block has started. It will then store the previous values in the dataframe and start executing next blocks and so on.

FYI - I am a pandas noob.

Thanks for all the help.

pandas csv python
2 Answers

First you need to convert the installed_date column to datetime:

df['installed_date'] = pd.to_datetime(df['installed_date'])

Then you can use one of the options below:

Option 1: sort the values on installed_date then drop_duplicates keeping only the last row per software_id.

df.sort_values('installed_date').drop_duplicates('software_id', keep='last')

Option 2: group the dataframe on softaware_id and aggregate using idxmax to get the index of most recent date per software_id group, then use loc with this index to filter the required rows:

idx = df.groupby('software_id')['installed_date'].idxmax()
df.loc[idx]

Result:

   software_id                               software_name installed_date software_version
1         8331  Intel(R) Graphics Media Accelerator Driver     2010-09-15     8.15.10.2008
5         8332                     Wireless Switch Utility     2011-01-25       4.3.1400.0
user_128331660

You can do a groupby, like this:

df.groupby("software_id", as_index=False)["software_name", "installed_date", "software_version"].max("installed_date")

It groups your rows by the software_id, keeps all the wanted columns, and keeps for each group the row having the highest installed_date.

df is the dataframe containing the content of the file.

user_82765310
Related