Python/Pandas merge and generate list of unique values from comma separated variable/column [duplicate]

Question

I have a table that looks like this: TABLE

LOCATION   ANALYSIS TYPE
A          A,B,C,D
A          D,E,F
B          KA
B          A,B,C
B          C,D,E

I wish to generate a single line inventory for each location that lists the Analysis types available using pandas. So, for this column of lists I want to merge and display a single list of unique values by location.

IE:
A         A,B,C,D,E,F
B         A,B,C,D,E,KA

DESIRED TABLE_OUT

Sorry, no code. I have tried various splits in pandas, not getting close.

Played with splitting on the "," in the list. It made a new column for each item....nowhere near what I was trying to accomplish. :D — life_on_python
– life_on_python, Commented Mar 3, 2020 at 19:54
You shouldn't have edited your question with images. Leave the tables as text. — Quang Hoang
– Quang Hoang, Commented Mar 3, 2020 at 19:58
Text is back...I need to sort out how to format a table in here. This is my first post...please dont hate me :D — life_on_python
– life_on_python, Commented Mar 3, 2020 at 20:02

Scott Boston · Accepted Answer · 2020-03-03 20:23:16Z

3

Try this:

df1 = pd.DataFrame({'Location':['A']*2+['B']*3
                   ,'Analysis_Type':['A,B,C,D','D,E,F','KA','A,B,C','C,D,E']})
df1.set_index('Location')['Analysis_Type'].str.split(',')\
   .groupby(level=0)\
   .agg(lambda x: ','.join(sorted(list(set(x.sum())))))\
   .reset_index()

Output:

  Location Analysis_Type
0        A   A,B,C,D,E,F
1        B  A,B,C,D,E,KA

edited Mar 3, 2020 at 20:23

answered Mar 3, 2020 at 20:02

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

4 Comments

life_on_python Over a year ago

df_newrng.set_index('UWI')['Analysis Test Type'].str.split(',')\ .groupby(level=0)\ .agg(lambda x: ','.join(sorted(list(set(x.sum())))))\ .reset_index()

life_on_python Over a year ago

I get a type error. Sorry figuring out how to do line breaks :D

Scott Boston Over a year ago

"\" is a line continuation character....

life_on_python Over a year ago

Thanks, this is exactly what I wanted...using the example data. With the real world data I am having sytax problems...but it will get there. Hi-5 Scott Boston

Collectives™ on Stack Overflow

Python/Pandas merge and generate list of unique values from comma separated variable/column [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related