Is there always a pure 'Pandas way' to provide same results as an 'outside Pandas' one does?

user95mw 0
Viewed 35

Answering a question How to mark start/end of a series of non-null and non-0 values in a column of a Pandas DataFrame? here on stackoverflow I have provided a more general solution as the other answers. But while the other answers are coded 'the Pandas way' mine is coded in its core 'outside of Pandas'.

What does 'the Pandas way' and 'outside of Pandas' exactly mean to me? The Pandas way is in my eyes if there is no need to define own Python functions to achieve the result (this includes usage of lambda functions). If providing own functions is necessary to achieve the result I consider such approach as an 'outside Pandas' one.

In order to code also a pure Pandas solution I tried to modify one of the other two provided pure Pandas solutions to make it more general too. Trying to translate the way I have done it using Pythons groupby to a pure 'Pandas way' of doing things I run into a problem because Pandas groupby I have used to group rows by two columns does not provide the same result as Python groupby does on a list of tuples with comparable values from the columns:

Python groupby: [((1, True), [(1, 1)]), ((1, False), [(1, 0), (1, 0), (1, 0)]), ((1, True), [(1, 1)]), ...]
Pandas groupby: { (1, 1.0):   [0, 4],    (1, nan):   [   1,      2,     3]    , #   in [0, 4] as 4  #  ...]

As can be seen from the above comparison while Python groupby groups only consecutive series of same values, so same values scattered over the sequence will be put in separate groups, Pandas groupby groups on the other hand also scattered values together making it useless as replacement for used Python groupby.

In this context my question is:

Is there always a pure 'Pandas way' to provide same results as an 'outside Pandas' one does?

How would a pure 'Pandas way' look like for duplicating the same functionality as in following code example? ( where 'A' marks start of a non-zero values series in the Value column within same series of Cycle values, 'B' marks the end and 'AB' is covering the case of only one value series within a Cycle ):

data = { 'Cycle': [1,1,1,1,1,2,2,2,2,2,3,3,3,3,3],
         'Value': [1,0,0,0,2,3,4,0,5,6,0,0,7,0,0]}  
df = pd.DataFrame(data)
from itertools import groupby
def getPOI(df):
    itrCV = zip(df.Cycle, df.Value)
    lstCV = list(zip(df.Cycle, df.Value)) # only for TEST purposes
    lstPOI = []
    print('Python groupby:', [ ((c, v), list(g)) for (c, v), g in groupby(lstCV, lambda cv: 
                          (cv[0], cv[1]!=0 and not pd.isnull(cv[1]))) ]
         ) # only for TEST purposes
    for (c, v), g in groupby(itrCV, lambda cv: 
                            (cv[0], not pd.isnull(cv[1]) and cv[1]!=0)):
        llg = sum(1 for item in g) # avoids creating a list
        if v is False: 
            lstPOI.extend([0]*llg)
        else: 
           lstPOI.extend(['A']+(llg-2)*[0]+['B'] if llg > 1 else ['AB'])
    return lstPOI
df["POI"] = getPOI(df)
print(df)
print('---')
print(df.POI.to_list())

Here the output created by the code above:

    Cycle  Value POI
0       1      1  AB
1       1      0   0
2       1      0   0
3       1      0   0
4       1      2  AB
5       2      3   A
6       2      4   B
7       2      0   0
8       2      5   A
9       2      6   B
10      3      0   0
11      3      0   0
12      3      7  AB
13      3      0   0
14      3      0   0
---
['AB', 0, 0, 0, 'AB', 'A', 'B', 0, 'A', 'B', 0, 0, 'AB', 0, 0]

Below the nice code provided by Scott Boston I consider to be a 'Pandas way' which fails to provide right results for series of scattered Value(s) within a Cycle:

mp = df.where(df!=0).groupby('Cycle')['Value'].agg([pd.Series.first_valid_index, 
                                            pd.Series.last_valid_index])
df.loc[mp['first_valid_index'], 'POI'] = 'A'
df.loc[mp['last_valid_index'], 'POI'] = 'B'
df['POI'] = df['POI'].fillna(0)

and for the sake of completeness also the code used to print a line used in the comparison between Python and Pandas groupby:

df.Value = df.Value.where(df.Value!=0).where(pd.isnull, 1)
print(  'Pandas groupby:',
        df.groupby(['Cycle','Value'], sort=False).groups
)
group-by dataframe pandas python
1 Answers

Not sure if this is pure enough as far as Pandas is concerned, but here is my take anyway on your interesting question, using shift, apply and concat.

def getPOI(df):
    """Helper function.
    """
    # Setup
    df = df.reset_index(drop=True)
    df["start"] = df.shift(-1).apply(lambda x: "A" if x["Value"] else "", axis=1)
    df["stop"] = df.shift(1).apply(lambda x: "B" if x["Value"] else "", axis=1)

    # Base case
    df["POI"] = df.apply(
        lambda x: x["start"] or x["stop"] if x["Value"] else "", axis=1
    )

    # First and last rows
    df.loc[0, "POI"] = df.head(1)["start"].values[0]
    df.loc[df.shape[0] - 1, "POI"] = df.tail(1)["stop"].values[0]

    # Special case
    df["POI"] = df.apply(
        lambda x: "AB" if x["Value"] and not x["POI"] else x["POI"], axis=1
    )
    
    return df.drop(columns=["start", "stop"])

And then:

df = pd.concat(
    [getPOI(df.loc[df["Cycle"] == i, :]) for i in df["Cycle"].unique()]
).reset_index(drop=True)

print(df)
# Output
    Cycle  Value POI
0       1      1  AB
1       1      0    
2       1      0    
3       1      0    
4       1      2  AB
5       2      3   A
6       2      4   B
7       2      0    
8       2      5   A
9       2      6   B
10      3      0    
11      3      0    
12      3      7  AB
13      3      0    
14      3      0
userCDbL0
Related