离开的是风景，留下的是人生
1,102 浏览  • 
     ...

文本中按规则分组区段随机抽样

 
text(30)

【问题】

This is a bit complex, and I greatly appreciate any help! I am trying to randomly sample rows from a .csv file. Essentially, I want a resulting file of unique locations (Locations are specified by Easting and Northing columns of the data file, below). I want to randomly pull 1 location per 12 hour period per SessionDate in this file (12 hour periods divided into: between 0631 and 1829 hours and between 1830 and 0630 hours; Given as Start: and End: in Data File, below); BUT if any 2 locations are within 6 hours of each other (based on their Start: time), for that location to be tossed, and a new location to be randomly drawn, and for this sampling to continue until no new locations are drawn (i.e., sampling WITHOUT replacement). I have been trying to do this with python, but my experience is very limited. I tried first putting each row into a dictionary, and recently each row into a list, as follows: 

import random
import csv
f = open('file.csv', "U")
list = []
for line in f:
    list.append(line.split(','))

I'm unsure where to go from here - how to sample from these lists the way I need to, then write them to an output file with my'unique' locations.

Here is the top few lines of my data file:

SessionDate Start:  End:    Easting Northing
27-04-07   18:00   21:45   174739  9785206
28-04-07   18:00   21:30   171984  9784738
28-04-07   18:00   21:30   171984  9784738
28-04-07   18:00   21:30   171984  9784738
28-04-07   18:00   21:30   171984  9784738

It gets a bit complicated as some of the observations span midnight, so they may be on different dates, but can be within 6 hours of each other (which is why I have this criterion), for example: 

SessionDate Start:  End:    Easting Northing
27-04-07   22:30   23:25   171984  9784738
28-04-07   0:25    1:30    174739  9785206

【回答】

这类问题建议用SPL，可以简单且完整地实现设计思路：

A

B

C

D

1

=file("D:\\source.txt").import@t(SessionDate: date:"dd-MM-yy",Start:time:"HH:mm",End:time:"HH:mm",Easting,Northing).derive(datetime(SessionDate,Start):DateStart)

2

for A1.group(Easting,Northing)

=null

=A2.sort(DateStart).group@o(between(Start,time("6:30","HH:mm"):time("18:30","HH:mm")))

3

for C2

=if(B2,B3.select(interval@s(B3.DateStart,DateStart)>60*60*6),B3)

4

if C3!=[]

=@|(B2=C3(rand(C3.len())+1))

5

=file("D:\\result.txt").export@t(D4)

A1:读入文件，第一行作为列名，并设置SessionDate、Start和End的数据类型，最后将SessionDate和Start拼成完整日期DateStart。

undefined

A2:将A1按Easting,Northing分组，循环每一组。循环体即缩进的B2-D4，可用A2来引用循环变量。类似地，B3的循环体是C3-D4。

B2:暂存上一组选出记录，初值为null。

C2:将A2再按DateStart每隔12小时交替分组。@o表示不排序直接分组。

B3:循环C3每组数据。

C3:第1组(B2==null)数据直接返回，否则过滤出与B2间隔大于6小时的记录。

C4-D4:如果C3过滤的结果不空，则随机取记录存入B2，并将该记录追加到D4。@表示当前格（D4）。

D4结果为：

undefined

A5:将存在D4中的计算结果写出到文件result.txt。


text(30)