I have a data frame like this
df
col1 col2 col3
A black berry black
B green apple green
C red wine red
I want to subtract col3 values from col2 values, the result will look like
df1
col1 col2 col3
A berry black
B apple green
C wine red
How to do it in effective way using pandas
Use list comprehension with replace and split:
df['col2'] = [a.replace(b, '').strip() for a, b in zip(df['col2'], df['col3'])]
print (df)
col1 col2 col3
0 A berry black
1 B apple green
2 C wine red
If order is not important convert splitted values to sets and subtract:
df['col2'] = [' '.join(set(a.split())-set([b])) for a, b in zip(df['col2'], df['col3'])]
print (df)
col1 col2 col3
0 A berry black
1 B apple green
2 C wine red
Or use generator with if condition and join:
df['col2'] = [' '.join(c for c in a.split() if c != b) for a, b in zip(df['col2'], df['col3'])]
Performance:
This was the setup used to generate the perfplot above:
def calculation(val):
return val[0].replace(val[1],'').strip()
def regex(df):
df.col2=df.col2.replace(regex=r'(?i)'+ df.col3,value="")
return df
def lambda_f(df):
df["col2"] = df.apply(lambda x: x["col2"].replace(x["col3"], "").strip(), axis=1)
return df
def apply(df):
df['col2'] = df[['col2','col3']].apply(calculation, axis=1)
return df
def list_comp1(df):
df['col2'] = [a.replace(b, '').strip() for a, b in zip(df['col2'], df['col3'])]
return df
def list_comp2(df):
df['col2'] = [' '.join(set(a.split())-set([b])) for a, b in zip(df['col2'], df['col3'])]
return df
def list_comp3(df):
df['col2'] = [' '.join(c for c in a.split() if c != b) for a, b in zip(df['col2'], df['col3'])]
return df
def make_df(n):
d = {'col1': {0: 'A', 1: 'B', 2: 'C'}, 'col2': {0: 'black berry', 1: 'green apple', 2: 'red wine'}, 'col3': {0: 'black', 1: 'green', 2: 'red'}}
df = pd.DataFrame(d)
df = pd.concat([df] * n * 100, ignore_index=True)
return df
perfplot.show(
setup=make_df,
kernels=[regex, lambda_f, apply, list_comp1,list_comp2,list_comp3],
n_range=[2**k for k in range(2, 10)],
logx=True,
logy=True,
equality_check=False, # rows may appear in different order
xlabel='len(df)')
Single line solution:
df["col2"] = df.apply(lambda x: x["col2"].replace(x["col3"], "").strip(), axis=1)
We can use the apply method:
def calculation(val):
return val[0].replace(val[1],'').strip()
df['col4'] = df[['col2','col3']].apply(calculation, axis=1)
df:
col1 col2 col3 col4
0 A black berry black berry
1 B green apple green apple
2 C red wine red wine
No need for loop replace, notice this is replace by row
df.col2=df.col2.replace(regex=r'(?i)'+ df.col3,value="")
df
Out[627]:
col1 col2 col3
0 A berry black
1 B apple green
2 C wine red
More Info
col1 col2 col3
0 A berry black
1 B apple green
2 C wine apple# different row with row 2 , but same value
df.col2.replace(regex=r'(?i)'+ df.col3,value="")
Out[629]:
0 berry
1 apple# would not be replaced
2 wine
