Python Forum
Insert missing data in a dataframe
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Insert missing data in a dataframe
#5
(Jan-17-2022, 11:00 AM)perfringo Wrote: No code is posted, so neither do I, only provide idea: if time is index, then create new dataframe with step on 2 minutes for needed duration and value of 0. Then just add two columns, those rows which are only in new dataframe will keep value 0 and those which have value 1 in initial dataframe will have that value (1 + 0 = 1).

Implementation of idea above (I have little idea whether it address actual problem):

Creating sample data - three datetimes as indices with value of 1:

import pandas as pd

data = dict.fromkeys(('2006-09-17 12:49:00', '2006-09-17 12:57:00', '2006-09-17 13:01:00'), 1)
df = pd.DataFrame.from_dict(data, orient='index')
print(df)
Output:
0 2006-09-17 12:49:00 1 2006-09-17 12:57:00 1 2006-09-17 13:01:00 1
Create dataframe with all needed datetimes with interval of 2 minutes and values of 0:

import numpy as np

index = pd.date_range('2006-09-17 12:45:00', periods=10, freq='2T')
df_2 = pd.DataFrame(np.zeros(10), index=index)
print(df_2)
Output:
0 2006-09-17 12:45:00 0 2006-09-17 12:47:00 0 2006-09-17 12:49:00 0 2006-09-17 12:51:00 0 2006-09-17 12:53:00 0 2006-09-17 12:55:00 0 2006-09-17 12:57:00 0 2006-09-17 12:59:00 0 2006-09-17 13:01:00 0 2006-09-17 13:03:00 0
Now I just add these two dataframes. Addition will be indices based, just fill NaN-s with zeros:

new = (df_2 + df).fillna(0).astype(int)
print(new)
Output:
0 2006-09-17 12:45:00 0 2006-09-17 12:47:00 0 2006-09-17 12:49:00 1 2006-09-17 12:51:00 0 2006-09-17 12:53:00 0 2006-09-17 12:55:00 0 2006-09-17 12:57:00 1 2006-09-17 12:59:00 0 2006-09-17 13:01:00 1 2006-09-17 13:03:00 0
Now I have dataframe which have two minutes interval and have value 1 from source data and 0 where particular datetime missing in source data.
amdi40 likes this post
I'm not 'in'-sane. Indeed, I am so far 'out' of sane that you appear a tiny blip on the distant coast of sanity. Bucky Katt, Get Fuzzy

Da Bishop: There's a dead bishop on the landing. I don't know who keeps bringing them in here. ....but society is to blame.
Reply


Messages In This Thread
Insert missing data in a dataframe - by amdi40 - Jan-16-2022, 02:15 PM
RE: Insert missing data in a dataframe - by perfringo - Jan-17-2022, 05:57 PM
RE: Insert missing data in a dataframe - by amdi40 - Jan-17-2022, 02:40 PM
RE: Insert missing data in a dataframe - by amdi40 - Jan-19-2022, 07:47 AM
RE: Insert missing data in a dataframe - by amdi40 - Jan-19-2022, 08:55 AM

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020