Replace and remove duplicates string elements from one column in Python

Question

Given a small dataset as follows:

   id    room   area            room_vector
0   1   A-102  world        01 , 02, 03, 04
1   2     NaN     24                A; B; C
2   3    B309    NaN         s01, s02 , s02
3   4   C·102     25  E2702-2703,E2702-2703
4   5  E_1089  hello               03,05,06
5   6      27    NaN  05-08,09,10-12, 05-08
6   7      27    NaN                    NaN

I need to manipulate room_vector column with the following logic: (1) remove white spaces and replace ; with ,; (2) replace duplicates and keep one seperated by ,.

For the first one, I've tried:

df['room_vector'] = df['room_vector'].str.replace([' ', ';'], '')

Out:

TypeError: unhashable type: 'list'

How could I get the expected result as follows:

   id    room   area            room_vector
0   1   A-102  world            01,02,03,04
1   2     NaN     24                  A,B,C
2   3    B309    NaN                s01,s02
3   4   C·102     25             E2702-2703
4   5  E_1089  hello               03,05,06
5   6      27    NaN         05-08,09,10-12
6   7      27    NaN                    NaN

Many thanks.

There are spaces only before/after , and ; ?

jezrael
– jezrael

2020-11-09 10:00:29 +00:00
Commented Nov 9, 2020 at 10:00 — jezrael
– jezrael, Commented Nov 9, 2020 at 10:00
Not sure, it could be in any positions.

ah bon
– ah bon

2020-11-09 10:01:40 +00:00
Commented Nov 9, 2020 at 10:01 — ah bon
– ah bon, Commented Nov 9, 2020 at 10:01

ah bon · Accepted Answer · 2020-11-09 10:06:08Z

1

Idea is remove whitespaces, then split by , or ; in Series.str.split and then remove duplicates with original order by create dictionary from keys and extracted keys but only for lists else is returned original:

f = lambda x: ','.join(dict.fromkeys(x).keys()) if isinstance(x, list) else x
df['room_vector'] = df['room_vector'].str.replace(' ', '').str.split('[,;]').apply(f)
print(df)
   id    room   area     room_vector
0   1   A-102  world     01,02,03,04
1   2     NaN     24           A,B,C
2   3    B309    NaN         s01,s02
3   4   C·102     25      E2702-2703
4   5  E_1089  hello        03,05,06
5   6      27    NaN  05-08,09,10-12
6   7      27    NaN             NaN

edited Nov 9, 2020 at 10:06

ah bon

10.1k22 gold badges82 silver badges185 bronze badges

answered Nov 9, 2020 at 9:56

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

Replace and remove duplicates string elements from one column in Python

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