CODEWITHSUNDEEP
rdata.table
Daniel
Daniel

Reputation: 79

Replace value of column based on other datatable columns

I have three datatables created from the csv that are in this public bucket https://console.cloud.google.com/storage/browser/securitas

data_total = "source_year", "state", "is_male", "child_race", "weight_pounds" with more than 1M rows
race = (id_race=c(1,2,3,4,5,6,7,9,18,28,39,48),race=c("white","black","american indian","chinese","japanese","hawaiian","filipino","unknown/other","asian indian","korean","samoan","vietnamese"))
sex = (id_sex=c(false,true), de_sex=c("female","male"))

I need to create a new datatable with the following columns: - State - total births for the 70's for each state - total births for the 80's for each state - total births for the 90's for each state - total births for the 00's for each state - race (text) with more births for the 70's for each state - race (text) with more births for the 80's for each state - race (text) with more births for the 90's for each state - race (text) with more births for the 00's for each state - total males for each state - total females for each state - avg weight in kgs for each state

I create the data datatable with:

data_total = rbindlist(lapply(list.files(path=".", pattern="natalidad*"), data.table::fread , header = T, verbose = F, sep = ',', select = c("source_year", "state", "is_male", "child_race", "weight_pounds")))

and then the new datatables to later merge:

b70 = setnames(data_total[source_year %between% c(1970,1979), .N, keyby = state],"N","b70")
b80 = setnames(data_total[source_year %between% c(1980,1989), .N, keyby = state],"N","b80")
b90 = setnames(data_total[source_year %between% c(1990,1999), .N, keyby = state],"N","b90")
b00 = setnames(data_total[source_year %between% c(2000,2010), .N, keyby = state],"N","b00")

male = data_total[source_year %between% c(1970,2010), .(male=sum(is_male==TRUE)), keyby = state]
female = data_total[source_year %between% c(1970,2010), .(female=sum(is_male==FALSE)), keyby = state]

avg_weight = data_total[source_year %between% c(1970,2010), .(avg_weight_pounds=mean(weight_pounds,na.rm = TRUE)), keyby = state]
avg_weight[, avg_weight_kgs := avg_weight_pounds / conv_factor_pounds_to_kgs, by=state]
avg_weight_kgs = avg_weight[, c('state', 'avg_weight_kgs'), with=FALSE]

But I'm stuck on the replacement of race ids for race texts

I have tried with:

data_total$test <- match(race$race,data_total$child_race)

data_total$test <- race$race[match(race$race,data_total$child_race)]

r70 = data_total[source_year %between% c(1970,1979), .(r70 = length(.N)), keyby = .(state,child_race)]

r70$r70 <- race$race[match(r70$r70,race$id_race)]

r70[race, r70 := race, on = c(r70 = "race")]

I expect the output to be:

state  r70
AK     "japanese"
AL     "white"
AR     "black"
.      .
.      .
.      .

Upvotes: 0

Views: 84

Answers (1)

which_command
which_command

Reputation: 511

I downloaded 2 of your data files and did the following:

l=lapply(list.files(path=".", pattern="natalidad*"), data.table::fread , header = T, verbose = F, sep = ',', select = c("source_year", "state", "is_male", "child_race", "weight_pounds"))

data=do.call("rbind", l)

(rbindlist didn't work for me for some reason...)

I had to get rid of rows where child_race values were NA:

data=data[!is.na(data$child_race),]

And then your data looked like this:

head(data)

source_year state is_male child_race weight_pounds
1:        1998    AK   FALSE         10      4.874421
2:        1969    CA    TRUE          5      7.438397
3:        1971    CA    TRUE          4     10.562347
4:        1974    CA   FALSE          5      4.437905
5:        1981    CA    TRUE          4      6.790238
6:        1981    CA    TRUE          5      8.968405

Then I turned your race list into a data frame:

race=data.frame(race,stringsAsFactors = F)
race
   id_race            race
1        1           white
2        2           black
3        3 american indian
4        4         chinese
5        5        japanese
6        6        hawaiian
7        7        filipino
8        9   unknown/other
9       18    asian indian
10      28          korean
11      39          samoan
12      48      vietnamese

Then in order to make the replacements needed I had to make sure that the classes of data$child_race, race$id_race and race$race were all compatible

data$child_race=as.character(data$child_race)
race$id_race=as.character(race$id_race)
race$race=as.character(race$race)

Then I just used a for loop:

for(r in race$id_race){
     data$child_race[data$child_race==r]<-race$race[race$id_race==r]
}

head(data)
 source_year state is_male child_race weight_pounds
1:        1998    AK   FALSE         10      4.874421
2:        1969    CA    TRUE   japanese      7.438397
3:        1971    CA    TRUE    chinese     10.562347
4:        1974    CA   FALSE   japanese      4.437905
5:        1981    CA    TRUE    chinese      6.790238
6:        1981    CA    TRUE   japanese      8.968405

Is this what you wanted?

Upvotes: 1

Related Questions