How to delete a cell if the string value is also in another variable in python?

econbuffin2019 0
Viewed 39

I have a pd data frame like the following:

V1                                V2
some text here                  a string
a string                        the sky is blue
I see something written         here is a garden
See you soon                    Sun rises in the East
Sun rises in the East           I wake up early
astring                         byebye
new string                      may be new

I want to delete from V2 the instances of 'a string' and 'Sun rises in the East' if those two matches 'exactly' with the instances in V1 (so nothing will change for the row of 'astring'). My target dataframe would be like this:

V1                                V2
some text here                  
a string                        the sky is blue
I see something written         here is a garden
See you soon                    
Sun rises in the East           I wake up early
astring                         byebye
new string                      may be new

The instances in V2 is always one row above than where the respective string is in V1. I have tried shifting the V2 down and delete if it matches V1 but that does not work for all the cases. My thought is that V1 needs to be considered as a list and if any cell of V2 matches 'exactly' with any element of that list, that cell needs to be deleted. But I cannot seem to make this logic (if at all correct) work. Any help is appreciated!

python
2 Answers

you can try this:

data = {"V1" : ['some text here', 'a string', 'I see something written', 'See you soon',  'Sun rises in the East', 'astring', 'new string'], 
 "V2" : ['a string','the sky is blue','here is a garden','Sun rises in the East','I wake up early','byebye','may be new']}

df = pd.DataFrame(data)

for index, val in df.V2.items():
    if val in df.V1.values:
        df.loc[index, 'V2'] = pd.NA
df

output:

    V1                         V2
0   some text here           <NA>
1   a string                  the sky is blue
2   I see something written   here is a garden
3   See you soon              <NA>
4   Sun rises in the East     I wake up early
5   astring                   byebye
6   new string                may be new
Lucas M. Uriarte0

To achieve something without for looping through your dataframe, the .apply method comes in handy combined with a lambda function:

import pandas as pd

df = pd.DataFrame(
    data=
    [['some text here', 'a string'],
     ['a string', 'the sky is blue'],
     ['I see something written', ' here is a garden'],
     ['See you soon', 'Sun rises in the East'],
     ['Sun rises in the East', ' I wake up early'],
     ['astring', ' byebye'],
     ['new string', 'may be new']],
    columns=['V1', 'V2']
)

v1 = df['V1'].to_list()  # Convert V1 to a list
df['V2'] = df.apply(lambda row: '' if row['V2'] in v1 else row['V2'], axis=1)  # Do the check

print(df)  # Show results

Output:

                        V1                 V2
0           some text here                   
1                 a string    the sky is blue
2  I see something written   here is a garden
3             See you soon                   
4    Sun rises in the East    I wake up early
5                  astring             byebye
6               new string         may be new
Jan Willem0
Related