Pandas iterate over rows and remove string value in one column from another

user_2119941 0
Viewed 434

I have dataframe (carsML) that looks something like this:

+-----------------+----------+--------------+
| carManufacturer | carModel |   carType    |
+-----------------+----------+--------------+
| VW              | POLO     | 1.4 TDI      |
| VW              | POLO     | POLO 1.4 TDI |
| VW              | POLO     | 1.6 TDI      |
| VW              | POLO     | 1.4          |
| VW              | POLO     | POLO 1.6 TDI |
|+-----------------+----------+--------------+

I want to iterate over rows, check weather carModel is contained in carType and if it is than remove it. So instead of having POLO 1.4 TDI it should be just 1.4 TDI.

One constraint - some carModels can be single letter long (like just 1 or A). In that case skip replacement and do nothing. Script should work only for carModels that are len(carModel)>1

So far I have:

for row in carsML.itertuples():
    if len(row.carModel) > 1:
        carsML.iloc[row.Index].carType = row.carType.replace(row.carModel,"")

But this doesn't changes anything..I don't know why..

pandas python
3 Answers

If I understand you well, the following one-liner could do your job:

carsML.carType = carsML.apply(lambda row: row.carType.strip(row.carModel) if len(row.carModel) > 1 else row.carType, axis=1)
user_71222720

Use pandas.Series.replace with where:

# Extra row with single letter carModel:
  carManufacturer carModel       carType
0              VW     POLO       1.4 TDI
1              VW     POLO  POLO 1.4 TDI
2              VW     POLO       1.6 TDI
3              VW     POLO           1.4
4              VW     POLO  POLO 1.6 TDI
5              VW        P  POLO 1.6 TDI

df['carType'] = df['carType'].where(~df['carModel'].str.len().gt(1), 
                                    df['carType'].replace(df['carModel'], "", regex=True)).str.strip()

Output:

  carManufacturer carModel       carType
0              VW     POLO       1.4 TDI
1              VW     POLO       1.4 TDI
2              VW     POLO       1.6 TDI
3              VW     POLO           1.4
4              VW     POLO       1.6 TDI
5              VW        P  POLO 1.6 TDI
user_70937410

How did you declared your dataFrame? I done the test:

>>> raw_data = {
...   'carManufacturer': ['VW','VW','VW','VW','VW'],
...   'carModel': ['POLO','POLO','POLO','POLO','POLO'],
...   'carType': ['1.4 TDI', 'POLO 1.4 TDI', '1.6 TDI', '1.4', 'POLO 1.6 TDI']
>>> df = pd.DataFrame(raw_data, columns=["carManufacturer", "carModel", "carType"])
>>> df
  carManufacturer carModel       carType
0              VW     POLO       1.4 TDI
1              VW     POLO  POLO 1.4 TDI
2              VW     POLO       1.6 TDI
3              VW     POLO           1.4
4              VW     POLO  POLO 1.6 TDI

after I done:

>>> for row in df.itertuples():
...   if len(row.carModel) > 1:
...      df.iloc[row.Index].carType = row.carType.replace(row.carModel,"")
...
>>> df

And that is the result:

>>> df
  carManufacturer carModel   carType
0              VW     POLO   1.4 TDI
1              VW     POLO   1.4 TDI
2              VW     POLO   1.6 TDI
3              VW     POLO       1.4
4              VW     POLO   1.6 TDI

It works perfectly.

user_128415580
Related