求助：数据框添加新列，并根据不同的样本类型对新列赋值

lilya · 2019年5月13日

我有的数据：
1、23年的贸易数据，分别有23个csv文件
2、根据commodity.code这一变量，将商品分为了36种类型
想要达成的目的:
1、根据商品的36种类型，将不同类型的商品筛选出来，并添加一个名为icsnb的新变量，变量的值依次为1-36
2、对每年的贸易数据都这样处理
目前写的代码:
前期准备：

setwd("D:/data")
list.files()
library(xlsx)
a<-read.xlsx("匹配结果最终版 - 副本.xlsx" ,1,encoding = "UTF-8",stringsAsFactors = FALSE)[,5]
a2<-strsplit(a,split=",")
a2[23]
#> [[1]]
#>   [1] "20110"  "20120"  "20130"  "20210"  "20220"  "20230"  "20311" 
#>   [8] "20312"  "20319"  "20321"  "20322"  "20329"  "20410"  "20421" 
#>  [15] "20422"  "20423"  "20430"  "20441"  "20442"  "20443"  "20450" 
#>  [22] "20500"  "20610"  "20621"  "20622"  "20629"  "20630"  "20641" 
#>  [29] "20649"  "20680"  "20690"  "20710"  "20721"  "20722"  "20723" 
#>  [36] "20731"  "20739"  "20741"  "20742"  "20743"  "20750"  "20810" 
#>  [43] "20820"  "20890"  "21011"  "21012"  "21019"  "21020"  "21090" 
#>  [50] "160100" "160210" "160220" "160231" "160239" "160241" "160242"
#>  [57] "160249" "160250" "160290" "160300" "40110"  "40120"  "40130" 
#>  [64] "40210"  "40221"  "40229"  "40291"  "40299"  "40310"  "40390" 
#>  [71] "40410"  "40490"  "40500"  "40610"  "40620"  "40630"  "40640" 
#>  [78] "40690"  "40700"  "40811"  "40819"  "40891"  "40899"  "100110"
#>  [85] "100190" "100200" "100300" "100400" "100510" "100590" "100610"
#>  [92] "100620" "100630" "100640" "100700" "100810" "100820" "100830"
#>  [99] "100890" "110100" "110210" "110220" "110230" "110290" "110311"
#> [106] "110312" "110313" "110314" "110319" "110321" "110329" "110411"
#> [113] "110412" "110419" "110421" "110422" "110423" "110429" "110430"
#> [120] "110710" "110720" "190110" "190120" "190190" "190211" "190219"
#> [127] "190240" "190410" "190510" "190520" "190530" "190540" "190590"
#> [134] "70110"  "70190"  "70200"  "70310"  "70320"  "70390"  "70410" 
#> [141] "70420"  "70490"  "70511"  "70519"  "70521"  "70529"  "70610" 
#> [148] "70690"  "70700"  "70810"  "70820"  "70890"  "70910"  "70920" 
#> [155] "70930"  "70940"  "70951"  "70952"  "70960"  "70970"  "70990" 
#> [162] "71010"  "71021"  "71022"  "71029"  "71030"  "71080"  "71090" 
#> [169] "71110"  "71120"  "71130"  "71140"  "71190"  "71210"  "71220" 
#> [176] "71230"  "71290"  "71310"  "71320"  "71331"  "71332"  "71333" 
#> [183] "71339"  "71340"  "71350"  "71390"  "71410"  "71420"  "71490" 
#> [190] "80110"  "80120"  "80130"  "80211"  "80212"  "80221"  "80222" 
#> [197] "80231"  "80232"  "80240"  "80250"  "80290"  "80300"  "80410" 
#> [204] "80420"  "80430"  "80440"  "80450"  "80510"  "80520"  "80530" 
#> [211] "80540"  "80590"  "80610"  "80620"  "80710"  "80720"  "80810" 
#> [218] "80820"  "80910"  "80920"  "80930"  "80940"  "81010"  "81020" 
#> [225] "81030"  "81040"  "81090"  "81110"  "81120"  "81190"  "81210" 
#> [232] "81220"  "81290"  "81310"  "81320"  "81330"  "81340"  "81350" 
#> [239] "81400"  "110510" "110520" "110610" "110620" "110630" "121010"
#> [246] "121020" "121210" "121230" "121291" "121292" "121299" "190300"
#> [253] "200110" "200120" "200190" "200210" "200290" "200310" "200320"
#> [260] "200410" "200490" "200520" "200530" "200540" "200551" "200559"
#> [267] "200560" "200570" "200590" "200600" "200710" "200791" "200799"
#> [274] "200811" "200819" "200820" "200830" "200840" "200850" "200860"
#> [281] "200870" "200880" "200892" "200899" "200911" "200919" "200920"
#> [288] "200930" "200940" "200950" "200960" "200970" "200980" "200990"
#> [295] "40900"  "170111" "170112" "170191" "170199" "170210" "170220"
#> [302] "170230" "170240" "170260" "170290" "170310" "170390" "170410"
#> [309] "170490" "90111"  "90112"  "90121"  "90122"  "90130"  "90140" 
#> [316] "90210"  "90220"  "90230"  "90240"  "90300"  "90411"  "90412" 
#> [323] "90420"  "90500"  "90610"  "90620"  "90700"  "90810"  "90820" 
#> [330] "90830"  "90910"  "90920"  "90930"  "90940"  "90950"  "91010" 
#> [337] "91020"  "91030"  "91040"  "91050"  "91091"  "91099"  "180100"
#> [344] "180310" "180320" "180400" "180500" "180610" "180620" "180631"
#> [351] "180632" "180690" "210110" "210130" "41000"  "71040"  "150100"
#> [358] "151710" "151790" "190220" "190230" "190490" "200510" "200580"
#> [365] "200891" "210120" "210210" "210220" "210230" "210310" "210320"
#> [372] "210330" "210390" "210410" "210420" "210500" "210610" "210690"
#> [379] "220900" "220110" "220190" "220210" "220290" "220300" "220410"
#> [386] "220421" "220429" "220430" "220510" "220590" "220600" "220810"
#> [393] "220820" "220830" "220840" "220850" "220890" "120100" "120210"
#> [400] "120220" "120300" "120400" "120500" "120600" "120710" "120720"
#> [407] "120730" "120740" "120750" "120760" "120791" "120792" "120799"
#> [414] "120810" "120890" "20900"  "150200" "150300" "150410" "150420"
#> [421] "150430" "150510" "150590" "150600" "150710" "150790" "150810"
#> [428] "150890" "150910" "150990" "151000" "151110" "151190" "151211"
#> [435] "151219" "151221" "151229" "151311" "151319" "151321" "151329"
#> [442] "151410" "151490" "151511" "151519" "151521" "151529" "151530"
#> [449] "151540" "151550" "151560" "151590" "151610" "151620" "151800"
#> [456] "151911" "151912" "151913" "151919" "151920" "152110" "152190"
#> [463] "152200" "110811" "110812" "110813" "110814" "110819" "110820"
#> [470] "110900" "843780" "843790" "843810" "843820" "843830" "843840"
#> [477] "843850" "843860" "843880" "843890" "847920"
#给每行数据命名
ICS1<-unlist(a2[1])
ICS2<-unlist(a2[2])
ICS3<-unlist(a2[3])
ICS4<-unlist(a2[4])
ICS5<-unlist(a2[5])
ICS6<-unlist(a2[6])
ICS7<-unlist(a2[7])
ICS8<-unlist(a2[8])
ICS9<-unlist(a2[9])
ICS10<-unlist(a2[10])
ICS11<-unlist(a2[11])
ICS12<-unlist(a2[12])
ICS13<-unlist(a2[13])
ICS14<-unlist(a2[14])
ICS15<-unlist(a2[15])
ICS16<-unlist(a2[16])
ICS17<-unlist(a2[17])
ICS18<-unlist(a2[18])
ICS19<-unlist(a2[19])
ICS20<-unlist(a2[20])
ICS21<-unlist(a2[21])
ICS22<-unlist(a2[22])
ICS23<-unlist(a2[23])
ICS24<-unlist(a2[24])
ICS25<-unlist(a2[25])
ICS26<-unlist(a2[26])
ICS27<-unlist(a2[27])
ICS28<-unlist(a2[28])
ICS29<-unlist(a2[29])
ICS30<-unlist(a2[30])
ICS31<-unlist(a2[31])
ICS32<-unlist(a2[32])
ICS33<-unlist(a2[33])
ICS34<-unlist(a2[34])
ICS35<-unlist(a2[35])
ICS36<-unlist(a2[36])
ICS<-list(ICS1,ICS2,ICS3,ICS4,ICS5,ICS6,ICS7,ICS8,ICS9,ICS10,ICS11,ICS12,ICS13,ICS14,ICS15,ICS16,ICS17,ICS18,ICS19,ICS20,ICS21,ICS22,ICS23,ICS24,ICS25,ICS26,ICS27,ICS28,ICS29,ICS30,ICS31,ICS32,ICS33,ICS34,ICS35,ICS36)

