CODEWITHSUNDEEP
pythonpandasdataframeaggregate
ascripter
ascripter

Reputation: 6223

pandas: Conditionally Aggregate Consecutive Rows

I have a dataframe with a consecutive index (date for every calendar day) and a reference vector that does not contain every date (only working days).

I want to reindex the dataframe to only the dates in the reference vector with the missing data being aggregated to the latest entry before a missing-date-section (i.e. weekend data shall be aggregated together to the last Friday).

Currently I have implemented this by looping over the reversed index and collecting the weekend data, then adding it later in the loop. I'm asking if there is a more efficient "array-way" to do it.

import pandas as pd
import numpy as np
df = pd.DataFrame({'x': np.arange(10), 'y': np.arange(10)**2},
                  index=pd.date_range(start="2018-01-01", periods=10))
print(df)
ref_dates = pd.date_range(start="2018-01-01", periods=10)
ref_dates = ref_dates[:5].append(ref_dates[7:])  # omit 2018-01-06 and -07

# inefficient approach by reverse-traversing the dates, collecting the data
# and aggregating it together with the first date that's in ref_dates
df.sort_index(ascending=False, inplace=True)
collector = []
for dt in df.index:
    if collector and dt in ref_dates:
        # data from previous iteration was collected -> aggregate it and reset collector
        # first append also the current data
        collector.append(df.loc[dt, :].values)
        collector = np.array(collector)

        # applying aggregation function, here sum as example
        aggregates = np.sum(collector, axis=0)

        # setting the new data
        df.loc[dt,:] = aggregates

        # reset collector
        collector = []

    if dt not in ref_dates:
        collector.append(df.loc[dt, :].values)

df = df.reindex(ref_dates)
print(df)

Gives the output (first: source dataframe, second: target dataframe)

            x   y
2018-01-01  0   0
2018-01-02  1   1
2018-01-03  2   4
2018-01-04  3   9
2018-01-05  4  16
2018-01-06  5  25
2018-01-07  6  36
2018-01-08  7  49
2018-01-09  8  64
2018-01-10  9  81
             x   y
2018-01-01   0   0
2018-01-02   1   1
2018-01-03   2   4
2018-01-04   3   9
2018-01-05  15  77   # contains the sum of Jan 5th, 6th and 7th
2018-01-08   7  49 
2018-01-09   8  64
2018-01-10   9  81

Upvotes: 3

Views: 885

Answers (1)

PdevG
PdevG

Reputation: 3677

Still has a list comprehension loop, but works.

import pandas as pd
import numpy as np

# Create dataframe which contains all days
df = pd.DataFrame({'x': np.arange(10), 'y': np.arange(10)**2},
                  index=pd.date_range(start="2018-01-01", periods=10))

# create second dataframe which only contains week-days or whatever dates you need.
ref_dates = [x for x in df.index if x.weekday() < 5]

# Set the index of df to a forward filled version of the ref days
df.index = pd.Series([x if x in ref_dates else float('nan') for x in df.index]).fillna(method='ffill')

# Group by unique dates and sum
df = df.groupby(level=0).sum()

print(df)

Upvotes: 1

Related Questions