最近沉迷于学习sparklyr。看标题就知道，今天讲的是如何手撕数据。

这次会用到一个sparklyr扩展包：

首先读取数据：

conf <- spark_config()
conf$spark.executor.memory <- "16GB"
conf$spark.memory.fraction <- 0.9
conf$spark.executor.cores <- 4
conf$spark.dynamicAllocation.enabled <- "false"

sc <- spark_connect(master="spark://127.0.1.1:7077", 
                    version = "2.3.2",
                    config = conf,
                    spark_home = "/home/spark/spark-2.3.2-bin-hadoop2.7/")


test<-spark_read_parquet(sc, 
                         path = '/home/spark_test_file',
                         memory = F)
> test
# Source: spark<?> [?? x 2]
     V17 V18                                                            
   <int> <chr>                                                          
 1    41 12150.3112-3                                                   
 2  1394 12150.3112-14|12150.3113-93|12149.3113-201                     
 3   747 12150.3113-105|12149.3113-34                                   
 4   605 12149.3113-47

步骤有两个：1. 根据“|”符号把V18每行分成多个元素的list，再根据每行V18中元素数量，一行拆成多行，也就是unnest；2. 根据“-”符号把拆成多行的V18分成两列。

首先执行第一步：

> test2<-test %>%
+   mutate(V18 = split(V18, '\\\\|')) %>%
+   sdf_explode('V18')
> test2 %>% select(V17,V18)
# Source: spark<?> [?? x 2]
     V17 V18           
   <int> <chr>         
 1    41 12150.3112-3  
 2  1394 12150.3112-14 
 3  1394 12150.3113-93 
 4  1394 12149.3113-201

这里只能用split来拆分，strsplit、str_split等函数都不适用。需要注意的是，在sparklyr中使用split且避免程序将符号识别为正则表达式，需要加4个反斜杠（“\\\\”）。之后用sparklyr.nested中的sdf_explode完成unnest操作。

参考资料：https://stackoverflow.com/questions/48689826/unnest-seperate-multiple-column-values-into-new-rows-using-sparklyr

第二步比较简单，用sparklyr自带函数就行：

> test3<-test2 %>%
+   mutate(V18 = split(V18, '-')) %>%
+   sdf_separate_column('V18', into = c('V19','V20'))
> test3 %>% select(V19,V20)
# Source: spark<?> [?? x 2]
   V19        V20  
   <chr>      <chr>
 1 12150.3112 3    
 2 12150.3112 14   
 3 12150.3113 93   
 4 12149.3113 201  
 5 12150.3113 105

搞定。改天再来研究怎么把拆开的数据再拼回去。

最后，用data.table完成相同操作，来个速度对比：

> system.time({
+   test2<-test %>%
+     mutate(V18 = split(V18, '\\\\|')) %>%
+     sdf_explode('V18') %>%
+     mutate(V18 = split(V18, '-')) %>%
+     sdf_separate_column('V18', into = c('V19','V20'))
+   print(test2 %>% select(V19,V20))
+ })
# Source: spark<?> [?? x 2]
   V19        V20  
   <chr>      <chr>
 1 12150.3112 3    
 2 12150.3112 14   
 3 12150.3113 93   
 4 12149.3113 201  
 5 12150.3113 105  
 6 12149.3113 34   
 7 12149.3113 47   
 8 12149.3113 14   
 9 12148.3113 132  
10 12146.3112 62   
# … with more rows
   user  system elapsed 
  0.162   0.001   1.093 

> system.time({
+   dt2<-data.table(dt)[,.(V18 = unlist(strsplit(V18, split = '\\|')))]
+   dt2[,c('V19','V20'):=data.table(str_split(V18, pattern = '-', simplify = T))]
+   print(dt2[,.(V19,V20)])
+ })
                V19 V20
      1: 12150.3112   3
      2: 12150.3112  14
      3: 12150.3113  93
      4: 12149.3113 201
      5: 12150.3113 105
     ---               
1768545: 12130.3112  45
1768546: 12130.3113  90
1768547: 12129.3114  71
1768548: 12129.3115  19
1768549: 12128.3115  45
   user  system elapsed 
  4.795   0.000   4.793

ITPub博客

R语言sparklyr：拆分字符串、分行、分列

最新文章