Created on 2019-05-13 by the reprex package (v0.2.1)

第一种:

ICS<list(ICS1,ICS2,ICS3,ICS4,ICS5,ICS6,ICS7,ICS8,ICS9,ICS10,ICS11,ICS12,ICS13,ICS14,ICS15,ICS16,ICS17,ICS18,ICS19,ICS20,ICS21,ICS22,ICS23,ICS24,ICS25,ICS26,ICS27,ICS28,ICS29,ICS30,ICS31,ICS32,ICS33,ICS34,ICS35,ICS36)
icsn<-c(1:36)
setwd("E:/论文数据/双边贸易数据/美国贸易数据")
file.names<-list.files()
data <- lapply(file.names, function(x){
  read.csv(x, header=T)}) 
ICSM=NULL
result=NULL
for (i in 1:length(data)) {
  for (j in 1:length(ICS)) {
    result=list(ICSM<-rbind(ICSM,(mutate(filter(as.data.frame(data[[i]]),Commodity.Code%in%ICS[[j]]),icsnb=icsn[j]))))
  }
}

第二种：

result=NULL
for (i in 1:length(data)) {
  for (j in 1:length(ICS)) {
    result=list(result,sapply(as.data.frame(data[i]),function(x){
      mutate(filter(x,Commodity.Code%in%ICS[[j]]),icsnb=icsn[j]))})
  }
}

