How to append grouped results form one data frame to another one

Question

I need to append a column results that contains an average per each country from one data frame (df) to another data frame that contains countries as well (df1).If there are countries that are not appearing in the aggregated table than I should get an empty cell. Here is my code for creating the average per country of the first data frame:

 df <- read.table(text = "target birds    wolfs     Country
                                 3        9         7 a
                                 3        8         4 b
                                 1        2         8 c
                                 1        2         3 a
                                 1        8         3 a
                                 6        1         2 a
                                 6        7         1 b
                                 6        1         5 c   ",header = TRUE)
dfCountries<-summaryBy(wolfs ~ Country , data = df, FUN = mean)

dfCountries
  Country wolfs.mean
1       a       3.75
2       b       2.50
3       c       6.50

Now I would like to append those results per country on a new data frame in a new column:Append_Country.How can I do it?

Here is the df1 data:

 df1<-read.table(text = "     target birds    wolfs     Country  
                                  6        4         5      a       
                                  4        5         3      a       
                                  3        8         2      a       
                                  1        6         4      b       
                                  3        5         1      a       
                                  2        2         1      b       
                                  9        9         4      b       
                                  8        9         5      f       
                                  2        3         1      f       ",header = TRUE)

This is the output that I would like to get:

df1
                             target birds    wolfs     Country   Append_Country
                              6        4         5      a           3.75  
                              4        5         3      a           3.75
                              3        8         2      a           3.75
                              1        6         4      b           2.50
                              3        5         1      a           3.75
                              2        2         1      b           2.50
                              9        9         4      b           2.50
                              8        9         5      f       
                              2        3         1      f

Tag [tag:dataframes] should not be used in for R data.frames. — Frank, Sep 16 '15 at 15:10

RHertel · Accepted Answer · 2015-09-13T07:35:10.333

You can try using merge() with the option all=TRUE:

df3 <- merge(df1,df2, by="Country", all=TRUE)
#> df3
#   Country target birds wolfs Append_Country
#1        a      4     5     3           3.75
#2        a      3     8     2           3.75
#3        a      6     4     5           3.75
#4        a      3     5     1           3.75
#5        b      2     2     1           2.50
#6        b      9     9     4           2.50
#7        b      1     6     4           2.50
#8        f      2     3     1             NA
#9        f      8     9     5             NA
#10       c     NA    NA    NA           6.50

If you don't like the NA entries, you can replace them with an empty string:

df3[is.na(df3)] <- ""

We can moreover reorder the column sequence to obtain a result according to the desired output:

df3 <- df3[, c(2,3,4,1,5)]

#> df3
#   target birds wolfs Country Append_Country
#1       4     5     3       a           3.75
#2       3     8     2       a           3.75
#3       6     4     5       a           3.75
#4       3     5     1       a           3.75
#5       2     2     1       b            2.5
#6       9     9     4       b            2.5
#7       1     6     4       b            2.5
#8       2     3     1       f               
#9       8     9     5       f               
#10                          c            6.5

data:

df <- read.table(text = "target birds    wolfs     Country
                             3        9         7 a
                             3        8         4 b
                             1        2         8 c
                             1        2         3 a
                             1        8         3 a
                             6        1         2 a
                             6        7         1 b
                             6        1         5 c   ",header = TRUE)
df2 <- aggregate(wolfs ~ Country , data = df, FUN = mean)
colnames(df2) <- c("Country", "Append_Country")
df1<-read.table(text = "     target birds    wolfs     Country  
                              6        4         5      a       
                              4        5         3      a       
                              3        8         2      a       
                              1        6         4      b       
                              3        5         1      a       
                              2        2         1      b       
                              9        9         4      b       
                              8        9         5      f       
                              2        3         1      f ",header = TRUE)

You're welcome; glad I could help :) – RHertel Sep 13 '15 at 07:34 — RHertel, Sep 13 '15 at 07:34

score 0 · Answer 2 · answered Sep 13 '15 at 07:44

I'm adding my own answer using the package sqldf Although I think that @RHertel's solutions is better as his solution is based on base-R function.

df_sq<-sqldf("select country as country_main, avg(wolfs)  as Append_Country from df group by Country" )
 df_sq
  country_main Append_Country
1            a           3.75
2            b           2.50
3            c           6.50

df_sq1<-sqldf("select df1.*,df_sq.Append_Country from df1 left join df_sq on df_sq.country_main=df1.Country")

 df_sq1
  target birds wolfs Country Append_Country
1      6     4     5       a           3.75
2      4     5     3       a           3.75
3      3     8     2       a           3.75
4      1     6     4       b           2.50
5      3     5     1       a           3.75
6      2     2     1       b           2.50
7      9     9     4       b           2.50
8      8     9     5       c           6.50
9      2     3     1       c           6.50

How to append grouped results form one data frame to another one

2 Answers2