77

I would like to replace an entire column on a Pandas DataFrame with another column taken from another DataFrame, an example will clarify what I am looking for

import pandas as pd
dic = {'A': [1, 4, 1, 4], 'B': [9, 2, 5, 3], 'C': [0, 0, 5, 3]}
df = pd.DataFrame(dic)

df is

'A' 'B' 'C'
 1   9   0
 4   2   0
 1   5   5
 4   3   3

Now I have another dataframe called df1 with a column "E" that is

df1['E'] = [ 4, 4, 4, 0]

and I would like to replace column "B" of df with column "E" of df1

'A' 'E' 'C'
 1   4   0
 4   4   0
 1   4   5
 4   0   3

I tried to use the .replace() method in many ways but I didn't get anything good. Can you help me?

ah bon
  • 9,293
  • 12
  • 65
  • 148
Stefano Fedele
  • 6,877
  • 9
  • 29
  • 48

5 Answers5

107

If the indices match then:

df['B'] = df1['E']

should work otherwise:

df['B'] = df1['E'].values

will work so long as the length of the elements matches

EdChum
  • 376,765
  • 198
  • 813
  • 562
  • I had the same issue and this method worked for me. df['B'] = df1['E'].values Can someone explain the reason why this one worked vs the other one did np – Santiago Restrepo Serna Nov 20 '22 at 00:36
  • 5
    I get the following warning with this method: `SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame.` – Sameen Dec 21 '22 at 12:10
44

If you don't mind getting a new data frame object returned as opposed to updating the original Pandas .assign() will avoid SettingWithCopyWarning. Your example:

df = df.assign(B=df1['E'])
jedge
  • 937
  • 2
  • 11
  • 18
  • 2
    Let me extend this answer to replacing multiple columns at once. This can be achieved with: `df = df.assign(**df1.to_dict(orient='series'))` :) – Pushkar Nimkar Oct 05 '20 at 14:18
  • 4
    This answer assumes that the name of the column to be updated is always a valid Python variable name, which won't be the case in general. But, more generally it can be passed by constructing a dictionary `df = df.assign(**{'columnname':newvalues})` – MRule May 26 '21 at 10:24
  • @MRule Thank you very much! I had this exact problem and your solution worked perfectly! – kerfuffle Mar 14 '22 at 12:15
10

For those that struggle with the "SettingWithCopy" warning, here's a workaround which may not be so efficient, but still gets the job done.

Suppose you with to overwrite column_1 and column_3, but retain column_2 and column_4

columns_to_overwrite = ["column_1", "column_3"]

First delete the columns that you intend to replace...

original_df.drop(labels=columns_to_overwrite, axis="columns", inplace=True)

... then re-insert the columns, but using the values that you intended to overwrite

original_df[columns_to_overwrite] = other_data_frame[columns_to_overwrite]
Chege
  • 353
  • 4
  • 9
3

Simply do:

df.B = df1.E

That's all!

Art
  • 2,836
  • 4
  • 17
  • 34
HellRazer01
  • 54
  • 1
  • 2
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 06 '21 at 07:12
1

Another way is to use eval:

In [7]: df.eval('B = @df1.E', inplace=True)

In [8]: df
Out[8]: 
   A  B  C
0  1  4  0
1  4  4  0
2  1  4  5
3  4  0  3

Since inplace=True you don't need to assign it back to df.

rachwa
  • 1,805
  • 1
  • 14
  • 17