1

So, I have a .csv file.

Here an example how the a row from the original .csv looks like:

"AREA",44.37,-00.83,"5","1","8","E","E",,,"KKI","KKO"

Some lines have missing latitudes and longitudes, so I want to get rid of them. Should be easy. Thats my snippet (Python 2.7) that does it:

import csv

n = 0
m = 0

nf = open("newfilefile.csv", "w")

with open("oldfile.csv", "r") as csvFile:
    originalData = csv.reader(csvFile, delimiter=",")
    for lines in originalData:

        if len(lines[9]) < 1:
            n += 1
            print 'Missing Latitude', n

        elif len(lines[10]) < 1:
            m += 1
            print 'Missing Longitude', m

        else:
             nf.write(str(lines[0]).strip() + "," + str(lines[1]).strip() + "," + str(lines[2]).strip()  + "," + str(lines[3]).strip() + "," + str(lines[4]).strip() + "," + str(lines[5]).strip() ...  + "\n" )

nf.close()

Thats how the same row would look like in my new .csv:

AREA,44.37,-00.83,5,1,8,E,E,,,KKI,KKO

Beforehand I could open the original csv file. QGis just said that there are missing geometry information. Now I get a bunch of errors, namely: Invalid record format at line 324 and so forth... I compared the lines where it marks the error and the only difference is that in the oldfile, everything is wrapped in " while in the file my script creates that is not the case.

Why does QGis need " around every from a column (if that is the mistake) and how would I add that to my snippet?

  • 2
    Not everything is wrapped with " but only columns which contain strings. "5" is a string but 5is a number -> schema mismatch -> failure. – user30184 Apr 15 '15 at 8:07
  • @user30184 Alright. I understand. Thanks! – Stophface Apr 15 '15 at 8:16
  • @user30184 What I still do not understand is why my columns do not get wrapped with " since I convert everything into a string... – Stophface Apr 15 '15 at 8:27
  • There is no general standard that strings should be surrounded by double quotes. You must code it yourself: if string then put it between double quotes else just write. Read also stackoverflow.com/questions/5308097/…. – user30184 Apr 15 '15 at 8:39
  • @user30184 Ah i thought there is a general standard! Thanks – Stophface Apr 15 '15 at 8:41
2

Currently the best solution to read, work on and write csv files is the pandas module.

Read a csv file (an example)

import pandas as pd
df = pd.read_csv("test.csv")
print df 
   type   long   lat   o   x   y   z   t
0  AREA  44.37 -0.83   5   1 NaN NaN   KKO
1  AREA    NaN   NaN   5   1   8   3   KKO
2  AREA  46.48 -0.85 NaN NaN NaN NaN   KKO
3  AREA  46.52   NaN NaN NaN NaN NaN   KKO
4  AREA    NaN -0.75 NaN NaN NaN NaN   KKO

Eliminate all the rows with no longitude or with no latitude or with no longitude and no latitude

nolong = df.dropna(subset=['long'])
print nolong
   type   long   lat   o   x   y   z    t
0  AREA  44.37 -0.83   5   1 NaN NaN   KKO
2  AREA  46.48 -0.85 NaN NaN NaN NaN   KKO
3  AREA  46.52   NaN NaN NaN NaN NaN   KKO
nolat = df.dropna(subset=['lat'])
print nolat
   type   long   lat   o   x   y   z    t
0  AREA  44.37 -0.83   5   1 NaN NaN   KKO
2  AREA  46.48 -0.85 NaN NaN NaN NaN   KKO
4  AREA    NaN -0.75 NaN NaN NaN NaN   KKO
nolonglat = df.dropna(subset=['long','lat'])
print nolonglat
   type   long   lat   o   x   y   z prout
0  AREA  44.37 -0.83   5   1 NaN NaN   KKO
2  AREA  46.48 -0.85 NaN NaN NaN NaN   KKO

Save the resulting csv file (you can also use the geopandas module to create directly a shapefile, Using the python shape library: pyshp - how to convert polygons in .csv file to .shp)

nolonglat.to_csv('test2.csv', sep=',', index = False)

Import in QGIS

enter image description here

If you want to use the csv module, use the DictReader and DictWriter classes

import csv
with open('test.csv') as csvfile:
    for row in csv.DictReader(csvfile):
        if row['long'] and row['lat']:
            print row
 {'t': 'KKO', 'o': '5', 'y': '', 'long': '44.37', 'lat': '-00.83', 'x': '1', 'z': '', 'type': 'AREA'}
 {'t': 'KKO', 'o': '', 'y': '', 'long': '46.48', 'lat': '-00.85', 'x': '', 'z': '', 'type': 'AREA'}

Then you can use the DictWriter class to write the csv file or the Fiona module to create directly a shapefile (Using Fiona to write a new shapefile from scratch)

  • thanks. I will look into the pandas module. looks promising! – Stophface Apr 16 '15 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.