CODEWITHSUNDEEP
pythonpython-3.xpandas
Searching Python
Searching Python

Reputation: 61

How to add a new column in pandas dataframe based on conditions satisfied in another column?

My dataframe looks like the following:

   id   state          level
0   1  [p, t]          [dsd]
1   3  [t, t]    [dsds, dsd]
2   4  [l, l]   [jgddf, vdv]
3   6  [u, c]  [cxxc, jgddf]

What I am trying to do is to check if the level column contains part or whole string in the list and add a new column based on that. This is how I am trying to accomplish that (it includes how I am creating dataframe and sorting and filtering and merging elements in each row):

import numpy as np
import pandas as pd

something = [[1, "p", "dsd"], [3, "t", "dsd"], [6, "u", "jgddf"], [1, "p", "dsd"], [4, "l", "jgddf"], [1, "t", "dsd"], 
             [3, "t", "dsds"], [6, "c", "cxxc"], [1, "p", "dsd"], [4, "l", "vdv"]]

test = pd.DataFrame(something)
test = test.drop_duplicates()
test.columns = ['id', 'state', 'level']
test = test.sort_values(by=['id'], ascending=True)
test_unique = test["id"].unique()

df_aslist = test.groupby(['id']).aggregate(lambda x: list(x)).reset_index()
#making it a set to remove duplicates
df_aslist['level'] = df_aslist['level'].apply(lambda x: list(set(x)))
print(df_aslist)
conditions = [(df_aslist["level"].str.contains("ds") & df_aslist["level"].str.contains("sd")), 
              (df_aslist["level"].str.contains("cx") & df_aslist["level"].str.contains("vd"))]
values = ["term 1", "term 2"]
df_aslist["label"] = np.select(conditions, values)
print(df_aslist)

Output:

   id   state        level label
0   1  [p, t]        [tere]     0
1   3  [t, t]  [dsds, dsd]     0
2   4  [l, l]  [vdv, jgddf]     0
3   6  [u, c]  [cxxc, jgddf]     0

Ideally it should show the following, where the rows that didnt match the condition should disappear and rest remain with new labels.

   id   state        level label
1   3  [t, t]  [dsds, dsd]     term 1
2   4  [l, l]  [vdv, jgddf]    term 2
3   6  [u, c]  [cxxc, jgddf]   term 2

Upvotes: 0

Views: 63

Answers (1)

Anurag Dabas
Anurag Dabas

Reputation: 24314

Try with astype() method:

df_aslist[['state','level']]=df_aslist[['state','level']].astype(str)
#the above code change the list inside your columns to string

conditions=[(df_aslist["level"].str.contains("ds") & df_aslist["level"].str.contains("sd")),
            (df_aslist["level"].str.contains("cx") & df_aslist["level"].str.contains("vd"))
           ]

values = ["term 1", "term 2"]

df_aslist["label"] = np.select(conditions, values)

Finally filter out your dataframe:

df_aslist=df_aslist.query("label!='0'")

If you print df_aslist you will get your desired output

Note: If you want those list back then use pd.eval():

df_aslist[['state','level']]=df_aslist[['state','level']].apply(pd.eval)

Upvotes: 1

Related Questions