Convert values in alternate rows of first n columns to negative

garage · November 1, 2018, 12:18pm

I have a dataframe df like below

V1	V2	V3	V4	V5	V6	score	Name	ID
1	0	0	0	0	0	5	AA	1
1	0	0	0	0	0	5	AA	2
0	1	0	0	0	0	0	CC	3
0	1	0	0	0	0	0	CC	4
0	0	1	0	0	0	10	EE	5
0	0	1	0	0	0	10	EE	6
0	0	0	1	0	0	15	GG	7
0	0	0	1	0	0	15	GG	8
0	0	0	0	1	0	5	HH	9
0	0	0	0	1	0	5	HH	10
0	0	0	0	0	1	0	KK	11
0	0	0	0	0	1	0	KK	12

i need to multiple the values from column 'V1' to column 'score' by -1 to change them to negative. i was thinking of using if(df$ID%%2==0) condition

the output should look like this

V1	V2	V3	V4	V5	V6	score	Name	ID
1	0	0	0	0	0	5	AA	1
-1	0	0	0	0	0	-5	AA	2
0	1	0	0	0	0	0	CC	3
0	-1	0	0	0	0	0	CC	4
0	0	1	0	0	0	10	EE	5
0	0	-1	0	0	0	-10	EE	6
0	0	0	1	0	0	15	GG	7
0	0	0	-1	0	0	-15	GG	8
0	0	0	0	1	0	5	HH	9
0	0	0	0	-1	0	-5	HH	10
0	0	0	0	0	1	0	KK	11
0	0	0	0	0	-1	0	KK	12

Any suggestions on how to do this effectively?

mfherman · November 1, 2018, 12:34pm

Hi, @garage First of all, it is going to be easier for folks around here to help if you include the data and code you've used to get to where you are stuck rather than just a table. See this post for more information about best practices for writing questions and including a reproducible example (reprex).

If I'm understanding what you are looking to do, you want to multiple all values in columns between V1 and score by -1. (Although the expected output you included is not exactly that. If this is not what you want to do, could you explain in a little more detail?) One approach is to use the mutate_at() function from dplyr which allows you to modify several columns in the same way. First, I select the columns I want to modify within the vars() function, and then I define an anonymous function to operate on each of the columns.

library(tidyverse)

df <- tibble::tribble(
  ~V1, ~V2, ~V3, ~V4, ~V5, ~V6, ~V7, ~V8, ~V9, ~score, ~Name, ~ID,
    1,   0,   0,   0,   0,   0,   0,   0,   0,      5,  "AA",   1,
    1,   0,   0,   0,   0,   0,   0,   0,   0,      5,  "AA",   2,
    0,   1,   0,   0,   0,   0,   0,   0,   0,      0,  "CC",   3,
    0,   1,   0,   0,   0,   0,   0,   0,   0,      0,  "CC",   4,
    0,   0,   1,   0,   0,   0,   0,   0,   0,     10,  "EE",   5,
    0,   0,   1,   0,   0,   0,   0,   0,   0,     10,  "EE",   6,
    0,   0,   0,   1,   0,   0,   0,   0,   0,     15,  "GG",   7,
    0,   0,   0,   1,   0,   0,   0,   0,   0,     15,  "GG",   8,
    0,   0,   0,   0,   1,   0,   0,   0,   0,      5,  "HH",   9,
    0,   0,   0,   0,   1,   0,   0,   0,   0,      5,  "HH",  10,
    0,   0,   0,   0,   0,   1,   0,   0,   0,      0,  "KK",  11,
    0,   0,   0,   0,   0,   1,   0,   0,   0,      0,  "KK",  12
  )

df %>%
  mutate_at(vars(V1:score), ~(.x * -1))
