Modifying specific rows in a Pandas DataFrame based on certain conditions is a fundamental part of data manipulation and cleaning. You might need to update values in one or more columns for rows that meet specific criteria (e.g., update the 'Status' for all products in a certain 'Category', or apply a discount to items with 'Price' above a threshold).
This guide explains several effective methods to modify a subset of rows in a Pandas DataFrame, primarily using DataFrame.loc, numpy.where(), and DataFrame.apply().
The Goal: Conditional Row Modification
Given a Pandas DataFrame, we want to identify a subset of rows based on a condition (or multiple conditions) and then change the values in one or more specific columns only for those selected rows.
Example DataFrame
import pandas as pd
import numpy as np # For np.where
data = {
'ProductID': [101, 102, 103, 104, 105, 106],
'Category': ['Electronics', 'Books', 'Apparel', 'Electronics', 'Books', 'Apparel'],
'Price': [299.99, 19.95, 45.50, 799.00, 12.50, 30.00],
'Stock': [10, 50, 0, 5, 120, 0],
'Status': ['Active', 'Active', 'Discontinued', 'Active', 'Active', 'Discontinued']
}
df_original = pd.DataFrame(data)
print("Original DataFrame:")
print(df_original)Output:
Original DataFrame:
ProductID Category Price Stock Status
0 101 Electronics 299.99 10 Active
1 102 Books 19.95 50 Active
2 103 Apparel 45.50 0 Discontinued
3 104 Electronics 799.00 5 Active
4 105 Books 12.50 120 Active
5 106 Apparel 30.00 0 DiscontinuedMethod 1: Using DataFrame.loc (Recommended)
The DataFrame.loc indexer is primarily label-based but also excels at boolean array indexing, making it ideal for conditional selection and assignment. The syntax is df.loc[row_condition, column_to_modify] = new_value.
Modifying a Single Column
Let's update the 'Status' to 'Clearance' for all 'Apparel' items with 0 'Stock'.
import pandas as pd
df = pd.DataFrame({
'ProductID': [101, 102, 103, 104, 105, 106],
'Category': ['Electronics', 'Books', 'Apparel', 'Electronics', 'Books', 'Apparel'],
'Price': [299.99, 19.95, 45.50, 799.00, 12.50, 30.00],
'Stock': [10, 50, 0, 5, 120, 0],
'Status': ['Active', 'Active', 'Discontinued', 'Active', 'Active', 'Discontinued']
})
condition = (df['Category'] == 'Apparel') & (df['Stock'] == 0)
print("Boolean mask for selection:")
print(condition)
print()
df.loc[condition, 'Status'] = 'Clearance'
print("DataFrame after modifying 'Status' for Apparel with 0 stock:")
print(df)Output:
Boolean mask for selection:
0 False
1 False
2 True
3 False
4 False
5 True
dtype: bool
DataFrame after modifying 'Status' for Apparel with 0 stock:
ProductID Category Price Stock Status
0 101 Electronics 299.99 10 Active
1 102 Books 19.95 50 Active
2 103 Apparel 45.50 0 Clearance
3 104 Electronics 799.00 5 Active
4 105 Books 12.50 120 Active
5 106 Apparel 30.00 0 ClearanceModifying Based on Existing Values in the Target Column
You can use the current values of the column being modified on the right side of the assignment. For example, increase the 'Price' by 10% for 'Electronics'.
import pandas as pd
df = pd.DataFrame({
'ProductID': [101, 102, 103, 104, 105, 106],
'Category': ['Electronics', 'Books', 'Apparel', 'Electronics', 'Books', 'Apparel'],
'Price': [300.0, 20.0, 45.0, 800.0, 12.0, 30.0], # Simplified prices
'Status': ['Active', 'Active', 'Clearance', 'Active', 'Active', 'Clearance']
})
condition_electronics = (df['Category'] == 'Electronics')
df.loc[condition_electronics, 'Price'] = df.loc[condition_electronics, 'Price'] * 1.10
print("DataFrame after increasing 'Price' for Electronics by 10%:")
print(df)Output:
DataFrame after increasing 'Price' for Electronics by 10%:
ProductID Category Price Status
0 101 Electronics 330.0 Active
1 102 Books 20.0 Active
2 103 Apparel 45.0 Clearance
3 104 Electronics 880.0 Active
4 105 Books 12.0 Active
5 106 Apparel 30.0 ClearanceIt's crucial that the selection on the right side df.loc[condition_electronics, 'Price'] aligns with the rows being modified on the left.
Modifying Multiple Columns
To modify multiple columns for the selected rows, pass a list of column names. The value assigned can be a single scalar (applied to all matched cells in the specified columns) or a list/array of values that aligns with the selection.
import pandas as pd
df = pd.DataFrame({
'ProductID': [101, 102, 103],
'Category': ['Electronics', 'Books', 'Apparel'],
'Price': [330.0, 20.0, 45.0],
'Stock': [10, 50, 0],
'Status': ['Active', 'Active', 'Clearance']
})
condition_no_stock = (df['Stock'] == 0)
columns_to_update = ['Status', 'Price']
df.loc[condition_no_stock, 'Status'] = 'Sold Out'
df.loc[condition_no_stock, 'Price'] = 0.00 # Update price separately or use a more complex assignment
print("DataFrame after updating multiple columns for 0 stock:")
print(df)Output:
DataFrame after updating multiple columns for 0 stock:
ProductID Category Price Stock Status
0 101 Electronics 330.0 10 Active
1 102 Books 20.0 50 Active
2 103 Apparel 0.0 0 Sold OutExample: Modify multiple columns based on their existing values
import pandas as pd
df_multi_mod = pd.DataFrame({
'A': [1, 2, 3, 4],
'B': [10, 20, 30, 40],
'C': ['x', 'y', 'x', 'y']
})
condition_c_x = (df_multi_mod['C'] == 'x')
updated_values = df_multi_mod.loc[condition_c_x, ['A', 'B']].apply(
lambda r: pd.Series([r['A'] + 1, r['B'] * 2]), axis=1
)
df_multi_mod.loc[condition_c_x, ['A', 'B']] = updated_values.values
print("Modifying multiple columns based on existing values:")
print(df_multi_mod)Output:
Modifying multiple columns based on existing values:
A B C
0 2 20 x
1 2 20 y
2 4 60 x
3 4 40 yWhen assigning calculated values to multiple columns, using .apply() on the right-hand side selection or preparing a DataFrame/array of new values can be necessary if the new values for each column are different and depend on existing data.
Method 2: Using numpy.where()
numpy.where(condition, value_if_true, value_if_false) is useful for conditional assignments, especially when creating a new column or updating one based on a condition.
import pandas as pd
import numpy as np
df = pd.DataFrame({
'ProductID': [101, 102, 103, 104, 105, 106],
'Category': ['Electronics', 'Books', 'Apparel', 'Electronics', 'Books', 'Apparel'],
'Price': [299.99, 19.95, 45.50, 799.00, 12.50, 30.00],
'Stock': [10, 50, 0, 5, 120, 0],
'Status': ['Active', 'Active', 'Discontinued', 'Active', 'Active', 'Discontinued']
})
condition = (df['Stock'] == 0)
df['Status'] = np.where(condition, 'Sold Out', df['Status'])
print("DataFrame after modifying 'Status' using np.where():")
print(df)Output:
DataFrame after modifying 'Status' using np.where():
ProductID Category Price Stock Status
0 101 Electronics 299.99 10 Active
1 102 Books 19.95 50 Active
2 103 Apparel 45.50 0 Sold Out
3 104 Electronics 799.00 5 Active
4 105 Books 12.50 120 Active
5 106 Apparel 30.00 0 Sold OutMethod 3: Using DataFrame.apply() (Row-wise)
For more complex conditional logic that is hard to express with direct boolean indexing or np.where, you can use DataFrame.apply(custom_function, axis=1). The custom function receives each row as a Series.
import pandas as pd
df_apply_example = pd.DataFrame({
'ProductID': [101, 102, 103, 104, 105, 106],
'Category': ['Electronics', 'Books', 'Apparel', 'Electronics', 'Books', 'Apparel'],
'Price': [299.99, 19.95, 45.50, 799.00, 12.50, 30.00],
'Stock': [10, 50, 0, 5, 120, 0],
'Status': ['Active', 'Active', 'Discontinued', 'Active', 'Active', 'Discontinued']
})
def update_status_based_on_stock(row):
if row['Stock'] == 0 and row['Category'] == 'Apparel':
return 'Clearance_Applied' # New value for 'Status'
return row['Status'] # Keep original status otherwise
df_apply_example['Status'] = df_apply_example.apply(update_status_based_on_stock, axis=1)
print("DataFrame after modifying 'Status' using apply():")
print(df_apply_example)Output:
DataFrame after modifying 'Status' using apply():
ProductID Category Price Stock Status
0 101 Electronics 299.99 10 Active
1 102 Books 19.95 50 Active
2 103 Apparel 45.50 0 Clearance_Applied
3 104 Electronics 799.00 5 Active
4 105 Books 12.50 120 Active
5 106 Apparel 30.00 0 Clearance_Appliedaxis=1: Ensures the function is applied to each row.apply()can be less performant than vectorized methods like.locornp.wherefor simple conditions, but offers maximum flexibility for complex row-wise logic.
Choosing the Right Method
DataFrame.loc[condition, column(s)] = value: Generally recommended for its clarity, conciseness, and directness for most conditional updates, especially when assigning a fixed value or values derived from the same selection.numpy.where(condition, value_if_true, value_if_false): Excellent for updating a single column based on a condition. It's very performant.DataFrame.apply(func, axis=1): Best suited for complex row-by-row conditional logic that is difficult to express with boolean indexing ornp.where. It's typically slower than vectorized approaches.
Conclusion
Modifying a subset of rows in a Pandas DataFrame based on conditions is a core data manipulation skill.
df.loc[row_condition, column_to_change] = new_valueis the most idiomatic and versatile method for most cases.np.where()offers an efficient alternative for updating a single column conditionally.df.apply(..., axis=1)provides maximum flexibility for complex row-wise transformations.
Choose the method that makes your code clear, readable, and performs adequately for your dataset size and the complexity of your conditions.