Creating a new variable using conditional to another variable in a different dataframe

Question

I have the following dataframes

df1:

    round mun1 mun2
     1     SP   PA
     1     RJ   PR
     1     BH   BA
     2     BA   SP
     2     PR   BH
     2     PA   RJ
     3     RJ   BH
     3     PA   PR
     3     SP   BA

df2:

mun p01 p02 p03
 SP  3   4   7
 RJ  0   3   4
 BH  3   6   9
 BA  0   1   1
 PA  1   2   3 
 PR  1   4   5

I need a column in df1, P, that equals 0 if round==1, maxvalue of p01 if round ==2 and the maxvalue of p02 if round==3.

In the real data, in df1 i have 38 rounds, and 380 lines, and in df2 i have 20 lines (each for unique mun). I tried the following loop:

p <-matrix(0, nrow=380,ncol=1)

for(i in 2:38){ 
  p <- if(round==i) max(p[[i-1]] %in% df2)
}

But this doesn't work. Is there a way to do that?

tmfmnk · Accepted Answer · 2018-11-10 22:43:06Z

2

Using dplyr. It will work if you can merge df1 and df2 based on mun1 or mun2 and mun.

df %>% 
  left_join(df2, by = c("mun1" = "mun")) %>% #Merging the data
  mutate(P = ifelse(round == 1, 0, #Applying the condition
                    ifelse(round == 2, max(p01),
                           ifelse(round == 3, max(p02), NA))))

  round mun1 mun2 p01 p02 p03 P
1     1   SP   PA   3   4   7 0
2     1   RJ   PR   0   3   4 0
3     1   BH   BA   3   6   9 0
4     2   BA   SP   0   1   1 3
5     2   PR   BH   1   4   5 3
6     2   PA   RJ   1   2   3 3
7     3   RJ   BH   0   3   4 6
8     3   PA   PR   1   2   3 6
9     3   SP   BA   3   4   7 6

answered Nov 10, 2018 at 22:43

tmfmnk

40.4k4 gold badges54 silver badges73 bronze badges

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

2 Comments

mmramame Over a year ago

This would work even if df1 and df2 have differente dimensions?

tmfmnk Over a year ago

Yes, It will work even if df1 and df2 have different number of columns and rows. Look at ?left_join.

Collectives™ on Stack Overflow

Creating a new variable using conditional to another variable in a different dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related