Replacing multiple value within cell in dataframe - Python/Pandas

Question

I'm searching for solution how to replace several strings within one cell inside of data frame in Python-Pandas.

Each column has unique elements to be replaced based on legend that is already defined.

I've already find solution how to replace values within column, but result that I get replace only one string at time, and removing other. For example cell value: AA, BB, CC legend: AA - Level 1, BB - Level 2, CC - Level 3, DD - Level 4 result: Level 1.

Data set:
Field Name | Category 1 | Category 2
Test1        AA BB CC      LD DD
Test2        BB CC         DD
Test3        AA            LD
Test4        AA BB DD      LD DD

Legend:
AA - Level 1, BB - Level 2, CC - Level 3, DD - Level 4
LD - High, DD - Low

I expect result to be combined with one cell, for example: Level 1; Level 2 while cell value was AA, BB

What kind of data structure does your `legend` have? Is that a dictionary? — Erfan, Aug 06 '19 at 08:27
@jezrael, this would not work for this case since he has multiple values in one cell — Erfan, Aug 06 '19 at 08:29
yes legend can be dictionary it simple match string to value: AA - Level 1 — mikemorrison, Aug 06 '19 at 08:30

jezrael · Answer 1 · 2019-08-06T09:17:39.520

1

Use:

d = {'AA':'Level 1','BB':'Level 2','CC':'Level 3','DD':'Level 4','LD': 'High', 'DD' :'Low'}

regex = '|'.join(r"\b{}\b".format(x) for x in d.keys())
df = df.apply(lambda x: x.str.replace(regex, lambda x: d[x.group()], regex=True))

print (df)

  Field Name               Category 1 Category 2
0      Test1  Level 1 Level 2 Level 3   High Low
1      Test2          Level 2 Level 3        Low
2      Test3                  Level 1       High
3      Test4      Level 1 Level 2 Low   High Low

If need apply solution only for one column:

df['Category 1'] = df['Category 1'].str.replace(regex, lambda x: d[x.group()], regex=True)

edited Aug 06 '19 at 09:17

answered Aug 06 '19 at 08:32

jezrael

822,522
95
1,334
1,252

Nice one, what does `d[x.group()]` do in this case? +1 – Erfan Aug 06 '19 at 08:42
1

Thank you! I will check this very shortly and come back with feedback – mikemorrison Aug 06 '19 at 08:44
@Erfan - It is regex with callback :) – jezrael Aug 06 '19 at 08:58
Once I add code as described below (add reference to the specify column in my data set) it is throwing an error as follow: File "", line 4 df.['Data Class'] = df.['Data Class'].apply(lambda x: x.str.replace(regex, lambda x: d[x.group()]) ^ SyntaxError: invalid syntax – mikemorrison Aug 06 '19 at 09:15
Change `df.['Data Class']` to `df['Data Class']` – jezrael Aug 06 '19 at 09:16
Done: Now it is throwing this: AttributeError: 'NoneType' object has no attribute 'str' – mikemorrison Aug 06 '19 at 09:19
@mikemorrison - Answer was modify with solution for one column, tested with None and `NaN` and working nice for me. – jezrael Aug 06 '19 at 09:20
I excluded all None values and it is returning: AttributeError: 'str' object has no attribute 'str' – mikemorrison Aug 06 '19 at 09:25
@mikemorrison - there is no typo? – jezrael Aug 06 '19 at 09:28
@mikemorrison - Can you change data for better data sample for see your problem? – jezrael Aug 06 '19 at 09:41
I've checked and my data is identified not as string, but as object. Does it relevant for solution? – mikemorrison Aug 06 '19 at 13:50

Replacing multiple value within cell in dataframe - Python/Pandas

1 Answers1