How to check if identifier existed in previous months list

user_3967578 0
Viewed 306

I am trying to determine, if an identifier occurs first in a given month (i.e. it is 'new' to the list of identifier). Below, there is the first attempt, but it flags identifier a3 as old on 28 Feb 2020, although it was not in the list on 31 Jan 2020.

Note that this is a simplified example: in practice, I would have more group by columns, not just date, and I would need to check, if the identifier is new to the 'cell' created by the combination of date, industry, age, etc. There could be many.

import pandas as pd, numpy as np

data = """
date                            identifier     value
 31-Dec-2019                    a1   10
 31-Dec-2019                    a2   20
 31-Dec-2019                    a3   30
 31-Jan-2020                    a1   40
 31-Jan-2020                    a2   50
 31-Jan-2020                    a4   60
 31-Jan-2020                    a5   60
 28-Feb-2020                    a1   70
 28-Feb-2020                    a4   80
 28-Feb-2020                    a3   90
"""

res=[]
for row in [el.split() for el in data.splitlines()][1:]:
    rrow=[]
    for col in row:
        try:
            if float(col):
                col = np.float32(col)
        except:
            pass
        rrow.append(col)
    res.append(rrow)
df = pd.DataFrame(data=res[1:], columns=res[0])

df.date = pd.to_datetime(df.date)
df = df.set_index(["date", "identifier"]).sort_index()

df["valprev"] = df.groupby(level="identifier")["value"].shift(1)
df["isnew"] = df.valprev.isnull(

enter image description here

pandas-groupby pandas dataframe python
2 Answers

I guess you can make a new data frame with previous month values:

>>> prev_identifiers = df\
...     .groupby(['date'])\
...     .identifier.apply(lambda x: ','.join(x))\
...     .shift(1)\
...     .fillna('')\
...     .reset_index()\
...     .rename({'identifier': 'identifier_prev'}, axis=1)
>>> print(prev_identifiers)
        date identifier_prev
0 2019-12-31                
1 2020-01-31        a1,a2,a3
2 2020-02-28     a1,a2,a4,a5

After that you can merge df and prev_identifiers together and decide, if current identifier is, or is not in previous identifiers list:

>>> df = pd.merge(df, prev_identifiers, how='left', on='date')
>>> df['is_new'] = df.apply(lambda row: row['identifier'] not in row['identifier_prev'], axis=1)
>>> print(df)
        date identifier value identifier_prev  is_new
0 2019-12-31         a1    10                    True
1 2019-12-31         a2    20                    True
2 2019-12-31         a3    30                    True
3 2020-01-31         a1    40        a1,a2,a3   False
4 2020-01-31         a2    50        a1,a2,a3   False
5 2020-01-31         a4    60        a1,a2,a3    True
6 2020-01-31         a5    60        a1,a2,a3    True
7 2020-02-28         a1    70     a1,a2,a4,a5   False
8 2020-02-28         a4    80     a1,a2,a4,a5   False
9 2020-02-28         a3    90     a1,a2,a4,a5    True
user_71222720

Using pd.to_datetime convert the date column to pandas datetime series:

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

Then use:

s1 = df.groupby('date')['identifier'].value_counts()
s2 = s1.unstack().diff().replace({0: False, np.nan: True}).stack()
df['isnew'] = df.set_index(['date', 'identifier']).index.map(s2)

Details:

Use DataFrame.groupby on date and aggregate the column identifier using Groupby.value_counts:

# print(s1):

date        identifier
2019-12-31  a1            1
            a2            1
            a3            1
2020-01-31  a1            1
            a2            1
            a4            1
            a5            1
2020-02-28  a1            1
            a3            1
            a4            1
Name: identifier, dtype: int64

Use Series.unstack on series s1 to reshape it, then use DataFrame.diff to calculate successive differences between counts of identifier, this step will help in identifying the occurrence of repeated identifiers from previous month, next use DataFrame.stack to again convert it into MultiLevel index series named s2.

# s1.unstack().diff()
identifier   a1   a2  a3   a4  a5
date                             
2019-12-31  NaN  NaN NaN  NaN NaN
2020-01-31  0.0  0.0 NaN  NaN NaN
2020-02-28  0.0  NaN NaN  0.0 NaN

# print(s2) # this series will be use to map in next step
date        identifier
2019-12-31  a1             True
            a2             True
            a3             True
            a4             True
            a5             True
2020-01-31  a1            False
            a2            False
            a3             True
            a4             True
            a5             True
2020-02-28  a1            False
            a2             True
            a3             True
            a4            False
            a5             True
dtype: bool

Use set_index to set the index of dataframe df as date and identifier and use Series.map to map this index from the values from s2, assign this mapped value to the new column isnew:

# print(df)
        date identifier  value  isnew
0 2019-12-31         a1     10   True
1 2019-12-31         a2     20   True
2 2019-12-31         a3     30   True
3 2020-01-31         a1     40  False
4 2020-01-31         a2     50  False
5 2020-01-31         a4     60   True
6 2020-01-31         a5     60   True
7 2020-02-28         a1     70  False
8 2020-02-28         a4     80  False
9 2020-02-28         a3     90   True
user_128331660
Related