CODEWITHSUNDEEP
rrowsmultiple-columns
Greg Peckory
Greg Peckory

Reputation: 8058

Add new columns with row elements in R

I have a table of Ratings and Users. I would like to add a new column to the Users table called "AvRating" And for each row of that column I would like the average rating each user gives. I loop through all the User IDs in the Ratings table and get the mean of all their corresponding ratings. However the column "AvRating" contains a bunch of N/A's and that is all.

Ratings = read.table("Ratings.txt", 
               sep="\t", 
               col.names=c("ID", "MId", "Rating"), 
               fill=FALSE, 
               strip.white=TRUE)    


Users = read.table("Users.txt", 
               sep="\t", 
               col.names=c("ID", "Age", "Gender", "Occupation", "ZIP"), 
               fill=FALSE, 
               strip.white=TRUE) 


Users["AvRating"] <- NA   


for(i in 1:943){    # 943 rows in "Ratings" table

    N = 0
    x = i

    # Counting number of ratings by specific User

    while(Ratings[1, i]==x){

        N=N+1

    }

    x = i

    temp = rep(0, N)

    for(j in 0:N){

        temp[j] = Ratings[3, i] 

    }

    t = mean(temp)


    Users[6][i] = t

}  

Users[6]

Upvotes: 0

Views: 74

Answers (1)

Gregor Thomas
Gregor Thomas

Reputation: 145775

With R, you almost never need for loops. Using dplyr,

# first load data and dplyr   
library(dplyr)
user.ave.rating <- Ratings %>% 
    group_by(ID) %>%
    summarize(AvRating = mean(Rating, na.rm = TRUE))
# Join this to your user table
Users <- left_join(Users, user.ave.rating)

It's also easy in base R, but I find the syntax for aggregate more difficult to understand/remember:

user.ave.rating <- aggregate(Rating ~ ID, FUN = mean, data = Ratings, na.rm = TRUE)
names(user.ave.rating)[2] <- "AvRating"
Users <- merge(Users, user.ave.rating, by = "ID")

Upvotes: 1

Related Questions