Replace column values based on another dataframe python pandas - better way?
pandas, python
Solution
Use the boolean mask from `isin` to filter the df and assign the desired row values from the rhs df:
In [27]:
df.loc[df.Name.isin(df1.Name), ['Nonprofit', 'Education']] = df1[['Nonprofit', 'Education']]
df
Out[27]:
Name Nonprofit Business Education
0 X 1 1 0
1 Y 1 1 1
2 Z 1 0 1
3 Y 1 1 1
[4 rows x 4 columns]
Problem
Note:for simplicity's sake, i'm using a toy example, because copy/pasting dataframes is difficult in stack overflow (please let me know if there's an easy way to do this). Is there a way to merge the values from one dataframe onto another without getting the _X, _Y columns? I'd like the values on one column to replace all zero values of another column. ``` df1: Name Nonprofit Business Education X 1 1 0 Y 0 1 0 <- Y and Z have zero values for Nonprofit and Educ Z 0 0 0 Y 0 1 0 df2: Name Nonprofit Education Y 1 1 <- this df has the correct values. Z 1 1 pd.merge(df1, df2, on='Name', how='outer') Name Nonprofit_X Business Education_X Nonprofit_Y Education_Y Y 1 1 1 1 1 Y 1 1 1 1 1 X 1 1 0 nan nan Z 1 1 1 1 1 ``` In a previous post, I tried combine_First and dropna(), but these don't do the job. I want to replace zeros in df1 with the values in df2. Furthermore, I want all rows with the same Names to be changed according to df2. ``` Name Nonprofit Business Education Y 1 1 1 Y 1 1 1 X 1 1 0 Z 1 0 1 ``` (need to clarify: The value in 'Business' column where name = Z should 0.) My existing solution does the following: I subset based on the names that exist in df2, and then replace those values with the correct value. However, I'd like a less hacky way to do this. ``` pubunis_df = df2 sdf = df1 regex = str_to_regex(', '.join(pubunis_df.ORGS)) pubunis = searchnamesre(sdf, 'ORGS', regex) sdf.ix[pubunis.index, ['Education', 'Public']] = 1 searchnamesre(sdf, 'ORGS', regex) ```