Replace values in pandas dataframe based on column names

Question

I would like to replace the values in a pandas dataframe from another series based on the column names. I have the foll. dataframe:

Y2000   Y2001   Y2002    Y2003    Y2004    Item    Item Code
34        43      0      0          25     Test      Val

and I have another series:

Y2000    41403766
Y2001    45283735
Y2002    47850796
Y2003    38639101
Y2004    45226813

How do I replace the values in the first dataframe based on the values in the 2nd series?

--MORE EDITS: To recreate the proble, code and data is here: umd.box.com/s/hqd6oopj6vvp4qvpwnj8r4lm3z7as4i3

Instructions to run teh code:

To run this code:

Replace data_dir in config_rotations.txt with the path to the input directory i.e. where the files are kept
Replace out_dir in config_rotations.txt with whatever output path you want
Run python code\crop_stats.py. The problem is in line 133 of crop_stats.py

--EDIT:

Based on @Andy's query, here's the result I want:

Y2000      Y2001   Y2002     Y2003      Y2004          Item    Item Code
41403766  45283735 47850796  38639101  45226813     Test      Val

I tried

df_a.replace(df_b)

but this does not change any value in df_a

What is the index of your dataframe? With that information you could potentially use df.update(). — Alex
– Alex, Commented Sep 22, 2015 at 20:48
What is the result you want? Can you include that in the question? (Is it the column names to change or the values?) — Andy Hayden
– Andy Hayden, Commented Sep 22, 2015 at 20:58
@AndyHayden, updated question to reflect the answer I want. I want change in values — user308827
– user308827, Commented Sep 22, 2015 at 21:10

Community · Accepted Answer · 2017-05-23 11:58:23Z

1

You can construct a df from the series after reshaping and overwrite the columns:

In [85]:
df1[s.index] = pd.DataFrame(columns = s.index, data = s.values.reshape(1,5))
df1

Out[85]:
      Y2000     Y2001     Y2002     Y2003     Y2004  Item Item  Code
0  41403766  45283735  47850796  38639101  45226813  Test        Val

So this uses the series index values to sub-select from the df and then constructs a df from the same series, here we have to reshape the array to make a single row df

EDIT

The reason my code above won't work on your real code is firstly when assigning you can't do this:

df.loc[(df['Country Code'] == replace_cnt) & (df['Item'] == crop)][s.index]

This is called chained indexing and raises a warning, see the docs.

So to correct this you can put the columns inside the []:

df.loc[(df['Country Code'] == replace_cnt) & (df['Item'] == crop),s.index]

Additionally pandas tries to align along index values and column names, if they don't match then you'll get NaN values so you can get around this by calling .values to get a np array which just becomes anonymous data that has no index or column labels, so long as the data shape is broadcast-able then it will do what you want:

df.loc[(df['Country Code'] == replace_cnt) & (df['Item'] == crop),s.index] = pd.DataFrame(columns=s.index, data=s.values.reshape(1, len(s.index))).values

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Sep 22, 2015 at 20:36

EdChum

397k204 gold badges836 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

user308827 Over a year ago

thanks @EdChum, this should work but somehow my original dataframe i.e. df1 in your answer is same as before

user308827 Over a year ago

specifically, I am getting this warning; C:\Anaconda64\lib\site-packages\pandas\core\frame.py:2148: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the the caveats in the documentation: pandas.pydata.org/pandas-docs/stable/… self[k1] = value[k2] ---

EdChum Over a year ago

That means there is no alignment between either the row values or the column names, if the column names definitely match then try

df.loc[(df['Country Code'] == replace_cnt) & (df['Item'] == crop),s.index] = pd.DataFrame(columns=s.index, data=s.values.reshape(1, len(s.index))).values

can you edit your question with what the row/columns are for the lhs and rhs, thanks

EdChum Over a year ago

The reason that works is because when you assign values, pandas will try to align the lhs and rhs index and columns so if for instance the index values don't match then NaN will be assigned, the same thing happens if the column names don't match. By calling the attribute .values you return a numpy array this has no index or column labels so it's anonymous data so as long as the shape is broadcast-able it will assign as desired

EdChum Over a year ago

if the column names don't match then it won't overwrite, I answered something about broadcasting wrt to pandas here which should demonnstrate what I'm talking about

|

Collectives™ on Stack Overflow

Replace values in pandas dataframe based on column names

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related