1

I have an extremely large CSV file (3GB) and want to choose a part of that based on columns(lat,lon) and do analysis on that. I can't load it into a desktop app like ArcGIS and select a region manually. I used GeoPandas for converting lat/lon columns into geometry objects and create points.

I tried:

df=pd.read_csv('file.csv')
points=df.apply(lambda row: point (row.LON, row.LAT), axis=1)

But the file is too big and Jupyter notebook gets stuck in this step (converting every row into geometry object).

1
  • You can open the entire file in Gigasheet. It's a free, no-code data analysis platform that can handle 1 billion rows of data and display it in a familiar rows-and-columns interface. You can check it out for free at gigasheet.co. Disclosure: I work at Gigasheet Jan 5 at 1:20

2 Answers 2

3

You can read a CSV one line at a time, deal with that line, then either output it or skip to the next one.

Tutorials on reading CSV files in python using the csv module from the standard library are everywhere. First duckduckgo hit is:

https://thispointer.com/python-read-a-csv-file-line-by-line-with-or-without-header/

Here's a full working example. This will not read the whole file in, but it still might take a while to process a huge CSV. Its done in the blink of an eye on 100,000 records, a couple of seconds on 2 million records.

Given lines of CSV like this:

"x","y","N"
-21.8714018538594,-0.503809251822531,"Q"
96.0263147950172,-69.9362510349602,"U"
81.40420909971,-38.342371112667,"W"
....etc...

Then the following python:

#!/usr/bin/env python

import csv

def inbox(p):
    return float(p[0])>25 and float(p[0])<30 and \
        float(p[1])>17 and float(p[1])<19

def comma(L):
    return ",".join(L)

with open('pts.csv', 'r') as read_obj:
    csv_reader = csv.reader(read_obj)
    header = next(csv_reader)
    print(comma(header))
    for row in csv_reader:
        if inbox(row):
            print(comma(row))

will print out the rows where x is between 25 and 30, and y is between 17 and 19:

$ python filter.py 
x,y,N
25.6576492544264,17.61342425365,Q
27.0452616736293,17.7616416150704,K
25.6611929554492,18.5779095254838,X
27.1376479323953,17.3779100412503,V
28.8249599095434,18.005088083446,S

If you want to write this to another file, either redirect the output or use standard python file writing (well-documented elsewhere).

3
  • thank you very much.unfortunately i dont have a Unix system , but yes for example i want to take a rectangle boundary and choose whatever falls into it from csv file.so which way is more sufficent in your opinion?going through every record would be so much hard.thx
    – milad
    Oct 8, 2021 at 21:33
  • 1
    There's no magic way of selecting the correct records without reading every record in the file, regardless if you do that with python or with AWK. The advantage of the method described in this answer is that you are never loading the file into memory; you read each record in sequence, decide if it's inside the area of interest, and then apply your analysis only to those records. Just as you have only a single record in memory at any time, you can write each analysis result to a file as it's completed, meaning you don't have a massive in-memory results file.
    – Llaves
    Oct 9, 2021 at 2:26
  • I've added a complete working example code now but please go look at examples of how to process CSV data using the csv module, there's more there than I could show here.
    – Spacedman
    Oct 9, 2021 at 11:27
2

Based on this SO answer you could iterate the CSV in chunks:

import geopandas as gpd
import pandas as pd


xmin, ymin, xmax, ymax = 127, -18, 128, -17

with pd.read_csv("/tmp/test.csv", chunksize=1000) as chunks:
    df = pd.concat([c[(c['LON'] > xmin) & (c['LON'] < xmax) & (c['LAT'] > ymin) & (c['LAT'] < ymax)] for c in chunks])

gdf = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df.LON, df.LAT), crs='EPSG:4326')
print(gdf.head())
2
  • What is actually a chunk. I need to check :)
    – Taras
    Oct 8, 2021 at 23:10
  • As per the doc: "an iterable object of type TextFileReader"
    – user2856
    Oct 8, 2021 at 23:50

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.