topic Re: da.InsertCursor and Parsing Large Text file in Python Questions

da.InsertCursor and Parsing Large Text file

AlanDodson — Sat, 11 Dec 2021 10:44:45 GMT

I have a very large text file (~5 GB, ~30 million lines) that I need to parse and then output some of the data to a new point feature class. I cannot figure out how to proceed with the da.InsertCursor.

I've created the feature class so the fields are in the required order. There is an if statement that parses out the required lines of the file. The units in the text file are Feet, however I need rounded Metres in the output and the output field for that information must be Long. The round command returns float but my final value in the Tree_Height output must be long - can the float value be mapped into the long field? Converting from Feet to Metres is simple multiplication, but I believe my field types might be messed up then?

The index positions of the Lat and Long fields in the source file are 5 and 6.

The index position of the Description field in the source file is 1.

The index position of the Tree_Height (in Feet) in the source file is 9.

The index position of the Code in the source file is 2 - this is the link between the user-inputted codes and the codes in the if statement. The if statement works, but after that....

Can someone help me set up the da.InsertCursor so that these operations can (a) get done and (b) get done efficiently? I've been fumbling with lists of fields and tuples of things and am not making any progress. Have looked at help files and Penn State's online courses but still no joy.....

import arcpy, os

treesfile = arcpy.GetParameterAsText(0)
codes = arcpy.GetParameterAsText(1).split(",")   # user types in comma-delimited wildlife codes for the AOI
arcpy.env.workspace = arcpy.GetParameterAsText(2) 
sr = arcpy.SpatialReference(4326)

arcpy.env.overwriteOutput = True

