Pandas/Python: Replace multiple values in multiple columns

Question

All, I have an analytical csv file with 190 columns and 902 rows. I need to recode values in several columns (18 to be exact) from it's current 1-5 Likert scaling to 0-4 Likert scaling.

I've tried using replace:

df.replace({'Job_Performance1': {1:0, 2:1, 3:2, 4:3, 5:4}}, inplace=True)

But that throws a Value Error: "Replacement not allowed with overlapping keys and values"

I can use map:

df['job_perf1'] = df.Job_Performance1.map({1:0, 2:1, 3:2, 4:3, 5:4})

But, I know there has to be a more efficient way to accomplish this since this use case is standard in statistical analysis and statistical software e.g. SPSS

I've reviewed multiple questions on StackOverFlow but none of them quite fit my use case. e.g. Pandas - replacing column values, pandas replace multiple values one column, Python pandas: replace values multiple columns matching multiple columns from another dataframe

Suggestions?

I feel this question is not properly answered. What if you cannot just do -1, for example because you want a replacement such as {1:0, 2:1, 4:3}? — ssice
– ssice, Commented Oct 5, 2016 at 15:01

EdChum · Accepted Answer · 2015-12-23 00:16:42Z

3

You can simply subtract a scalar value from your column which is in effect what you're doing here:

df['job_perf1'] = df['job_perf1'] - 1

Also as you need to do this on 18 cols, then I'd construct a list of the 18 column names and just subtract 1 from all of them at once:

df[col_list] = df[col_list] - 1

answered Dec 23, 2015 at 0:16

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

Comments

Nelewout · Accepted Answer · 2015-12-22 23:54:35Z

1

No need for a mapping. This can be done as a vector addition, since effectively, what you're doing, is subtracting 1 from each value. This works elegantly:

df['job_perf1'] = df['Job_Performance1'] - numpy.ones(len(df['Job_Performance1']))

Or, without numpy:

df['job_perf1'] = df['Job_Performance1'] - [1] * len(df['Job_Performance1'])

answered Dec 22, 2015 at 23:54

Nelewout

6,6446 gold badges31 silver badges43 bronze badges

7 Comments

EdChum Over a year ago

you don't need to do this just do df['job_perf1'] = df['job_perf1'] -1

R_Queery Over a year ago

@EdChum aaaah, that makes sense, thanks for pointing that out. :) Now I just need to figure out how to loop through my 18 columns. thanks!

EdChum Over a year ago

Is your entire df 18 cols? if so then df = df -1 will just do it all otherwise, compose a list of the cols of interest and then do for col in col_list: df[col] = df[col] - 1

Nelewout Over a year ago

@EdChum I really need to look through the docs more often. Anyways, for what it's worth, pandas converts a scalar to an appropriately sized array under the hood, cf. pandas source.

R_Queery Over a year ago

@EdChum entire dataframe is 190+ columns so can't do the operation on the entire data frame. Also, that code is much less complex than I imagined, is that because we are doing a column operation instead of a row or cell based operation? (obvi still pretty new to Python/Pandas)

|

Collectives™ on Stack Overflow

Pandas/Python: Replace multiple values in multiple columns

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related