user299791 user299791 - 1 year ago 81
R Question

How to find percent values by joining to column from another data frame

I have build a data.table dataframe like:

State Bounced Opened Delivered
FL 2 1 0
NY 0 1 1

and I have another data.frame with the total values:

State Total
NY 2
FL 3

I'd like to find the percent frequency for each column in the first data frame:

State Bounced Opened Delivered
FL 66.66 33.33 0
NY 0 50 50

If I simply * 100 and divide all the first data frame by the Total column of the second data frame, I have the result but the first column with State names became NA, which is not good.

If I do like:

df1[, percen:=Bounced/df2$Total]

it works but I have to repeat the process for all the columns. Is there a way to replicate to all the columns but the first one?

As second question is: what is the best way to check that the Total value used is the right one - i.e. belongs to the right State? What I have done so far is to just order the State column alphabetically, but I am wondering if data.table offers a way to control this while doing the percentages...

Answer Source

I am not very good with data.table , however, I think this would work.

cbind(State = df1$State, 
      df1[, -1, with = FALSE]/df2$Total[match(df1$State, df2$State)]* 100)

#   State  Bounced   Opened  Delivered
#1:    FL  66.667    33.333         0
#2:    NY  0.000     50.000        50

Considering df1 and df2 are data.table.

This would also answer your second question as it matches the State in df1 with that of df2.