Edward Desouza Edward Desouza - 1 year ago 48
Python Question

Complex Pandas Dataframe Manipulation

I have a data frame that looks something like this:

import pandas as pd
df= pd.DataFrame({'ID1':['A','B','C','D','E'],\
'ID2':['B','A','D','C','E'],\
'Account':['94000','94500','94000','18300','94500'],\
'Amount':[100,-100,50,-50,100],\
'Match':['-','-','-','-','-']})
df


I am struggling with the most efficient way to identify when an item in 'ID1' is present in 'ID2' with a particular value of Account. For example, with a condition of Account=94500 should yield:

df= pd.DataFrame({'ID1':['A','B','C','D','E'],\
'ID2':['B','A','D','C','E'],\
'Account':['94000','94500','94000','18300','94500'],\ 'Amount':[100,-100,50,-50,200],'Match':['True','-','-','-','-']})
df


i.e. only the first row should be tagged because A (in ID2) matches Account 94500

Answer Source

Your explanation is a bit unclear, but I think you want this:

mask = df[df.Account == '94500'].ID2
df.loc[df.ID1.isin(mask),"Match"] = True

  Account  Amount ID1 ID2 Match
0   94000     100   A   B  True
1   94500    -100   B   A     -
2   94000      50   C   D     -
3   18300     -50   D   C     -
4   94500     100   E   E  True

Also comparing both correct answers just for fun.

%timeit -r 10 df['Match'] = df['ID1'].apply(lambda x: any((df['ID2']==x) & (df['Account']=='94500')))
100 loops, best of 10: 4.21 ms per loop


 %timeit -r 10 df.loc[df.ID1.isin(df[df.Account == '94500'].ID2),"Match"] = True
1000 loops, best of 10: 1.48 ms per loop
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download