funjoin(1)

💖 funjoin(1) — SAORD Documentation 💖

okay bestie, sit down, hydrate, because today we're joining FITS binary tables and it's gonna be iconic ✨

---

🌸 NAME 🌸

funjoin — join two or more FITS binary tables on specified columns 🫶

---

✨ SYNOPSIS ✨

funjoin [switches] <ifile1> <ifile2> ... <ifilen> <ofile>

---

💅 OPTIONS 💅

• -a cols — the columns to activate (in all the files, queen)
• -a1 cols ... -an cols — columns to activate in each individual file, one at a time
• -b 'c1:bvl,c2:bv2' — blank values for the columns that are common to all files
• -bn 'c1:bv1,c2:bv2' — blank values for columns in a specific file
• -j col — the column to join on, in all files
• -j1 col ... -jn col — the column to join on in each file
• -m min — the minimum number of matches needed to output a row
• -M max — the maximum number of matches allowed to output a row
• -s — add the 'jfiles' status column
• -S col — name your status column col instead
• -t tol — tolerance for joining numeric columns [2 files only, no exceptions bestie]

---

🦋 DESCRIPTION 🦋

funjoin joins rows from two or more FITS Binary Table files — up to 32 of them, which is a lot of files and honestly kind of a flex — based on the values of specified join columns in each file. NB: the join columns must have an index file associated with them. Those index files? You generate them with the funindex program. no index, no join, periodt 💀

The first argument is the first input FITS table or raw event file. If you say "stdin", the data get read from the standard input instead. The arguments after that are more event files and tables to join. The last argument is the output FITS file. 🌈

NB: do NOT use Funtools Bracket Notation to specify FITS extensions and row filters when you run funjoin, or you will get wrong results. fr fr. Rows are accessed and joined using the index files directly, and that straight-up bypasses all filtering. 🙈

---

🎀 picking your join columns 🎀

You can specify the join columns two ways:

• with the -j col switch, which sets one column name to use for all the files
• with -j1 col1, -j2 col2, ... -jn coln, which sets a column name for each file individually

You must specify a join column for every single file. And if you specify both -j col and -jn coln for the same file, the latter wins — -jn takes it, no contest. 💅

Join columns have to be either string type or numeric type — mixing numeric and string columns in a single join is illegal. don't do it, it's not the vibe. 🚫

So like, to join three files using the same key column for each one:

funjoin -j key in1.fits in2.fits in3.fits out.fits

And if you want a different key for the third file:

funjoin -j key -j3 otherkey in1.fits in2.fits in3.fits out.fits

---

🌸 choosing which columns to keep 🌸

The -a "cols" switch (and its -a1 "col1", -a2 "cols2" counterparts) lets you specify columns to activate — meaning, write to the output file — for each input file. By default, all columns are output. generous of it, honestly.

If two or more columns from separate files have the same name, the second one (and every one after it) gets renamed: an underscore plus a numeric value get appended to the name. so AKEY, AKEY_2, AKEY_3... you've seen it, you know. ✨

---

💜 min and max joins 💜

The -m min and -M max switches set the minimum and maximum number of joins required to write out a row.

• default minimum: 0 joins — meaning all rows get written out
• default maximum: 63 — which is the max possible joins given a limit of 32 input files