#> # A tibble: 12 x 12
#>       V1    V2    V3    V4    V5    V6    V7    V8    V9 score Name     ID
#>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <dbl>
#>  1    -1     0     0     0     0     0     0     0     0    -5 AA        1
#>  2    -1     0     0     0     0     0     0     0     0    -5 AA        2
#>  3     0    -1     0     0     0     0     0     0     0     0 CC        3
#>  4     0    -1     0     0     0     0     0     0     0     0 CC        4
#>  5     0     0    -1     0     0     0     0     0     0   -10 EE        5
#>  6     0     0    -1     0     0     0     0     0     0   -10 EE        6
#>  7     0     0     0    -1     0     0     0     0     0   -15 GG        7
#>  8     0     0     0    -1     0     0     0     0     0   -15 GG        8
#>  9     0     0     0     0    -1     0     0     0     0    -5 HH        9
#> 10     0     0     0     0    -1     0     0     0     0    -5 HH       10
#> 11     0     0     0     0     0    -1     0     0     0     0 KK       11
#> 12     0     0     0     0     0    -1     0     0     0     0 KK       12

^{Created on 2018-11-01 by the reprex package (v0.2.1)}

garage · November 1, 2018, 12:41pm

Thanks for the tips. i will reformat accordingly.

my requirement is to convert the values in alternate rows (rows with even ID) to negative.

mfherman · November 1, 2018, 1:01pm

Oh, I see. How about something like this, it's the same logic as above but wrapped in an ifelse() that finds even numbered rows.

library(tidyverse)

df <- tibble::tribble(
  ~V1, ~V2, ~V3, ~V4, ~V5, ~V6, ~V7, ~V8, ~V9, ~score, ~Name, ~ID,
    1,   0,   0,   0,   0,   0,   0,   0,   0,      5,  "AA",   1,
    1,   0,   0,   0,   0,   0,   0,   0,   0,      5,  "AA",   2,
    0,   1,   0,   0,   0,   0,   0,   0,   0,      0,  "CC",   3,
    0,   1,   0,   0,   0,   0,   0,   0,   0,      0,  "CC",   4,
    0,   0,   1,   0,   0,   0,   0,   0,   0,     10,  "EE",   5,
    0,   0,   1,   0,   0,   0,   0,   0,   0,     10,  "EE",   6,
    0,   0,   0,   1,   0,   0,   0,   0,   0,     15,  "GG",   7,
    0,   0,   0,   1,   0,   0,   0,   0,   0,     15,  "GG",   8,
    0,   0,   0,   0,   1,   0,   0,   0,   0,      5,  "HH",   9,
    0,   0,   0,   0,   1,   0,   0,   0,   0,      5,  "HH",  10,
    0,   0,   0,   0,   0,   1,   0,   0,   0,      0,  "KK",  11,
    0,   0,   0,   0,   0,   1,   0,   0,   0,      0,  "KK",  12
  )

df %>%
  mutate_at(vars(V1:score), ~ifelse(row_number() %% 2 == 0, .x * -1, .x))
#> # A tibble: 12 x 12
#>       V1    V2    V3    V4    V5    V6    V7    V8    V9 score Name     ID
#>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <dbl>
#>  1     1     0     0     0     0     0     0     0     0     5 AA        1
#>  2    -1     0     0     0     0     0     0     0     0    -5 AA        2
#>  3     0     1     0     0     0     0     0     0     0     0 CC        3
#>  4     0    -1     0     0     0     0     0     0     0     0 CC        4
#>  5     0     0     1     0     0     0     0     0     0    10 EE        5
#>  6     0     0    -1     0     0     0     0     0     0   -10 EE        6
#>  7     0     0     0     1     0     0     0     0     0    15 GG        7
#>  8     0     0     0    -1     0     0     0     0     0   -15 GG        8
#>  9     0     0     0     0     1     0     0     0     0     5 HH        9
#> 10     0     0     0     0    -1     0     0     0     0    -5 HH       10
#> 11     0     0     0     0     0     1     0     0     0     0 KK       11
#> 12     0     0     0     0     0    -1     0     0     0     0 KK       12

^{Created on 2018-11-01 by the reprex package (v0.2.1)}

garage · November 12, 2018, 10:46am

thanks.
but when i try this method, i am getting the below error

Error in mutate_impl(.data, dots) :
Evaluation error: argument "x" is missing, with no default.

any idea how to resolve it?

mfherman · November 12, 2018, 11:25am

Can you paste all of the code you are using as well as any error message? Even better would be to use the reprex package!

FAQ: What's a reproducible example (`reprex`) and how do I create one? meta

