如何按键/值交错两个数据帧中的行？

I would like to combine data from two tables by interleaving them based on time stamp. Because the "Box" column has multiple values they are of unequal length.

我想通过基于时间戳交错来组合来自两个表的数据。因为“Box”列具有多个值，所以它们的长度不相等。

DateTime    Box TempIn
10/10/2017 4:00 B1  5
10/10/2017 4:00 B2  5
10/10/2017 4:00 B3  5
10/10/2017 5:00 B1  5
10/10/2017 5:00 B2  5
10/10/2017 5:00 B3  5

DateTime    TempOut
10/10/2017 4:00 22
10/10/2017 5:00 22

My goal is to interleave the "Temp*" columns so the final table looks like this:

我的目标是交错“Temp *”列，以便最终表格如下所示：

DateTime    Box Temp
10/10/2017 4:00 B1  5
10/10/2017 4:00 B2  5
10/10/2017 4:00 B3  5
10/10/2017 4:00 Ext 22
10/10/2017 5:00 B1  5
10/10/2017 5:00 B2  5
10/10/2017 5:00 B3  5
10/10/2017 5:00 Ext 22

Any advice? Using TidyR I can get this far but my dates are out of order.

任何建议？使用TidyR我可以做到这一点，但我的日期不正常。

spread(df, Box, TempIn)

         DateTime B1 B2 B3
1 10/10/2017 4:00  5  5  5
2 10/10/2017 5:00  5  5  5

df$Ext <- df2$TempOut

         DateTime B1 B2 B3 Ext
1 10/10/2017 4:00  5  5  5  22
2 10/10/2017 5:00  5  5  5  22


df %>% gather(Box, Temp, -DateTime)
         DateTime Box Temp
1 10/10/2017 4:00  B1    5
2 10/10/2017 5:00  B1    5
3 10/10/2017 4:00  B2    5
4 10/10/2017 5:00  B2    5
5 10/10/2017 4:00  B3    5
6 10/10/2017 5:00  B3    5
7 10/10/2017 4:00 Ext   22
8 10/10/2017 5:00 Ext   22

1 个解决方案

#1

I'm assuming the larger data frame is called df and the other one is called df2

我假设较大的数据帧称为df而另一个称为df2

# Rename to prepare for rbind
names(df) <- ifelse(names(df) == 'TempIn', 'Temp', names(df))
names(df2) <- ifelse(names(df2) == 'TempOut', 'Temp', names(df))
# Add box to df2
df2$Box <- 'Ext'

# Split each by DateTime, rbind each of the splits, then rbind everything
do.call(rbind, Map(rbind, split(df, df$DateTime), split(df2, df2$DateTime)))

#           DateTime Box Temp
# 1: 10/10/2017 4:00  B1    5
# 2: 10/10/2017 4:00  B2    5
# 3: 10/10/2017 4:00  B3    5
# 4: 10/10/2017 4:00 Ext   22
# 5: 10/10/2017 5:00  B1    5
# 6: 10/10/2017 5:00  B2    5
# 7: 10/10/2017 5:00  B3    5
# 8: 10/10/2017 5:00 Ext   22

These all gives the same result

这些都给出了相同的结果

do.call(rbind, rbind(split(df, df$DateTime), split(df2, df2$DateTime)))

##################################

library(tidyverse)

list(df, df2) %>% 
  map(~split(.x, .x$DateTime)) %>% 
  do.call(what = rbind) %>% 
  do.call(what = rbind)

##################################

list(df, df2) %>% 
  map(~split(.x, .x$DateTime)) %>%
  flatten %>% 
  .[order(names(.))] %>% 
  bind_rows

#1