Fill in missing values in dataset using values from another column

Question

I have a data set like

    Student_ID   City        Branch  Name_of_University
    M2001        Hyderabad   C.S.E   JNTU
    M2002        Delhi       E.C.E   DelhiUniversity
    M2003        Hyderabad   C.S.E   
    M2004        Chennai     I.T     
    M2005        Chennai     C.S.E   AnnaUniversity 
    M2006        Hyderabad   E.C.E   OU
    M2007        Delhi       I.T     
    M2008        Chennai     E.C.E

I would like to fill the missing values of Name_University based on city, say M2003 can be filled with either OU or JNTU , but if JNTU appears more than OU, it is better to fill with JNTU. So how can i decide University name based on Maximum number of occurrences corresponding to a city.

I need to do this in R. Please help me!

How will you do when the number of two cities are tie?

kohske
– kohske

2011-09-18 14:36:21 +00:00
Commented Sep 18, 2011 at 14:36 — kohske
– kohske, Commented Sep 18, 2011 at 14:36

kohske · Accepted Answer · 2011-09-18 14:51:35Z

3

probably this is close to what you want:

> # example data set
> set.seed(0)
> df <- data.frame(city = LETTERS[sample(3,20,TRUE)], univ = letters[sample(3,20,TRUE)])
> df$univ[sample(20, 5)] <- NA
> df
   city univ
1     C    c
2     A    c
3     B    a
# .. snip ..
18    C    c
19    C    a
20    B <NA>
> 
> # find max occurance of univ for each city
> ma <- daply(df, .(city), function(x) names(which.max(table(x$univ))))
> ma
  A   B   C 
"b" "a" "a" 
> 
> # replace the NA with the max value 
> df$univ <- ifelse(is.na(df$univ), ma[df$city], as.character(df$univ))
> df
   city univ
1     C    c
2     A    c
3     B    a
# .. snip ..
18    C    c
19    C    a
20    B    a

answered Sep 18, 2011 at 14:51

kohske

67.2k9 gold badges168 silver badges155 bronze badges

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

1 Comment

Sandesh Nagularapu Over a year ago

Thanks a lot , But when there are equal number of Max Occurences or completely distinct values it is selecting "". though your Method is correct for my question :) , can u add to code, so that when all are distinct values OR Equal Number of Occurences when are there it should select any one of the value.

Collectives™ on Stack Overflow

Fill in missing values in dataset using values from another column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related