Merge lists is multiple columns of a pandas dataframe into a sigle list in a column

Question

I have a dataframe with two columns containing lists. I want to combine these columns into a single column and merge the lists into a single list. Also this list should only contain unique values from the original lists.

I've tried merging them using df['E']=df[['B','C']].values.tolist().

However this creates a single column with values comprising two lists.

The dataframe looks something like this:

A       B       C       D
a1      [b1,b2] [c1,b1] d1
a2      [b1,b1] [b3]    d2
a3      [b2]    [b2,b2] d3

The final dataframe should look like this:

A       B       C       D       E
a1      [b1,b2] [c1,b1] d1      [b1,b2,c1]
a2      [b1,b1] [b3]    d2      [b1,b3]
a3      [b2]    [b2,b2] d3      [b2]

Edit: The values within the lists of the dataframe are strings.

@jpp I've added a clarification to the question. The lists within the dataframe are strings and hence the provided solutions were not working. How can I solve this? — coding_monkey, Dec 21 '18 at 16:31

score 8 · Accepted Answer · answered Dec 20 '18 at 20:55

8

IIUC

df['E']=(df.B+df.C).map(set).map(list)
df
Out[81]: 
    A         B         C   D             E
0  a1  [b1, b2]  [c1, b1]  d1  [b2, b1, c1]
1  a2  [b1, b1]      [b3]  d2      [b3, b1]
2  a3      [b2]  [b2, b2]  d3          [b2]

answered Dec 20 '18 at 20:55

BENY

317,841
20
164
234

@jpp I got it working. However do you know of a way to show the dataframe without empty list in column. I'm looking for something like: df = df[df['E']!=[]] – coding_monkey Dec 21 '18 at 16:47
1

`df.E=df.E.where(df.E.astype(bool),'')` @Mohnish – BENY Dec 21 '18 at 16:51
Do you know how I can drop a particular value from a list within the datafame cell? – coding_monkey Dec 21 '18 at 17:28
@Mohnish this is another question , you may check list.remove – BENY Dec 21 '18 at 17:39

score 3 · Answer 2 · answered Dec 20 '18 at 20:54

You can use itertools.chain with dict.fromkeys within a list comprehension. Note by your choice of lists within series you are losing all vectorisation benefits.

from itertools import chain

df = pd.DataFrame({'A': ['a1', 'a2', 'a3'],
                   'B': [['b1', 'b2'], ['b1', 'b1'], ['b2']],
                   'C': [['c1', 'b1'], ['b3'], ['b2', 'b2']],
                   'D': ['d1', 'd2', 'd3']})

df['E'] = [list(dict.fromkeys(chain(x, y))) for x, y in zip(df['B'], df['C'])]

print(df)

    A         B         C   D             E
0  a1  [b1, b2]  [c1, b1]  d1  [b1, b2, c1]
1  a2  [b1, b1]      [b3]  d2      [b1, b3]
2  a3      [b2]  [b2, b2]  d3          [b2]

The benefit of this method in Python v3.7+ (and unofficially in v3.6 as a CPython implementation detail) is ordering is preserved, as dictionaries are insertion-ordered.

Sure, the lists within your example dataframe also contain strings. If your dataframe is different, [post a new question defining your dataframe precisely](https://stackoverflow.com/questions/ask). — jpp, Dec 21 '18 at 16:23

score 2 · Answer 3 · answered Dec 20 '18 at 20:53

If order does not matter, set will do the job:

import pandas as pd

data = [['a1', ['b1', 'b2'], ['c1', 'b1'], 'd1'],
        ['a2', ['b1', 'b1'], ['b3'], 'd2'],
        ['a3', ['b2'], ['b2', 'b2'], 'd3']]

df = pd.DataFrame(data=data, columns=['A', 'B', 'C', 'D'])


def uniques(xs):
    return list(set(xi for x in xs for xi in x))


df['E'] = df[['B', 'C']].apply(uniques, axis=1)

print(df)

Output

    A         B         C   D             E
0  a1  [b1, b2]  [c1, b1]  d1  [b1, b2, c1]
1  a2  [b1, b1]      [b3]  d2      [b1, b3]
2  a3      [b2]  [b2, b2]  d3          [b2]

If order does matter use OrderedDict:

import pandas as pd
from collections import OrderedDict

data = [['a1', ['b1', 'b2'], ['c1', 'b1'], 'd1'],
        ['a2', ['b1', 'b1'], ['b3'], 'd2'],
        ['a3', ['b2'], ['b2', 'b2'], 'd3']]

df = pd.DataFrame(data=data, columns=['A', 'B', 'C', 'D'])


def uniques(xs):
    return list(OrderedDict().fromkeys(xi for x in xs for xi in x))


df['E'] = df[['B', 'C']].apply(uniques, axis=1)

Output

    A         B         C   D             E
0  a1  [b1, b2]  [c1, b1]  d1  [b1, b2, c1]
1  a2  [b1, b1]      [b3]  d2      [b1, b3]
2  a3      [b2]  [b2, b2]  d3          [b2]

Merge lists is multiple columns of a pandas dataframe into a sigle list in a column

3 Answers3

Linked