For example, to write out only those rows where exactly two files have columns that match (that's one join):

funjoin -j key -m 1 -M 1 in1.fits in2.fits in3.fits ... out.fits

---

🧚 blank values, because not every file shows up 🧚

A given row can hit the requisite number of joins without all the files being joined — like, three files are being joined but only two of them have a given join key value. when that happens, all the columns of the non-joined file get written out by default using blanks (zeros or NULLs).

The -b c1:bv1,c2:bv2 and -b1 'c1:bv1,c2:bv2' -b2 'c1:bv1,c2:bv2' ... switches set the blank value for:

• columns common to all files (that's -b)
• columns in a specified file (that's -bn)

Each blank value string is a comma-separated list of column:blank_val specifiers.

And listen: for floating point values (single or double), a case-insensitive string value of "nan" means the IEEE NaN (not-a-number) should be used. slay. 🔥

So for example:

funjoin -b "AKEY:???" -b1 "A:-1" -b3 "G:NaN,E:-1,F:-100" ...

Which means:

• a non-joined AKEY column in any file will contain the string "???"
• the non-joined A column of file 1 will contain a value of -1
• the non-joined G column of file 3 will contain IEEE NaNs
• the non-joined E and F columns of that same file will contain values -1 and -100

And obviously, where common and specific blank values are specified for the same column, the specific blank value is the one that gets used. specific overrides general, always. 💅

---

👑 the status column 👑

To distinguish which files are the non-blank components of a given row, use the -s (status) switch. It adds a bitmask column named "JFILES" to the output file. In that column:

• a bit is set for each non-blank file composing the given row
• bit 0 corresponds to the first file
• bit 1 corresponds to the second file
• ...and so on down the line

The file names themselves get stored in the FITS header as parameters named JFILE1, JFILE2, etc. 📝

And the -S col switch lets you change the name of the status column away from the default "JFILES". main character energy, pick your own name. 💖

---

⭐ how a join actually works ⭐

A join between rows is the Cartesian product of all rows in one file having a given join column value with all rows in a second file having the same value for its join column, and so on.

So: if file1 has 2 rows with join column value 100, file2 has 3 rows with the same value, and file3 has 4 rows, then the join results in 2*3*4=24 rows being output. math is math bestie ✨

The join algorithm directly processes the index file associated with the join column of each file. The smallest value of all the current columns is selected as a base, and that value is used to join equal-valued columns in the other files. This way, the index files are traversed exactly once. efficient. obsessed. 🔥

---

🍓 the tolerance thing 🍓

The -t tol switch specifies a tolerance value for numeric columns. At present, a tolerance value can join only two files at a time. (A completely different algorithm is required to join more than two files using a tolerance — something they might consider implementing in the future. so, not yet, bestie. 😭)

---

🌈 THE BIG EXAMPLE 🌈

This one shows off basically every feature of funjoin. The input files t1.fits, t2.fits, and t3.fits contain these columns:

[sh] fundisp t1.fits
      AKEY    KEY      A      B
 ----------- ------ ------ ------
        aaa      0      0      1
        bbb      1      3      4
        ccc      2      6      7
        ddd      3      9     10
        eee      4     12     13
        fff      5     15     16
        ggg      6     18     19
        hhh      7     21     22

fundisp t2.fits
      AKEY    KEY      C      D
 ----------- ------ ------ ------
        iii      8     24     25
        ggg      6     18     19
        eee      4     12     13
        ccc      2      6      7
        aaa      0      0      1

fundisp t3.fits
      AKEY    KEY        E        F           G
 ----------- ------ -------- -------- -----------
        ggg      6       18       19      100.10
        jjj      9       27       28      200.20
        aaa      0        0        1      300.30
        ddd      3        9       10      400.40

Given those input files, this funjoin command:

funjoin -s -a1 "-B" -a2 "-D" -a3 "-E" -b \
"AKEY:???" -b1 "AKEY:XXX,A:255" -b3 "G:NaN,E:-1,F:-100" \
-j key t1.fits t2.fits t3.fits foo.fits

will join the files on the KEY column, outputting all columns except:

• B (in t1.fits)
• D (in t2.fits)
• E (in t3.fits)

and setting blank values for:

• AKEY (globally — but overridden for t1.fits)
• A (in file 1)
• G, E, and F (in file 3)

A JFILES column will be output to flag which files were used in each row:

      AKEY    KEY      A       AKEY_2  KEY_2      C       AKEY_3  KEY_3        F           G   JFILES
------------ ------ ------ ------------ ------ ------ ------------ ------ -------- ----------- --------
       aaa      0      0          aaa      0      0          aaa      0        1      300.30        7
       bbb      1      3          ???      0      0          ???      0     -100         nan        1
       ccc      2      6          ccc      2      6          ???      0     -100         nan        3
       ddd      3      9          ???      0      0          ddd      3       10      400.40        5
       eee      4     12          eee      4     12          ???      0     -100         nan        3
       fff      5     15          ???      0      0          ???      0     -100         nan        1
       ggg      6     18          ggg      6     18          ggg      6       19      100.10        7
       hhh      7     21          ???      0      0          ???      0     -100         nan        1
       XXX      0    255          iii      8     24          ???      0     -100         nan        2
       XXX      0    255          ???      0      0          jjj      9       28      200.20        4

look at those nans and those XXXes go. ate and left no crumbs 💅✨

---

🫶 SEE ALSO 🫶

See funtools(7) for a list of Funtools help pages 🌸

---

and that's the whole page bestie!! this one's version 1.4.5, dated April 14, 2011 — oldie but a goodie, lowkey a classic. go join some tables, you've got this 👑💖✨