Compare dataframes of different dimensions

Question

I am trying to compare two timeseries dataframes with different dimensions.

Market_Cap

Date          Stock_A       Stock_B     Stock_C
01.01.1990    1             3           7
01.02.1990    2             4           8
01.03.1990    3             5           1
01.04.1990    4             6           2

Market_Cap_quantiles

Date          Bottom_quantile      
01.01.1990    1.4                       
01.02.1990    2.4                     
01.03.1990    1.4                     
01.04.1990    2.4

Is there an elegant solution that checks each row of Market_Cap if the stocks belonges in the Market_Cap_quantile, and if not replace the value by NA. So that I end up with something like this:

Bottom_Stocks

Date          Stock_A       Stock_B     Stock_C
01.01.1990    1             NA           NA
01.02.1990    2             NA           NA
01.03.1990    NA            NA           1
01.04.1990    NA            NA           2

I would greatly appreciate your help!

[Merge](http://stackoverflow.com/questions/1299871) on Date, then update columns. — zx8754, Mar 02 '17 at 08:23

akrun · Accepted Answer · 2017-03-02T08:29:44.533

0

We can do

df1[-1][df1[-1] > df2[,2]] <- NA
df1
#        Date Stock_A Stock_B Stock_C
#1 01.01.1990       1      NA      NA
#2 01.02.1990       2      NA      NA
#3 01.03.1990      NA      NA       1
#4 01.04.1990      NA      NA       2

edited Mar 02 '17 at 08:29

answered Mar 02 '17 at 08:24

akrun

874,273
37
540
662

2

This works if assuming rows of Date column on both dataframes match row by row. I think they want `<` instead of `!=`. – zx8754 Mar 02 '17 at 08:28
Hey man thanks a lot for your help! I have a question is it somehow possible to insert the NA i row later? I receive the error value is wrong length. – Mar 02 '17 at 10:56

score 0 · Answer 2 · answered Mar 02 '17 at 08:32

An ifelse check and %in% should be able to do the work.

> a <- c(1:5)
> b <- c(11:15)
> c <- c(13:17)
> df <- data.frame(a, b)
> df2 <- data.frame(a, c)

> df
  a  b
1 1 11
2 2 12
3 3 13
4 4 14
5 5 15

> df2
  a  c
1 1 13
2 2 14
3 3 15
4 4 16
5 5 17

> df[,3] <- ifelse(df[,2] %in% df2[,2], df[,2], NA)

> df
  a  b V3
1 1 11 NA
2 2 12 NA
3 3 13 13
4 4 14 14
5 5 15 15

Compare dataframes of different dimensions

2 Answers2