但是两个代码都没有算出结果，第一种代码我觉得是for循环的结果赋值错了，第二种不知道哪里出错了。

Liechi · 2019年5月13日

lilya 我按照新手必读里给出的正确发帖姿势帮你把帖子编辑了一下，请你自己也阅读一遍。

tctcab · 2019年5月13日

lilya

写的太复杂了，用factor就行


ICS = c(
  "ICS1",
  "ICS2",
  "ICS3",
  "ICS4",
  "ICS5",
  "ICS6",
  "ICS7",
  "ICS8",
  "ICS9",
  "ICS10",
  "ICS11",
  "ICS12",
  "ICS13",
  "ICS14",
  "ICS15",
  "ICS16",
  "ICS17",
  "ICS18",
  "ICS19",
  "ICS20",
  "ICS21",
  "ICS22",
  "ICS23",
  "ICS24",
  "ICS25",
  "ICS26",
  "ICS27",
  "ICS28",
  "ICS29",
  "ICS30",
  "ICS31",
  "ICS32",
  "ICS33",
  "ICS34",
  "ICS35",
  "ICS36"
  )

## some example
commodity.code = sample(ICS, 20)
commodity.code
#>  [1] "ICS6"  "ICS26" "ICS16" "ICS31" "ICS27" "ICS7"  "ICS9"  "ICS17"
#>  [9] "ICS4"  "ICS21" "ICS20" "ICS22" "ICS23" "ICS19" "ICS14" "ICS12"
#> [17] "ICS3"  "ICS28" "ICS10" "ICS35"

## code commodity.code to factor
commodity.code.f = factor(commodity.code, levels = ICS)

## get icsnbe
icsnbe = as.numeric(commodity.code.f)
icsnbe
#>  [1]  6 26 16 31 27  7  9 17  4 21 20 22 23 19 14 12  3 28 10 35

Created on 2019-05-13 by the reprex package (v0.2.1)

对应到你的文件的话

data.m = lapply(data, function(df){
  df$icsnbe = as.numeric(factor(df$commodity.code, levels = ICS ))
  return(df)
})

lilya · 2019年5月13日

Liechi 好的好的，非常感谢，下次发帖一定按照正确姿势

lilya · 2019年5月13日

