Friends don't let friends export to CSV

Post by kylebarron » Wed, Mar 27, 2024, 2:26 AM UTC

culebron21 wrote: post #408

kylebarron wrote: post #363

Earlier quoted context omitted.

Sorry, this is not true _at all_ for geospatial data. A quick benchmark [0] shows that saving to GeoPackage, FlatGeobuf, and GeoParquet are roughly 10x faster than saving to CSV. Additionally, the CSV is much larger than any other format. [0]: https://gist.github.com/kylebarron/f632bbf95dbb81c571e4e64cd...

And here's my quick benchmark, dataset from my full-time job: > import geopandas as gpd > import pandas as pd > from shapely.geometry import Point > d = pd.read_csv('data/tracks/2024_01_01.csv') > d.shape (3690166, 4) > list(d) ['user_id', 'timestamp', 'lat', 'lon'] > %%timeit -n 1 > d.to_csv('/tmp/test.csv') 14.9 s ± 1.18 s per loop (mean ± std. dev. of 7 runs, 1 loop each) > d2 = gpd.GeoDataFrame(d.drop(['lon', 'la…

Post by kylebarron » Wed, Mar 27, 2024, 2:36 AM UTC

culebron21 wrote: post #432

kylebarron wrote: post #431

Earlier quoted context omitted.

Your issue is that you're using the default (old) binding to GDAL, based on Fiona [0]. You need to use pyogrio [1], its vectorized counterpart, instead. Make sure you use `engine="pyogrio"` when calling `to_file` [2]. Fiona does a loop in Python, while pyogrio is exclusively compiled. So pyogrio is usually about 10-15x faster than fiona. Soon, in pyogrio version 0.8, it will be another ~2-4x faster than pyogrio is no…

Still CSV is 2x smaller than GPKG with this kind of data. And CSV.gz is 7x smaller.

Post by chaps » Wed, Mar 27, 2024, 5:12 AM UTC

r-w wrote: post #353

chaps wrote: post #253

Earlier quoted context omitted.

Every now and then, yep :) https://mchap.io/that-time-the-city-of-seattle-accidentally-...

Wow, what a treasure trove you’ve got there! I’ve subscribed via RSS, in case anything else comes down the pipe :)

Post by culebron21 » Wed, Mar 27, 2024, 7:04 AM UTC

kylebarron wrote: post #431

culebron21 wrote: post #408

Earlier quoted context omitted.

And here's my quick benchmark, dataset from my full-time job: > import geopandas as gpd > import pandas as pd > from shapely.geometry import Point > d = pd.read_csv('data/tracks/2024_01_01.csv') > d.shape (3690166, 4) > list(d) ['user_id', 'timestamp', 'lat', 'lon'] > %%timeit -n 1 > d.to_csv('/tmp/test.csv') 14.9 s ± 1.18 s per loop (mean ± std. dev. of 7 runs, 1 loop each) > d2 = gpd.GeoDataFrame(d.drop(['lon', 'la…

Your issue is that you're using the default (old) binding to GDAL, based on Fiona [0]. You need to use pyogrio [1], its vectorized counterpart, instead. Make sure you use `engine="pyogrio"` when calling `to_file` [2]. Fiona does a loop in Python, while pyogrio is exclusively compiled. So pyogrio is usually about 10-15x faster than fiona. Soon, in pyogrio version 0.8, it will be another ~2-4x faster than pyogrio is no…

        > %%timeit -n 1
        > d.to_csv('/tmp/test.csv')
        10.8 s ± 1.05 s per loop (mean ± std. dev. of 7 runs, 1 loop each)

        > %%timeit -n 1
        > d2.to_file('/tmp/test.gpkg', engine='pyogrio')
        1min 15s ± 5.96 s per loop (mean ± std. dev. of 7 runs, 1 loop each)

        > %%timeit -n 1
        > d.to_csv('/tmp/test.csv.gz')
        35.3 s ± 1.37 s per loop (mean ± std. dev. of 7 runs, 1 loop each)

        > %%timeit -n 1
        > d2.to_file('/tmp/test.fgb', driver='FlatGeobuf', engine='pyogrio')
        19.9 s ± 512 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

        > ls -lah /tmp/test*
        -rw-rw-r-- 1 culebron culebron 228M мар 27 11:02 /tmp/test.csv
        -rw-rw-r-- 1 culebron culebron  63M мар 27 11:27 /tmp/test.csv.gz
        -rw-rw-r-- 1 culebron culebron 545M мар 27 11:52 /tmp/test.fgb
        -rw-r--r-- 1 culebron culebron 423M мар 27 11:14 /tmp/test.gpkg

Post by ranguna » Wed, Mar 27, 2024, 9:25 AM UTC

elric wrote: post #409

ranguna wrote: post #319

Earlier quoted context omitted.

There's a difference between "1" and 1. When you import a csv and try to do maths on a "number" you won't get the expected result. Some importers won't even allow you to specify that "number" columns are numbers, they'll outright fail and force you to say it's a string, or you'll have to specify which columns are "numbers" and map the strings to numbers on the importer side. If they are numbers to begin with (not "nu…

Sounds to me like you're using a shitty parser. CSV is schemaless. It is up to you to tell the parser what types to use if it isn't unambiguous. Quoted values can be numbers, and unquoted values can be strings. I have not used any CSV tools that don't support this behaviour.