I have a dataframe:
df =
col1 col2 col3
1 2 3
1 4 6
3 7 2
I want to edit df, such that when the value of col1 is smaller than 2 , take the value from col3.
So I will get:
new_df =
col1 col2 col3
3 2 3
6 4 6
3 7 2
I tried to use assign and df.loc but it didn't work.
What is the best way to do so?
df['col1'] = df.apply(lambda x: x['col3'] if x['col1'] < x['col2'] else x['col1'], axis=1)
The most eficient way is by using the loc operator:
mask = df["col1"] < df["col2"]
df.loc[mask, "col1"] = df.loc[mask, "col3"]
["col1", "col2"] instead of "col1" in the second parameter for loc?
df.loc statements with different filtersdf.loc[mask, ["col1", "col2"]] = df.loc[mask, ["col3", "col4"]]. It doesn't work for me. But it works when I copy them one by one.df.loc[df["col1"] < 2, "col1"] = df["col3"]
As mentioned by @anky_91 use np.where to update the 'col1' values:
df['col1'] = np.where(df['col1'] < df['col2'], df['col3'], df['col1'])
You could look at using the apply function.
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.apply.html
df['col1'] = df.apply(lambda c: c['col3'] if c['col1'] < 2 else c['col1'], axis=1)
Edit: Sorry, I see from your mock outcome you are referring to col2 rather than an int of 2. Eric Yang's answer will solve your problem.
df['col1'] = df['col1'].mask(df['col1'] < 2,df['col3']), ordf = df.assign(col1=df['col1'].mask(df['col1'] < 2,df['col3']))or similar withnp.where