python - Setting DataFrame values with enlargement

Question

I have two DataFrames (with DatetimeIndex) and want to update the first frame (the older one) with data from the second frame (the newer one).

The new frame may contain more recent data for rows already contained in the the old frame. In this case, data in the old frame should be overwritten with data from the new frame. Also the newer frame may have more columns / rows, than the first one. In this case the old frame should be enlarged by the data in the new frame.

Pandas docs state, that

"The .loc/.ix/[] operations can perform enlargement when setting a non-existant key for that axis"

and

"a DataFrame can be enlarged on either axis via .loc"

However this doesn't seem to work and throws a KeyError. Example:

In [195]: df1
Out[195]: 
                     A  B  C
2015-07-09 12:00:00  1  1  1
2015-07-09 13:00:00  1  1  1
2015-07-09 14:00:00  1  1  1
2015-07-09 15:00:00  1  1  1

In [196]: df2
Out[196]: 
                     A  B  C  D
2015-07-09 14:00:00  2  2  2  2
2015-07-09 15:00:00  2  2  2  2
2015-07-09 16:00:00  2  2  2  2
2015-07-09 17:00:00  2  2  2  2

In [197]: df1.loc[df2.index] = df2
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-197-74e630e87cf8> in <module>()
----> 1 df1.loc[df2.index] = df2

/.../pandas/core/indexing.pyc in __setitem__(self, key, value)
    112 
    113     def __setitem__(self, key, value):
--> 114         indexer = self._get_setitem_indexer(key)
    115         self._setitem_with_indexer(indexer, value)
    116 

/.../pandas/core/indexing.pyc in _get_setitem_indexer(self, key)
    107 
    108         try:
--> 109             return self._convert_to_indexer(key, is_setter=True)
    110         except TypeError:
    111             raise IndexingError(key)

/.../pandas/core/indexing.pyc in _convert_to_indexer(self, obj, axis, is_setter)
   1110                 mask = check == -1
   1111                 if mask.any():
-> 1112                     raise KeyError('%s not in index' % objarr[mask])
   1113 
   1114                 return _values_from_object(indexer)

KeyError: "['2015-07-09T18:00:00.000000000+0200' '2015-07-09T19:00:00.000000000+0200'] not in index"

What is the best way (with respect to performance, as my real data is much larger) two achieve the desired updated and enlarged DataFrame. This is the result I would like to see:

                     A  B  C    D
2015-07-09 12:00:00  1  1  1  NaN
2015-07-09 13:00:00  1  1  1  NaN
2015-07-09 14:00:00  2  2  2    2
2015-07-09 15:00:00  2  2  2    2
2015-07-09 16:00:00  2  2  2    2
2015-07-09 17:00:00  2  2  2    2

Answer 1

df2.combine_first(df1) (documentation) seems to serve your requirement; PFB code snippet & output

import pandas as pd

print 'pandas-version: ', pd.__version__

df1 = pd.DataFrame.from_records([('2015-07-09 12:00:00',1,1,1),
                                 ('2015-07-09 13:00:00',1,1,1),
                                 ('2015-07-09 14:00:00',1,1,1),
                                 ('2015-07-09 15:00:00',1,1,1)],
                                columns=['Dt', 'A', 'B', 'C']).set_index('Dt')
# print df1

df2 = pd.DataFrame.from_records([('2015-07-09 14:00:00',2,2,2,2),
                                 ('2015-07-09 15:00:00',2,2,2,2),
                                 ('2015-07-09 16:00:00',2,2,2,2),
                                 ('2015-07-09 17:00:00',2,2,2,2),],
                               columns=['Dt', 'A', 'B', 'C', 'D']).set_index('Dt')
res_combine1st = df2.combine_first(df1)
print res_combine1st

output

pandas-version:  0.15.2
                     A  B  C   D
Dt                              
2015-07-09 12:00:00  1  1  1 NaN
2015-07-09 13:00:00  1  1  1 NaN
2015-07-09 14:00:00  2  2  2   2
2015-07-09 15:00:00  2  2  2   2
2015-07-09 16:00:00  2  2  2   2
2015-07-09 17:00:00  2  2  2   2