CODEWITHSUNDEEP
python-3.xpandas
Anubhav Dikshit
Anubhav Dikshit

Reputation: 1829

Fill values in rows of a column based on a condition applied to another column in a data frame

I have a simple doubt,say I have a data frame called df

Structure:

ID   col1   col2  col3   status
1    1      A            Unmapped
2    2            X      Unmapped
3           C     Q      Unmapped
4                 S      Unmapped
5    4      E            Unmapped

I want to fill values in column 'status' based on condition. The condition is if for 1st row in col1 is not equal to blank then 1st row in column status must be 'mapped_one'. If col1 is blank then it must check if row under col2 is not blank, then status must be 'mapped_two' so on and so forth

Expected output:

ID   col1   col2  col3   status
1    1      A            mapped_one
2    2            X      mapped_one
3           C     Q      mapped_two
4                 S      mapped_three
5    4      E            mapped_one

This is what I tired but this didn't work, all rows were assigned the same value

 df.loc[df.col1 != "    ", 'status'] = "mapped_one"

Thanks in advance

Upvotes: 1

Views: 1182

Answers (1)

EdChum
EdChum

Reputation: 394459

You could do it using apply and first_valid_index:

In [137]:
def func(x):
    return 'mapped' + str(x.first_valid_index())[-1]
df['status'] = df.ix[:, 1:].apply(func, axis=1)
df

Out[137]:
   ID  col1 col2 col3   status
0   1     1    A  NaN  mapped1
1   2     2  NaN    X  mapped1
2   3   NaN    C    Q  mapped2
3   4   NaN  NaN    S  mapped3
4   5     4    E  NaN  mapped1

A more generic way would be:

In [146]:
def func(x):
    return 'mapped' + str(df.columns.get_loc(x.first_valid_index()))
df.ix[:, 1:].apply(func, axis=1)

Out[146]:
0    mapped1
1    mapped1
2    mapped2
3    mapped3
4    mapped1
dtype: object

if just want the col name:

In [148]:
def func(x):
    return 'mapped_' + str(x.first_valid_index())
df['status'] = df.ix[:, 1:].apply(func, axis=1)
df

Out[148]:
   ID  col1 col2 col3       status
0   1     1    A  NaN  mapped_col1
1   2     2  NaN    X  mapped_col1
2   3   NaN    C    Q  mapped_col2
3   4   NaN  NaN    S  mapped_col3
4   5     4    E  NaN  mapped_col1

Upvotes: 1

Related Questions