CODEWITHSUNDEEP
pythonpandas
dj2560
dj2560

Reputation: 75

Updating pandas dataframe with new column

I want to create a new column with all the distinct values across the rows. Each value in a row is a string(not list).

This is how dataframe looks like:

+-----------------------------+-------------------------+---------------------------------------------+
|         first               |            second       |           third                             |  
+-----------------------------+-------------------------+---------------------------------------------+
|['able', 'shovel', 'door']   |['shovel raised']        |['shovel raised', 'raised', 'door', 'shovel']|
|['grade control']            |['grade']                |['grade']                                    |
|['light telling', 'love']    |['would love', 'closed'] |['closed', 'light']                          |
+-----------------------------+-------------------------+---------------------------------------------+

This is how the dataframe should look like after creating a new column with distinct values. 

df = pd.DataFrame({'first': "['able', 'shovel', 'door']" , 'second': "['shovel raised']", 'third': "['shovel raised', 'raised', 'door', 'shovel']", "Distinct_set": "['able', 'shovel', 'door', 'shovel raised', 'raised']" }, index = [0])

How can I do it?

Upvotes: 0

Views: 580

Answers (3)

mac13k
mac13k

Reputation: 2663

How about this:

import pandas as pd
import numpy as np

df = pd.DataFrame([[['able', 'shovel', 'door'], ['shovel raised'], ['shovel raised', 'raised', 'door', 'shovel']], [['grade control'], ['grade'], ['grade']], [['light telling', 'love'], ['would love', 'closed'], ['closed', 'light']]], columns=['first', 'second', 'third'])

df.apply(lambda row: [np.unique(np.hstack(row))], raw=True, axis=1)

The last command produces:

0        [[able, door, raised, shovel, shovel raised]]
1                             [[grade, grade control]]
2    [[closed, light, light telling, love, would lo...

which can be saved in a new column of the dataframe:

df['Distinct_set'] = df.apply(lambda row: [np.unique(np.hstack(row))], raw=True, axis=1)

Upvotes: 1

RockStar
RockStar

Reputation: 1314

You can try out below snippet

import json
def get_list_from_str(s):
    return json.loads(s.replace("'", '"'))

def flatten_list_rows(row):
    return (set(
        get_list_from_str(row['first']) + 
        get_list_from_str(row['second']) + 
        get_list_from_str(row['third']) 
    ))

df['Distinct_set'] = df.apply(flatten_list_rows, axis=1)

Upvotes: 0

Mohit Sharma
Mohit Sharma

Reputation: 590

try this:

df['new_col'] = df.apply(lambda x: list(set(x['first'] + x['second']+x['third'])), axis =1)

its creating set of single char as your data in cell is string.

"['able', 'shovel', 'door']"

to correct this use below:

df['new_col'] = df.apply(lambda x: list(set(eval(x['first']) + eval(x['second'])+eval(x['third']))), axis =1)

Upvotes: 1

Related Questions