I am new to packages dplyr and tibbletime and have the following objective. I need to sum the quantities of trades (qty in a dataset) that occur in the same interval of 24 hours. For instance, I have 3 trades within 1 hour with 300, 200 and 100 quantities respectively and I want them to be summed up to 600 and in the same fashion for all trades within one hour for the whole trading day. I tried the following:
library(dplyr)
library(tibbletime)
df <- data_BTC
df %>%
collapse_by("hourly") %>%
group_by(timestamp) %>%
summarise(total_buy=sum(qty))
Error: Object is not of class `tbl_time`.
Help me to solve this issue or advise another way to complete my objective. Thank you! The head (first tick values) of the data in html code format is attached below:
<table class="table table-bordered table-hover table-condensed">
<thead><tr><th title="Field #1">FIELD1</th>
<th title="Field #2">timestamp</th>
<th title="Field #3">symbol</th>
<th title="Field #4">side</th>
<th title="Field #5">qty</th>
<th title="Field #6">price</th>
</tr></thead>
<tbody><tr>
<td align="right">1</td>
<td>2018-11-04 03:00:02</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">2</td>
<td>2018-11-04 03:00:02</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">64</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">3</td>
<td>2018-11-04 03:00:02</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">4</td>
<td>2018-11-04 03:00:04</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">10000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">5</td>
<td>2018-11-04 03:00:04</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">10000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">6</td>
<td>2018-11-04 03:00:07</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">4626</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">7</td>
<td>2018-11-04 03:00:09</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">7000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">8</td>
<td>2018-11-04 03:00:09</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2240</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">9</td>
<td>2018-11-04 03:00:11</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">236</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">10</td>
<td>2018-11-04 03:00:12</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">70</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">11</td>
<td>2018-11-04 03:00:12</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">164</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">12</td>
<td>2018-11-04 03:00:12</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">393</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">13</td>
<td>2018-11-04 03:00:13</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">129</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">14</td>
<td>2018-11-04 03:00:13</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">19</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">15</td>
<td>2018-11-04 03:00:13</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">123</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">16</td>
<td>2018-11-04 03:00:13</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">258</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">17</td>
<td>2018-11-04 03:00:13</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">53</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">18</td>
<td>2018-11-04 03:00:15</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2294</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">19</td>
<td>2018-11-04 03:00:17</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">300</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">20</td>
<td>2018-11-04 03:00:18</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1281</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">21</td>
<td>2018-11-04 03:00:18</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">37493</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">22</td>
<td>2018-11-04 03:00:18</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">30667</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">23</td>
<td>2018-11-04 03:00:18</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2866</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">24</td>
<td>2018-11-04 03:00:19</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1766</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">25</td>
<td>2018-11-04 03:00:30</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">385</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">26</td>
<td>2018-11-04 03:00:30</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">36</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">27</td>
<td>2018-11-04 03:00:30</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">83</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">28</td>
<td>2018-11-04 03:00:32</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">250</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">29</td>
<td>2018-11-04 03:00:50</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1667</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">30</td>
<td>2018-11-04 03:00:50</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">165</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">31</td>
<td>2018-11-04 03:00:50</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">797</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">32</td>
<td>2018-11-04 03:01:00</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">171</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">33</td>
<td>2018-11-04 03:01:00</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2329</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">34</td>
<td>2018-11-04 03:01:02</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">204</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">35</td>
<td>2018-11-04 03:01:03</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1190</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">36</td>
<td>2018-11-04 03:01:10</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">3946</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">37</td>
<td>2018-11-04 03:01:13</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">500</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">38</td>
<td>2018-11-04 03:01:13</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">500</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">39</td>
<td>2018-11-04 03:01:14</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">4000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">40</td>
<td>2018-11-04 03:01:15</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">500</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">41</td>
<td>2018-11-04 03:01:15</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">500</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">42</td>
<td>2018-11-04 03:01:16</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">558</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">43</td>
<td>2018-11-04 03:01:18</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">3946</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">44</td>
<td>2018-11-04 03:01:19</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">500</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">45</td>
<td>2018-11-04 03:01:41</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">15000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">46</td>
<td>2018-11-04 03:01:41</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">360</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">47</td>
<td>2018-11-04 03:01:54</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">554</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">48</td>
<td>2018-11-04 03:01:56</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">49</td>
<td>2018-11-04 03:01:57</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">50</td>
<td>2018-11-04 03:01:57</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">51</td>
<td>2018-11-04 03:01:58</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">52</td>
<td>2018-11-04 03:01:59</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">53</td>
<td>2018-11-04 03:01:59</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">54</td>
<td>2018-11-04 03:02:00</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">55</td>
<td>2018-11-04 03:02:05</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">60</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">56</td>
<td>2018-11-04 03:02:05</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">10000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">57</td>
<td>2018-11-04 03:02:05</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">58</td>
<td>2018-11-04 03:02:09</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">7000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">59</td>
<td>2018-11-04 03:02:11</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">198</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">60</td>
<td>2018-11-04 03:02:19</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">198</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">61</td>
<td>2018-11-04 03:02:32</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">40</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">62</td>
<td>2018-11-04 03:02:35</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">51</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">63</td>
<td>2018-11-04 03:02:36</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">20</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">64</td>
<td>2018-11-04 03:02:40</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">70</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">65</td>
<td>2018-11-04 03:02:42</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">17</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">66</td>
<td>2018-11-04 03:02:43</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">156</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">67</td>
<td>2018-11-04 03:02:47</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">100</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">68</td>
<td>2018-11-04 03:02:49</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">69</td>
<td>2018-11-04 03:02:49</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">325</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">70</td>
<td>2018-11-04 03:02:57</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">71</td>
<td>2018-11-04 03:03:17</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">20</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">72</td>
<td>2018-11-04 03:03:24</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">73</td>
<td>2018-11-04 03:03:25</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">50000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">74</td>
<td>2018-11-04 03:03:28</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">108</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">75</td>
<td>2018-11-04 03:03:28</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">97</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">76</td>
<td>2018-11-04 03:04:11</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">400</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">77</td>
<td>2018-11-04 03:04:14</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">4000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">78</td>
<td>2018-11-04 03:04:34</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">5000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">79</td>
<td>2018-11-04 03:06:16</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">80</td>
<td>2018-11-04 03:07:25</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">6330</td>
<td align="right">6333</td>
</tr>
<tr>
<td align="right">81</td>
<td>2018-11-04 03:08:15</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">5</td>
<td align="right">6332.5</td>
</tr>
<tr>
<td align="right">82</td>
<td>2018-11-04 03:08:27</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">124</td>
<td align="right">6330.5</td>
</tr>
<tr>
<td align="right">83</td>
<td>2018-11-04 03:08:27</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1</td>
<td align="right">6330.5</td>
</tr>
<tr>
<td align="right">84</td>
<td>2018-11-04 03:08:31</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1</td>
<td align="right">6330</td>
</tr>
<tr>
<td align="right">85</td>
<td>2018-11-04 03:08:37</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6330</td>
</tr>
<tr>
<td align="right">86</td>
<td>2018-11-04 03:08:43</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">50</td>
<td align="right">6329.5</td>
</tr>
<tr>
<td align="right">87</td>
<td>2018-11-04 03:08:55</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">50</td>
<td align="right">6329.5</td>
</tr>
<tr>
<td align="right">88</td>
<td>2018-11-04 03:08:58</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">20</td>
<td align="right">6329.5</td>
</tr>
<tr>
<td align="right">89</td>
<td>2018-11-04 03:08:58</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">6723</td>
<td align="right">6329.5</td>
</tr>
<tr>
<td align="right">90</td>
<td>2018-11-04 03:08:58</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">6496</td>
<td align="right">6329.5</td>
</tr>
<tr>
<td align="right">91</td>
<td>2018-11-04 03:08:58</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">30439</td>
<td align="right">6329.5</td>
</tr>
<tr>
<td align="right">92</td>
<td>2018-11-04 03:08:58</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">14314</td>
<td align="right">6329.5</td>
</tr>
<tr>
<td align="right">93</td>
<td>2018-11-04 03:08:58</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">11652</td>
<td align="right">6329.5</td>
</tr>
<tr>
<td align="right">94</td>
<td>2018-11-04 03:09:04</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">10</td>
<td align="right">6329.5</td>
</tr>
<tr>
<td align="right">95</td>
<td>2018-11-04 03:09:07</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">100</td>
<td align="right">6328.5</td>
</tr>
<tr>
<td align="right">96</td>
<td>2018-11-04 03:09:19</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6328.5</td>
</tr>
<tr>
<td align="right">97</td>
<td>2018-11-04 03:09:21</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">20</td>
<td align="right">6328.5</td>
</tr>
<tr>
<td align="right">98</td>
<td>2018-11-04 03:09:23</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">500</td>
<td align="right">6328</td>
</tr>
<tr>
<td align="right">99</td>
<td>2018-11-04 03:09:23</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6328</td>
</tr>
<tr>
<td align="right">100</td>
<td>2018-11-04 03:09:24</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1200</td>
<td align="right">6328</td>
</tr>
<tr>
<td align="right">101</td>
<td>2018-11-04 03:09:31</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6327.5</td>
</tr>
<tr>
<td align="right">102</td>
<td>2018-11-04 03:09:32</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">10000</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">103</td>
<td>2018-11-04 03:09:33</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">104</td>
<td>2018-11-04 03:09:34</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">109</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">105</td>
<td>2018-11-04 03:09:47</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">109</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">106</td>
<td>2018-11-04 03:10:00</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2000</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">107</td>
<td>2018-11-04 03:10:07</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">20</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">108</td>
<td>2018-11-04 03:10:09</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">6000</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">109</td>
<td>2018-11-04 03:10:11</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">9</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">110</td>
<td>2018-11-04 03:10:16</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">111</td>
<td>2018-11-04 03:10:25</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2692</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">112</td>
<td>2018-11-04 03:10:25</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">24</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">113</td>
<td>2018-11-04 03:10:25</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">126</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">114</td>
<td>2018-11-04 03:10:25</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">763</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">115</td>
<td>2018-11-04 03:10:25</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1395</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">116</td>
<td>2018-11-04 03:10:33</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">500</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">117</td>
<td>2018-11-04 03:10:38</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2000</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">118</td>
<td>2018-11-04 03:10:43</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2837</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">119</td>
<td>2018-11-04 03:10:43</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">30000</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">120</td>
<td>2018-11-04 03:10:43</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">50</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">121</td>
<td>2018-11-04 03:10:44</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">50</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">122</td>
<td>2018-11-04 03:10:44</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">126</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">123</td>
<td>2018-11-04 03:10:44</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">3829</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">124</td>
<td>2018-11-04 03:10:51</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">1000</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">125</td>
<td>2018-11-04 03:11:03</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">3000</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">126</td>
<td>2018-11-04 03:11:26</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">582</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">127</td>
<td>2018-11-04 03:11:26</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">879</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">128</td>
<td>2018-11-04 03:11:26</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">3539</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">129</td>
<td>2018-11-04 03:11:26</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">200</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">130</td>
<td>2018-11-04 03:11:30</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">2000</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">131</td>
<td>2018-11-04 03:11:36</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">6500</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">132</td>
<td>2018-11-04 03:11:37</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">50</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">133</td>
<td>2018-11-04 03:11:46</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">50</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">134</td>
<td>2018-11-04 03:12:04</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">15</td>
<td align="right">6327</td>
</tr>
<tr>
<td align="right">135</td>
<td>2018-11-04 03:12:44</td>
<td>XBTUSD</td>
<td>Buy</td>
<td align="right">657</td>
<td align="right">6327</td>
</tr>
<tr>
<tbody></table>I prefer to work directly with the dates itself using the lubridate package and then combine it with the rest of the tidyverse
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
library(lubridate)
#>
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#>
#> date, intersect, setdiff, union
data_BTC <- tibble::tribble(
~FIELD1, ~timestamp, ~symbol, ~side, ~qty, ~price,
1L, "2018-11-04 03:00:02", "XBTUSD", "Buy", 1L, 6333,
2L, "2018-11-04 03:00:02", "XBTUSD", "Buy", 64L, 6333,
3L, "2018-11-04 03:00:02", "XBTUSD", "Buy", 1000L, 6333,
4L, "2018-11-04 03:00:04", "XBTUSD", "Buy", 10000L, 6333,
5L, "2018-11-04 03:00:04", "XBTUSD", "Buy", 10000L, 6333,
6L, "2018-11-04 03:00:07", "XBTUSD", "Buy", 4626L, 6333,
7L, "2018-11-04 03:00:09", "XBTUSD", "Buy", 7000L, 6333,
8L, "2018-11-04 03:00:09", "XBTUSD", "Buy", 2240L, 6333,
9L, "2018-11-04 03:00:11", "XBTUSD", "Buy", 236L, 6333,
10L, "2018-11-04 03:00:12", "XBTUSD", "Buy", 70L, 6333,
11L, "2018-11-04 03:00:12", "XBTUSD", "Buy", 164L, 6333,
12L, "2018-11-04 03:00:12", "XBTUSD", "Buy", 393L, 6333,
13L, "2018-11-04 03:00:13", "XBTUSD", "Buy", 129L, 6333,
14L, "2018-11-04 03:00:13", "XBTUSD", "Buy", 19L, 6333,
15L, "2018-11-04 03:00:13", "XBTUSD", "Buy", 123L, 6333,
16L, "2018-11-04 03:00:13", "XBTUSD", "Buy", 258L, 6333,
17L, "2018-11-04 03:00:13", "XBTUSD", "Buy", 53L, 6333,
18L, "2018-11-04 03:00:15", "XBTUSD", "Buy", 2294L, 6333,
19L, "2018-11-04 03:00:17", "XBTUSD", "Buy", 300L, 6333,
20L, "2018-11-04 03:00:18", "XBTUSD", "Buy", 1281L, 6333,
21L, "2018-11-04 03:00:18", "XBTUSD", "Buy", 37493L, 6333,
22L, "2018-11-04 03:00:18", "XBTUSD", "Buy", 30667L, 6333,
23L, "2018-11-04 03:00:18", "XBTUSD", "Buy", 2866L, 6333,
24L, "2018-11-04 03:00:19", "XBTUSD", "Buy", 1766L, 6333,
25L, "2018-11-04 03:00:30", "XBTUSD", "Buy", 385L, 6333,
26L, "2018-11-04 03:00:30", "XBTUSD", "Buy", 36L, 6333,
27L, "2018-11-04 03:00:30", "XBTUSD", "Buy", 83L, 6333,
28L, "2018-11-04 03:00:32", "XBTUSD", "Buy", 250L, 6333,
29L, "2018-11-04 03:00:50", "XBTUSD", "Buy", 1667L, 6333,
30L, "2018-11-04 03:00:50", "XBTUSD", "Buy", 165L, 6333,
31L, "2018-11-04 03:00:50", "XBTUSD", "Buy", 797L, 6333,
32L, "2018-11-04 03:01:00", "XBTUSD", "Buy", 171L, 6333,
33L, "2018-11-04 03:01:00", "XBTUSD", "Buy", 2329L, 6333,
34L, "2018-11-04 03:01:02", "XBTUSD", "Buy", 204L, 6333,
35L, "2018-11-04 03:01:03", "XBTUSD", "Buy", 1190L, 6333,
36L, "2018-11-04 03:01:10", "XBTUSD", "Buy", 3946L, 6333,
37L, "2018-11-04 03:01:13", "XBTUSD", "Buy", 500L, 6333,
38L, "2018-11-04 03:01:13", "XBTUSD", "Buy", 500L, 6333,
39L, "2018-11-04 03:01:14", "XBTUSD", "Buy", 4000L, 6333,
40L, "2018-11-04 03:01:15", "XBTUSD", "Buy", 500L, 6333,
41L, "2018-11-04 03:01:15", "XBTUSD", "Buy", 500L, 6333,
42L, "2018-11-04 03:01:16", "XBTUSD", "Buy", 558L, 6333,
43L, "2018-11-04 03:01:18", "XBTUSD", "Buy", 3946L, 6333,
44L, "2018-11-04 03:01:19", "XBTUSD", "Buy", 500L, 6333,
45L, "2018-11-04 03:01:41", "XBTUSD", "Buy", 15000L, 6333,
46L, "2018-11-04 03:01:41", "XBTUSD", "Buy", 360L, 6333,
47L, "2018-11-04 03:01:54", "XBTUSD", "Buy", 554L, 6333,
48L, "2018-11-04 03:01:56", "XBTUSD", "Buy", 2L, 6333,
49L, "2018-11-04 03:01:57", "XBTUSD", "Buy", 2L, 6333,
50L, "2018-11-04 03:01:57", "XBTUSD", "Buy", 2L, 6333,
51L, "2018-11-04 03:01:58", "XBTUSD", "Buy", 2L, 6333,
52L, "2018-11-04 03:01:59", "XBTUSD", "Buy", 2L, 6333,
53L, "2018-11-04 03:01:59", "XBTUSD", "Buy", 2L, 6333,
54L, "2018-11-04 03:02:00", "XBTUSD", "Buy", 2L, 6333,
55L, "2018-11-04 03:02:05", "XBTUSD", "Buy", 60L, 6333,
56L, "2018-11-04 03:02:05", "XBTUSD", "Buy", 10000L, 6333,
57L, "2018-11-04 03:02:05", "XBTUSD", "Buy", 2L, 6333,
58L, "2018-11-04 03:02:09", "XBTUSD", "Buy", 7000L, 6333,
59L, "2018-11-04 03:02:11", "XBTUSD", "Buy", 198L, 6333,
60L, "2018-11-04 03:02:19", "XBTUSD", "Buy", 198L, 6333,
61L, "2018-11-04 03:02:32", "XBTUSD", "Buy", 40L, 6333,
62L, "2018-11-04 03:02:35", "XBTUSD", "Buy", 51L, 6333,
63L, "2018-11-04 03:02:36", "XBTUSD", "Buy", 20L, 6333,
64L, "2018-11-04 03:02:40", "XBTUSD", "Buy", 70L, 6333,
65L, "2018-11-04 03:02:42", "XBTUSD", "Buy", 17L, 6333,
66L, "2018-11-04 03:02:43", "XBTUSD", "Buy", 156L, 6333,
67L, "2018-11-04 03:02:47", "XBTUSD", "Buy", 100L, 6333,
68L, "2018-11-04 03:02:49", "XBTUSD", "Buy", 1000L, 6333,
69L, "2018-11-04 03:02:49", "XBTUSD", "Buy", 325L, 6333,
70L, "2018-11-04 03:02:57", "XBTUSD", "Buy", 1000L, 6333,
71L, "2018-11-04 03:03:17", "XBTUSD", "Buy", 20L, 6333,
72L, "2018-11-04 03:03:24", "XBTUSD", "Buy", 1L, 6333,
73L, "2018-11-04 03:03:25", "XBTUSD", "Buy", 50000L, 6333,
74L, "2018-11-04 03:03:28", "XBTUSD", "Buy", 108L, 6333,
75L, "2018-11-04 03:03:28", "XBTUSD", "Buy", 97L, 6333,
76L, "2018-11-04 03:04:11", "XBTUSD", "Buy", 400L, 6333,
77L, "2018-11-04 03:04:14", "XBTUSD", "Buy", 4000L, 6333,
78L, "2018-11-04 03:04:34", "XBTUSD", "Buy", 5000L, 6333,
79L, "2018-11-04 03:06:16", "XBTUSD", "Buy", 1000L, 6333,
80L, "2018-11-04 03:07:25", "XBTUSD", "Buy", 6330L, 6333,
81L, "2018-11-04 03:08:15", "XBTUSD", "Buy", 5L, 6332.5,
82L, "2018-11-04 03:08:27", "XBTUSD", "Buy", 124L, 6330.5,
83L, "2018-11-04 03:08:27", "XBTUSD", "Buy", 1L, 6330.5,
84L, "2018-11-04 03:08:31", "XBTUSD", "Buy", 1L, 6330,
85L, "2018-11-04 03:08:37", "XBTUSD", "Buy", 1000L, 6330,
86L, "2018-11-04 03:08:43", "XBTUSD", "Buy", 50L, 6329.5,
87L, "2018-11-04 03:08:55", "XBTUSD", "Buy", 50L, 6329.5,
88L, "2018-11-04 03:08:58", "XBTUSD", "Buy", 20L, 6329.5,
89L, "2018-11-04 03:08:58", "XBTUSD", "Buy", 6723L, 6329.5,
90L, "2018-11-04 03:08:58", "XBTUSD", "Buy", 6496L, 6329.5,
91L, "2018-11-04 03:08:58", "XBTUSD", "Buy", 30439L, 6329.5,
92L, "2018-11-04 03:08:58", "XBTUSD", "Buy", 14314L, 6329.5,
93L, "2018-11-04 03:08:58", "XBTUSD", "Buy", 11652L, 6329.5,
94L, "2018-11-04 03:09:04", "XBTUSD", "Buy", 10L, 6329.5,
95L, "2018-11-04 03:09:07", "XBTUSD", "Buy", 100L, 6328.5,
96L, "2018-11-04 03:09:19", "XBTUSD", "Buy", 1000L, 6328.5,
97L, "2018-11-04 03:09:21", "XBTUSD", "Buy", 20L, 6328.5,
98L, "2018-11-04 03:09:23", "XBTUSD", "Buy", 500L, 6328,
99L, "2018-11-04 03:09:23", "XBTUSD", "Buy", 1000L, 6328,
100L, "2018-11-04 03:09:24", "XBTUSD", "Buy", 1200L, 6328,
101L, "2018-11-04 03:09:31", "XBTUSD", "Buy", 1000L, 6327.5,
102L, "2018-11-04 03:09:32", "XBTUSD", "Buy", 10000L, 6327,
103L, "2018-11-04 03:09:33", "XBTUSD", "Buy", 1000L, 6327,
104L, "2018-11-04 03:09:34", "XBTUSD", "Buy", 109L, 6327,
105L, "2018-11-04 03:09:47", "XBTUSD", "Buy", 109L, 6327,
106L, "2018-11-04 03:10:00", "XBTUSD", "Buy", 2000L, 6327,
107L, "2018-11-04 03:10:07", "XBTUSD", "Buy", 20L, 6327,
108L, "2018-11-04 03:10:09", "XBTUSD", "Buy", 6000L, 6327,
109L, "2018-11-04 03:10:11", "XBTUSD", "Buy", 9L, 6327,
110L, "2018-11-04 03:10:16", "XBTUSD", "Buy", 2L, 6327,
111L, "2018-11-04 03:10:25", "XBTUSD", "Buy", 2692L, 6327,
112L, "2018-11-04 03:10:25", "XBTUSD", "Buy", 24L, 6327,
113L, "2018-11-04 03:10:25", "XBTUSD", "Buy", 126L, 6327,
114L, "2018-11-04 03:10:25", "XBTUSD", "Buy", 763L, 6327,
115L, "2018-11-04 03:10:25", "XBTUSD", "Buy", 1395L, 6327,
116L, "2018-11-04 03:10:33", "XBTUSD", "Buy", 500L, 6327,
117L, "2018-11-04 03:10:38", "XBTUSD", "Buy", 2000L, 6327,
118L, "2018-11-04 03:10:43", "XBTUSD", "Buy", 2837L, 6327,
119L, "2018-11-04 03:10:43", "XBTUSD", "Buy", 30000L, 6327,
120L, "2018-11-04 03:10:43", "XBTUSD", "Buy", 50L, 6327,
121L, "2018-11-04 03:10:44", "XBTUSD", "Buy", 50L, 6327,
122L, "2018-11-04 03:10:44", "XBTUSD", "Buy", 126L, 6327,
123L, "2018-11-04 03:10:44", "XBTUSD", "Buy", 3829L, 6327,
124L, "2018-11-04 03:10:51", "XBTUSD", "Buy", 1000L, 6327,
125L, "2018-11-04 03:11:03", "XBTUSD", "Buy", 3000L, 6327,
126L, "2018-11-04 03:11:26", "XBTUSD", "Buy", 582L, 6327,
127L, "2018-11-04 03:11:26", "XBTUSD", "Buy", 879L, 6327,
128L, "2018-11-04 03:11:26", "XBTUSD", "Buy", 3539L, 6327,
129L, "2018-11-04 03:11:26", "XBTUSD", "Buy", 200L, 6327,
130L, "2018-11-04 03:11:30", "XBTUSD", "Buy", 2000L, 6327,
131L, "2018-11-04 03:11:36", "XBTUSD", "Buy", 6500L, 6327,
132L, "2018-11-04 03:11:37", "XBTUSD", "Buy", 50L, 6327,
133L, "2018-11-04 03:11:46", "XBTUSD", "Buy", 50L, 6327,
134L, "2018-11-04 03:12:04", "XBTUSD", "Buy", 15L, 6327,
135L, "2018-11-04 03:12:44", "XBTUSD", "Buy", 657L, 6327
)
df <- data_BTC
df %>%
mutate(timestamp = timestamp %>% ydm_hms()) %>%
group_by(timestamp %>% as_date(),timestamp %>% hour()) %>%
summarise(total_buy = sum(qty))
#> `summarise()` regrouping output by 'timestamp %>% as_date()' (override with `.groups` argument)
#> # A tibble: 1 x 3
#> # Groups: timestamp %>% as_date() [1]
#> `timestamp %>% as_date()` `timestamp %>% hour()` total_buy
#> <date> <int> <int>
#> 1 2018-04-11 3 396209
Created on 2020-06-14 by the reprex package (v0.3.0)