Export using selections/filters previously defined.

[TomasRL]

Hi all,

I have defined some selections as usual:

df.select('(flag_vlos<5.5)&(ruwe < 1.4)&(0.001+0.039*(bp_rp) < log10(phot_bp_rp_excess_factor))&(log10(phot_bp_rp_excess_factor) < 0.12 + 0.039*(bp_rp))', name='quality')
df.select('(Lz>1000.0)&(Lz<1500.0)&(Lperp>1750.0)&(Lperp<2600)', name='helmiA')

And now I want to export a hdf5 file. In previuos versions of VAEX, I would export as follows:

df.export_hdf5(path=f'{aux_path}helmi_good_vv_5kpc_quality.hdf5', virtual=True, selection='(HelmiA)&(quality)', progress=None)

However, now the way to export things has changed (selection is not an option anymore):

df[df.poe > 5].export(...)

The question: if I have defined conditions with names as 'quality' or 'helmiA'... how can I export it now easily, not having to copy all the expression? Is there a way of doing so?

Thanks!!!

[maartenbreddels]

Hi Tomas,

good question, in the comments where we made the change:
#1003
We advice to use df.filter, so that would be

df.filter('(HelmiA)&(quality)').export(...)

Let us know if that helps.

Regards,

Maarten

[TomasRL]

Thanks for the quick reply!!

It seems it works, but very slowly. I exported the same file with the same selection using an older version of VAEX and it took 2 minutes, 32 seconds. Now, doing the equivalent but with a newer version of vaex this is says:

