7

I would like to add the CSV file, where both Longitude and Latitude head have been defined in one cell as per below:

enter image description here

But QGIS can't add them, as both X and Y are deemed as the same.

I've tried with WKT geometry (Option 2) but it's still wrong. The layer is inappropriate.

enter image description here

Is there any way to parse the following CSV data in QGIS?

All the numbers inside have been allocated to the specific coordinates defined in the top row and the first column.

I need them populated in QGIS.

3
  • What do other numbers mean? What kind of data is this, point, line, polygon (I guess, it is line)? All numbers in the same row are equal. What does it mean? Why are some rows empty? Apr 26, 2021 at 16:25
  • 1
    These rows correspond to the data, which occur at the specific location (UK). The empty cells mean, that there is no record at that location.
    – MKR
    Apr 26, 2021 at 16:40
  • definetly you have to go to python. could have tried this if I had a copy or sample of the csv file. this csv file should be reduced to 3 columns "X", "Y", and the "DataValue" Apr 26, 2021 at 17:35

3 Answers 3

12
+50

You can add the CSV file, structured as below, as a layer using the following script:

import numpy as np 

c = np.genfromtxt('C:/path/to/file.csv', delimiter=',') # check delimiter
lats = c[:,0][1:]
lons = -c[0][1:] # remove minus if you get a mirrored result
data = c[1:,1:]

crs = "EPSG:4326"  # change if crs is different
layer = QgsVectorLayer("Point?crs=" + crs + "&field=value:double", "Layer", "memory")

layer.startEditing()

for i, lat in enumerate(lats):
    for j, lon in enumerate(lons):
        v = data[i][j]
        
        if not np.isnan(v):
            feat = QgsFeature(layer.fields())
            feat["value"] = float(v)
            geom = QgsGeometry.fromPointXY(QgsPointXY(lon, lat))
            feat.setGeometry(geom)
            layer.addFeature(feat)            

layer.commitChanges()

QgsProject.instance().addMapLayer(layer)

Sample CSV data:

enter image description here

Result:

enter image description here enter image description here

3
  • I will go with it tomorrow. I can see from your screen, that these points have been rotated by 90 degrees. These 2 dots on the left correspond to Norman Islands (Jersey & Guernsey), which names you can see on the bottom left next, south of the English Channel.
    – MKR
    Apr 26, 2021 at 21:52
  • 1
    @MKR I've edited the answer. If those two points should be on the bottom left, then the longitude values in your data are incorrect. They should have been the additive inverse of their values in the table. But I've changed the script to give the result that you mentioned, though. Apr 26, 2021 at 22:26
  • Works perfectly! Thank you
    – MKR
    Apr 27, 2021 at 14:13
8

Your problem is that, that is not a CSV file - which is a series of features one per row. What you have is more like an ASCII grid file, so with some editing you might be able to import it that way.

You need to create a header block:

ncols         4
nrows         6
xllcorner     0.0
yllcorner     0.0
cellsize      0.25
NODATA_value  -9999

where you can fill in the values from your sheet and then you need to fill all the empty cells with -9999.

Finally, you need to remove the lat/long row column and write the whole file out with spaces between the cell values.

You can then import it using the raster menu.

6
  • How can I create that block? Is it in Python console maybe?
    – MKR
    Feb 22, 2021 at 17:24
  • 1
    I would do it in your spreadsheet
    – Ian Turton
    Feb 22, 2021 at 18:10
  • Lookup table in Excel to reformat maybe.
    – wingnut
    Apr 26, 2021 at 17:18
  • @wingnut i will try it tomorrow, sounds quite nice
    – MKR
    Apr 26, 2021 at 21:53
  • @IanTurton I wish I could accept your answer. Despite the answer below is correct, could you explain to me how should I create this header block? Shall I make it in Excel? Use some function, and so on? Or could you advise some tutorials about it?
    – MKR
    Apr 27, 2021 at 14:15
4

You could use Miller (https://github.com/johnkerl/miller).

Starting in example from this example input.csv

lat/lon 12 14 15 13
38 5
37 4 2 3
35 4 4 3
38 7 8 6

and running

mlr --csv reshape -r "[0-9]" -o item,value \
then filter -x -S '$value==""' \
then label y,x,value ./input.csv >./output.csv

you will have

y,x,value
38,14,5
37,12,4
37,14,2
37,15,3
35,12,4
35,14,4
35,13,3
38,12,7
38,15,8
38,13,6
5
  • This will be imported as separate points, which may or may not be useful
    – Ian Turton
    Feb 22, 2021 at 18:10
  • I know @IanTurton, but MKR in its screenshot shows import points wizard. If he needs to import it as raster, the right way is yours
    – aborruso
    Feb 22, 2021 at 18:14
  • @aborruso could be possible to explain to me where should I place the code you wrote for me? Is it the Python file provided in the Miller dorectory? Could you advise? I am eager to accept your answer and give you +50
    – MKR
    Apr 27, 2021 at 14:16
  • It's a command line tool. You use it in shell, both Linux and windows and Mac. What's your os?
    – aborruso
    Apr 27, 2021 at 19:16
  • I have WIndows. Can I launch it from Python console in QGIS?
    – MKR
    Apr 30, 2021 at 8:58

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.