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?

underdark
  • 84,148
  • 21
  • 231
  • 413
four-eyes
  • 3,378
  • 5
  • 33
  • 58
  • 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 08:07
  • @user30184 Alright. I understand. Thanks! – four-eyes Apr 15 '15 at 08:16
  • @user30184 What I still do not understand is why my columns do not get wrapped with " since I convert everything into a string... – four-eyes Apr 15 '15 at 08: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 http://stackoverflow.com/questions/5308097/writing-double-quotes-in-python. – user30184 Apr 15 '15 at 08:39
  • @user30184 Ah i thought there is a general standard! Thanks – four-eyes Apr 15 '15 at 08:41

1 Answers1

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)

Community
  • 1
gene
  • 54,868
  • 3
  • 110
  • 187