Doug Baldwin Doug Baldwin - 5 months ago 23
Python Question

Pandas Panel Merge

What is the current, best Pandas recipe for merging panel data, such as the following:

p = pd.Panel(np.random.randn(2,5,4),
items=['IBM', 'AA'],
major_axis=pd.date_range('1/1/2000', periods=5),
minor_axis=['Open', 'High', 'Low', 'Close'])
dp = pd.Panel(np.random.randn(2,1,1),
items=['IBM', 'Z'],
major_axis=pd.date_range('1/8/2000', periods=1),
minor_axis=['Close'])


The intended merge is like this:

p[:,:,'Close'].merge(dp[:,:,'Close'],
how='outer',
on=list(set(p.items) & set(dp.items)),
left_index=True,
right_index=True)


But, I do not understand how to efficiently update the original panel p, to include this merge.

if print (p[:,:,'Close']) is this:

IBM AA
2000-01-01 0.190049 0.200745
2000-01-02 -0.239746 -0.434157
2000-01-03 -0.112571 -0.302251
2000-01-04 -1.764957 -0.810951
2000-01-05 -0.961327 1.436247


Then the above table merge will look something like this:

IBM AA Z
2000-01-01 0.190049 0.200745 NaN
2000-01-02 -0.239746 -0.434157 NaN
2000-01-03 -0.112571 -0.302251 NaN
2000-01-04 -1.764957 -0.810951 NaN
2000-01-05 -0.961327 1.436247 NaN
2000-01-08 0.006128 NaN 0.383452


Thank you.

Answer

I'd convert to dataframe, combine_first, and back again

new = p.to_frame().combine_first(dp.to_frame()).to_panel()

print new[:,:,'Close']

                  AA       IBM        Z
major                                  
2000-01-01  1.348884  0.472272      NaN
2000-01-02  1.599357 -0.228739      NaN
2000-01-03  2.041504 -0.325773      NaN
2000-01-04  0.348960 -0.451274      NaN
2000-01-05 -1.902347  0.146647      NaN
2000-01-08       NaN -0.240884  0.39855
Comments