Adding CSV file by Python to QGIS throws #Line20 got ...columns instead of... error

Question

I am struggling with the following code:

 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)

because I want to add the CSV file with the same X and Y field coordinates.

Unfortunately, I am getting an error:

ValueError: Some errors were detected ! Line #2 (got 42 columns instead of 72)

whereas it was working for another example

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

I tried to change the number of columns, and fill up empty values, but the error is roughly the same.

The thread below:

https://stackoverflow.com/questions/23353585/got-1-columns-instead-of-error-in-numpy

didn't help me with this problem.

What is the best way for removing this error?

Answer 1

The problem is caused by the comma in lat/long values. In your previous question, lat/long contains dot instead of comma for decimal (this is the main reason why you get the error) and lat/long values are not in double quotes.

The CSV in the previous question:

Current CSV:

One solution I found is to read the file with csv.reader() from Python's csv module into a list, replace comma with dot and then dump it into a numpy array with some edits.

import csv
import numpy as np

f = list(csv.reader(open('C:/path/to/file.csv', encoding='utf-8-sig'), delimiter=",", quotechar='"'))

# convert comma to dot in lat/long
a = [[j.replace(",", ".") for j in i] for i in f ]
c = np.array(a)
# convert empty items to "numpy.nan" not to get error while converting values to float
c[c==''] = np.nan

lats = c[:,0][1:-1].astype(float)
lons = -c[0][1:-1].astype(float)
data = c[1:-1,1:-1].astype(float)

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)

References:

1. numpy.loadtxt: how to ignore comma delimiters that appear inside quotes?

2. Using numpy.genfromtxt to read a csv file with strings containing commas

Answer 2

You can create a reshaped csv and add to qgis

import pandas as pd

df = pd.read_csv(r'/home/bera/Desktop/coords.csv')
# df.head()
#    Lat/Lon  24     23.75      23.5  ...     22.75      22.5     22.25        22
# 0    49.00 NaN       NaN       NaN  ...       NaN       NaN       NaN       NaN
# 1    49.25 NaN       NaN       NaN  ...       NaN       NaN       NaN       NaN
# 2    49.50 NaN  0.835699  0.432839  ...  0.131610  0.795437  0.564926  0.153382

df = df.melt(id_vars='Lat/Lon', var_name='Lon').rename(columns={'Lat/Lon':'Lat'}).dropna()
df.to_csv(r'/home/bera/Desktop/coords_fixed.csv')

# >>df.head()

#       Lat    Lon     value
# 7   49.50  23.75  0.835699
# 12  49.50   23.5  0.432839
# 13  49.75   23.5  0.807998