tctcab
如果我理解没有错的话，我这个ICS1-36都是向量，里边是有很多元素的。具体代码如下：

setwd("D:/data")
library(xlsx)
a<-read.xlsx("匹配结果最终版 - 副本.xlsx" ,1,encoding = "UTF-8",stringsAsFactors = FALSE)[,5]
a2<-strsplit(a,split=",")
a2[23]
#> [[1]]
#>   [1] "20110"  "20120"  "20130"  "20210"  "20220"  "20230"  "20311" 
#>   [8] "20312"  "20319"  "20321"  "20322"  "20329"  "20410"  "20421" 
#>  [15] "20422"  "20423"  "20430"  "20441"  "20442"  "20443"  "20450" 
#>  [22] "20500"  "20610"  "20621"  "20622"  "20629"  "20630"  "20641" 
#>  [29] "20649"  "20680"  "20690"  "20710"  "20721"  "20722"  "20723" 
#>  [36] "20731"  "20739"  "20741"  "20742"  "20743"  "20750"  "20810" 
#>  [43] "20820"  "20890"  "21011"  "21012"  "21019"  "21020"  "21090" 
#>  [50] "160100" "160210" "160220" "160231" "160239" "160241" "160242"
#>  [57] "160249" "160250" "160290" "160300" "40110"  "40120"  "40130" 
#>  [64] "40210"  "40221"  "40229"  "40291"  "40299"  "40310"  "40390" 
#>  [71] "40410"  "40490"  "40500"  "40610"  "40620"  "40630"  "40640" 
#>  [78] "40690"  "40700"  "40811"  "40819"  "40891"  "40899"  "100110"
#>  [85] "100190" "100200" "100300" "100400" "100510" "100590" "100610"
#>  [92] "100620" "100630" "100640" "100700" "100810" "100820" "100830"
#>  [99] "100890" "110100" "110210" "110220" "110230" "110290" "110311"
#> [106] "110312" "110313" "110314" "110319" "110321" "110329" "110411"
#> [113] "110412" "110419" "110421" "110422" "110423" "110429" "110430"
#> [120] "110710" "110720" "190110" "190120" "190190" "190211" "190219"
#> [127] "190240" "190410" "190510" "190520" "190530" "190540" "190590"
#> [134] "70110"  "70190"  "70200"  "70310"  "70320"  "70390"  "70410" 
#> [141] "70420"  "70490"  "70511"  "70519"  "70521"  "70529"  "70610" 
#> [148] "70690"  "70700"  "70810"  "70820"  "70890"  "70910"  "70920" 
#> [155] "70930"  "70940"  "70951"  "70952"  "70960"  "70970"  "70990" 
#> [162] "71010"  "71021"  "71022"  "71029"  "71030"  "71080"  "71090" 
#> [169] "71110"  "71120"  "71130"  "71140"  "71190"  "71210"  "71220" 
#> [176] "71230"  "71290"  "71310"  "71320"  "71331"  "71332"  "71333" 
#> [183] "71339"  "71340"  "71350"  "71390"  "71410"  "71420"  "71490" 
#> [190] "80110"  "80120"  "80130"  "80211"  "80212"  "80221"  "80222" 
#> [197] "80231"  "80232"  "80240"  "80250"  "80290"  "80300"  "80410" 
#> [204] "80420"  "80430"  "80440"  "80450"  "80510"  "80520"  "80530" 
#> [211] "80540"  "80590"  "80610"  "80620"  "80710"  "80720"  "80810" 
#> [218] "80820"  "80910"  "80920"  "80930"  "80940"  "81010"  "81020" 
#> [225] "81030"  "81040"  "81090"  "81110"  "81120"  "81190"  "81210" 
#> [232] "81220"  "81290"  "81310"  "81320"  "81330"  "81340"  "81350" 
#> [239] "81400"  "110510" "110520" "110610" "110620" "110630" "121010"
#> [246] "121020" "121210" "121230" "121291" "121292" "121299" "190300"
#> [253] "200110" "200120" "200190" "200210" "200290" "200310" "200320"
#> [260] "200410" "200490" "200520" "200530" "200540" "200551" "200559"
#> [267] "200560" "200570" "200590" "200600" "200710" "200791" "200799"
#> [274] "200811" "200819" "200820" "200830" "200840" "200850" "200860"
#> [281] "200870" "200880" "200892" "200899" "200911" "200919" "200920"
#> [288] "200930" "200940" "200950" "200960" "200970" "200980" "200990"
#> [295] "40900"  "170111" "170112" "170191" "170199" "170210" "170220"
#> [302] "170230" "170240" "170260" "170290" "170310" "170390" "170410"
#> [309] "170490" "90111"  "90112"  "90121"  "90122"  "90130"  "90140" 
#> [316] "90210"  "90220"  "90230"  "90240"  "90300"  "90411"  "90412" 
#> [323] "90420"  "90500"  "90610"  "90620"  "90700"  "90810"  "90820" 
#> [330] "90830"  "90910"  "90920"  "90930"  "90940"  "90950"  "91010" 
#> [337] "91020"  "91030"  "91040"  "91050"  "91091"  "91099"  "180100"
#> [344] "180310" "180320" "180400" "180500" "180610" "180620" "180631"
#> [351] "180632" "180690" "210110" "210130" "41000"  "71040"  "150100"
#> [358] "151710" "151790" "190220" "190230" "190490" "200510" "200580"
#> [365] "200891" "210120" "210210" "210220" "210230" "210310" "210320"
#> [372] "210330" "210390" "210410" "210420" "210500" "210610" "210690"
#> [379] "220900" "220110" "220190" "220210" "220290" "220300" "220410"
#> [386] "220421" "220429" "220430" "220510" "220590" "220600" "220810"
#> [393] "220820" "220830" "220840" "220850" "220890" "120100" "120210"
#> [400] "120220" "120300" "120400" "120500" "120600" "120710" "120720"
#> [407] "120730" "120740" "120750" "120760" "120791" "120792" "120799"
#> [414] "120810" "120890" "20900"  "150200" "150300" "150410" "150420"
#> [421] "150430" "150510" "150590" "150600" "150710" "150790" "150810"
#> [428] "150890" "150910" "150990" "151000" "151110" "151190" "151211"
#> [435] "151219" "151221" "151229" "151311" "151319" "151321" "151329"
#> [442] "151410" "151490" "151511" "151519" "151521" "151529" "151530"
#> [449] "151540" "151550" "151560" "151590" "151610" "151620" "151800"
#> [456] "151911" "151912" "151913" "151919" "151920" "152110" "152190"
#> [463] "152200" "110811" "110812" "110813" "110814" "110819" "110820"
#> [470] "110900" "843780" "843790" "843810" "843820" "843830" "843840"
#> [477] "843850" "843860" "843880" "843890" "847920"
#给每行数据命名
ICS1<-unlist(a2[1])
ICS2<-unlist(a2[2])
ICS3<-unlist(a2[3])
ICS4<-unlist(a2[4])
ICS5<-unlist(a2[5])
ICS6<-unlist(a2[6])
ICS7<-unlist(a2[7])
ICS8<-unlist(a2[8])
ICS9<-unlist(a2[9])
ICS10<-unlist(a2[10])
ICS11<-unlist(a2[11])
ICS12<-unlist(a2[12])
ICS13<-unlist(a2[13])
ICS14<-unlist(a2[14])
ICS15<-unlist(a2[15])
ICS16<-unlist(a2[16])
ICS17<-unlist(a2[17])
ICS18<-unlist(a2[18])
ICS19<-unlist(a2[19])
ICS20<-unlist(a2[20])
ICS21<-unlist(a2[21])
ICS22<-unlist(a2[22])
ICS23<-unlist(a2[23])
ICS24<-unlist(a2[24])
ICS25<-unlist(a2[25])
ICS26<-unlist(a2[26])
ICS27<-unlist(a2[27])
ICS28<-unlist(a2[28])
ICS29<-unlist(a2[29])
ICS30<-unlist(a2[30])
ICS31<-unlist(a2[31])
ICS32<-unlist(a2[32])
ICS33<-unlist(a2[33])
ICS34<-unlist(a2[34])
ICS35<-unlist(a2[35])
ICS36<-unlist(a2[36])
ICS<-list(ICS1,ICS2,ICS3,ICS4,ICS5,ICS6,ICS7,ICS8,ICS9,ICS10,ICS11,ICS12,ICS13,ICS14,ICS15,ICS16,ICS17,ICS18,ICS19,ICS20,ICS21,ICS22,ICS23,ICS24,ICS25,ICS26,ICS27,ICS28,ICS29,ICS30,ICS31,ICS32,ICS33,ICS34,ICS35,ICS36)

