我想分析网站上的一些货币交易数据，但是这些数据只能通过复制和粘贴来访问。我将其复制到计算机的剪贴板中，然后通过以下方式将其导入R：
#df <- read.table("clipboard", header = FALSE, sep = "\t", stringsAsFactors = FALSE, na.strings = "", fill = TRUE)
但是，当数据帧读入R时，它会将单个观察结果分成四行：
df <- structure(list(V1 = c("Buy", "Completed", "Fee1.00 USD", "Total199.00 USD", "Buy", "Completed", "Fee0.50 USD", "Total100.00 USD", "Buy", "Completed", "Fee0.64 USD", "Total127.00 USD"), V2 = c(NA, "2021-02-11 20:49:19", NA, NA, NA, "2021-02-11 20:48:03", NA, NA, NA, "2021-02-11 20:47:22", NA, NA), V3 = c(NA, "0.11057", NA, NA, NA, "82.146", NA, NA, NA, "30.15", NA, NA)), row.names = c(NA, 12L), class = "data.frame")
df
# V1 V2 V3
#1 Buy <NA> <NA>
#2 Completed 2021-02-11 20:49:19 0.11057
#3 Fee1.00 USD <NA> <NA>
#4 Total199.00 USD <NA> <NA>
#5 Buy <NA> <NA>
#6 Completed 2021-02-11 20:48:03 82.146
#7 Fee0.50 USD <NA> <NA>
#8 Total100.00 USD <NA> <NA>
#9 Buy <NA> <NA>
#10 Completed 2021-02-11 20:47:22 30.15
#11 Fee0.64 USD <NA> <NA>
#12 Total127.00 USD <NA> <NA>
因此，我想像这样将每四列的行折叠成一列，以覆盖由于数据导入过程而产生的缺失值：
want <- structure(list(V1 = structure(c(1L, 1L, 1L), .Label = "Buy", class = "factor"), V2 = structure(c(1L, 1L, 1L), .Label = "Completed", class = "factor"), V3 = structure(3:1, .Label = c("2/11/2021 20:47", "2/11/2021 20:48", "2/11/2021 20:49"), class = "factor"), V4 = c(0.11057, 82.146, 30.15), V5 = structure(c(3L, 1L, 2L), .Label = c("Fee0.50 USD", "Fee0.64 USD", "Fee1.00 USD"), class = "factor"), V6 = structure(c(3L, 1L, 2L), .Label = c("Total100.00 USD", "Total127.00 USD", "Total199.00 USD"), class = "factor")), class = "data.frame", row.names = c(NA, -3L))
want
# V1 V2 V3 V4 V5 V6
#1 Buy Completed 2/11/2021 20:49 0.11057 Fee1.00 USD Total199.00 USD
#2 Buy Completed 2/11/2021 20:48 82.14600 Fee0.50 USD Total100.00 USD
#3 Buy Completed 2/11/2021 20:47 30.15000 Fee0.64 USD Total127.00 USD
显然，事情仍然有些混乱，因为我需要将一些字符串分成单独的列（例如df $ V5 =“ Fee1.00 USD”将变成df $ Fee = 1.00），但这是另一个问题。
我尝试添加一个id变量，然后从长到宽进行重塑，如此处所述，但通过获取所需的值（例如“ Fee1.00 USD”中的1.00）并将它们作为新的列名，会变得更加混乱：
df$id <- gl((nrow(df)/4), 4)
reshape(df, timevar = "V1", idvar = "id", direction = "wide")
而且我已经尝试将数据帧拆分为数据帧列表，如此处所述，但是我仍然不确定如何折叠每个数据帧并将其缝合在一起：
split(df, f = df$id)
将数据转换为正确格式的最佳方法是什么？
这个怎么样：
library(dplyr)
library(tidyr)
df <- df %>% mutate(obs = rep(1:(nrow(.)/4), each=4))
df <- df %>%
pivot_longer(-obs, names_to="var", values_to="vals") %>%
na.omit() %>%
group_by(obs) %>%
mutate(col = seq_along(obs)) %>%
select(obs, col, vals) %>%
pivot_wider(names_from="col", names_prefix="V", values_from="vals")
df
# # A tibble: 3 x 7
# # Groups: obs [3]
# obs V1 V2 V3 V4 V5 V6
# <int> <chr> <chr> <chr> <chr> <chr> <chr>
# 1 1 Buy Completed 2021-02-11 20:49:19 0.11057 Fee1.00 USD Total199.00 USD
# 2 2 Buy Completed 2021-02-11 20:48:03 82.146 Fee0.50 USD Total100.00 USD
# 3 3 Buy Completed 2021-02-11 20:47:22 30.15 Fee0.64 USD Total127.00 USD
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句