How to automatically add date & time into dataframe index?

user_16149127 0
Viewed 328

I have a code that extracts tick data.

z=[SpanVariable1.text,datetime.now().strftime("%H:%M:%S")] 
dfl=dfl.append(pd.DataFrame(z).T)

This part of the code is written in a loop that keeps running in real-time. So for the very first time when on completion of 1000 datapoints I execute the below commands

dfl.set_index(1, inplace = True)
print (dfl)
dfl.index = pd.to_datetime(dfl.index)
print (dfl)

which returns the data frame as intended.

               0
1               
08:04:04  441.95
08:04:04  441.95
08:04:04  441.95
08:04:04  441.95
08:04:04  441.95
...          ...
08:04:29  441.95
08:04:29  441.95
08:04:29  441.95
08:04:29  441.95
08:04:29  441.95

[1001 rows x 1 columns]
                          0
1                          
2021-06-07 08:04:04  441.95
2021-06-07 08:04:04  441.95
2021-06-07 08:04:04  441.95
2021-06-07 08:04:04  441.95
2021-06-07 08:04:04  441.95
...                     ...
2021-06-07 08:04:29  441.95
2021-06-07 08:04:29  441.95
2021-06-07 08:04:29  441.95
2021-06-07 08:04:29  441.95
2021-06-07 08:04:29  441.95

[1001 rows x 1 columns]

But when loop forward for the next values that is 1002 all above index converts to NaN and Nat. except the last added row.

               0
1               
NaN       441.95
NaN       441.95
NaN       441.95
NaN       441.95
NaN       441.95
...          ...
NaN       441.95
NaN       441.95
NaN       441.95
NaN       441.95
08:04:29  441.95

[1002 rows x 1 columns]
                          0
1                          
NaT                  441.95
NaT                  441.95
NaT                  441.95
NaT                  441.95
NaT                  441.95
...                     ...
NaT                  441.95
NaT                  441.95
NaT                  441.95
NaT                  441.95
2021-06-07 08:04:29  441.95

[1002 rows x 1 columns]

I don't know what is the problem here. Is it possible that whenever I append live data to a data frame its index should be automatically kept as the current time?

Below code has reduced some steps but still I am not able to get rid of NaN and NaT after looping:

z=[SpanVariable1.text, datetime.now()]
dfl=dfl.append(pd.DataFrame(z).T)

dfl=dfl.append(pd.DataFrame(SpanVariable1.text).T)

dfl.set_index(1, inplace = True)
print (dfl)

again output for 1000 and 1001 loop comes respectively as below:

                                 0
1                                 
2021-06-07 09:46:44.571711  366.15
2021-06-07 09:46:44.600210  366.15
2021-06-07 09:46:44.628139  366.15
2021-06-07 09:46:44.654060  366.15
2021-06-07 09:46:44.679984  366.15
...                            ...
2021-06-07 09:47:13.492993  359.60
2021-06-07 09:47:13.528899  359.60
2021-06-07 09:47:13.556823  359.60
2021-06-07 09:47:13.583752  359.60
2021-06-07 09:47:13.611677  359.60

[1001 rows x 1 columns]
                                 0
1                                 
NaT                         366.15
NaT                         366.15
NaT                         366.15
NaT                         366.15
NaT                         366.15
...                            ...
NaT                         359.60
NaT                         359.60
NaT                         359.60
NaT                         359.60
2021-06-07 09:47:13.643593  359.60

[1002 rows x 1 columns]

I think there is some problem in appending the new rows of live data into the existing dataframe. Please suggest some solution..

pandas python
2 Answers

You have to perform the step of setting index on the date time for the newly added data separately before appending to the base dataframe df1. Otherwise, you will perform twice for the setting index on the first 1001 rows.

You can modify as follows:

Step (1) Define an empty helper dataframe df2 before the loop of tick data extraction:

df2 = pd.DataFrame()

Step (2) Modify the loop of tick data extraction to use df2 instead

z=[SpanVariable1.text,datetime.now().strftime("%H:%M:%S")] 
df2=df2.append(pd.DataFrame(z).T)

Step (3) After processing 1000 datapoints, replace the codes as follows:

df2.set_index(1, inplace = True)
df2.index = pd.to_datetime(df2.index)
dfl=dfl.append(df2)

Now, your df1 should contain the newly extracted tick data from the loop and also with the new data converted with datatime index. At the same time, previous data extracted in previous batch of 1000 datapoints extraction would not get the index modified more than once.

user_150706970

this is how to add datetime automatically in PyCharm, I hope it helps.

elupdatedon = datetime.datetime.now()
self.entryUpdatedOn.setDateTime(elupdatedon)
print(elupdatedon)

Output: 2021-06-07 10:30:11.451245

user_107798830
Related