0
$\begingroup$

I get real time data by the minute, and sometimes they miss the minute data.
A day has 1440 minutes, and I need to figure out a way to have data for all the 1440 minutes. If there is timestamp missing I need to fill it in with the average of the daily data. Can you help me figure this out using R, (tidyverse preferred).
The current time stamps are as follows: 07-06-2022 15:20:10 (they have seconds)

To be precise I have data that looks like the following:

time                 quantity 
07-06-2022 15:43:01    1
07-06-2022 15:44:17    2
07-06-2022 15:45:10    6
07-06-2022 15:45:54    7
07-06-2022 15:48:29    10

in the above data set one can see that there are two entries for 07-06-2022 15:45. and there missing minutes of data (15:46 and 15:47). I need to write a code that accounts for the missing time stamps and their respective data set. The missing timestamps data (quantity) can have average of the data set per day.
Basically I should account for all the 1440 minutes in a day and get a total table per day.

$\endgroup$
7
  • 1
    $\begingroup$ Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. $\endgroup$
    – Community Bot
    Jul 15, 2022 at 15:32
  • $\begingroup$ i have clarified above please let me know if that helps $\endgroup$
    – user363186
    Jul 15, 2022 at 17:45
  • $\begingroup$ We need you to tell us what "account for" means. That is, how exactly do you plan to use these imputed values? $\endgroup$
    – whuber
    Jul 15, 2022 at 17:52
  • $\begingroup$ I need to create daily totals, and since there are missing time stamps with its respective data the daily total is off by a huge error. So i was hoping to reduce the error percentage by filling the missing time stamp data with average of the data for that particular day. does that help? $\endgroup$
    – user363186
    Jul 15, 2022 at 18:06
  • 1
    $\begingroup$ Do you have a way to evaluate alternative methods for filling in the missing quantities? What do you mean by "the daily total is off by a huge error"? $\endgroup$
    – dipetkov
    Jul 16, 2022 at 19:37

1 Answer 1

0
$\begingroup$

There's a much easier way to create daily totals: sum up the entries you do have, and then multiply by 1440/n, where n is the number of entries you have.

This -- like the method you suggest -- depends on one big assumption: that the missing numbers are, in some sense, "the same" as those you get. This might not at all be the case. Imagine, for example, that the reason you're missing data is that the system is overloaded and the numbers you get are some sort of measure of system load.


Assuming the numbers you've shown us here are a truly random sample and there's no time dependence, a rough estimation of the daily total would be $\frac{1440}{5} \cdot 26 = 7488$, since you showed us five entries and they sum to 26.

$\endgroup$
4
  • $\begingroup$ can you show an example with the data i have given? how would i know the n value for different days? can we add a loop that checks that for everyday i am new at R so the example would help. $\endgroup$
    – user363186
    Jul 18, 2022 at 4:55
  • $\begingroup$ @user363186 , it's literally just 1440/count * sum -- I edited my answer with an example. If you need help with summing and counting things in R I suggest you ask those questions separately. $\endgroup$
    – kqr
    Jul 18, 2022 at 5:15
  • $\begingroup$ i tried that but the total is off, is there anyway we can fill the previous time values for the missing time.?\ $\endgroup$
    – user363186
    Jul 19, 2022 at 12:05
  • $\begingroup$ That would be just as off in that case, so I don't see why bother. It would be easier to help you if you would open a new question with more details than just saying it's "off" because that could mean you're doing one in literally a million things wrong. $\endgroup$
    – kqr
    Jul 19, 2022 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.