Append strings to pandas dataframe column with conditional

Question

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!

can i see desired output of df to be precise?

Panda Kim
– Panda Kim

2022-11-15 06:54:04 +00:00
Commented Nov 15, 2022 at 6:54 — Panda Kim
– Panda Kim, Commented Nov 15, 2022 at 6:54

jezrael · Accepted Answer · 2022-11-15 07:06:49Z

1

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

edited Nov 15, 2022 at 7:06

answered Nov 15, 2022 at 6:57

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Append strings to pandas dataframe column with conditional

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related