Check if value of a column is seen for the first time in the group

user_1950961 0
Viewed 94

I want to add a new boolean column to a DataFrame that indicates if the value of a given column is seen for the first time in the groupby group.

My DataFrame is like this:

    UserID  Value
0     1955     30
1     1955     40
2     1955     30
3     1956     30
4     1957     30
5     1957     50
6     1958     30
7     1958     50
8     1958     30
9     1958     30

I want to obtain this:

    UserID  Value  IsNewValue
0     1955     30        True
1     1955     40        True
2     1955     30       False
3     1956     30        True
4     1957     30        True
5     1957     50        True
6     1958     30        True
7     1958     30       False
8     1958     30       False
9     1958     30       False

It is important to note that the dataset is already ordered by UserID and Timestamp (here not shown) and i cannot change this sorting.

I came up with this code that works albeit being extremely inefficient:

def is_new(group, col):
  seen = []
  ret = []
  for i in range(len(group)):
    ret.append(group[col].iloc[i] not in seen)
    seen.append(group[col].iloc[i])
  group[f'IsNew{col}'] = ret
  return group

for col in ['ValueA', 'ValueB', 'ValueC']:
  dataset = dataset.groupby('UserID').apply(lambda x: is_new(x, col))

I was wondering how to rewrite the code and make it more efficient, maybe using windowing functions of Pandas or some numpy feature.

pandas-groupby pandas numpy python
3 Answers

Use: duplicated and negate the result

df['IsNewValue'] = ~df.duplicated(['UserID', 'Value'])

   UserID  Value  IsNewValue
0    1955     30        True
1    1955     40        True
2    1955     30       False
3    1956     30        True
4    1957     30        True
5    1957     50        True
6    1958     30        True
7    1958     50        True
8    1958     30       False
9    1958     30       False
user_49850990

Apart from sushanth's solution (which seems to answer the OP's question of using PANDAS functions), you can also manually compute the values by iterating through the df using itertuples().

Here is my implementation:

import pandas as pd

UserID = [1955,1955,1955,1956,1957,1957,1958,1958,1958,1958]
Value = [30,40,30,30,30,50,30,50,30,30]

df = pd.DataFrame(list(zip(UserID, Value)), columns = ["UserID", "Value"])

def createDuplicateCol(df):
    currentUserID = None
    values = set()
    newCol = []
    for row in df.itertuples():
        newColVal = True
        if row.UserID == currentUserID:
            if row.Value in values:
                newColVal = False
            else:
                values.add(row.Value)
        else:
            currentUserID = row.UserID
            values = set()
            values.add(row.Value)

        newCol.append(newColVal)

    df["IsNewValue"] = newCol

    return df

df = createDuplicateCol(df)

This method uses sets to store values and check for duplicates since they are optimized for that type of operation. It also exploits the sorted arrangement to only store the values that are needed for a given group. After some basic profiling with the OP's data, I found that the performance was comparable to using the df.duplicated method. However, the performance might change for larger dataframes.

user_149700070

Here is a way:

df['IsNewValue'] = df.sort_values('Value').groupby('UserID')['Value'].transform(lambda x: x.diff().ne(0))
user_138021150
Related