remove rows which have a specific attribute less than or equal to 0

Question

I have several large text files (30m+ lines, >1GB) which are being processed in ArcGIS after splitting (see Remove specific lines from a large text file in python and chunk a text database into N equal blocks and retain header for background).

Even after splitting the process takes over 3 days so I want to delete all xy points which have a (Rx) value less than or equal to 0.

I haven't got python to work on reading txt datasets over 500Mb so I have used cygwin/SED commands to do the initial cleaning of the data and then python to chunk the file. So ideally the process would be to add some code to the python (see below) to not include all lines with Rx<=0.

Latitude    Longitude   Rx  Best_Unit
-16.37617    144.68805  -012.9  7
-16.37617    144.68834  -015.1  7
-16.37617    144.68861  -017.2  7
-16.37617    144.68890  -018.1  7
-16.37617    144.68919  -025.0  7
-16.37617    144.68945  -019.5  7
-16.37617    144.68974  -020.0  7
-16.37617    144.69003  -020.4  7
-16.37617    144.69623   015.3  7
-16.37617    144.69652   015.6  7
-16.37617    144.69679   015.8  7
-16.37617    144.69708   016.0  7
-16.37617    144.70076   005.0  7
-16.37617    144.70103   002.2  7
-16.37617    144.70131  -000.2  7
-16.37617    144.70160  -001.5  7
-16.37617    144.70187  -001.0  7
-16.37617    144.70216   000.7  7
-16.37617    144.70245   002.2  7
-16.37617    144.70273   008.4  7
-16.37617    144.70300   017.1  7
-16.37617    144.70329   017.2  7

I want all rows (lines) where Rx>0 to be written into a new text file. I also want the column Best_Unit deleted.

from itertools import islice

import arcpy, os
#fc = arcpy.GetParameter(0)
#chunk_size = arcpy.GetParameter(1) # number of records in each dataset

fc='cb_vhn007_5.txt'
Name = fc[:fc.rfind('.')]
fl = Name+'.txt'

headers_count = 1
chunk_size = 500000

with open(fl) as fin:
  headers = list(islice(fin, headers_count))

  part = 1
  while True:
    line_iter = islice(fin, chunk_size)
    try:
      first_line = line_iter.next()
    except StopIteration:
      break
    with open(Name+'_%d.txt' % part, 'w') as fout:
      for line in headers:
        fout.write(line)
      fout.write(first_line)
      for line in line_iter:
         ## add something here to check if value after third tab
         ## is >0 and if so then write the row or skip.
        fout.write(line) 

    print "Created part %d" % part
    part += 1

New Code - first line includes - Rx values.

from itertools import islice

import arcpy, os
#fc = arcpy.GetParameter(0)
#chunk_size = arcpy.GetParameter(1) # number of records in each dataset

fc='cb_vhn007_5.txt'
Name = fc[:fc.rfind('.')]
fl = Name+'.txt'

headers_count = 1
chunk_size = 500000

with open(fl) as fin:
  headers = list(islice(fin, headers_count))

  part = 1
  while True:
    line_iter = islice(fin, chunk_size)
    try:
      first_line = line_iter.next()
    except StopIteration:
      break
    with open(Name+'_%d.txt' % part, 'w') as fout:
      for line in headers:
        fout.write(line)
      fout.write(first_line)
      for line in line_iter:
        if line.split()[2][0:1] != '-':
          #print line.split()[2]
          fout.write(line)

    print "Created part %d" % part
    part += 1

Answer 1

Probably just checking line_iter[24] != '-' should be enough.

I.e. replace:

fout.write(line)

with

if line_iter[24] != '-':
  fout.write(line)

Answer 2

You can use line.split() to split your line into a list containing the value of each of the 4 columns.

For example:

line='-16.37617\t144.70329\t017.2\t7'
line.split()
# ['-16.37617', '144.70329', '017.2', '7']

Then you can either coerce line[2] (remember python is 0-based indexing) to a number and check whether it's > 0:

if float(line.split()[2]) > 0:
    fout.write(line)

Or you could just check whether it has a minus sign in it:

if line.split()[2].find('-') != -1:
    fout.write(line)

If your columns might not be in the same order for each text file, you could perform split() on your headers, determine which is Rx, and use that instead of the 2:

i = headers.split().index('Rx')
# now use line[i]

Answer 3

I know its not python but it is probably the right tool for the job:

cat cb_vhn007_5.txt | awk '($3 > 0) {print $0}' > parsedfile

Answer 4

The lines you're looking for are something like this:

if line.split()[2][0:1] != "-"
  fout.write(line)

This splits the input, looks at the third entry, looks at the first character, and skips printing the line if it is a -.

Answer 5

Here is a python script that will read a file containing data formatted as four space-separated fields per row, inspect the third field, and output any lines whose third field is a positive floating point number.

Tested working on Python 2.7.2.

import re

in_fh = open ("gis.txt","r")
out_fh = open ("outfile.txt","w")

for row in in_fh:
    row = re.sub(' +',',',row) # convert to comma-separated format
    try:
        latitude, longitude, rx, best_unit = row.split(',')
    except ValueError: # row didn't have four fields
        print ("complain - not four fields")
        continue

    try:
        float_rx = float(rx)
    except ValueError: # rx could not be cast to float
        print ("complain - third field not float")
        continue

    if float_rx > 0:
        out_fh.write(latitude + "," + longitude + "," + rx + "\n")
    else:
        pass # discard the row

in_fh.close()
out_fh.close()

Only one row is processed at a time, so memory usage should be constant regardless of the size of the input and output files.

Alternately, have you considered using a database? sqlite3 is built in and would probably handle 1Gb of data just fine. Then you could get this result by doing SELECT * FROM data WHERE rx > 0.