[#---------------------------------------] 3.16% estimated time: 3497.51s = 58.3m = 1.0h
(the estimated time keeps increasing)

Any ideas on what might be going on?

Thanks!

PS: the file is around 800 kb.... so almost nothing....

---

Older version of VAEX I am using (the one in Gaia2 under module load anaconda3/2019.10):
1.0.0-beta.6 (this is the output from vaex.version)
df.export_hdf5(path=f'{aux_path}helmi_good_vv_5kpc_quality.hdf5', virtual=True, selection='((helmi1_a)|(helmi2_a))&(large_dist)&(quality)', progress=None)

Newer version of VAEX I am using:
{'vaex': '4.0.0a5', 'vaex-core': '4.0.0a10', 'vaex-viz': '0.5.0.dev1', 'vaex-hdf5': '0.7.0a4', 'vaex-server': '0.4.0a1', 'vaex-astro': '0.8.0.dev1', 'vaex-ui': '0.3.0', 'vaex-jupyter': '0.6.0.dev1', 'vaex-ml': '0.11.0a4', 'vaex-arrow': '0.4.2', 'vaex-graphql': '0.2.0.dev1'}

df.filter('((helmi1_a)|(helmi2_a))&(large_dist)&(quality)').export_hdf5(path=f'{aux_path}helmi_good_vv_5kpc_quality.hdf5', progress=True)

[maartenbreddels]

strange, can you try:

df.filter('(HelmiA)&(quality)').extract().export(...)

and let us know if it is faster?

[TomasRL]

Thanks for the suggestion! It worked perfectly well for small samples:

[########################################] 100.00% elapsed time : 23.38s = 0.4m = 0.0h
CPU times: user 17.8 s, sys: 14.3 s, total: 32.1 s
Wall time: 30.9 s

However, I tried to create a subset of the large file from Gaia (gaia-edr3.hdf5, 1.8 billion sources) and the export takes around 1 day (24 hours), while with an older version of VAEX (the same I stated yesterday) it took only 9.5 minutes. The output file I am aiming at has around 70 Gb...

I have tried export_many as well and I did several tries and this is what I got:

• 9min 34s (old version) export_hdf5
• 14min 38s (new version, chunk_size=200000000) 3 files created. hdf5
• 11min 50s (new version, chunk_size=20000000) 29 files created. hdf5
• 10min 7s (new version, chunk_size=20000000) 29 files created. parquet

After this I need to join all individual files to have a single table.... so in the end the old version seems to me more efficient.... any ideas? I hope this helps.

Thanks a lot!!

[maartenbreddels]

I have trouble reproducing this. Could you try exporting only numerical columns, and not the string columns, that might affect it.

[TomasRL]

Hi again, sorry it took so long to come back to this. Here you have an example that you can run in gaia2 to see what I mean. Using an older version of vaex it takes in total around 20 minutes to run. Using a newer version of vaex it takes around 12 hours (according to the progress bar). As you mentioned, in this example I am selecting which columns to store, and I am avoiding string columns.... ideas?

Thanks in advance!!

---

Code:

import numpy as np
import sys,glob,os,time
import vaex
import matplotlib.pyplot as plt
import math
from astropy.io import ascii
import matplotlib.cm as cm
import glob
#os.environ["OMP_NUM_THREADS"] = "1"

pi = math.pi

gaia = vaex.open('/net/gaia2/data/users/gaia/gaia-edr3/gaia-edr3.hdf5')

#Adding geometric columns and colour, magnitude, etc etc...

gaia.add_column('new_parallax', gaia.evaluate('parallax')+0.017)
gaia.add_column('distance', 1.0/gaia.evaluate('new_parallax')) # new_parallax in 'mas', 1/parallax --> distance in kpc.
gaia.add_variable('Z0', 0.0196) ; Z0 = 0.0196 # kpc from a conversation somewhere on the email, with references and everything.

#Adding Cartesian coordinates (tilde X, Y, Z), because they are centred at the Sun
gaia.add_virtual_columns_spherical_to_cartesian(alpha='l', delta='b', distance='distance', xname='tildex', yname='tildey', zname='tildez')

#Adding Cylindrical (polar) coordinates, with the Sun at the centre. --> tilde Rho, tild Phi
gaia.add_virtual_columns_cartesian_to_polar(x='(tildex)', y='(tildey)', radius_out='tildeRho', azimuth_out='tildePhi', radians=True)

#Adding Cartesian coordinates centred at the galactic PLANE. --> xyz
gaia.add_column('x', gaia.evaluate('tildex'))
gaia.add_column('y', gaia.evaluate('tildey'))
gaia.add_column('z', gaia.evaluate('tildez+Z0'))

gaia.add_column('vt', (4.74/gaia.evaluate('new_parallax'))*np.sqrt(gaia.evaluate('pmra')**2.0+gaia.evaluate('pmdec')**2.0))

path = '/net/gaia2/data/users/ruizlara/disc_files_bayestar_bab18/' # Path to save files.
columns = ['source_id', 'new_parallax', 'parallax_over_error', 'l', 'b', 'distance', 'phot_g_mean_mag', 'phot_bp_mean_mag', 'phot_rp_mean_mag', 'phot_bp_rp_excess_factor', 'bp_rp', 'ruwe', 'phot_g_mean_flux', 'phot_g_mean_flux_error', 'phot_bp_mean_flux', 'phot_bp_mean_flux_error', 'phot_rp_mean_flux' , 'phot_rp_mean_flux_error', 'x', 'y', 'z', 'vt']

#All selections:
gaia.select('(0.0<distance)&(distance<3.501)', name='cut_dist')
gaia.select('tildeRho<1.001', name='cylinder')

#Saving files (Old VAEX):
#gaia.export_hdf5(str(path)+'cylinder_catalogue_eDR3_old_vaex.hdf5', column_names = columns, virtual=True, selection='(cut_dist)&(cylinder)', progress=None)

#Saving files (New VAEX):
gaia[columns].filter('(cut_dist)&(cylinder)').extract().export_hdf5(path+'gaia_edr3_nearby_new_vaex.hdf5', progress=True)

del gaia

[maartenbreddels]

Hi Tomas,

Could you compare it to:

gaia[columns].filter('(cut_dist)&(cylinder)').extract().export_hdf5(path+'gaia_edr3_nearby_new_vaex.hdf5', progress=True, column_count=len(columns))

This will evaluate all columns in 1 batch, instead of writing 1 column at a time, see the docstring of export_hdf5 for details. You could also experiment with the writer_threads arguments.

Curious to hear if this speeds things up, let us know what you find!

cheers,

Maarten

[TomasRL]

Hi Maarten,

Thanks a lot for your help!! In fact, when I implemented your suggestion (column_count), that option was not part of export_hdf5, I checked and then I realized I was using a slightly older version than the current one:

{'vaex': '4.0.0a5', 'vaex-core': '4.0.0a10', 'vaex-viz': '0.5.0.dev1', 'vaex-hdf5': '0.7.0a4', 'vaex-server': '0.4.0a1', 'vaex-astro': '0.8.0.dev1', 'vaex-ui': '0.3.0', 'vaex-jupyter': '0.6.0.dev1', 'vaex-ml': '0.11.0a4', 'vaex-arrow': '0.4.2', 'vaex-graphql': '0.2.0.dev1'}

I updated to:

{'vaex': '4.1.0', 'vaex-core': '4.1.0', 'vaex-viz': '0.5.0', 'vaex-hdf5': '0.7.0', 'vaex-server': '0.4.0', 'vaex-astro': '0.8.0', 'vaex-ui': '0.3.0', 'vaex-jupyter': '0.6.0', 'vaex-ml': '0.11.1', 'vaex-arrow': '0.4.2', 'vaex-graphql': '0.2.0'}

And now everything works. Here some numbers in case you are interested (creation of a 40Gb hdf5 file):

• 'vaex': '4.0.0a5' --> ~12 hours
  gaia[columns].filter('(cut_dist)&(cylinder)').extract().export_hdf5(path+'gaia_edr3_nearby_new_vaex.hdf5', progress=True)
• 'vaex': '4.1.0' --> ~5.2 hours
  gaia[columns].filter('(cut_dist)&(cylinder)').extract().export_hdf5(path+'gaia_edr3_nearby_new_vaex.hdf5', progress=True)
• 'vaex': '4.1.0' --> ~20 minutes
  gaia[columns].filter('(cut_dist)&(cylinder)').extract().export_hdf5(path+'gaia_edr3_nearby_new_vaex.hdf5', progress=True, column_count=len(columns))
• 'vaex': '4.1.0' --> ~17 minutes
  gaia[columns].filter('(cut_dist)&(cylinder)').extract().export_hdf5(path+'gaia_edr3_nearby_new_vaex.hdf5', progress=True, column_count=len(columns), writer_threads=4)

So, all in all, just the change of the version makes it run quicker, but without a doubt the column_count arguments was the main difference!

Previously I used two versions of vaex depending on what I had to do (old for storing large databases and new for crossmatches and the rest).... now I can just use this version!! Awesome!! :)

Thanks a lot for your help!!

[maartenbreddels]

Thanks for the reaction, this is useful data, based on this I wonder if column_count=len(columns) should be the default behavior.