python - Remove substring from column based on another column

5
votes

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?

2
pythonpandasdataframe
you can use the .apply method. dfTest['bar'] = dfTest.apply(lambda row: row['foo'].str.replace(str(dfTest['date']), ''), axis=1) - IWHKYB

2 Answers

6
votes

So, I tried this and it worked pretty well:

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

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)