Joining numpy arrays on matching index values in a specific column

I have multiple numpy arrays with two columns each: one containing a measurement, the other one the year of that measurement. The length and start/end points of those arrays are all different. My goal is to create one large array that contains one column with the years for the entire period for which I have data, and a number of columns with the measurements from each of the input arrays.

Basically I would like to write a script that turns this:

import numpy as np

arr1 = np.array([[1920, 1921, 1922, 1924, 1925], [23, 54, 23, 54, 65]]).T
arr2 = np.array([[1922, 1923, 1924, 1925, 1926], [12, 43, 17, 42, 87]]).T

into this:

year  arr1  arr2
1920  23    nan
1921  54    nan
1922  23    12
1923  nan   43
1924  54    17
1925  65    42
1926  nan   87

I have tried to write an if-else loop that checks, whether the years of two rows match and then add the measurement to the correct row in the output array, but I can't seem to get it to work. I am sure that this is a fairly common task, so I am very sorry if this question has been asked before, but I wasn't able to find a solution.

Any help is greatly appreciated!

It looks like you shouldn't be using [tag:numpy] for this but rather [tag:pandas]:

import pandas as pd

df = (pd.DataFrame(arr1, columns=['year', 'arr1'])
        .merge(pd.DataFrame(arr2, columns=['year', 'arr2']),
               on='year', how='outer')
        .sort_values(by='year')
      )

Output:

   year  arr1  arr2
0  1920  23.0   NaN
1  1921  54.0   NaN
2  1922  23.0  12.0
5  1923   NaN  43.0
3  1924  54.0  17.0
4  1925  65.0  42.0
6  1926   NaN  87.0

I worked out this answer mostly for my own learning.

Here's an example using numpy.librecfunctions. I don't have much experience with this feature. And I don't think it is heavily used, especially with the more powerful panadas now. But for what it's worth.

In [14]: import numpy.lib.recfunctions as rf

Make structured arrays from your arrays, with the same 'idx' field name, but different data fields:

In [17]: rarr1=rf.unstructured_to_structured(arr1,names=['idx','col1'])
In [18]: rarr1
Out[18]: 
array([(1920, 23), (1921, 54), (1922, 23), (1924, 54), (1925, 65)],
      dtype=[('idx', '<i4'), ('col1', '<i4')])

In [19]: rarr2=rf.unstructured_to_structured(arr2, names=['idx','col1'])

Using join_by:

In [22]: rjoint = rf.join_by('idx', rarr1, rarr2, 'outer')

In [23]: rjoint
Out[23]: 
masked_array(data=[(1920, 23, --), (1921, 54, --), (1922, 23, 12),
                   (1923, --, 43), (1924, 54, 17), (1925, 65, 42),
                   (1926, --, 87)],
             mask=[(False, False,  True), (False, False,  True),
                   (False, False, False), (False,  True, False),
                   (False, False, False), (False, False, False),
                   (False,  True, False)],
       fill_value=(999999, 999999, 999999),
            dtype=[('idx', '<i4'), ('col1', '<i4'), ('col2', '<i4')])

or without the mask (99999 is the 'int' equivalent of a 'nan' fill)

In [27]: rjoint = rf.join_by('idx', rarr1, rarr2, 'outer',usemask=False)

In [28]: rjoint
Out[28]: 
array([(1920,     23, 999999), (1921,     54, 999999),
       (1922,     23,     12), (1923, 999999,     43),
       (1924,     54,     17), (1925,     65,     42),
       (1926, 999999,     87)],
      dtype=[('idx', '<i4'), ('col1', '<i4'), ('col2', '<i4')])

Or using defaults (nan does not play nicely with int values)

In [32]: rjoint = rf.join_by('idx', rarr1, rarr2, 'outer',usemask=False, defaults={'col1':-1, 'col2':-1})

In [33]: rjoint
Out[33]: 
array([(1920, 23, -1), (1921, 54, -1), (1922, 23, 12), (1923, -1, 43),
       (1924, 54, 17), (1925, 65, 42), (1926, -1, 87)],
      dtype=[('idx', '<i4'), ('col1', '<i4'), ('col2', '<i4')])

In [34]: rf.structured_to_unstructured(rjoint)
Out[34]: 
array([[1920,   23,   -1],
       [1921,   54,   -1],
       [1922,   23,   12],
       [1923,   -1,   43],
       [1924,   54,   17],
       [1925,   65,   42],
       [1926,   -1,   87]])