如何将键/值字符串转换为不同的行? [英] How to transform a key/value string into distinct rows?

问题描述

我有一个带有键值字符串的R数据集，如下所示:

I have a R dataset with key value strings which looks like below:

quest<-data.frame(city=c("Atlanta","New York","Atlanta","Tampa"), key_value=c("rev=63;qty=1;zip=45987","rev=10.60|34;qty=1|2;zip=12686|12694","rev=12;qty=1;zip=74268","rev=3|24|8;qty=1|6|3;zip=33684|36842|30254"))

其翻译为:

      city                                  key_value
1  Atlanta                     rev=63;qty=1;zip=45987
2 New York       rev=10.60|34;qty=1|2;zip=12686|12694
3  Atlanta                     rev=12;qty=1;zip=74268
4    Tampa rev=3|24|8;qty=1|6|3;zip=33684|36842|30254

基于上述数据框，我如何创建一个如下所示的新数据框:

Based on the above dataframe how can I create a new data frame which looks like below :

      city  rev qty   zip
1  Atlanta 63.0   1 45987
2 New York 10.6   1 12686
3 New York 34.0   2 12686
4  Atlanta 12.0   1 74268
5    Tampa  3.0   1 33684
6    Tampa 24.0   6 33684
7    Tampa  8.0   3 33684

"|"是通用的定界符，它将确定要创建的行数.

"|" is the common delimiter which will determine the number of rows to be created.

推荐答案

先用;分开，然后用=和|分开，并组合成矩阵，并使用第一部分作为名称.然后重复原始数据帧的行，但是每行要找到很多行，然后合并.在这里，我不会将任何列都转换为数字，而是将其保留为字符.

Split by ;, then by = and |, and combine into a matrix, using the first part as the name. Then repeat the rows of the original data frame by however many rows were found for each, and combine. I don't convert here any columns to numeric, they're left as character.

a <- strsplit(as.character(quest$key_value), ";")
a <- lapply(a, function(x) {
    x <- do.call(cbind, strsplit(x, "[=|]"))
    colnames(x) <- x[1,]
    x[-1,,drop=FALSE]
})
b <- quest[rep(seq_along(a), sapply(a, nrow)), colnames(quest) != "key_value", drop=FALSE]
out <- cbind(b, do.call(rbind, a), stringsAsFactors=FALSE)
rownames(out) <- NULL
out
##       city   rev qty   zip
## 1  Atlanta    63   1 45987
## 2 New York 10.60   1 12686
## 3 New York    34   2 12694
## 4  Atlanta    12   1 74268
## 5    Tampa     3   1 33684
## 6    Tampa    24   6 36842
## 7    Tampa     8   3 30254

这篇关于如何将键/值字符串转换为不同的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！