import arcpy, os # access your table(s) intbl = r'C:\path\to\your\FileGeodatabase.gdb\inputtable' outtbl = r'C:\path\to\your\FileGeodatabase.gdb\outputtable' # optionally create output table, using the input table as schema # CreateTable_management (out_path, out_name, {template}, {config_keyword}) outpath, outname = os.path.split(outtbl) arcpy.CreateTable_management(outpath, outname, intbl) # have a close look at the fieldnames, some names may be restricted # and are changed by ArcGIS when imported from for instance Excel flds = ['number','Code','Year'] # do a search cursor on the input table and an insert cursor on the output table outcurs = arcpy.da.InsertCursor(outtbl, flds) with arcpy.da.SearchCursor(intbl, flds) as incurs: for row in incurs: numbers = row[0].split(',') for number in numbers: # for each 'number' a record is written outcurs.insertRow((number.strip(), row[1], row[2])) del outcurs
Hi James, it's posts like yours (thinking outside the box) that lets me learn something new every day... (+1 for that).Kind regards,Xander
Nice! I pulled from one of our impmentations with lots of pandas DataFrame processing, but I like this suggestion for just numpy work.
While we're at it, using part of the idea James suggested (the numpy part) and mixing it with some list comprehensions I came up with this:import arcpy, numpy intbl = r'C:\Project\_Forums\CommaDelimitField\test.gdb\intable' outtbl = r'C:\Project\_Forums\CommaDelimitField\test.gdb\tst01' flds = ('number_','Code','Year_') # list comprehensions lst_in = [row for row in arcpy.da.SearchCursor(intbl, flds)] lst_out = [(num, row[1], row[2]) for row in lst_in for num in row[0].split(', ')] # use numpy to store the table npa = numpy.array(lst_out, numpy.dtype([('number', '|S10'), ('code', '|S10'), ('year', numpy.int32)])) arcpy.da.NumPyArrayToTable(npa, outtbl, ("number", "code", "year")) Kind regards,Xander
import arcpy, numpy intbl = r'C:\Project\_Forums\CommaDelimitField\test.gdb\intable' outtbl = r'C:\Project\_Forums\CommaDelimitField\test.gdb\tst01' flds = ('number_','Code','Year_') # list comprehensions lst_in = [row for row in arcpy.da.SearchCursor(intbl, flds)] lst_out = [(num, row[1], row[2]) for row in lst_in for num in row[0].split(', ')] # use numpy to store the table npa = numpy.array(lst_out, numpy.dtype([('number', '|S10'), ('code', '|S10'), ('year', numpy.int32)])) arcpy.da.NumPyArrayToTable(npa, outtbl, ("number", "code", "year"))
Hi Amy,I think this will do the job:
import arcpy import pandas as pd import numpy as np input_table = r'H:\Documents\ArcGIS\Default.gdb\fliptab' ##convert the input_table into a numpy array nparr = arcpy.da.TableToNumPyArray(input_table, ['number', 'code', 'year']) ##convert the array into a pandas data frame df = pd.DataFrame(nparr.tolist(), columns=['number', 'code', 'year']) ##deconstruct the column containing the commas and transpose into rows s = df['number'].apply(lambda x: pd.Series(x.split(','))).stack() s.index = s.index.droplevel(-1) s.name = 'number' ##join the original dataframe and the temp series del df['number'] outdf = df.join(s) ##convert the df into a numpy array out_nparr = np.array(outdf.to_records(), np.dtype([('code', '|S10'), ('year', np.int32), ('number', '|S10')])) outTable = r'H:\Documents\ArcGIS\Default.gdb\fliptab_output' ##finally covert the numpy array back into the gdb table arcpy.da.NumPyArrayToTable(out_nparr, outTable, ("code", "year", "number"))
Hello!I am working with a table and I would like to get this:number(stored as text) Code Year0500, 0600, 0700 ABC1 2012to this:number Code Year0500 ABC1 20120600 ABC1 2012 0700 ABC1 2012I have gotten to where I can insert each number/space/comma into a new row but obviously I need it to break it at the commas. Ideas?
Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
