Update df column values on groupby object based on condition

user_17931350 0
Viewed 39

I'm having trouble finding an efficient way to update some column values in a large pandas DataFrame.

The code below creates a DataFrame in a similar format to what I'm working with. A summary of the data: the DataFrame contains three days of consumption data with each day being split into 10 periods of measurement. Each measurement period is also recorded during four separate processes being a preliminary reading, end of day reading and two later revisions with all updates being recorded by the Last_Update column with the date.

dates = ['2022-01-01']*40 + ['2022-01-02']*40 + ['2022-01-03']*40
periods = list(range(1,11))*12
versions = (['PRELIM'] * 10 + ['DAILY'] * 10 + ['REVISE'] * 20) * 3


data = {'Date': dates,
 'Period' : periods,
 'Version': versions,
 'Consumption': np.random.randint(1, 30, 120)}
 
df = pd.DataFrame(data)
df.Date = pd.to_datetime(df.Date)

## Add random times to the REVISE Last_Update values
df['Last_Update'] = df['Date'].apply(lambda x: x + pd.Timedelta(hours=np.random.randint(1,23), minutes=np.random.randint(1,59)))
df['Last_Update'] = df['Last_Update'].where(df.Version == 'REVISE', df['Date'])

The problem is that the two revision categories are both specified by the same value: "REVISE". One of these "REVISE" values must be changed to something like "REVISE_2". If you group the data in the following way df.groupby(['Date', 'Period', 'Version', 'Last_Update'])['Consumption'].sum() you can see there are two Last_Update dates for each period in each day for REVISE. So we need to set the REVISE with the largest date to REVISE_2.

The only way I've managed to find a solution is using a very convoluted function with the apply method to test which date is larger and store its index and then change the value using loc. This ended up taking huge amount of time for small segments of the data (the full dataset is millions of rows).

I feel like there is an easy solution using groupby functions by I'm having difficulties navigating the multi index output.

Any help would be appreciated cheers.

pandas-groupby pandas dataframe python
1 Answers

We figure our the index of the max REVISE date using idxmax after some grouping, and then change the labels:

last_revised_date_idx = df[df['Version'] == 'REVISE'].groupby(['Date', 'Period'], group_keys = False)['Last_Update'].idxmax()
df.loc[last_revised_date_idx, 'Version'] = 'REVISE_2'

check the output:

df.groupby(['Date', 'Period', 'Version', 'Last_Update'])['Consumption'].count().head(20)

produces

Date        Period  Version   Last_Update        
2022-01-01  1       DAILY     2022-01-01 00:00:00    1
                    PRELIM    2022-01-01 00:00:00    1
                    REVISE    2022-01-01 03:50:00    1
                    REVISE_2  2022-01-01 12:10:00    1
            2       DAILY     2022-01-01 00:00:00    1
                    PRELIM    2022-01-01 00:00:00    1
                    REVISE    2022-01-01 10:45:00    1
                    REVISE_2  2022-01-01 22:05:00    1
            3       DAILY     2022-01-01 00:00:00    1
                    PRELIM    2022-01-01 00:00:00    1
                    REVISE    2022-01-01 17:03:00    1
                    REVISE_2  2022-01-01 19:10:00    1
            4       DAILY     2022-01-01 00:00:00    1
                    PRELIM    2022-01-01 00:00:00    1
                    REVISE    2022-01-01 15:23:00    1
                    REVISE_2  2022-01-01 18:08:00    1
            5       DAILY     2022-01-01 00:00:00    1
                    PRELIM    2022-01-01 00:00:00    1
                    REVISE    2022-01-01 12:19:00    1
                    REVISE_2  2022-01-01 18:04:00    1
user_145514260
Related