CODEWITHSUNDEEP
pythonpandasdataframe
Vladimir Sazonov
Vladimir Sazonov

Reputation: 139

How to rightly extract data from some different columns?

I try to extract data from some columns and merge it to new column. I have pandas dataframe :

col1 col2 col3 col4 
 A    No   No   No
 A    No   No   No
 A    No   No   No
 No   No   No   D
 No   B    No   No
 No   No   C    No
 No   No   C    No

I want to get new column like:

col1 col2 col3 col4 targetCol
 A    No   No   No     A
 A    No   No   No     A
 A    No   No   No     A
 No   No   No   D      D
 No   B    No   No     B
 No   No   C    No     C
 No   No   C    No     C

I tried to use lambda and simple condition, but it doesn't work. Some code which didn't work:

df['targetCol'] = df['A'] if df_razm['A'] != 'No' else None

Error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Thanks for any suggestions.

Upvotes: 2

Views: 61

Answers (5)

Lawis
Lawis

Reputation: 125

Maybe you can use apply with a custom function :

def extract_value(x,invalid_value):
    return x[x!=invalid_value].values[0]

df["target_column"]=df.apply(func=extract_value,axis=1,invalid_value="No")

Upvotes: 1

Erfan
Erfan

Reputation: 42916

Use replace with max over axis=1

df['targetCol'] = df.replace('No', '').max(axis=1)

  col1 col2 col3 col4 targetCol
0    A   No   No   No         A
1    A   No   No   No         A
2    A   No   No   No         A
3   No   No   No    D         D
4   No    B   No   No         B
5   No   No    C   No         C
6   No   No    C   No         C

Upvotes: 4

anky
anky

Reputation: 75100

You can use justify:

df=df.assign(target=justify(df.values,invalid_val='No')[:,0])

  col1 col2 col3 col4 target
0    A   No   No   No      A
1    A   No   No   No      A
2    A   No   No   No      A
3   No   No   No    D      D
4   No    B   No   No      B
5   No   No    C   No      C
6   No   No    C   No      C

Upvotes: 2

yatu
yatu

Reputation: 88276

Here's one way using argmax:

df['targetCol'] = df.values[df.index, df.ne('No').values.argmax(1)]

   col1 col2 col3 col4 targetCol
0    A   No   No   No         A
1    A   No   No   No         A
2    A   No   No   No         A
3   No   No   No    D         D
4   No    B   No   No         B
5   No   No    C   No         C
6   No   No    C   No         C

Upvotes: 2

Rajat Jain
Rajat Jain

Reputation: 2032

Try:

df['targetCol'] = df.replace("No", np.NaN).apply(lambda x: x.str.cat(), 1)

Upvotes: 1

Related Questions