根据列值映射行

Question

如果日志文件位于csv中，则可以使用merge在R/Python中轻松完成此任务。

但日志文件是写在下面的语法

Key=1|Time=146656456446 
Key=2|Time=146656456447 
Key=1|Time=146656456448|field=10 
Key=2|Time=146656456450|field=11 

可能是有什么办法合并，并得到以下列方式的差异，以

Key,Time1,Time2,diff,field 
Key=1,146656456446,146656456448,2,10 
Key=2,146656456447,146656456450,3,11 

Answer 1

如果您不需要时间列中，以下将起作用

library(tidyverse) 
library(data.table) 

df <- read_table(
"test  
Key=1|Time=146656456446 
Key=2|Time=146656456447 
Key=1|Time=146656456448 
Key=2|Time=146656456450") 

将字符串分隔为“|”然后通过“=”，以获得数量

df <- 
df %>% 
    separate(test, into = c("Key", "Time"), sep = "\\|") %>% 
    separate(Time, into = c("Timepoint", "Time"), sep = "=") 

df 
# A tibble: 4 × 3 
    Key Timepoint   Time 
* <chr>  <chr>  <chr> 
1 Key=1  Time 146656456446 
2 Key=2  Time 146656456447 
3 Key=1  Time 146656456448 
4 Key=2  Time 146656456450 

更改时间数字和组由密钥计算差异

df$Time <- as.numeric(df$Time) 

df <- 
df %>% 
    group_by(Key) %>% 
    summarise(Diff = diff(Time)) 

df 
# A tibble: 2 × 2 
    Key Diff 
    <chr> <dbl> 
1 Key=1  2 
2 Key=2  3 

Answer 2

我的评论转换为答案，这里是一个使用“data.table的方法“包。

library(data.table) 
x <- "path/to/yourLogFile.txt"  
mydt <- fread(x, header = FALSE, col.names = c("Key", "Time")) 

dcast(mydt[, Time := as.numeric(sub("Time=", "", Time))][ 
    , Ind := sequence(.N), Key], Key ~ Ind, value.var = "Time")[ 
    , Diff := `2` - `1`][] 
#  Key   1   2 Diff 
# 1: Key=1 146656456446 146656456448 2 
# 2: Key=2 146656456447 146656456450 3 

使用我的“splitstackshape”包和相同的步骤，在数据读取可能看起来像另一个类似的办法：

library(splitstackshape) 
dcast(getanID(cSplit(mydt, "Time", "="), "Key"), 
     Key ~ Time_1 + .id, value.var = "Time_2")[ 
     , Diff := Time_2 - Time_1, by = Key][] 
#  Key  Time_1  Time_2 Diff 
# 1: Key=1 146656456446 146656456448 2 
# 2: Key=2 146656456447 146656456450 3 

对于读取日志文件，我做了如下假设：

• 你知道有两列预期。
• 您的日志文件当前没有列名称（因此为header = FALSE）。
• 您希望数据由|字符分隔，fread可以自动检测。

---

更新

这是不漂亮，但工程....

dcast(getanID(cSplit(mydt, names(mydt), "="), "Key_2"), 
     Key_2 ~ .id, fun=list(I, I), value.var = list("Field_2", "Time_2"), fill = 0)[ 
     , c("Field_2_I_1", "Diff") := list(NULL, Time_2_I_2 - Time_2_I_1)][] 
## Key_2 Field_2_I_2 Time_2_I_1 Time_2_I_2 Diff 
## 1:  1   10 146656456446 146656456448 2 
## 2:  2   11 146656456447 146656456450 3 

的样本数据

## Just to simulate a log file like the one you describe.... 
## "temp" would be your actual file.... 
x <- c("Key=1|Time=146656456446", "Key=2|Time=146656456447", 
     "Key=1|Time=146656456448|field=10", "Key=2|Time=146656456450|field=11") 
temp <- tempfile() 
writeLines(x, temp) 

mydt <- fread(temp, header = FALSE, fill = TRUE, 
       col.names = c("Key", "Time", "Field")) 
mydt 
##  Key    Time Field 
## 1: Key=1 Time=146656456446   
## 2: Key=2 Time=146656456447   
## 3: Key=1 Time=146656456448 field=10 
## 4: Key=2 Time=146656456450 field=11