Can I update an HDFStore?

Question

Consider the following hdfstore and dataframes df and df2

import pandas as pd

store = pd.HDFStore('test.h5')

midx = pd.MultiIndex.from_product([range(2), list('XYZ')], names=list('AB'))
df = pd.DataFrame(dict(C=range(6)), midx)

df

     C
A B   
0 X  0
  Y  1
  Z  2
1 X  3
  Y  4
  Z  5

midx2 = pd.MultiIndex.from_product([range(2), list('VWX')], names=list('AB'))
df2 = pd.DataFrame(dict(C=range(6)), midx2)

df2

     C
A B   
0 V  0
  W  1
  X  2
1 V  3
  W  4
  X  5

I want to first write df to the store.

store.append('df', df)

store.get('df')

     C
A B   
0 X  0
  Y  1
  Z  2
1 X  3
  Y  4
  Z  5

At a later point in time I will have another dataframe that I want to update the store with. I want to overwrite the rows with the same index values as are in my new dataframe while keeping the old ones.

When I do

store.append('df', df2)

store.get('df')

     C
A B   
0 X  0
  Y  1
  Z  2
1 X  3
  Y  4
  Z  5
0 V  0
  W  1
  X  2
1 V  3
  W  4
  X  5

This isn't at all what I want. Notice that (0, 'X') and (1, 'X') are repeated. I can manipulate the combined dataframe and overwrite, but I expect to be working with a lot data where this wouldn't be feasible.

How do I update the store to get?

You'll see that For each level of 'A', 'Y' and 'Z' are the same, 'V' and 'W' are new, and 'X' is updated.

What is the correct way to do this?

Can I update an HDFStore?

1 Answers