How to map list values to dataframe based on search in column contain value

Isambard Growett 0
Viewed 25

I have a dataframe scraped from a classifieds website containing model, version and an ad title. I would like to search the ad title column for mention of car version and map the results to the version column.

        model   version title
4609    F-150   NaN     FORD F150 XLT Reg 2017 3.5L EcoBoost Twin Turb...
4615    Pajero  NaN     Mitsubishi Pajero 3.5L
6316    Pajero  NaN     Mitsubishi PAJERO 3.5L V6 2015
... ... ... ...
4608    Patrol  NaN     2008 Nissan Patrol Safari 4WD
4610    Armada  NaN     2014 Nissan. Armada SE
4613    Duster  NaN     WELL MAINTAINED RENAULT DUSTER WHITE 2015. 81,...
4618    Sentra  NaN     Nissan Sentra 2018

I have a dictionary of car models and versions.

version_list = ['3.5L','S', 'XLT', 'SE', 'Safari', ]

I've tried the following for loop to match the correct versions.

dfs = []
for version in version_list:
  df2 = df[df['title'].str.contains(version)]
  if df2.empty != True:
    df2.version = version
    dfs.append(df2)

res = pd.concat(dfs)

Which gives the following dataframe.

        model   version title
4609    F-150   3.5L    FORD F150 XLT Reg 2017 3.5L EcoBoost Twin Turb...
4615    Pajero  3.5L    Mitsubishi Pajero 3.5L
6316    Pajero  3.5L    Mitsubishi PAJERO 3.5L V6 2015
... ... ... ...
4608    Patrol  S       2008 Nissan Patrol Safari 4WD
4610    Armada  S       2014 Nissan. Armada SE
4613    Duster  S       WELL MAINTAINED RENAULT DUSTER WHITE 2015. 81,...
4618    Sentra  S       Nissan Sentra 2018

I would like the dataframe to look like this:

        model   version title
4609    F-150   XLT     FORD F150 XLT Reg 2017 3.5L EcoBoost Twin Turb...
4615    Pajero  3.5L    Mitsubishi Pajero 3.5L
6316    Pajero  3.5L    Mitsubishi PAJERO 3.5L V6 2015
... ... ... ...
4608    Patrol  Safari  2008 Nissan Patrol Safari 4WD
4610    Armada  SE      2014 Nissan. Armada SE
4613    Duster  NaN     WELL MAINTAINED RENAULT DUSTER WHITE 2015. 81,...
4618    Sentra  Nan     Nissan Sentra 2018

As you can see my results are inaccurate. How can I extract potential versions from the advertisement title in a more accurate way?

Would a dictionary such as the following help accuracy when searching?

version_dict = {'Pajero': ['3.5L', '3.8L', 'GLS', '3.0L', '3.8L Gold Edition'],
                'Armada': ['LE', 'SE'],
                'Patrol': ['LE', 'XE', 'SE'],
                'F-150': ['XLT', 'Raptor', 'FX4', 'Lariat'],
                'Duster': ['RXV', 'RXV BSIV']
}

Any ideas or help would be greatly appreciated.

contains dictionary list pandas
1 Answers

Use Series.str.extract with filtered rows by dictionary and assign to column version:

for k, v in version_dict.items():
    m = df['model'].eq(k)
    pat = '|'.join(r"\b{}\b".format(x) for x in v)
    df.loc[m, 'version'] = df.loc[m, 'title'].str.extract(rf'({pat})', expand=False)

print (df)
       model version                                              title
4609   F-150     XLT  FORD F150 XLT Reg 2017 3.5L EcoBoost Twin Turb...
4615  Pajero    3.5L                             Mitsubishi Pajero 3.5L
6316  Pajero    3.5L                     Mitsubishi PAJERO 3.5L V6 2015
4608  Patrol     NaN                      2008 Nissan Patrol Safari 4WD
4610  Armada      SE                             2014 Nissan. Armada SE
4613  Duster     NaN  WELL MAINTAINED RENAULT DUSTER WHITE 2015. 81,...
4618  Sentra     NaN                                 Nissan Sentra 2018
jezrael0
Related