-2

enter image description here

I have an excel file that looks like somewhat like the above matrix.

There are two three columns, namely: ID, Object and AE.

I want to create a new column first to copy just the first two letters of Object column (e.g., V2, V3 etc.) and put those in a new column named Object1.

Secondly, I want to see if for any value of V2 in the newly created column Object1, the value of AE. If so, I want to get such records. An example matrix is given below:

enter image description here

khelwood
  • 55,782
  • 14
  • 81
  • 108
Devendra Mishra
  • 19
  • 1
  • 1
  • 5
  • 1
    I think you can just do `new_df = df[df['Object'].str.startswith('V2')]`. Or if you only want the ones with 'Yes' in the `AE` column you could use `new_df = df[df['Object'].str.startswith('V2') & (df['AE']=='Yes')]`. – Matthias Fripp Jul 28 '21 at 07:32
  • Anything like [Efficient way to apply multiple filters to pandas DataFrame or Series](https://stackoverflow.com/q/13611065/3890632)? – khelwood Jul 28 '21 at 07:32
  • @MatthiasFripp. your code can't work because it matches V20, V21, V30, ... – Corralien Jul 28 '21 at 07:35
  • 1
    @Corralien, the question seems to say they want to match on the first two characters of the `Object` column, which mine does. If that's wrong, the poster should clarify the question. It's also unclear what condition they want tested in the `AE` column (if any), and whether they really want an `Object1` column or just want to use that for testing (it's not in the example output). – Matthias Fripp Jul 28 '21 at 07:39
  • @MatthiasFripp, actual the situation that I had, had Object values up to V15 only, that way the code worked perfectly fine for me. Thank you very much. Thank you all as well :) – Devendra Mishra Jul 28 '21 at 10:21

1 Answers1

0

Your question should be clearer. Is this code below gives you the result you want?

df.loc[(df['Object'].str.extract('^(V\d+)').isin(['V2', 'V3']).any(axis=1))
       & (df['AE'] == 'Yes')]
Corralien
  • 109,409
  • 8
  • 28
  • 52