Removing value from list in column that matches value from another column-CodePudding

I have the following dataframe:

   df = pd.DataFrame(
       {
          "Student": ['Scooby','Daphne','Shaggy','Fred'],
          "window": [['Scooby','Daphne'], ['Daphne','Velma','Scrappy'], ['Daphne','Shaggy','Fred'],['Fred','Velma','Scrappy']]
       }
    )

which gives me:

  Student                    window
0  Scooby          [Scooby, Daphne]
1  Daphne  [Daphne, Velma, Scrappy]
2  Shaggy    [Daphne, Shaggy, Fred]
3    Fred    [Fred, Velma, Scrappy]

How would I get rid of the name that is in the student column from the corresponding list in the window column?

The result should be:

  Student                    window
0  Scooby                  [Daphne]
1  Daphne          [Velma, Scrappy]
2  Shaggy            [Daphne, Fred]
3    Fred          [Velma, Scrappy]

I was thinking of using something similar to this:

df['window'] = df['window'].apply(lambda x: x.remove(df['Student'])

but obviously I'm missing the element of selecting only the corresponding rows student name. Does anyone have any idea how I should go by figuring this out?

CodePudding user response：

You need to apply on the whole df, not just one column, in order to access the Student column.

df['window'] = df.apply(lambda row: [w for w in row['window'] if w != row['Student']], axis=1)

CodePudding user response：

You need to loop here. The most efficient will be a list comprehension:

df['window'] = [[x for x in b if x!=a]
                for a,b in zip(df['Student'], df['window'])]

Modified DataFrame:

  Student            window
0  Scooby          [Daphne]
1  Daphne  [Velma, Scrappy]
2  Shaggy    [Daphne, Fred]
3    Fred  [Velma, Scrappy]

CodePudding user response：

Loop through the dict:

df = pd.DataFrame(
    {
        "Student": ['Scooby', 'Daphne', 'Shaggy', 'Fred'],
        "window": [['Scooby', 'Daphne'], ['Daphne', 'Velma', 'Scrappy'], ['Daphne', 'Shaggy', 'Fred'],
                   ['Fred', 'Velma', 'Scrappy']]
    }
)

for student, window in zip(df["Student"], df["window"]):
    window.remove(student)

CodePudding user response：

Another way using .explode and .map

s = df.explode("window")

df["window_new"] = df["Student"].map(
         s[s["window"].ne(s["Student"])].groupby("Student").agg(list)["window"]
     )

print(df)

  Student                    window        window_new
0  Scooby          [Scooby, Daphne]          [Daphne]
1  Daphne  [Daphne, Velma, Scrappy]  [Velma, Scrappy]
2  Shaggy    [Daphne, Shaggy, Fred]    [Daphne, Fred]
3    Fred    [Fred, Velma, Scrappy]  [Velma, Scrappy]

CodePudding user response：

You can also use:

df.apply(lambda row : row['window'].remove(row['Student']), axis=1)

It changes your existing df.