Created on 2019-05-13 by the reprex package (v0.2.1)

tctcab · 2019年5月13日

lilya

终于看懂了，原来是个left_join的问题…

这样

library(tidyr)
library(dplyr)

x = data.table::fread(
"
V1 V2
ICS1 20110,20120,20130,20210
ICS2 20312,20319,20321
ICS3 20422,20423,20430
",
sep= " ")

## mapping between ICS and commodity.code
map = tidyr::separate_rows(x,V2)
map
#>       V1    V2
#>  1: ICS1 20110
#>  2: ICS1 20120
#>  3: ICS1 20130
#>  4: ICS1 20210
#>  5: ICS2 20312
#>  6: ICS2 20319
#>  7: ICS2 20321
#>  8: ICS3 20422
#>  9: ICS3 20423
#> 10: ICS3 20430

## example data.frame with commodity.code
df.test = data.frame(commodity.code = c(
  "20110",
  "20319",
  "20423"
),stringsAsFactors = FALSE)

## join
df.test %>%
  left_join(map, by = c("commodity.code" = "V2"))
#>   commodity.code   V1
#> 1          20110 ICS1
#> 2          20319 ICS2
#> 3          20423 ICS3

lilya · 2019年5月14日

tctcab 哦_{我懂了，你这个比我用循环的思路方便多了，非常感谢。还有几个问题，想要麻烦你帮我看看。1、如果我对多个文件都要执行这个操作，是不是要使用for循环去做了？}
2、可以麻烦你看下我的第一种代码吗？我对for循环结果赋值不怎么懂。
3、我的第二种代码，一直显示说filter不能用于"factor"和"charater"的数据

