dataframe merging + comparison with existence test

Question

I am looking to perform the following task:

Given 2 pandas DataFrames, each with one column but of different length, create a new DataFrame whose index is the union of the 2 other DataFrames and possesses two columns: one indicating whether DataFrame 1 contained a value for that particular index, and one indicating whether DataFrame 2 contained a value for that particular index.

I have the following example data:

rng = pd.date_range('1/1/2017', periods=365, freq='D')
rng2 = pd.date_range('1/1/2016',periods=730, freq='D')
x1 = np.random.randn(365)
x2 = np.random.randn(730)
df1 = pd.DataFrame({'x':x1}, index=rng)
df2 = pd.DataFrame({'x':x2}, index=rng2)

I can obtain the union of the indices by doing:

idx = df1.index.union(df2.index)

Now, I would like to create a new DataFrame, df3, which has index of idx and 2 columns populated with zeroes and ones as per the above requirements.

I have explored using the .isin() functionality but from what I can tell that might require knowing a little bit too much about the DataFrames beforehand, while I would like to achieve this more flexibly.

Answer 1

An outer join and a test for notnull() achieves the desired behavior. With your example data it would look something like:

notnull = df1.join(df2.rename(columns={'x': 'x2'}), how='outer').notnull()

Sample Data:

rng1 = pd.date_range('1/2/2017', periods=4, freq='D')
rng2 = pd.date_range('1/1/2017', periods=4, freq='D')
x = np.random.randn(4)
df1 = pd.DataFrame({'x': x}, index=rng1)
df2 = pd.DataFrame({'x': x}, index=rng2)

Test it:

notnull = df1.join(df2.rename(columns={'x': 'x2'}), how='outer').notnull()
print(notnull)

Output:

                x     x2
2017-01-01  False   True
2017-01-02   True   True
2017-01-03   True   True
2017-01-04   True   True
2017-01-05   True  False

Update from the Comments:

If you want actual ones and zeros instead of bool,

ones_and_zeros= df1.join(df2.rename(columns={'x': 'x2'}), 
                                    how='outer').notnull().astype(np.uint8)
print(ones_and_zeros)

Output:

            x  x2
2017-01-01  0   1
2017-01-02  1   1
2017-01-03  1   1
2017-01-04  1   1
2017-01-05  1   0