Filtered_Trees = arcpy.CreateFeatureclass_management(arcpy.env.workspace, "Filtered_Trees", "POINT", "", "DISABLED", "DISABLED", sr)
arcpy.AddField_management(Filtered_Trees, "Lat", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Filtered_Trees, "Long", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Filtered_Trees, "Description", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Filtered_Trees, "Tree_Height", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")  # height value must be rounded and in Metres.
# there are many more fields but you get the idea

with open(treesfile, 'r') as file:
  for line in file:
    values = line.split("\t")
    if values[2] in codes and float(values[9]) >= 100:  
      # now I am stuck...	  	  ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: da.InsertCursor and Parsing Large Text file

JoshuaBixby — Thu, 04 Apr 2019 01:51:55 GMT

It is difficult to provide a specific suggestion because you haven't shared complete structure information of the data file and your code snippet states, "there are many more field but you get the idea." Are you transferring all the columns/fields from the CSV into the feature class when you insert a record of just some of the fields? The insert cursor requires that the length of the row you are inserting matches the number of fields you define in the cursor, so knowing which fields are being inserted is important.

Re: da.InsertCursor and Parsing Large Text file

AlanDodson — Thu, 04 Apr 2019 02:35:10 GMT

Ah, yes I guess the casual-ness doesn't lend itself to getting the full picture across, sorry about that. I didn't want to further bog down my question but I see that the missing information is pertinent.

My input file is tab-delimited (which I have accounted for) and has ~50 fields of which I am only interested in 10. I've created the Feature Class to have those 10 fields. 3 of the fields need to be transformed, converted from feet to meters and then rounded to have no decimal places (I only included one of those fields in the example because the methodology would be identical). I could do this in 2 steps by creating an imperial field in the output file, populating the metric field via field calculator, then deleting the imperial field but was hoping to do it in one step to save that file maintenance at the end.

I have the index positions of all the input file fields and I know the order in which they should be inserted into the Feature Class. For example, for the 4 fields in my original post the index positions are [5], [6], [9], [4]. (The Lat and Long fields [5] [6] are required in the output file and will also be used with the SHAPE@XY token to create the point geometry.)

The if statement filters only records whose code has been specified by the user and tree height is >= 100 ft.

This bottom part of this post from Stack Exchange looks promising, but I haven't been able to test it out yet and I clearly am not entirely sure what I'm doing just yet.

Re: da.InsertCursor and Parsing Large Text file

DanPatterson_Retired — Sat, 11 Dec 2021 10:44:48 GMT

If it is a csv file, there is little point creating a bunch of fields... just read the file.

To read the file, you can use numpy (lines 1, 6, 8). You need to describe your input columns.

lat and lon are doubles (f8), Desc is a text column (unicode 50 characters), hght is an integer (i4)

Line 8, reads the tree.csv file, skips the first row since that was column text headers, the data type was described on line 6 and it is a comma-delimited file.

arcpy needs a featureclass name (line 10), then it use the data access Numpyarraytofeatureclass method to create a point featureclass (fc) from an array (a), which uses the longitude, latitude fields to make the point geometry... and every file needs a spatial reference (back to line 4, which is a GCS-WGS84)

import numpy as np
import arcpy

SR = arcpy.SpatialReference(4326)

dt = [('lat', 'f8'), ('lon', 'f8'), ('Desc', 'U50'), ('hght', 'i4')]

a = np.genfromtxt("c:/temp/tree.csv", skip_header=1, dtype=dt, delimiter=",")

fc = r"C:\Arc_projects\Polygon_lineTools\Polygon_lineTools.gdb\trees"

arcpy.da.NumPyArrayToFeatureClass(a, fc, shape_fields=['lon', 'lat'], spatial_reference=SR)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

If you can describe your fields better and whether there are null values in them (ie blanks) and whether each column contains the same data type (ie... no spreadsheet mixing text and numbers in columns), then I can provide further help.

If you have a large number of columns to skip, you only need to record their names and column position, the rest will be skipped.

dt = 'f8,f8,U20,i4,i4,f8,U5'

n = ['lat', 'lon', 'desc', 'hght', 'junk2', 'another', 'again']


a0 = np.genfromtxt("c:/temp/tree.csv", dtype=dt, skip_header=1, delimiter=",",
                   names=n, usecols=[0, 1, 2, 3, 5, 6, 8])

a0
 
array([(45.  , -75. , 'Poplar', 100, 1, 1., 'd'),
       (45.1 , -75.1, 'Aspen',  90, 2, 2., 'e'),
       (45.05, -75.2, 'Pine',  85, 3, 3., 'f')],
      dtype=[('lat', '<f8'), ('lon', '<f8'), ('desc', '<U20'), ('hght', '<i4'),
             ('junk2', '<i4'), ('another', '<f8'), ('again', '<U5')])

#--- out it goes to a featureclass as before‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Also, reading that in one go is going to be iffy, so chunking might be better or you might have to get into memory mapping to read portions from disk.

Re: da.InsertCursor and Parsing Large Text file

curtvprice — Thu, 04 Apr 2019 04:26:12 GMT

At times like this I miss awk.

Re: da.InsertCursor and Parsing Large Text file

AlanDodson — Thu, 04 Apr 2019 11:38:09 GMT

Amen!

Re: da.InsertCursor and Parsing Large Text file

AlanDodson — Thu, 04 Apr 2019 11:53:25 GMT

Hi Dan; your suggestions are different from what I was expecting so it might take me a bit longer to understand, but I'm willing to try them out, thanks. Glad to have some different routes to try.

For more reference information: I am writing this to replace a standalone VB parser that wrote to a new text file which was then manually ingested to Arc using the Add XY Data function in Catalog. I have a bit of time so I thought I'd try to migrate that process into the Arc environment and create the output file all in one go. The VB parser is legacy from many years ago and chunked the data. I'm using 10.3 but am making the leap to 10.6 or 10.7 soon. Input file is tab-delimited. My input file does not mix data types within columns. The format of the output file is important because other processes after this depend on the field types - 5 fields are required by further process while the other 5 fields are needed just for reference. There is no header on the file but I know the schema. Some fields use -999 as a null while others have blank space - but all the fields I need to perform further tasks on will be 100% populated with real information (this is a condition of the file when it is created and is defined in the schema).

As for the memory issue - I thought the reason for processing the file line-by-line was to avoid the memory issues? That's what all the guidance and help I could find online said, that reading the file in one go was a bad move and that the easy solution was to process a line, dump it, process the next line, etc.... I could be misunderstanding your process and the guidance I read though... Or perhaps line-by-line isn't available in the method you're suggesting?

Thanks, hope this additional information helps.

Re: da.InsertCursor and Parsing Large Text file

AlanDodson — Thu, 04 Apr 2019 12:23:35 GMT

Also the if statement I'm using is crucial to finding the needles in the haystack and then only processing and outputting the needles... Can it be incorporated using your method?

Re: da.InsertCursor and Parsing Large Text file

DanPatterson_Retired — Thu, 04 Apr 2019 15:07:48 GMT

The big issue was how many fields out of the identified do you actually need (ie their index numbers as in my second example). with either approach it sounds like there is a lot of baggage in the file to get rid of
Once those are assembled, then it looked like you want to parse the records based on some threshold criteria

You can prune while you gather, but you don't want to process... ie you don't want to make the jam while you are picking the strawberries, just leave the rotten ones

this whole insertcursor as you go can be done all at once, You will end up with a point featureclass with a defined coordinate system

The stuff that is purely numpy is obvious and documented, the arcpy (ie the numpyarray2f...) is less so, but it works and is a great addition and largely underused

Re: da.InsertCursor and Parsing Large Text file

AlanDodson — Thu, 04 Apr 2019 19:02:53 GMT

Thanks for all your help on this. I have done some more reading and just want to make sure I understand the method here... It looks much more simple and lightweight than what I was doing previously so I'm glad to try this out.

For your bullet points:

1. You are right about the baggage - each time this is run, it will only return maybe 0.25% of the data from the input file, hence the tool's necessity. I understand that the field widths have a big impact on memory usage with this method, so I'd have to constrain those really see if all the fields I output currently are crucial to my goals for this... There may be one or two that I can drop; they are integers so it won't save a lot of room but every little bit helps I suppose. In your example would the code create a massive array of my specified columns for the entire file? Or does it work one line at a time? I am confusing myself I think.

2. If I have the metaphor right, can I do the pruning before the gathering - just leave the rotten berries on the plant? ie can the if statement come before the array so lines that don't meet the threshold don't even get considered? Or is that just not how it works? Or does the array need to be completely created before I pick and choose the data to come out of it and into my featureclass? Pick every berry, throw out the bad ones, then make the jam.... Or does something else need to happen altogether?

3. Do you mean that the insertcursor business is compatible with your code, or that the insertcursor is a separate function that will also achieve the same result?

I will do much more reading on this...... btw I was never all that good with Avenue back when I was in school, but that was my fault and not my instructor's, he tried his best with me and I'm still doing GIS so that's good

Re: da.InsertCursor and Parsing Large Text file

DanPatterson_Retired — Fri, 05 Apr 2019 00:06:46 GMT

Alan, a small sample of the data could be useful to demonstrate the points

Re: da.InsertCursor and Parsing Large Text file

curtvprice — Fri, 05 Apr 2019 02:34:45 GMT

I think you should read 1000 lines into a list, remove the columns you do not need and do your value calculations (using python list operations or as Dan suggests, list to numpy array, operate on your data blazingly fast, convert back to list), then, when you have your data in python lists (or tuples) use arcpy.da.InsertCursor to write your 1000 records. Then repeat.

Re: da.InsertCursor and Parsing Large Text file

AlanDodson — Fri, 05 Apr 2019 02:42:12 GMT

OK, that would go a long way to me understanding this. I will attach 100 lines of the data Friday morning. Will have to condition and anonymize it but for these purposes it will be good to go. Appreciate it

Re: da.InsertCursor and Parsing Large Text file

AlanDodson — Fri, 05 Apr 2019 02:46:18 GMT

All that sounds ideal - just to wrap my head around it now. The ratio of input lines to output lines is very lopsided due to the filtering I need to do so I'm wondering how that will work. I'm going to post some data in the morning and hopefully that will help.

Re: da.InsertCursor and Parsing Large Text file

DanPatterson_Retired — Sat, 11 Dec 2021 10:44:50 GMT

Curtis, since the array would contain coordinates, the processing 'result' can be converted directly to a point featureclass. Even without coordinates they can be converted directly to a table (via arcpy.da.NumPyArrayToFeatureclass and arcpy.da.NumPyArrayToTable ) If you had to process them in batches (worse case scenario) then I would simply save the intermediate results to disk in *.npy format, assemble when all is done, then use those. You don't need InsertCursors or Update cursors as long as the data format is known. For point featureclasses and/or plain old gdb table, there is no need for cursors at all. Processing polygon and polyline output requires a few extra steps. But I only use cursors for the geometry. I use arcpy.da.ExtendTable to bring in and join the array to featureclass tables (even taking care not to use OBJECTID as the join key)

Even if you like cursors, numpy lurks somewhere around it

cur = arcpy.da.SearchCursor(in_fc, "*", None, SR)
cur._dtype  # ---- looks np.dtype to me

dtype([('OBJECTID', '<i4'), ('Shape', '<f8', (2,)), ('Id', '<i4'), ('Long_1', '<i4'),
       ('Short_1', '<i4'), ('Float_1', '<f4'), ('Double_1', '<f8'), ('Text_1', '<U10'),
       ('Shape_Length', '<f8'), ('Shape_Area', '<f8'), ('DT_str', '<U20')])‍‍‍‍‍‍

Same with the other access point to arrays... with the added bonus of skipping any row that hasn't been cleanup from flotsam … like nulls (which you can control what is considered 'null')

a = arcpy.da.FeatureClassToNumPyArray(in_fc, "*", skip_nulls=True)
a

array([(1, [ 300015.   , 5000005.   ], 1, 1, 4, 1., 100., 'A 10 chars', 40.   , 100., '2019/03/28 00:00:00'),
       (3, [ 300010.495, 5000010.593], 3, 3, 6, 3., 300., 'C not null', 99.416, 182., '2019/03/30 00:00:00')],
      dtype=[('OBJECTID', '<i4'), ('Shape', '<f8', (2,)), ('Id', '<i4'), ('Long_1', '<i4'),
             ('Short_1', '<i4'), ('Float_1', '<f4'), ('Double_1', '<f8'), ('Text_1', '<U10'),
             ('Shape_Length', '<f8'), ('Shape_Area', '<f8'), ('DT_str', '<U20')])‍‍‍‍‍‍‍‍

NumPy and SciPy are really underused in gis analysis sadly, let alone in data preparation. Cute Pandas get all the attention,

Re: da.InsertCursor and Parsing Large Text file

AlanDodson — Fri, 05 Apr 2019 12:37:23 GMT

Hi Dan - attached is some sample data and another file that explains my goal in clearer terms. In my previous posts I'm sure wasn't as succinct as I could've been. One thing to keep in mind and that I am clearly getting hung up on, is that by filtering the data first, 99% of the lines from the input file could be weeded out. In my head this seems like the best way to go about processing the data, but it's clear that what I see in my head doesn't always easily translate to the code or my understanding of how the most efficient code works.

I appreciate your posts on this and I've already learned a ton, thank you!

Re: da.InsertCursor and Parsing Large Text file

AlanDodson — Sat, 11 Dec 2021 10:44:53 GMT

I have figured out a solution that give me exactly what I want and it is fast (enough). It parses through the large file and writes the data I need in about 2.5 minutes, which is about 4 times faster than the external VB script it replaces, plus it gives me a GIS output within my current workflow.

I did not use any of the array code that Dan mentioned but I will be reading those answers very closely and applying whatever I can find there to future projects indeed. Very appreciated Dan Patterson

In case anyone is in the same circumstance this is what I am using:

Input = arcpy.GetParameterAsText(0)
codes = arcpy.GetParameterAsText(1).split(",")   
arcpy.env.workspace = arcpy.GetParameterAsText(2) 
sr = arcpy.SpatialReference(4326)

arcpy.env.overwriteOutput = True

Output = arcpy.CreateFeatureclass_management(arcpy.env.workspace, "Output", "POINT", "", "DISABLED", "DISABLED", sr)
arcpy.AddField_management(Output, "LAT", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "LONG", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "IOH", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "MEASURE1", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "MEASURE2", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "MEASURE3", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "CODE", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "TYPECODE", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "TYPENAME", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "FEATCODE", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "FEATNAME", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "COL", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "MULT", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "DATE3", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "DATE5", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(Output, "AORD", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

with open(Input, 'r') as file:
  with arcpy.da.InsertCursor(Output,["SHAPE@XY", "LAT", "LONG", "IOH", "MEASURE1", "MEASURE2", "MEASURE3", "CODE", "TYPECODE", "TYPENAME", "FEATCODE", "FEATNAME", "COL", "MULT", "DATE3", "DATE5", "AORD"]) as InsCur:
    for line in file:
      values = line.split("\t")
      LAT = values[5]
      LONG = values[6]
      IOH = values[1]
      MEASURE1 = float(values[9])*0.2691
      MEASURE2 = float(values[10])*0.2691
      MEASURE3 = float(values[11])*0.2691
      CODE = values[2]
      TYPECODE = values[7]
      TYPENAME = values[8]
      FEATCODE = values[38]
      FEATNAME = values[39]
      COL = values[18]
      MULT = values[20]
      DATE3 = values[23]
      DATE5 = values[24]
      AORD = values[32]
      if values[2] in codes and float(values[9])*0.2691 >= 75:  
  	      newGeom = arcpy.PointGeometry(arcpy.Point(LONG,LAT))
  	      InsCur.insertRow([newGeom,LAT,LONG,IOH,MEASURE1,MEASURE2,MEASURE3,CODE,TYPECODE,TYPENAME,FEATCODE,FEATNAME,COL,MULT,DATE3,DATE5,AORD])
		  
OutputCount = arcpy.GetCount_management(Output)
OutputCountInt = int(OutputCount.getOutput(0))
if (OutputCountInt < 1):
  arcpy.AddMessage("There are no valid points in your AOI.")
else:
  arcpy.AddMessage("Points have been written to the Output feature class in your input database.")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