1

My first dataframe (df1) looks like this:

 pvalue trend time 0 0.000065 0.000076 2019-03-18 04:00:04 1 0.000087 0.000098 2019-03-18 04:00:06 2 0.000000 0.000000 2019-03-18 04:00:22 3 0.000000 0.000087 2019-03-18 04:02:29 4 0.000000 0.000000 2019-03-18 04:03:04 5 0.000000 0.000023 2019-03-18 04:03:05 6 0.000000 0.000000 2019-03-18 04:03:18 7 0.000000 0.000067 2019-03-18 04:18:55 8 0.000000 0.000000 2019-03-18 04:18:56 9 0.000000 0.000000 2019-03-18 04:20:41

My second data (df2) looks like that:

 time price 0 2019-03-18 04:00:00 0.00190633 1 2019-03-18 04:00:01 0.00190633 2 2019-03-18 04:00:02 0.00190633 3 2019-03-18 04:00:03 0.00190633 4 2019-03-18 04:00:04 0.00190633 5 2019-03-18 04:00:05 0.00190633 6 2019-03-18 04:00:06 0.00190800 7 2019-03-18 04:00:07 0.00190800 8 2019-03-18 04:00:08 0.00190800 9 2019-03-18 04:00:09 0.00190800

df2['time'] at each row, there is a change of one second. But on df1, there are some gaps between each df1['time'] of a few seconds... what I would like to have is the following:

 time price pvalue trend 0 2019-03-18 04:00:00 0.00190633 0.000000 0.000000 1 2019-03-18 04:00:01 0.00190633 0.000000 0.000000 2 2019-03-18 04:00:02 0.00190633 0.000000 0.000000 3 2019-03-18 04:00:03 0.00190633 0.000000 0.000000 4 2019-03-18 04:00:04 0.00190633 0.000065 0.000076 5 2019-03-18 04:00:05 0.00190633 0.000000 0.000000 6 2019-03-18 04:00:06 0.00190800 0.000087 0.000098

So basically have all the seconds and when there is data at pvalue and trend in df1 put them in the new dataframe. What I tried is the following: df_all =df_pvalue_trade.merge(df_check,on='time',left_index=True) but I only have the rows of df1, not every seconds like in my example... any idea? thanks!

The result I have with the above tried code is the following: 

pvalue trend time mkt_result price 6 0.000000 0.000000 2019-03-18 04:00:06 reject Ha := upward OR downward trend 0.00190800 21 0.000000 0.000000 2019-03-18 04:00:21 reject Ha := upward OR downward trend 0.00190800 22 0.000000 0.000000 2019-03-18 04:00:22 reject Ha := upward OR downward trend 0.00190800 149 0.000000 0.000000 2019-03-18 04:02:29 reject Ha := upward OR downward trend 0.00190594 184 0.000000 0.000000 2019-03-18 04:03:04 reject Ha := upward OR downward trend 0.00190594 185 0.000000 0.000000 2019-03-18 04:03:05 reject Ha := upward OR downward trend 0.00190594 198 0.000000 0.000000 2019-03-18 04:03:18 reject Ha := upward OR downward trend 0.00190594

Which is not what I want...

Improve this question
6
  • df_pvalue_trade.merge(df_check,on='time',left_index=True,how='left') Commented Mar 28, 2019 at 14:29
  • @Wen-Ben, nope it gives me the same result as my line of code... could you erase that market duplicate please? thanks Commented Mar 28, 2019 at 14:32
  • Change the how= left to 'right' ? Commented Mar 28, 2019 at 14:34
  • @Viktor.w - For me working like your expected output - df = pd.merge(df2, df1, on='time', how='left').fillna(0) Commented Mar 28, 2019 at 14:36
  • 1
    @jezrael, the first one is the right one, and your piece of code works thanks! Commented Mar 28, 2019 at 14:44

1 Answer 1

Reset to default
1

Use merge with DataFrame.fillna:

df = pd.merge(df2, df1, on='time', how='left').fillna(0) print (df) time price pvalue trend 0 2019-03-18 04:00:00 0.001906 0.000000 0.000000 1 2019-03-18 04:00:01 0.001906 0.000000 0.000000 2 2019-03-18 04:00:02 0.001906 0.000000 0.000000 3 2019-03-18 04:00:03 0.001906 0.000000 0.000000 4 2019-03-18 04:00:04 0.001906 0.000065 0.000076 5 2019-03-18 04:00:05 0.001906 0.000000 0.000000 6 2019-03-18 04:00:06 0.001908 0.000087 0.000098 7 2019-03-18 04:00:07 0.001908 0.000000 0.000000 8 2019-03-18 04:00:08 0.001908 0.000000 0.000000 9 2019-03-18 04:00:09 0.001908 0.000000 0.000000

Also if need replace NaNs only columns from df1.columns which are different like df2.columns:

d = dict.fromkeys(df1.columns.difference(df2.columns), 0) print (d) {'pvalue': 0, 'trend': 0} df = pd.merge(df2, df1, on='time', how='left').fillna(d) print (df) time price pvalue trend 0 2019-03-18 04:00:00 0.001906 0.000000 0.000000 1 2019-03-18 04:00:01 0.001906 0.000000 0.000000 2 2019-03-18 04:00:02 0.001906 0.000000 0.000000 3 2019-03-18 04:00:03 0.001906 0.000000 0.000000 4 2019-03-18 04:00:04 0.001906 0.000065 0.000076 5 2019-03-18 04:00:05 0.001906 0.000000 0.000000 6 2019-03-18 04:00:06 0.001908 0.000087 0.000098 7 2019-03-18 04:00:07 0.001908 0.000000 0.000000 8 2019-03-18 04:00:08 0.001908 0.000000 0.000000 9 2019-03-18 04:00:09 0.001908 0.000000 0.000000
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.