Adding CSV layer to QGIS when X and Y fields are the same

Question

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.

• Kadir Şahbaz: 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?
• Geographos: 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.
• Uditha Herath: 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"

Answer 1

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

• Geographos: 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.
• Kadir Şahbaz: @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.

Answer 2

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.

You need to generate a text file that looks like this:

ncols 6
nrows 3
xllcorner -1.75
yllcorner 53.25
cellsize 0.25
NODATA_value -9999
-9999 -9999 -9999 -9999 -9999 0.1 0.1
-9999 -9999 -9999 -9999 -9999 -9999 -9999
-9999 0.6 0.6 0.6 0.6 0.6 0.6

So you have a header block with the number of rows and columns in you table, what the lower left corner coordinates are, followed by the size of the cells and a nodata value that you have used to fill in the blank cells. Then you follow it with the actual values you have with the spaces replaced by your no data value (-9999 in the one above).

• Geographos: How can I create that block? Is it in Python console maybe?
• Ian Turton: I would do it in your spreadsheet
• wingnut: Lookup table in Excel to reformat maybe.
• Geographos: @wingnut i will try it tomorrow, sounds quite nice
• Geographos: @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?

Answer 3

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

• Ian Turton: This will be imported as separate points, which may or may not be useful
• aborruso: 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
• Geographos: @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
• aborruso: It's a command line tool. You use it in shell, both Linux and windows and Mac. What's your os?
• Geographos: I have WIndows. Can I launch it from Python console in QGIS?