tctcab · 2019年5月14日

lilya

你的两段代码都有很多错，不知如何说起，而且写的鬼斧神工我理半天也没理清…

两段代码目前理出你的思路都是筛出data.frame里特定行，添加新列，再合起来。

看下面的警句（敲黑板）：

在R里不要拆开数据框的行分开处理，如果你拆了，那就一定哪里不对
tctcab

用tidyverse时如果发现特别费劲，仿佛在跟tidyverse包搏斗，那就一定哪里不对
tctcab

我已經给你指出了left_join()这条正确的道路了，放弃你的方法1方法2那两段代码，远离万恶之源，怎么样？

要对一堆文件用left_join()的话，lapply和for循环其实都可以，不过apply的代码更紧凑一些，推荐

tctcab · 2019年5月14日

lilya

按照你已有的ICS和data数据，完整代码：


library(dplyr)

ICS =list(
  ICS1 = c("20110","20120","20130","20210"),
  ICS2 = c("120110","120120","120130","120210","120210")
)

data =list(
  d1 = data.frame(commodity.code = c("20110","120110"), stringsAsFactors=FALSE),
  d2 = data.frame(commodity.code = c("20120","120130"), stringsAsFactors=FALSE)
)



get_ICSMAP = function(ICS){
  tmp.list = lapply(1:length(ICS), function(i){
    tmpdf = data.frame(commodity.code = ICS[[i]])
    tmpdf$icsnb = names(ICS)[i]
    
    return(tmpdf)
  })
  
  return(do.call(rbind,tmp.list))
}

ICSMAP = get_ICSMAP(ICS)

data.mapped = lapply(data, function(df){
  df.new = df %>% left_join(ICSMAP)
  return(df.new)
})
#> Joining, by = "commodity.code"
#> Warning: Column `commodity.code` joining character vector and factor,
#> coercing into character vector
#> Joining, by = "commodity.code"
#> Warning: Column `commodity.code` joining character vector and factor,
#> coercing into character vector

data.mapped
#> $d1
#>   commodity.code icsnb
#> 1          20110  ICS1
#> 2         120110  ICS2
#> 
#> $d2
#>   commodity.code icsnb
#> 1          20120  ICS1
#> 2         120130  ICS2

Created on 2019-05-14 by the reprex package (v0.2.1)

lilya · 2019年5月16日

tctcab好滴，懂了，非常非常感谢！