2

I have two unevenly spaced timeseries:

      time    X1
1234567000 96.32
1234567005 96.01
1234567009 96.05

      time    X2
1234567001 23.88
1234567005 23.96

I would like to merge them:

      time    X1    X2
1234567000 96.32   nan
1234567001   nan 23.88
1234567005 96.01 23.96
1234567009 96.05   nan

How can I do so in Python? I'm open to using packages, e.g. pandas.

time is integer (It isn't a UNIX timestamp). X1 and X2 are float.

Franck Dernoncourt
  • 77,520
  • 72
  • 342
  • 501

2 Answers2

2

Here's a super naive way to merge. First format your two series so they are dictionaries, define a row object, and then go through each element of each series adding it's entry to the row object.

from collections import defaultdict

class series_row:
    def __init__(self):
        self.x1 = 'nan'
        self.x2 = 'nan'

    def __repr__(self):
        return f"{self.x1}, {self.x2}"


series1 = {
    1234567000: 96.32,
    1234567005: 96.01,
    1234567009: 96.05,
}

series2 = {
    1234567001: 23.88,
    1234567005: 23.96
}


merged_series = defaultdict(series_row)

for k, v in series1.items():
    merged_series[k].x1 = v

for k, v in series2.items():
    merged_series[k].x2 = v

for entry in merged_series.items():
    print(entry)
Nathaniel Rivera Saul
  • 647
  • 1
  • 5
  • 13
2

If you want to use pandas, you first format your inputs as pandas dataframes like follows :

import pandas as pd

s1 = pd.DataFrame({
    'time':[1234567000,1234567005,1234567009],
    'X1':[96.32,96.01,96.05]
},columns=['time','X1'])  # to keep columns order

s2 = pd.DataFrame({
    'time':[1234567001,1234567005],
    'X2':[23.88,23.96]
},columns=['time','X2'])  # to keep columns order

and then simply merge them with this one line:

df = pd.merge(s1,s2,how='outer')

if you want the time values to be sorted use this instead:

df = pd.merge(s1,s2,how='outer').sort_values(by='time')
Rayhane Mama
  • 2,374
  • 11
  • 20