CODEWITHSUNDEEP
pythonarraysnumpyarray-mergenumpy-ndarray
RazorHail
RazorHail

Reputation: 431

Merge 2 numpy Arrays with timestamps

I have two numpy ndarrays - each with their own timestamp-dimension. I want to merge them together. However the interval of their timestamps is not necessarily the same. Here's an example of what I mean:

Array 1: names =  ['timestamp', 'value']
a1 = [(1531000000, 0), (1532000000, 1), (1533000000, 2), (1534000000, 3)]

Array 2: names =  ['timestamp', 'color']
a2 = [(1531500000, "blue"), (1532000000, "black"), (1533500000, "green"), (1534000000, "red")]

Resulting Array: names =  ['timestamp', 'value', 'color']
a3 = [(1531000000, 0, nan), (1531500000, nan, "blue"), (1532000000, 1, "black"), (1533000000, 2, nan), (1533500000, nan, "green"), (1534000000, 3, "red")]

Upvotes: 2

Views: 1214

Answers (2)

user3483203
user3483203

Reputation: 51155

Setup

It looks like you are showing structured arrays here, so I assume that you are using them. If you are not using structured arrays, you should be, in which case you can create them like so:

a1 = np.array(a1, dtype=[('timestamp', int), ('value', int)])
a2 = np.array(a2, dtype=[('timestamp', int), ('color', '<U5')])

Now, you can make use of numpy.lib.recfunctions here:

import numpy.lib.recfunctions as recfunctions

out = recfunctions.join_by('timestamp', a1, a2, jointype='outer')


masked_array(data=[(1531000000, 0, --), (1531500000, --, 'blue'),
                   (1532000000, 1, 'black'), (1533000000, 2, --),
                   (1533500000, --, 'green'), (1534000000, 3, 'red')],
             mask=[(False, False,  True), (False,  True, False),
                   (False, False, False), (False, False,  True),
                   (False,  True, False), (False, False, False)],
       fill_value=(999999, 999999, 'N/A'),
            dtype=[('timestamp', '<i4'), ('value', '<i4'), ('color', '<U5')])

The output looks a bit convoluted, but that's simply how the representation of a np.ma.masked_array looks. It's easy to see this is the correct output:

out.tolist()


[(1531000000, 0, None),
 (1531500000, None, 'blue'),
 (1532000000, 1, 'black'),
 (1533000000, 2, None),
 (1533500000, None, 'green'),
 (1534000000, 3, 'red')]

However, with a masked array, you have access to a whole host of utility functions to properly fill in the missing values.

Upvotes: 1

jpp
jpp

Reputation: 164673

With Pandas, you can perform an outer merge and then sort. This is natural since NumPy arrays are used within the Pandas framework.

import pandas as pd

res = pd.merge(df1, df2, how='outer').sort_values('timestamp').values.tolist()

Result

[[1531000000, 0.0, nan],
 [1531500000, nan, 'blue'],
 [1532000000, 1.0, 'black'],
 [1533000000, 2.0, nan],
 [1533500000, nan, 'green'],
 [1534000000, 3.0, 'red']]

Setup

names =  ['timestamp', 'value']
a1 = [(1531000000, 0), (1532000000, 1), (1533000000, 2), (1534000000, 3)]
df1 = pd.DataFrame(a1, columns=names)

names =  ['timestamp', 'color']
a2 = [(1531500000, "blue"), (1532000000, "black"), (1533500000, "green"), (1534000000, "red")]
df2 = pd.DataFrame(a2, columns=names)

Upvotes: 2

Related Questions