[GRASS-dev] Write array to (point) attribute table

Michel Wortmann wortmann at pik-potsdam.de
Thu Oct 1 01:01:20 PDT 2015


Hi Paulo,
I had the same problem some time ago. I am using pandas for a lot of 
things and discovered the quickest way to append a column to a vector 
table is using pandas' .to_sql dataframe method. Here is my full 
function for it:

import pandas as pa

dataframe = pa.DataFrame( ... )

def appendColumns(vecttbl,dataframe,join=None,layer=1):

     '''Upload a DataFrame (Series should be first converted into a sql compliant datafram)

     to a grass vector table. Join is a valid column in vecttbl or if None take categories.

     Always joins on df.index, which is not uploaded.'''

     # get sqlite.db info

     db = grass.vector_db(vecttbl)[layer]

     con = sqlite3.connect(db['database'])

     # decide join indeces

     itbl = {False:db['key'],True:join}[join!=None]

     # make series to dataframe

     dataframe.to_sql('pandas_temp',con,if_exists='replace',index_label='ix') # index label?

     # join columns to vecttbl

     grass.run_command('v.db.join',map=vecttbl,column=itbl,

                        other_table='pandas_temp',other_column='ix',

                        subset_columns=','.join(map(str,dataframe.columns)))

     # remove temp table

     cur = con.cursor()

     cur.execute('DROP TABLE IF EXISTS pandas_temp')

     con.commit(); con.close()

     return


The dataframe.to_sql line is essentially all you need, the rest of the 
function is just prepping.
Hope it helps,
Michel


