Selecting columns based on value given in other column using pandas in python

Question

I have a data frame as:

a   b   c   d......

1   1
3   3   3   5
4   1   1   4   6
1   0

I want to select number of columns based on value given in column "a". In this case for first row it would only select column b. How can I achieve something like:

df.iloc[:,column b:number of columns corresponding to value in column a]

My expected output would be:

a   b   c   d   e
1   1   0   0   1     # 'e' contains value in column b because colmn a = 1 
3   3   3   5   335   #  'e' contains values of column b,c,d because colm a 
4   1   1   4   1      #  = 3
1   0           NAN

@cᴏʟᴅsᴘᴇᴇᴅ no! not necessarily. Could be NaNs or some value — gokyori
– gokyori, Commented Jan 15, 2018 at 5:43
@gokyori Checkout my update. e is used. So I put it in f. Please consider accepting the answer if this works for you :P Also, I think your output is wrong. 6 is gone. You should have it as f column like my result. — Tai
– Tai, Commented Jan 15, 2018 at 6:43

cs95 · Accepted Answer · 2018-01-15 07:13:58Z

3

Define a little function for this:

def select(df, r):
    return df.iloc[r, 1:1 + df.iat[r, 0]]

The function uses iat to query the a column for that row, and iloc to select columns from the same row.

Call it as such:

select(df, 0)

b    1.0
Name: 0, dtype: float64

And,

select(df, 1)

b    3.0
c    3.0
d    5.0
Name: 1, dtype: float64

Based on your edit, consider this -

df

   a  b  c  d  e
0  1  1  0  0  0
1  3  3  3  5  0
2  4  1  1  4  6
3  1  0  0  0  0

Use where/mask (with numpy broadcasting) + agg here -

df['e'] = df.iloc[:, 1:]\
            .astype(str)\
            .where(np.arange(df.shape[1] - 1) < df.a[:, None], '')\
            .agg(''.join, axis=1)

df

   a  b  c  d     e
0  1  1  0  0     1
1  3  3  3  5   335
2  4  1  1  4  1146
3  1  0  0  0     0

If nothing matches, then those entries in e will have an empty string. Just use replace -

df['e'] = df['e'].replace('', np.nan)

edited Jan 15, 2018 at 7:13

answered Jan 15, 2018 at 5:43

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

piRSquared · Accepted Answer · 2018-01-15 10:14:24Z

2

A numpy slicing approach

a = v[:, 0]
b = v[:, 1:]
n, m = b.shape
b = b.ravel()
b = np.where(b == 0, '', b.astype(str))
r = np.arange(n) * m
f = lambda t: b[t[0]:t[1]]

df.assign(g=list(map(''.join, map(f, zip(r, r + a)))))

   a  b  c  d  e     g
0  1  1  0  0  0     1
1  3  3  3  5  0   335
2  4  1  1  4  6  1146
3  1  0  0  0  0

answered Jan 15, 2018 at 10:14

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Comments

Tai · Accepted Answer · 2018-01-15 16:43:35Z

1

Edit: one line solution with slicing.

df["f"] = df.astype(str).apply(lambda r: "".join(r[1:int(r["a"])+1]), axis=1)

# df["f"] = df["f"].astype(int)  if you need `f` to be integer

df    
    a   b   c   d   e   f
0   1   1   X   X   X   1
1   3   3   3   5   X   335
2   4   1   1   4   6   1146
3   1   0   X   X   X   0

Dataset used:

df = pd.DataFrame({'a': {0: 1, 1: 3, 2: 4, 3: 1},
                   'b': {0: 1, 1: 3, 2: 1, 3: 0},
                   'c': {0: 'X', 1: '3', 2: '1', 3: 'X'},
                   'd': {0: 'X', 1: '5', 2: '4', 3: 'X'},
                   'e': {0: 'X', 1: 'X', 2: '6', 3: 'X'}})

Suggestion for improvement would be appreciated!

edited Jan 15, 2018 at 16:43

answered Jan 15, 2018 at 6:11

Tai

8,0643 gold badges31 silver badges50 bronze badges

3 Comments

gokyori Over a year ago

Thankyou very much, this approach is very much useful for this particular problem. could you please suggest me a way to do the similar operation if column "a" was in different dataframe? I should have asked it as a different question though.

Tai Over a year ago

That should be easy @gokyori Just change the lambda function a bit. r.name can help you get the index and you can use the index to access values in other df. @gokyori

Tai Over a year ago

@gokyori I am not fully aware about the structure of your new problem. One way is to temporarily add the column to this df. Another is to use the r.name method I mentioned above. You can tag me in your new question if you want.

Collectives™ on Stack Overflow

Selecting columns based on value given in other column using pandas in python

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related