Adding multiple points using Excel data in QGIS

Question

How can I add more that one point to a specific location (defined by lat and long coordinates) using an Excel file? My table looks like this:

City name	Latitude	Latitude	A1	A2	A3
New York	40.785591	-74.188452	1	1	1

So, what I need, is to add 3 different points on my map according to the data from columns A1, A2, A3. I know in ArcGIS you can define them in a query, but how can I do this in QGIS?

• zabop: Are you open to open the csv (or if it is xlsx, export it to csv) with Python, create a shapefile and then add that to QGIS?
• zabop: If yes, I can show you how to do that.
• Catalin Alexa: Thanks! I had some issues saving my excel files as .csv, but I managed to add my data from a .txt file. dropbox.com/s/kox5qpol54nqpuj/temp.txt?dl=0
• MrXsquared: 3 different points? Do you mean 3 points at the same location?
• Catalin Alexa: Exactly! I have a point that represents a city / village, and three or more points next to it, representing different data for that city.

Answer 1

If you have a text file (removed the spaces from the one you provided):

Name,Latitude,Longitude,A1,A2,A3
City1,44.499232,23.651273,1,1,1
City2,44.199232,23.251273,1,0,1

in data.txt, you can proceed with Python.

import pandas as pd
import geopandas as gpd
from shapely.geometry import Point

Read in the file:

df = pd.read_csv("data.txt")

Load in the coordinates into shapely objects:

points = df.apply(lambda row: Point([row['Longitude'],row['Latitude']]),axis=1)

points now is:

0 POINT (23.651273 44.499232)
1 POINT (23.251273 44.199232)
dtype: object

Note that df[['A1','A2','A3']].sum(axis=1) returns:

0 3
1 2
dtype: int64

Indeed, we need to repeat point with index 0 3 times, the one with index 1 2 times. Let's do this, loading the result into a list called geometry:

geometry = []
for index, value in df[['A1','A2','A3']].sum(axis=1).items():
 for _ in range(value):
 geometry.append(points.loc[index])

Write to file:

gpd.GeoSeries(geometry).to_file("example.geojson")

example.geojson is now:

{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "properties": { }, "geometry": { "type": "Point", "coordinates": [ 23.651273, 44.499232 ] } },
{ "type": "Feature", "properties": { }, "geometry": { "type": "Point", "coordinates": [ 23.651273, 44.499232 ] } },
{ "type": "Feature", "properties": { }, "geometry": { "type": "Point", "coordinates": [ 23.651273, 44.499232 ] } },
{ "type": "Feature", "properties": { }, "geometry": { "type": "Point", "coordinates": [ 23.251273, 44.199232 ] } },
{ "type": "Feature", "properties": { }, "geometry": { "type": "Point", "coordinates": [ 23.251273, 44.199232 ] } }
]
}

Then you can load example.geojson to QGIS. The above code is available (and runnable) here.

• Catalin Alexa: Thanks! But I'm totally new to this, and I know nothing about Python and how can I integrate it. Aren't any other solutions? I was hoping I can use something like in ArcGis, where you can define a query that says something like: A1=0 or A1>0 and you get the point on the map.
• katagena: Just install this plugin, and you would be able to load your Excel files: plugins.qgis.org/plugins/SpreadsheetLayers
• Catalin Alexa: I can get what I want by duplicating a layer and changing its marker colour and offset coordinates. This will do the trick. I can also use different .txt (which include the same lat and long coord.) files as layers and get the same result. @katagena I installed your recommended plugin, I get the new layer from an excel file, which is great, cause I don't have to work with csv or txt files, but how can I use the data from this layer (A1, A2) in order to show on my map 2 different points for the same location?
• katagena: To show 2 points at the same location, it is not anymore a question of projection, but a question of symbology. You should open a new question! Try to search on gis.stack... there is a lot of similar questions!