0

I have two Pandas DataFrames indexed by a timeline. We'll call the first df_A, in which the 'epoch' corresponds to the index.

df_A:

     timeline  epoch       price  z-value
0  1476336104      0     434.313        1
1  1476336120      1     434.312    false
2  1476336134      2     434.312    false
3  1476336149      3     435.900    false
4  1476336165      4     435.900        1
5  1476336178      5     435.500        1

The second, df_B, has entries that may have one, none, or multiple entries per index of df_A, as you can see by the 'epoch' column.

df_B:

     timeline  epoch   send-value   tx-in
0  1476336123      1        10000   False
1  1476336169      4    299950000   False
2  1476336187      5  22879033493   False
3  1476336194      5    130000000   False
4  1476336212      7  10000000000   False

How can I merge these on the index of df_A, and add extra values contained in df_B as columns? I'd like to also add a suffix to differentiate the additional columns. The two example datasets should create a new DataFrame, df_AB that looks like this:

     timeline  epoch       price  z-value   send-value   tx-in   send-value_1 tx-in_1
0  1476336104      0     434.313        1          NaN     NaN          NaN     NaN
1  1476336120      1     434.312    false        10000   False          NaN     NaN
2  1476336134      2     434.312    false          NaN     NaN          NaN     NaN
3  1476336149      3     435.900    false          NaN     NaN          NaN     NaN
4  1476336165      4     435.900        1    299950000   False          NaN     NaN
5  1476336178      5     435.500        1  22879033493   False     130000000   False

It looks like there are a few different methods where I might be able to reindex and then merge on 'timeline', or use something like merge_asof, but I can't seem to get any of them to produce the result I am looking for.

How can I do this?

alphaleonis
  • 1,329
  • 11
  • 18
  • Can you explain your logic (algorithm) for producing / merging `send-value_1` and `tx-in_1` columns? – MaxU - stand with Ukraine Oct 22 '16 at 20:32
  • Basically, if there are multiple values of df_B in a single index of of df_A, each row of df_B should create a new column in df_AB. So, here, df_B[2,3] corresponds to df_A[5]. df_B[3] concatenates into new columns `'send-value_1'` and `'tx-in_1'` at df_AB[5]. – alphaleonis Oct 22 '16 at 20:39
  • 2
    I think you need to first reshape df_B from long to wide, see here for example: http://stackoverflow.com/questions/22798934/pandas-long-to-wide-reshape Then should be a standard merge on epoch – JohnE Oct 22 '16 at 21:33

0 Answers0