我正在尝试对一个大型数据结构(行数在数千万到上亿之间)进行复杂的自连接操作,因此我希望避免为这个操作创建新列,因为这会在对象中增加大量内存压力,特别是因为我希望尝试不同的连接时间参数。
dt_sample
的结构如下:
str(dt_sample)
Classes ‘data.table’ and 'data.frame': 50 obs. of 6 variables:
$ gateway_airport: chr "BOS" "BOS" "BOS" "BOS" ...
$ final_airport : chr "ORD" "BNA" "ORD" "RSW" ...
$ dept_utc : POSIXct, format: "2016-11-17 15:09:00" "2016-11-17 21:00:00" "2016-11-17 12:40:00" ...
$ arriv_utc : POSIXct, format: "2016-11-17 17:03:00" "2016-11-17 23:00:00" "2016-11-17 14:35:00" ...
$ airlines_id : chr "UA" "B6" "UA" "B6" ...
$ flight_number : num 1472 1907 449 965 3839 ...
这个想法是在X的final_airport和Y的gateway_airport相等时进行自连接,在我的下面的示例中,Y的dept_utc在X的arriv_UTC范围内(包括+30分钟至+8小时)。
这种方法可以实现,但会产生一个大的数据结构,需要在合并后进行过滤。
result <- merge(dt_sample, dt_sample, by.x=c("final_airport"),
by.y=("gateway_airport"))[arriv_utc.x + 1800 <= dept_utc.y
&arriv_utc.x +28800 >= dept_utc.y,]
我更喜欢使用
on = .()
符号,但它似乎不允许在连接元素上进行算术操作,并且自连接似乎也会使其混乱。再次强调,我不希望添加新的列。有没有关于如何有效地在此处使用data.table的想法?
head(result)的结果是一个每行有3个机场(起点、中转站和终点)的数据表,还包括一些额外信息。样例的完整结果在下面的dput中共有19行。 final_airport gateway_airport dept_utc.x arriv_utc.x airlines_id.x flight_number.x final_airport dept_utc.y arriv_utc.y
1: IAD BOS 2016-11-17 14:35:00 2016-11-17 16:18:00 UA 525 JAX 2016-11-17 17:30:00 2016-11-17 19:37:00
2: IAD BOS 2016-11-17 14:35:00 2016-11-17 16:18:00 UA 525 SAV 2016-11-17 17:30:00 2016-11-17 19:16:00
3: IAD BOS 2016-11-17 14:35:00 2016-11-17 16:18:00 UA 525 TYS 2016-11-17 17:31:00 2016-11-17 19:10:00
4: IAD BOS 2016-11-17 14:35:00 2016-11-17 16:18:00 UA 525 DEN 2016-11-17 17:35:00 2016-11-17 19:36:00
5: IAD BOS 2016-11-17 14:35:00 2016-11-17 16:18:00 UA 525 GSO 2016-11-17 17:40:00 2016-11-17 19:09:00
6: IAD BOS 2016-11-17 14:35:00 2016-11-17 16:18:00 UA 525 LAX 2016-11-17 17:40:00 2016-11-17 20:25:00
airlines_id.y flight_number.y
1: AC 3891
2: AC 2736
3: AC 2567
4: UA 735
5: AC 2727
6: UA 632
Click below to expand 50-row sample dput structure for reproducibility:
structure(list(gateway_airport = c("BOS", "BOS", "BOS", "BOS",
"IAD", "IAD", "IAD", "BOS", "IAD", "BOS", "BOS", "BOS", "BOS",
"IAD", "BOS", "BOS", "IAD", "BOS", "BOS", "BOS", "IAD", "BOS",
"BOS", "BOS", "BOS", "IAD", "BOS", "IAD", "BOS", "IAD", "IAD",
"IAD", "BOS", "IAD", "BOS", "BOS", "BOS", "IAD", "IAD", "BOS",
"IAD", "BOS", "BOS", "BOS", "IAD", "BOS", "IAD", "BOS", "BOS",
"IAD"), final_airport = c("ORD", "BNA", "ORD", "RSW", "ORF",
"MCO", "DEN", "CLT", "DFW", "PHL", "ORD", "MIA", "ORD", "GSO",
"JFK", "FLL", "ORD", "ORD", "LGA", "LGA", "LAX", "ORD", "IAD",
"RIC", "DEN", "TYS", "SEA", "TPA", "SAV", "ROA", "SEA", "DFW",
"PHL", "MIA", "IAH", "PHL", "LGA", "JFK", "JAX", "TPA", "TPA",
"IAH", "DFW", "LAS", "SAV", "IAD", "LAX", "LGA", "SFO", "LAX"
), dept_utc = structure(c(1479395340, 1479416400, 1479386400,
1479397800, 1479420600, 1479422700, 1479404100, 1479386100, 1479384840,
1479387600, 1479378840, 1479386700, 1479402000, 1479404400, 1479403800,
1479418500, 1479421500, 1479384000, 1479420900, 1479387600, 1479404400,
1479412500, 1479411000, 1479381000, 1479412920, 1479403860, 1479395700,
1479389100, 1479398400, 1479421500, 1479415200, 1479400140, 1479415440,
1479380400, 1479406080, 1479382200, 1479413700, 1479394800, 1479403800,
1479414300, 1479423000, 1479392520, 1479411600, 1479384000, 1479403800,
1479393300, 1479391200, 1479400200, 1479397500, 1479420600), class = c("POSIXct",
"POSIXt"), tzone = ""), arriv_utc = structure(c(1479402180, 1479423600,
1479393300, 1479410880, 1479424620, 1479431160, 1479411360, 1479395520,
1479393900, 1479393360, 1479386700, 1479400020, 1479408780, 1479409740,
1479408240, 1479431340, 1479425280, 1479391860, 1479425640, 1479392100,
1479414300, 1479419280, 1479417120, 1479387600, 1479422940, 1479409800,
1479407460, 1479397800, 1479408180, 1479426180, 1479425580, 1479409500,
1479421740, 1479390420, 1479418260, 1479387900, 1479418320, 1479399360,
1479411420, 1479426420, 1479431940, 1479404880, 1479423900, 1479395340,
1479410160, 1479399480, 1479401580, 1479404640, 1479411300, 1479430860
), class = c("POSIXct", "POSIXt"), tzone = ""), airlines_id = c("UA",
"B6", "UA", "B6", "AC", "UA", "UA", "AA", "AA", "B6", "AA", "AA",
"AA", "AC", "EI", "B6", "UA", "AA", "B6", "AA", "UA", "UA", "UA",
"B6", "UA", "AC", "B6", "UA", "B6", "AC", "UA", "AA", "B6", "AA",
"UA", "AA", "B6", "B6", "AC", "EI", "UA", "UA", "B6", "B6", "AC",
"UA", "UA", "B6", "UA", "UA"), flight_number = c(1472, 1907,
449, 965, 3839, 419, 735, 1735, 2569, 459, 1155, 1274, 1240,
2727, 5021, 1969, 511, 1404, 1331, 2126, 632, 981, 511, 1481,
448, 2567, 597, 2002, 49, 2726, 357, 1556, 1059, 1083, 1233,
543, 1231, 1308, 3891, 5290, 360, 167, 1115, 1077, 2736, 525,
470, 831, 477, 325)), .Names = c("gateway_airport", "final_airport",
"dept_utc", "arriv_utc", "airlines_id", "flight_number"), class = c("data.table",
"data.frame"), row.names = c(NA, -50L), .internal.selfref = <pointer: 0x2301358>)
arriv_utc + 1800 > dept_utc
的东西。 - Serban Tanasaarriv_utc_plus := arriv_utc + 1800
是微不足道且高效的。 - Rolandtruelength(DT)
告诉你什么?如果这个值大于列数,那么你的data.table内存是过度分配的,添加一列不需要额外的RAM(这是data.table的一个特性)。无论如何,算术连接已经计划好了,但是尚未实现。 - Roland