Merging with empty DataFrame

Question

I'm trying to merge a dataframe (df1) with another dataframe (df2) for which df2 can potentially be empty. The merge condition is df1.index=df2.z (df1 is never empty), but I'm getting the following error.

Is there any way to get this working?

In [31]:
import pandas as pd
In [32]:
df1 = pd.DataFrame({'a': [1, 2, 3], 'b': [4, 5, 6], 'c': [1, 2, 3]})
df2 = pd.DataFrame({'x':[], 'y':[], 'z':[]})
dfm = pd.merge(df1, df2, how='outer', left_index=True, right_on='z')
---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
<ipython-input-34-4e9943198dae> in <module>()
----> 1 dfmb = pd.merge(df1, df2, how='outer', left_index=True, right_on='z')

/usr/local/lib/python2.7/dist-packages/pandas/tools/merge.pyc in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy)
     37                          right_index=right_index, sort=sort, suffixes=suffixes,
     38                          copy=copy)
---> 39     return op.get_result()
     40 if __debug__:
     41     merge.__doc__ = _merge_doc % '\nleft : DataFrame'

/usr/local/lib/python2.7/dist-packages/pandas/tools/merge.pyc in get_result(self)
    185 
    186     def get_result(self):
--> 187         join_index, left_indexer, right_indexer = self._get_join_info()
    188 
    189         ldata, rdata = self.left._data, self.right._data

/usr/local/lib/python2.7/dist-packages/pandas/tools/merge.pyc in _get_join_info(self)
    277                 join_index = self.left.index.take(left_indexer)
    278             elif self.left_index:
--> 279                 join_index = self.right.index.take(right_indexer)
    280             else:
    281                 join_index = Index(np.arange(len(left_indexer)))

/usr/local/lib/python2.7/dist-packages/pandas/core/index.pyc in take(self, indexer, axis)
    981 
    982         indexer = com._ensure_platform_int(indexer)
--> 983         taken = np.array(self).take(indexer)
    984 
    985         # by definition cannot propogate freq

IndexError: cannot do a non-empty take from an empty axes.

why not just check if its empty first? that takes like no time at all — Joran Beasley
– Joran Beasley, Commented Mar 3, 2015 at 0:48
That's not the problem. In the subsequent code, I expect a merged dataframe with columns from df1 and df2 (even though some of them may be None/nan). — orange
– orange, Commented Mar 3, 2015 at 1:51

Andy Hayden · Accepted Answer · 2015-03-03 01:57:30Z

6

Another alternative, similar to Joran's:

try:
    dfm = pd.merge(df1, df2, how='outer', left_index=True, right_on='z')
except IndexError:
    dfm = df1.reindex_axis(df1.columns.union(df2.columns), axis=1)

I'm not sure which is clearer but both the following work:

In [11]: df1.reindex_axis(df1.columns.union(df2.columns), axis=1)
Out[11]:
   a  b  c   x   y   z
0  1  4  1 NaN NaN NaN
1  2  5  2 NaN NaN NaN
2  3  6  3 NaN NaN NaN

In [12]: df1.loc[:, df1.columns.union(df2.columns)]
Out[12]:
   a  b  c   x   y   z
0  1  4  1 NaN NaN NaN
1  2  5  2 NaN NaN NaN
2  3  6  3 NaN NaN NaN

(I prefer the former.)

answered Mar 3, 2015 at 1:57

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

orange Over a year ago

This works fine, but how would I go about preserving the type? union just copies the column names, but not the type. In my case, some of the values are datetimes, so I'd expect NaT instead of NaN as column value.

Andy Hayden Over a year ago

@orange I would just hit these columns with pd.to_datetime afterwards.

David Norrish Over a year ago

NB that reindex_axis is deprecated since Pandas 0.21.0. Use reindex for current versions of Pandas.

Joran Beasley · Accepted Answer · 2015-03-03 00:49:40Z

6

try:
    dfm = pd.merge(df1, df2, how='outer', left_index=True, right_on='z')
except IndexError:
    dfm = df1 if not df1.empty else df2

might be sufficient for your needs

answered Mar 3, 2015 at 0:49

Joran Beasley

114k13 gold badges167 silver badges187 bronze badges

1 Comment

orange Over a year ago

This isn't the same as merging. After a merge, I would expect all columns to be part of dfm.

Collectives™ on Stack Overflow

Merging with empty DataFrame

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related