Building a new Pandas DataFrame based on dates from another DataFrame

user_11695044 0
Viewed 152

My title is not great because I'm having trouble articulating my question. Basically, I have a DateFrame with transactional data consisting of a few DateTime columns and a value column. I need to apply filters to the dates and sum the resulting values in a new DataFrame.

Here is a simplified version of my DateFrame df:

    Sched Week  Ship Week   Ready Week  vals
0   2021-01-04  2021-01-11  2021-01-04  10
1   2021-01-04  2021-01-11  2021-01-04  10
2   2021-01-04  2021-01-04  2021-01-04  2
3   2021-01-07  2021-01-18  2021-01-04  9
4   2021-01-12  2021-01-18  2021-01-11  1
5   2021-01-13  2021-01-11  2021-01-11  6
6   2021-01-13  2021-01-11  2021-01-11  4
7   2021-01-13  2021-01-25  2021-01-11  8
8   2021-01-15  2021-01-25  2021-01-18  4
9   2021-01-19  2021-01-25  2021-01-18  5
10  2021-01-19  2021-01-25  2021-01-18  6
11  2021-01-21  2021-01-25  2021-01-18  10
12  2021-01-21  2021-01-25  2021-01-18  6

The new DataFrame df_result I want to create should look like this based on the values in df. The Sched Week column in this DataFrame is simply df['Sched Week'].unique() and foo is the sum of df['values'] for the rows that meet the conditions below.

    Sched Week  foo
0   2021-01-04  20
1   2021-01-07  29
2   2021-01-12  10
3   2021-01-13  18
4   2021-01-15  18
5   2021-01-19  23
6   2021-01-21  39

And here is the basic logic to generate the new DataFrame:

df['Sched Week'] <= df_result['Sched Week'] &
df['Ship Week'] > df_result['Sched Week'] &
df['Ready Week'] <= df_result['Sched Week']

This test needs to be performed for each row in the new df_result DataFrame and the values summed.

So, the 20 at index 0 is the sum of the values at index 0 and 1 from the original df, since those rows meet the conditions for 2021-01-04.

I have tried every way to boolean mask and groupby that I can think of but nothing I've done so far has worked.

EDIT

Here is the equivalent in Excel.

The formula in cell J3 is =SUMIFS(F:F,C:C,"<="&I3,D:D,">"&I3,E:E,"<="&I3)

DataFrames represented in Excel

pandas dataframe python
2 Answers

I kept digging and found a solution to my question with a lot of help from this answer from kait

def usr(x):
    mask = df['Sched Week'] <= x['Sched Week']
    mask &= df['Ship Week'] > x['Sched Week']
    mask &= df['Ready Week'] <= x['Sched Week']
    x['foo'] = df[mask].vals.sum()
    return x

df_result.apply(lambda x: usr(x), axis=1)
user_116950440

One option is to use use an inequality join to get the relevant rows, sum the values with a groupby before merging back to the original dataframe. The conditional_join from pyjanitor is helpful here for the non-equi merge, where we use binary search, instead of going through every row; depending on the data size, the performance may be helpful:

# pip install pyjanitor
import janitor
import pandas as pd

outcome = (
          df.conditional_join(
              dates.rename(columns={"Sched Week": "Sched"}),
              ("Sched Week", "Sched", "<="),
              ("Ship Week", "Sched", ">"),
              ("Ready Week", "Sched", "<="),
              how="right",
          )
          .groupby("Sched")
          .vals.sum()
      )


# merge back to the original dataframe
df.merge(outcome.rename('foo'), 
         left_on="Sched Week", 
         right_on="Sched", 
         how="left")


   Sched Week  Ship Week Ready Week  vals  foo
0  2021-01-04 2021-01-11 2021-01-04    10   20
1  2021-01-04 2021-01-11 2021-01-04    10   20
2  2021-01-04 2021-01-04 2021-01-04     2   20
3  2021-01-07 2021-01-18 2021-01-04     9   29
4  2021-01-12 2021-01-18 2021-01-11     1   10
5  2021-01-13 2021-01-11 2021-01-11     6   18
6  2021-01-13 2021-01-11 2021-01-11     4   18
7  2021-01-13 2021-01-25 2021-01-11     8   18
8  2021-01-15 2021-01-25 2021-01-18     4   18
9  2021-01-19 2021-01-25 2021-01-18     5   23
10 2021-01-19 2021-01-25 2021-01-18     6   23
11 2021-01-21 2021-01-25 2021-01-18    10   39
12 2021-01-21 2021-01-25 2021-01-18     6   39
user_71757130
Related