Fill Down based on pattern

user_8528899 0
Viewed 35

I have 4 columns - City, Locality.Name, Buy.Rates and Buy.Rates.1. If a value in these columns (Locality.Name, Buy.Rates and Buy.Rates.1) are same, take the value and populate it in a new column called 'Updated.City'. Use that value until a new value appears it

structure(list(City = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L), .Label = c("delhi-ncr", "gurgaon", "noida", 
"greater-noida", "ghaziabad", "faridabad", "mumbai", "bangalore", 
"chennai", "hyderabad", "pune", "kolkata", "ahmedabad", "bhubaneswar", 
"coimbatore", "indore", "nagpur", "vadodara", "chandigarh", "jaipur", 
"lucknow", "surat"), class = "factor"), Locality.Name = c("Delhi East", 
"Akshardham", "Dilshad Colony", "Dilshad Garden", "I P Extension", 
"Delhi South", "Adhchini", "Alaknanda", "Ashram", "Aya Nagar", 
"Chattarpur"), Buy.Rates = c("Delhi East", "Rs. 16,150 - 18,190/sq. ft.", 
"Rs. 5,398 - 6,290/sq. ft.", "Rs. 6,290 - 8,372/sq. ft.", "Rs. 8,288 - 9,435/sq. ft.", 
"Delhi South", "-", "Rs. 10,710 - 12,070/sq. ft.", "Rs. 9,520 - 11,008/sq. ft.", 
"-", "Rs. 3,485 - 4,760/sq. ft."), Buy.Rates.1 = c("Delhi East", 
"-1.7%", "-10.19%", "7.01%", "0.96%", "Delhi South", "-", "-3.24%", 
"-", "-", "7.78%")), row.names = c(1L, 2L, 3L, 4L, 5L, 70L, 71L, 
72L, 73L, 74L, 75L), class = "data.frame")

Desired Output (Added column 'Updated.City')

+-----------+--------------+----------------+-----------------------------+-------------+
|   City    | Updated.City | Locality.Name  |          Buy.Rates          | Buy.Rates.1 |
+-----------+--------------+----------------+-----------------------------+-------------+
| delhi-ncr | Delhi East   | Delhi East     | Delhi East                  | Delhi East  |
| delhi-ncr | Delhi East   | Akshardham     | Rs. 16,150 - 18,190/sq. ft. | -1.70%      |
| delhi-ncr | Delhi East   | Dilshad Colony | Rs. 5,398 - 6,290/sq. ft.   | -10.19%     |
| delhi-ncr | Delhi East   | Dilshad Garden | Rs. 6,290 - 8,372/sq. ft.   | 7.01%       |
| delhi-ncr | Delhi East   | I P Extension  | Rs. 8,288 - 9,435/sq. ft.   | 0.96%       |
| delhi-ncr | Delhi South  | Delhi South    | Delhi South                 | Delhi South |
| delhi-ncr | Delhi South  | Adhchini       | -                           | -           |
| delhi-ncr | Delhi South  | Alaknanda      | Rs. 10,710 - 12,070/sq. ft. | -3.24%      |
| delhi-ncr | Delhi South  | Ashram         | Rs. 9,520 - 11,008/sq. ft.  | -           |
| delhi-ncr | Delhi South  | Aya Nagar      | -                           | -           |
| delhi-ncr | Delhi South  | Chattarpur     | Rs. 3,485 - 4,760/sq. ft.   | 7.78%       |
+-----------+--------------+----------------+-----------------------------+-------------+
dplyr r
2 Answers

Using dplyr and tidyr:

library(dplyr)
library(tidyr)

df %>% 
  mutate(Updated.City = if_else(Locality.Name == Buy.Rates & Locality.Name == Buy.Rates.1,
                                Locality.Name, NA_character_)) %>% 
  fill(Updated.City, .direction = "down")

This first creates Updated.City with either the value of Locality.Name or NA, and then fills the column down, replacing NAs.

This gives

        City  Locality.Name                   Buy.Rates Buy.Rates.1 Updated.City
1  delhi-ncr     Delhi East                  Delhi East  Delhi East   Delhi East
2  delhi-ncr     Akshardham Rs. 16,150 - 18,190/sq. ft.       -1.7%   Delhi East
3  delhi-ncr Dilshad Colony   Rs. 5,398 - 6,290/sq. ft.     -10.19%   Delhi East
4  delhi-ncr Dilshad Garden   Rs. 6,290 - 8,372/sq. ft.       7.01%   Delhi East
5  delhi-ncr  I P Extension   Rs. 8,288 - 9,435/sq. ft.       0.96%   Delhi East
6  delhi-ncr    Delhi South                 Delhi South Delhi South  Delhi South
7  delhi-ncr       Adhchini                           -           -  Delhi South
8  delhi-ncr      Alaknanda Rs. 10,710 - 12,070/sq. ft.      -3.24%  Delhi South
9  delhi-ncr         Ashram  Rs. 9,520 - 11,008/sq. ft.           -  Delhi South
10 delhi-ncr      Aya Nagar                           -           -  Delhi South
11 delhi-ncr     Chattarpur   Rs. 3,485 - 4,760/sq. ft.       7.78%  Delhi South
user_10622250

Here is a base R solution. The main step is the creation of an logical index vector where TRUE means that the three values are equal. Then use that index to assign the values in the new column and fill down the NA's.

i <- apply(df1[-1], 1, function(x) all(x == x[1]))
df1$Updated.City <- NA_character_
df1$Updated.City[i] <- df1$Locality.Name[i]
df1$Updated.City <- zoo::na.locf(df1$Updated.City)
df1
#        City  Locality.Name                   Buy.Rates Buy.Rates.1 Updated.City
#1  delhi-ncr     Delhi East                  Delhi East  Delhi East   Delhi East
#2  delhi-ncr     Akshardham Rs. 16,150 - 18,190/sq. ft.       -1.7%   Delhi East
#3  delhi-ncr Dilshad Colony   Rs. 5,398 - 6,290/sq. ft.     -10.19%   Delhi East
#4  delhi-ncr Dilshad Garden   Rs. 6,290 - 8,372/sq. ft.       7.01%   Delhi East
#5  delhi-ncr  I P Extension   Rs. 8,288 - 9,435/sq. ft.       0.96%   Delhi East
#70 delhi-ncr    Delhi South                 Delhi South Delhi South  Delhi South
#71 delhi-ncr       Adhchini                           -           -  Delhi South
#72 delhi-ncr      Alaknanda Rs. 10,710 - 12,070/sq. ft.      -3.24%  Delhi South
#73 delhi-ncr         Ashram  Rs. 9,520 - 11,008/sq. ft.           -  Delhi South
#74 delhi-ncr      Aya Nagar                           -           -  Delhi South
#75 delhi-ncr     Chattarpur   Rs. 3,485 - 4,760/sq. ft.       7.78%  Delhi South
user_82454060
Related