CODEWITHSUNDEEP
pythonpandasdataframe
statdr
statdr

Reputation: 103

Remove substring from column based on another column

Attempting to use the values (as string) from one column to determine what gets removed from another column. Remainder of the column must be unchanged.

Example data:

import pandas as pd

dfTest = pd.DataFrame({
    'date': ['190225', '190225', '190226'],
    'foo': ['190225-file1_190225', '190225-file2_190225', '190226-file3_190226']
})

dfTest

Resulting data frame:

   |    date   |          foo
------------------------------------
0  |   190225  | 190225-file1_190225
1  |   190225  | 190225-file2_190225
2  |   190226  | 190226-file3_190226

I need to create the 'bar' column where 'foo' has all 'date' matches removed.

What I am looking for is this:

   |    date   |         foo          |   bar
-----------------------------------------------
0  |   190225  | 190225-file1_190225  | -file1_
1  |   190225  | 190225-file2_190225  | -file2_
2  |   190226  | 190226-file3_190226  | -file3_

The contents of the 'date' column, whether they appear in the beginning, middle, or end, need to be removed for each row of 'foo.'

I have tried a few things like the code below, but it doesn't work. It just replicates the original column without replacing anything. Note that changing regex = False does not impact the results.

dfTest['bar'] = dfTest['foo'].str.replace(str(dfTest['date']), '')

#or (removing .str, gives same result):

#dfTest['bar'] = dfTest['foo'].replace(str(dfTest['date']), '')

Both result in the below table (exactly the same in 'bar'):

   |    date   |         foo          |         bar
-----------------------------------------------------------
0  |   190225  | 190225-file1_190225  | 190225-file1_190225  
1  |   190225  | 190225-file2_190225  | 190225-file2_190225  
2  |   190226  | 190226-file3_190226  | 190226-file3_190226

How can I remove the contents of the date column but otherwise preserve the original data?

Upvotes: 10

Views: 7575

Answers (2)

merieme
merieme

Reputation: 311

So, I tried this and it worked pretty well:

dfTest['bar'] = dfTest.apply(lambda row : row['foo'].replace(str(row['date']), ''), axis=1)

Upvotes: 12

IWHKYB
IWHKYB

Reputation: 491

Eddited: I noticed that with replace on lambda it wasn't working as expected so I split into a function.

def replace(str1, str2):
    return str1.replace(str2, '')


dfTest['bar'] = dfTest.apply(lambda row: replace(row['foo'], row['date']), axis=1)

Upvotes: 1

Related Questions