Why reprex? Getting unstuck is hard. Your first step here is usually to create a reprex, or reproducible example. The goal of a reprex is to package your code, and information about your problem so that others can run it and feel your pain. Then, hopefully, folks can more easily provide a solution. What's in a Reproducible Example? Parts of a reproducible example: background information - Describe what you are trying to do. What have you already done? complete set up - include any library() calls and data to reproduce your issue. data for a reprex: Here's a discussion on setting up data for a reprex make it run - include the minimal code required to reproduce your error on the data…

V1	V2	V3	V4	V5	V6	score	Name	ID
1	0	0	0	0	0	5	AA	1
1	0	0	0	0	0	5	AA	2
0	1	0	0	0	0	0	CC	3
0	1	0	0	0	0	0	CC	4
0	0	1	0	0	0	10	EE	5
0	0	1	0	0	0	10	EE	6
0	0	0	1	0	0	15	GG	7
0	0	0	1	0	0	15	GG	8
0	0	0	0	1	0	5	HH	9
0	0	0	0	1	0	5	HH	10
0	0	0	0	0	1	0	KK	11
0	0	0	0	0	1	0	KK	12

V1	V2	V3	V4	V5	V6	score	Name	ID
1	0	0	0	0	0	5	AA	1
-1	0	0	0	0	0	-5	AA	2
0	1	0	0	0	0	0	CC	3
0	-1	0	0	0	0	0	CC	4
0	0	1	0	0	0	10	EE	5
0	0	-1	0	0	0	-10	EE	6
0	0	0	1	0	0	15	GG	7
0	0	0	-1	0	0	-15	GG	8
0	0	0	0	1	0	5	HH	9
0	0	0	0	-1	0	-5	HH	10
0	0	0	0	0	1	0	KK	11
0	0	0	0	0	-1	0	KK	12

V1	V2	V3	V4	V5	V6	score	Name	ID
1	0	0	0	0	0	5	AA	1
1	0	0	0	0	0	5	AA	2
0	1	0	0	0	0	0	CC	3
0	1	0	0	0	0	0	CC	4
0	0	1	0	0	0	10	EE	5
0	0	1	0	0	0	10	EE	6
0	0	0	1	0	0	15	GG	7
0	0	0	1	0	0	15	GG	8
0	0	0	0	1	0	5	HH	9
0	0	0	0	1	0	5	HH	10
0	0	0	0	0	1	0	KK	11
0	0	0	0	0	1	0	KK	12

V1	V2	V3	V4	V5	V6	score	Name	ID
1	0	0	0	0	0	5	AA	1
-1	0	0	0	0	0	-5	AA	2
0	1	0	0	0	0	0	CC	3
0	-1	0	0	0	0	0	CC	4
0	0	1	0	0	0	10	EE	5
0	0	-1	0	0	0	-10	EE	6
0	0	0	1	0	0	15	GG	7
0	0	0	-1	0	0	-15	GG	8
0	0	0	0	1	0	5	HH	9
0	0	0	0	-1	0	-5	HH	10
0	0	0	0	0	1	0	KK	11
0	0	0	0	0	-1	0	KK	12

V1	V2	V3	V4	V5	V6	score	Name	ID
1	0	0	0	0	0	5	AA	1
1	0	0	0	0	0	5	AA	2
0	1	0	0	0	0	0	CC	3
0	1	0	0	0	0	0	CC	4
0	0	1	0	0	0	10	EE	5
0	0	1	0	0	0	10	EE	6
0	0	0	1	0	0	15	GG	7
0	0	0	1	0	0	15	GG	8
0	0	0	0	1	0	5	HH	9
0	0	0	0	1	0	5	HH	10
0	0	0	0	0	1	0	KK	11
0	0	0	0	0	1	0	KK	12

V1	V2	V3	V4	V5	V6	score	Name	ID
1	0	0	0	0	0	5	AA	1
-1	0	0	0	0	0	-5	AA	2
0	1	0	0	0	0	0	CC	3
0	-1	0	0	0	0	0	CC	4
0	0	1	0	0	0	10	EE	5
0	0	-1	0	0	0	-10	EE	6
0	0	0	1	0	0	15	GG	7
0	0	0	-1	0	0	-15	GG	8
0	0	0	0	1	0	5	HH	9
0	0	0	0	-1	0	-5	HH	10
0	0	0	0	0	1	0	KK	11
0	0	0	0	0	-1	0	KK	12