Append strings to pandas dataframe column with conditional-CodePudding

I'm trying to achieve the following: check if each key value in the dictionary is in the string from column layers. If it meets the conditional, to append the value from the dictionary to the pandas dataframe.

For example, if BR and EWKS is contained within the layer, then in the new column there will be BRIDGE-EARTHWORKS.

Dataframe

mapping = {'IDs': [1244, 35673, 37863, 76373, 234298],
 'Layers': ['D-BR-PILECAPS-OUTLINE 2',
  'D-BR-STEEL-OUTLINE 2D-TERR-BOUNDARY',
  'D-SUBG-OTHER',
  'D-COMP-PAVE-CONC2',
  'D-EWKS-HINGE']}

df = pd.DataFrame(mapping)

Dictionary

d1 = {"BR": "Bridge", "EWKS": "Earthworks", "KERB": "Kerb", "TERR": "Terrain"}

My code thus far is:

for i in df.Layers 
  for x in d1.keys():
    first_key = list(d1)[0]
    first_val = list(d1.values())[0]
    print(first_key,first_val)
    if first_key in i:
      df1 = df1.append(first_val, ignore_index = True)
      # df.apply(first_val)

Note I'm thinking it may be easier to do the comprehension at the mapping step prior to creating the dataframe.. I'm rather new to python still so any tips are appreciated. Thanks!

CodePudding user response：

Use Series.str.extractall for all matched keys, then mapping by dictionary with Series.map and last aggregate join:

pat = r'({})'.format('|'.join(d1.keys()))
df['new'] = df['Layers'].str.extractall(pat)[0].map(d1).groupby(level=0).agg('-'.join)
print (df)
      IDs                               Layers             new
0    1244              D-BR-PILECAPS-OUTLINE 2          Bridge
1   35673  D-BR-STEEL-OUTLINE 2D-TERR-BOUNDARY  Bridge-Terrain
2   37863                         D-SUBG-OTHER             NaN
3   76373                    D-COMP-PAVE-CONC2             NaN
4  234298                         D-EWKS-HINGE      Earthworks