On 10/01/2015 12:05 AM, Paulo van Breugel wrote:
>
>
> On 30-09-15 17:52, Paulo van Breugel wrote:
>>
>>
>> On 30-09-15 17:47, Anna Petrášová wrote:
>>>
>>>
>>> On Wed, Sep 30, 2015 at 11:20 AM, Paulo van Breugel 
>>> <p.vanbreugel at gmail.com> wrote:
>>>
>>>
>>>
>>>     On 30-09-15 16:18, Anna Petrášová wrote:
>>>>
>>>>
>>>>     On Wed, Sep 30, 2015 at 9:27 AM, Paulo van Breugel
>>>>     <p.vanbreugel at gmail.com <mailto:p.vanbreugel at gmail.com>> wrote:
>>>>
>>>>
>>>>
>>>>         On Wed, Sep 30, 2015 at 11:53 AM, Pietro
>>>>         <peter.zamb at gmail.com> wrote:
>>>>
>>>>             On Wed, Sep 30, 2015 at 9:51 AM, Paulo van Breugel
>>>>             <p.vanbreugel at gmail.com> wrote:
>>>>             >
>>>>             >
>>>>             > On Wed, Sep 30, 2015 at 2:02 AM, Anna Petrášová
>>>>             <kratochanna at gmail.com>
>>>>             > wrote:
>>>>             >>
>>>>             >>
>>>>             >>
>>>>             >> On Tue, Sep 29, 2015 at 6:09 PM, Paulo van Breugel
>>>>             >> <p.vanbreugel at gmail.com> wrote:
>>>>             >>>
>>>>             >>> This must be a very basic question, but I can't
>>>>             find an easy/direct way
>>>>             >>> to do this. In python, if I have an array with
>>>>             values with a length equal to
>>>>             >>> the number of rows in an attribute table of a
>>>>             (point) vector layer, how can
>>>>             >>> I write those values to a new column in that
>>>>             attribute table. I can of
>>>>             >>> course first create the column, but than how to
>>>>             update that column with the
>>>>             >>> values in the array?
>>>>             >>
>>>>             >>
>>>>             >> it should be pretty easy to do with pygrass,
>>>>             unfortunately there is no
>>>>             >> example on assigning attributes in the official
>>>>             documentation [1],  but it
>>>>             >> should be pretty easy, something like that (not tested):
>>>>             >>
>>>>             >> with VectorTopo('myvector', mode='w') as vectormap:
>>>>             >>     for feature in vectormap:
>>>>             >>  feature.attrs['mycolumn'] = value
>>>>             >>
>>>>             >>
>>>>             > Thanks, but that seems to write the vector back
>>>>             without attribute table
>>>>
>>>>             You have to save the changes in the database out from
>>>>             your cycle, with:
>>>>
>>>>             vectormap.table.conn.commit()
>>>>
>>>>
>>>>         Thanks Pietro. I am, however, not sure I understand (I
>>>>         tried to use it, but thanks to my limited experience in
>>>>         Python / pygrass not much luck). Just to be more specific,
>>>>         I am trying to create a script that divides points in
>>>>         training and test groups, similar to v.kcv, but with points
>>>>         clustered in space. E.g.,
>>>>
>>>>         # Create vector
>>>>         grass.run_command("v.random", output="testB", npoints=10,
>>>>         overwrite=True)
>>>>         grass.run_command("v.db.addtable", map="testB", columns="X
>>>>         DOUBLE PRECISION,Y DOUBLE PRECISION,GR INTEGER")
>>>>         grass.run_command("v.to.db", map="test", option="coor",
>>>>         columns="X,Y")
>>>>
>>>>         # Create groups
>>>>         vectmap = 'test'
>>>>         cvals = array(grass.vector_db_select(vectmap, layer =
>>>>         int(1), columns = 'X,Y')['values'].values()).astype(np.float)
>>>>         centroids,_ = kmeans(cvals,2)
>>>>         idx,_ = vq(cvals,centroids)
>>>>
>>>>         # write results to tabel
>>>>         Now I would like to write idx to the column 'GR' in the
>>>>         attribute table of 'test'.
>>>>
>>>>         p.s. I am first creating the XY columns now, but is there a
>>>>         function to get the coordinates (cvals) in pygrass directly?
>>>>
>>>>
>>>>     I don't fully understand the example,
>>>
>>>     Thanks for the quick response. I basically have a list with
>>>     values (idx in the example above) which I like to add as a
>>>     column to the attribute table of an existing vector (point
>>>     layer). The length of idx is equal to the number of rows in the
>>>     attribute table. The solution of Anna seems like an elegant
>>>     solution (and easier and more flexible than other solutions I
>>>     tried using e.g., sqlite3). However, as I wrote, I end up with a
>>>     vector without attribute table. You wrote that I "have to save
>>>     the changes in the database out from your cycle, with:
>>>     vectormap.table.conn.commit()". I am, however, not sure what you
>>>     mean with 'out from your cycle' or how to implement that.
>>>
>>>
>>> probably after the for cycle ends you would call this 
>>> 'vectormap.table.conn.commit()', if it doesn't works, try to put it 
>>> in the cycle (I am not sure what is supposed to work).
>>
>> I did try both, with no luck so-far. I'll give it another try though, 
>> perhaps I did something else wrong.
>
> Tried out again, but after running the code below, I end up with an 
> empty vector layer (no points and no attribute table). Same if I put 
> the vectormap.table.conn.commit() in the for cycle.
>
> import grass.script as grass
> from grass.pygrass.vector import VectorTopo
> grass.run_command("v.random", output="testB", npoints=10, overwrite=True)
> grass.run_command("v.db.addtable", map="testB", columns="GR INTEGER")
> with VectorTopo('testB', mode='w') as vectormap:
>      for feature in vectormap:
>          feature.attrs['GR'] = 9
>          vectormap.table.conn.commit()
>
>>>
>>>>     but yes, you can get coordinates:
>>>>
>>>>     with VectorTopo('myvector', mode='w') as vectormap:
>>>>         for feature in vectormap:
>>>>     print feature.x
>>>>           print feature.y
>>>
>>>     Great, thanks. I did not find this in the manual. If it is not
>>>     there, perhaps it would be something worth including? I would
>>>     not mind providing a text, but I am not sure what would be the
>>>     best way to do that.
>>>
>>>
>>> Technically, it's in the manual:
>>> https://grass.osgeo.org/grass70/manuals/libpython/pygrass_vector.html#geometry-classes
>>>
>>> but we are lacking more examples of often used constructions. Some 
>>> other examples are available in the recent workshop we did:
>>>
>>> https://github.com/wenzeslaus/python-grass-addon/blob/master/02_pygrass_library.ipynb
>>
>> Thanks, I will have a look at it.
>>>
>>>
>>>>
>>>>
>>>>             Pietro
>>>>
>>>>
>>>>
>>>
>>>
>>
>
>
>
> _______________________________________________
> grass-dev mailing list
> grass-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/grass-dev

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/grass-dev/attachments/20151001/61db9c62/attachment-0001.html>


More information about the grass-dev mailing list