Replacing values in dataframe with values from other

pandas replace values in column based on multiple condition
pandas replace values in column based on condition
pandas replace specific values in column
pandas replace values in dataframe with values from another dataframe
pandas replace multiple values
pandas dataframe replace string in column
pandas replace column values with another column
pandas update column values

Having two dataframes where one of them has some value to be replaced in the other. What is the best way to replace the values?

For instance, the type:none in df1 should be replaced with the value in df2. This is the progress I have done so far, but I am not content with this approach:

df1=pd.DataFrame({"word":['The','big','cat','house'], "type": ['article','none','noun','none'],"pos":[1,2,3,4]})
df2=pd.DataFrame({"word":['big','house'], "type": ['adjective','noun'],"pos":[2,4]})

df1.set_index('pos',inplace=True, drop=True)
df2.set_index('pos',inplace=True, drop=True)

for i, row in df1.iterrows():
    if row['type']=='none':
        row['word']=df2.loc[df2.index[i],'word']

df1 dataframe should change to:

   word   type         pos 
0 The      article       1
1 big       adjective  2
2 cat       noun         3
3 house  noun        4

Thanks :)

If df2 always indicate the position of where the words in df1 should be replaced, you can simply do:

df1.loc[df2.index,"type"] = df2["type"]

print (df1)

#
      word       type
pos                  
1      The    article
2      big  adjective
3      cat       noun
4    house       noun

Replacing few values in a pandas dataframe column with another , The easiest way is to use the replace method on the column. The arguments are a list of the things you want to replace (here ['ABC', 'AB'] ) and� Replace values given in to_replace with value. Values of the DataFrame are replaced with other values dynamically. This differs from updating with .loc or .iloc, which require you to specify a location to update with some value. Parameters to_replace str, regex, list, dict, Series, int, float, or None. How to find the values that will be replaced.

Solution

Without any use of .apply() method.

condition = df1['type']=='none'
df1.loc[condition, 'type'] = df2.loc[condition]['type']
df1.reset_index(inplace=True)

Output:

   pos   word       type
0    1    The    article
1    2    big  adjective
2    3    cat       noun
3    4  house       noun

Pandas Tutorial: Replacing Values in DataFrames and Series, Matplotlib Tutorial: replace, at, loc to change values. �f replace is applied on a DataFrame, a dict can specify that different values should be replaced in� Notice that some of the values in the dataset contain text (i.e., ABC300 and 900XYZ), while other values are purely numeric (i.e., 700 and 500). You can then use to_numeric in order to convert the values in the dataset into a float format. But since two of those values contain text, you’ll get a ‘NaN’ result for those two values.

How about:

df= df2.set_index('word').combine_first(df1.set_index('word')) 
df.pos = df.pos.astype(int)

output:

            type  pos
word                 
The      article  1
big    adjective  2
cat         noun  3
house       noun  4

and

df.reset_index()
In [970]: df.reset_index()                                                                                                                                                                                 
Out[970]: 
    word       type  pos
0    The    article    1
1    big  adjective    2
2    cat       noun    3
3  house       noun    4

or by 'pos':

df = df2.set_index('pos').combine_first(df1.set_index('pos')).reset_index()
colidx=['word', 'type', 'pos']   
df.reindex(columns=colidx)

output:

Out[976]: 
    word       type  pos
0    The    article    1
1    big  adjective    2
2    cat       noun    3
3  house       noun    4

How to Replace Values in Pandas DataFrame, (4) Replace a single value to a new value for an entire DataFrame: replace. For example, I gathered the following data about different colors:� to_replace : [str, regex, list, dict, Series, numeric, or None] pattern that we are trying to replace in dataframe. value : Value to use to fill holes (e.g. 0), alternately a dict of values specifying which value to use for each column (columns not in the dict will not be filled). Regular expressions, strings and lists or dicts of such objects

Python, Syntax: DataFrame.replace(to_replace=None, value=None, Note: this will modify any other views on this object (e.g. a column from a� Just like pandas dropna() method manage and remove Null values from a data frame, fillna() manages and let the user replace NaN values with some value of their own. Syntax: DataFrame.fillna(value=None, method=None, axis=None, inplace=False, limit=None, downcast=None, **kwargs)

Pandas How to replace values based on Conditions, Using these methods either you can replace a single cell or all the values of a row and column in a dataframe based on conditions . all Art in Column Event with Theater. m = df.Event == 'Art' df.where(~m,other='Theater')� Part 1 of your question: Yes/No boolean values - you mentioned that, there are 100 columns of Boolean's. For this, I generally reconstruct the table with updated values or create a UDF returns 1 or 0 for Yes or No. I am adding two more columns can_vote and can_lotto to the DataFrame (df)

Replacing Values In pandas, Create dataframe. raw_data = {'first_name': ['Jason', 'Molly', 'Tina', 'Jake', Replace all values of -999 with NAN. df.replace(-999, np.nan)� Use merge with left join and replace missing values by original values by fillna:. df = df1.merge(df2, on=['ID', 'Month'], how='left', suffixes=('_','')) df['Value'] = df['Value'].fillna(df['Value_']).astype(int) df = df.drop('Value_', axis=1) print (df) ID Month City Brand Value 0 1 1 London Unilever 100 1 1 2 London Unilever 120 2 1 3 London Unilever 150 3 1 4 London Unilever 140 4 2 1 NY JP

Comments
  • Check out my updated answer, i think it might be what your looking for
  • Don’t use .iterrows().
  • Thanks Henry, I was looking for something like this, the simplest approach.
  • @JuanPerez Please try it out and leave a comment if it worked.
  • Thanks @CypherX, it worked as a charm. Thank you :)
  • I would prefer to set index to position because it would be more than one word that is repeated, so the position can differenciate these cases of the same word in dataframe.
  • @JuanPerez I changed it to that at the end of the answer