Tokyo.R#87 http://tokyor.connpass.com/event/182672/ での発表スライド
data.tableShinya Uryu ( @u_ribo uribo)20200801 Tokyo.R#87=Λֶ΅͏ʂύοέʔδࠓͦ͜ɺDT[i, j, by]fifelsefcase.SD.Nnafill.GRP
View Slide
(JU)VC4QPOTPSTͷօ͞·IUUQTHJUIVCDPNTQPOTPSTVSJCPఏڙ@yutannihilation @katsurakob @kanji14134 @siero5335 @ niszet@ ito4303 @ ak9782427
࣍ σʔλૢ࡞ EBUBUBCMFͷ֦͕Γ⁞ EBUBUBCMFͷར
MJCSBSZ EBUBUBCMFWFSTJPO 4JODFPO$3"/⁞
EBUBUBCMF&YUFOTJPOPGEBUBGSBNFʢେنσʔλͷʣޮతͳॲཧɺߴͳಡΈॻ͖͕ಘҙEBUBUBCMFΫϥε %5ΛఏڙEBUBGSBNFʹࣅͨ<Λ༻͍ͨૢ࡞IUUQTSEBUBUBCMFHJUMBCJPEBUBUBCMF
group_by joindata.table 13s 92sdplyr 157s 392sdatatable 72s 1301spandas 97s 1514sdask 127s out of memoryDataFrames.jl 43s 5755sspark 37s 369sੑೳଌఆIUUQTIPBJHJUIVCJPECCFODINBSL(# ߦྻ
ੑೳଌఆ .# ߦྻtimedata.table::fread() 2.85sfread()ʢcolClassࢦఆʣ 2.57sreadr::read_csv() 14.0sfst::read_fst() 2.44s
EQMZSͱͷൺֱґଘ͕গͳ͍ϝϞϦར༻ޮʹҧ͍อकతͳ։ൃʢWTϦϦʔεʣEQMZSΑΓྺ࢙ͷ͋Δ։ൃྺ͕ͩ҆ఆతେ෯ʹڍಈ͕มΘΔ͜ͱ͕كܗࣜͷૢ࡞ੑҰ؏
EQMZSͱͷൺֱ ґଘͷগͳ͞tools::package_dependencies("data.table", recursive = TRUE)[[1]]#> [1] "methods"tools::package_dependencies("dplyr", recursive = TRUE)[[1]]#> [1] "ellipsis" "generics" "glue" "lifecycle" "magrittr"#> [6] "methods" "R6" "rlang" "tibble" "tidyselect"#> [11] "utils" "vctrs" "cli" "crayon" "fansi"#> [16] "pillar" "pkgconfig" "purrr" "digest" "assertthat"#> [21] "grDevices" "utf8" "tools"⁞
EQMZSͱͷൺֱ ϝϞϦར༻ޮ $PNQBSJOH&GpDJFODZBOE4QFFEPGAEBUBUBCMFA"EEJOHWBSJBCMFT pMUFSJOHSPXT BOETVNNBSJ[JOHCZHSPVQu5ZTPO#BSSFUUDPNIUUQTUZTPOCBSSFUUDPNKFLZMMVQEBUFEBUBUBCMF@NFNPSZྻૢ࡞ʹ͓͍ͯEBUBUBCMFͷํ͕Θ͔ͣʹ༏ΕΔநग़ͷաఔͰάϧʔϓͷूఔඇޮʁEBUBUBCMF͕উΔIUUQTUXJUUFSDPNIFBMUIBOETUBUTTUBUVT
EQMZSͱͷൺֱ ϝϞϦར༻ޮ d <- data.table(x = rnorm(10))address(d)#> [1] “0x7f8afc694400"df_tbl <-copy(d) %>%as_tibble()address(df_tbl)#> [1] "0x7f8ace2fb1c8"d[, z := rnorm(10)]address(d)#> [1] "0x7f8afc694400"มʹॲཧΛՃ͑ͯΦϒδΣΫτ͕ڞ༗͞ΕΔϝϞϦΞυϨε͕มΘΒͳ͍df_tbl <-df_tbl %>%mutate(z = rnorm(1e6))address(df_tbl)#> [1] "0x7f8afc68eea8"UJEZWFSTFϝϞϦΞυϨε͕มΘΔ
ͳͥޮ͕ྑ͘ɺߴͳͷ͔$ݴޠ $Ͱͳ͍ʹΑΔ࣮TIBMMPXίϐʔͰͷྻͷͷՃɾߋ৽ɾআΩʔར༻ͱόΠφϦαʔνʹΑΔݕࡧ
BSSBOHFGJMUFSDT[i, j, by]EBUBUBCMFߏจNVUBUFTFMFDUHSPVQ@CZTVNNBSJTFର ૢ࡞ ू߹
df %>%group_by(id) %>%summarise(val1_sum = sum(val1),.groups = "drop")id code val11 c 11 b 21 c 32 a 42 a 51 b 62 a 71 c 8id val1_sum1 202 16JEྻͷ͝ͱʹWBMΛ߹ܭ͢Δྫ ⁞DT[,.(val1_sum = sum(val1)),by = id]
id code val11 c 11 b 21 c 32 a 42 a 51 b 62 a 71 c 8id val1_sum1 122 16DPEFCͷ݅Ͱྫ⁞ͱಉ͡ॲཧྫ df %>%filter(code != "b") %>%group_by(id) %>%summarise(val1_sum = sum(val1),.groups = "drop")DT[code != "b",.(val1_sum = sum(val1)),by = id]
id val1_sum val2_sum1 12 362 16 40DPEFCͷ݅ɺJE͝ͱʹWBMྻͷ߹ܭྫ id code val1 val21 c 1 91 b 2 101 c 3 112 a 4 122 a 5 131 b 6 142 a 7 151 c 8 16DT[code != "b",lapply(.SD, sum),by = id,.SDcols = patterns("^val")]4VCTFUPGUIF%BUBUBCMFdf %>%filter(code != "b") %>%group_by(id) %>%summarise(across(starts_with("val"), sum),.groups = "drop")
df %>%filter(val1 < 3)df %>%filter(val1 < 3 | val2 > 9)df %>%filter(val1 < 3 & val2 > 9)Jߦͷநग़ pMUFSDT[val1 < 3]DT[val1 < 3 | val2 > 9] # or݅DT[val1 < 3 & val2 > 9] # and݅df %>%filter(code %in% c("a", “d"))df %>%filter(!code %in% c("a", "b"))df %>%filter(stringr::str_detect(code, "^c$"))# on۟DT[c("a", "d"),on = "code",nomatch = 0]# a,bҎ֎DT[!code %in% c("a", "b")]# ਖ਼نදݱͰͷࢦఆDT[like(code, "^c$")]จࣈྻ
Jߦͷฒͼସ͑ BSSBOHFDT[order(id)] # খ͍͞ॱ(ঢॱ)#> id code val1 val2#> 1: 1 c 1 9#> 2: 1 b 2 10#> 3: 1 c 3 11#> 4: 1 b 6 14#> 5: 1 c 8 16#> 6: 2 a 4 12#> 7: 2 a 5 13#> 8: 2 a 7 15df %>%arrange(id)df %>%arrange(desc(id))setorder(DT, -id, na.last = TRUE)DT # େ͖͍ॱ(߱ॱ)#> id code val1 val2#> 1: 2 a 4 12#> 2: 2 a 5 13#> 3: 2 a 7 15#> 4: 1 c 1 9#> 5: 1 b 2 10#> 6: 1 c 3 11#> 7: 1 b 6 14#> 8: 1 c 8 16
Kྻͷબ TFMFDU୯ҰෳෳDT[, "id"] DT[, list(id)]DT[, .(id)]DT[, 1]cols <- c("val1", "val2") DT[, c(1, 2)]DT[, ..cols]DT[, cols, with = FALSE]DT[, list(val1, val2)]DT[, .(val1, val2)] จࣈྻϕΫτϧΛ༩͑ΔࡍʹඞཁʢDPMTΛྻ໊ѻ͍͠ͳ͍ʣdf %>%select(id)df %>%select(val1, val2)ෳআ DT[, !c("id")]DT[, !cols, with = FALSE]ˠ࣍ͷεϥΠυͰߦͬͨํ͕ޮత MJTU ͷγϣʔτΧοτ
KͷՃɾՃɾআ NVUBUFηΠνPSΞγΧԋࢉࢠ1ZUIPOͰಋೖ+PFM(BSMJDI.JMMFS 64'JTIBOE8JMEMJGF4FSWJDF1VCMJDEPNBJODT[, code_title = toupper(code)]#> Error in `[.data.table`(DT, , code_title = toupper(code)):ΘΕ͍ͯͳ͍Ҿ (code_title = toupper(code))DT[, code_title := toupper(code)]݁Ռग़ྗ͞Εͳ͍෭࡞༻͕͋Δdt_copy <-copy(DT)address(DT)#> [1] "0x7f953bc4b800"address(dt_copy)#> [1] "0x7f953bdb8800"EBUBUBCMFͰͷEFFQDPQZ
KͷՃɾՃɾআ NVUBUFDT[, code_title := toupper(code)] # ՃDT[, code := toupper(code)] # ߋ৽DT[, code := NULL] # আdf %>%mutate(code_title = toupper(code))df %>%mutate(code = toupper(code))df %>%mutate(code_title = NULL)df %>%mutate(val1 = val1 + 2,val2 = val2 + val1)୯ҰෳDT[, c("val1", "val2") := list(val1 + 2, val2 + val1)]DT[, ':=' (val1 = val1 + 2, val2 = val2 + val1)]ࠨลӈล݅DT[c("a", "c"), val2 := val2 * 2, on = c("code")]DPEFྻͷ͕B DͰ͋ΕWBM
CZू TVNNBSJTFDT[,.(val1_sum = sum(val1)),by = code]DT[, id := NULL][,lapply(.SD, sum),by = code,.SDcols = is.numeric]df %>%group_by(code) %>%summarise(val1_sum = sum(val1),.groups = “drop")df %>%select(!id) %>%group_by(code) %>%summarise(across(where(is.numeric), sum),.groups = "drop")EQMZSHSPVQ@CZ ͱಉ4%DPMTʹ༩͑ΒΕͨྻ͕4%ʹ͞ΕΔ୯Ұෳ.SDcols = val1:val2.SDcols = c("val1", "val2").SDcols = patterns("^val")͜ΕΒͷࢦఆํ๏Ͱ0,
DT[,.(val1_sum = sum(val1)),keyby = code]#> code val1_sum#> 1: a 16#> 2: b 8#> 3: c 12άϧʔϓԽͷิॿػೳ/ /(31(31DT[,.(val1_sum = sum(val1)),by = code]#> code val1_sum#> 1: c 12#> 2: b 8#> 3: a 16LFZCZʹࢦఆͨ͠มͷฒͼͰฒͼସ͑ΔDT[, .N, by = id]#> id N#> 1: 1 5#> 2: 2 3DT[, .GRP, by = id]#> id GRP#> 1: 1 1#> 2: 2 2άϧʔϓมͷΧϯτDT[, .NGRP, by = id]#> id GRP#> 1: 1 2#> 2: 2 2άϧʔϓ JEͷݸάϧʔϓมͷ֬ೝLFZCZ
݁߹ KPJOPO۟͘͠NFSHF inner_join(flights,planes,by = "tailnum")library(nycflights13)flights_dt <- as.data.table(flights)planes_dt <- as.data.table(planes)left_join(flights,planes,by = "tailnum")merge(flights_dt,planes_dt,all.x = TRUE,all.y = FALSE,by = "tailnum")flights_dt[planes_dt, on = "tailnum"]
ॎԣมlibrary(tidyr)data("fish_encounters")#> # A tibble: 114 x 3#> fish station seen#> #> 1 4842 Release 1#> 2 4842 I80_1 1#> 3 4842 Lisbon 1#> 4 4842 Rstr 1#> 5 4842 Base_TD 1#> 6 4842 BCE 1#> 7 4842 BCW 1#> 8 4842 BCE2 1#> 9 4842 BCW2 1#> 10 4842 MAE 1#> # … with 104 more rows#> fish Release_1 I80_1_1 Lisbon_1 Rstr_1 Base_TD_1 BCE_1 BCW_1 BCE2_1 BCW2_1#> 1: 4842 1 1 1 1 1 1 1 1 1#> 2: 4843 1 1 1 1 1 1 1 1 1#> 3: 4844 1 1 1 1 1 1 1 1 1…((লུ)fish_encounters %>%as.data.table() %>%dcast(... ~ station + seen, value.var = "seen")fish_encounters %>%pivot_wider(names_from = station, values_from = seen)͋Μ·ΓΠέͯͳ͍XJEFS⁞
data("billboard")#> # A tibble: 317 x 79#> artist track date.entered wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8#> #> 1 2 Pac Baby… 2000-02-26 87 82 72 77 87 94 99 NA#> 2 2Ge+h… The … 2000-09-02 91 87 92 NA NA NA NA NA#> 3 3 Doo… Kryp… 2000-04-08 81 70 68 67 66 57 54 53#> # … with 314 more rows, and 68 more variables: wk9 , wk10 ,billboard %>%pivot_longer(cols = starts_with("wk"),names_to = "week",names_prefix = "wk",values_to = "rank",values_drop_na = TRUE)#> artist track date.entered week rank#> 1: 2 Pac Baby Don't Cry (Keep... 2000-02-26 wk1 87#> 2: 2Ge+her The Hardest Part Of ... 2000-09-02 wk1 91#> 3: 3 Doors Down Kryptonite 2000-04-08 wk1 81#> 4: 3 Doors Down Loser 2000-10-21 wk1 76#> 5: 504 Boyz Wobble Wobble 2000-04-15 wk1 57#> ---#> 5303: Creed Higher 1999-09-11 wk63 50#> 5304: Lonestar Amazed 1999-06-05 wk63 45#> 5305: Creed Higher 1999-09-11 wk64 50#> 5306: Lonestar Amazed 1999-06-05 wk64 50#> 5307: Creed Higher 1999-09-11 wk65 49billboard %>%as.data.table() %>%melt(id = c("artist", "track", "date.entered"),measure = patterns("^wk"),variable.name = "week",value.name = "rank",na.rm = TRUE)ʜॎԣม MPOHFS UJEZGBTUͰੋඇ
ϕΫτϧૢ࡞ؔfcase(x < 5L, 1L,x >= 5L, 3L) # dplyr::case_when#> [1] 1 1 1 1 3 3 3 3 NA 3GDBTFx <- c(seq_len(8), NA, 10L)fifelse(x < 5L, TRUE, FALSE) # dplyr::if_else#> [1] TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE NA FALSEpGFMTF
ϕΫτϧૢ࡞ؔfcoalesce(x, seq_len(10)) # dplyr::coalesce#> [1] 1 2 3 4 5 6 7 8 9 10GDPBMFTDFOBpMMnafill(x, fill = 0)#> [1] 1 2 3 4 5 6 7 8 0 10
ؔ࿈ύοέʔδ
EUQMZSόοΫάϥϯυʹEBUBUBCMFEQMZSEBUBUBCMF࣮ߦEBUBUBCMFEUQMZSEQMZSEQMZSߏจ͕͑ͯEBUBUBCMFʹ͍ۙύϑΥʔϚϯε
EUQMZSdt_starwars %>%group_by(species) %>%summarise(height = mean(height, na.rm = TRUE),.groups = “drop")library(dtplyr)dt_starwars <-lazy_dt(starwars)#> Source: local data table [?? x 3]#> Call: `_DT1`[, .(height = mean(height,#> na.rm = TRUE), .groups = "drop"),#> keyby = .(species)]#>#> species height .groups#> #> 1 181. drop#> 2 Aleena 79 drop#> 3 Besalisk 198 drop#> 4 Cerean 198 drop#> 5 Chagrian 196 drop#> 6 Clawdite 168 drop#> # Use as.data.table()/as.data.frame()/as_tibble() to access results.Last.value %>%as_tibble()EQMZSͰͷॲཧ࣮ߦ݁ՌΛڥʹฦ͢
"OE.PSFʜUJEZUBCMFEUQMZS͕αϙʔτ͢ΔͷEQMZSUJEZSͷؚؔΊΔUJEZGBTUʜUJEZSͷQJWPU@ ͳͲUJEZGTUʜGTUΛ͍ͬͯͯߴʢͬΆ͍ʣ
·ͱΊEBUBUBCMF σʔλͷૢ࡞<ԋࢉࢠͰ UJEZWFSTFʹྼΒͳ͍ػೳ⁞ ߴɺϝϞϦར༻ޮྑ͍ؔ࿈ɺ֦ுύοέʔδॆ࣮
Enjoy☝ऴIUUQTHJUIVCDPNTQPOTPSTVSJCP
s_uryu
riku929hr
mazrean
kasacchiful
afilina
yuhei_fujita
codehex
trapezoid
hiroyaonoe
always_allokay
niftycorp
faithandbrave
lemiorhan
mojombo
eileencodes
smashingmag
bcantrill
62gerente
geoffreycrofte
dotmariusz
davidbonilla
holman
addyosmani
morganepeng