can't insert a row in a dataframe where index is a datetime

Question

data is typical stock quotes:

                     human_date     open    high     low   close   volume
date                                                                       
2017-10-13 00:00:00  13/10/17 0:00  5464.7  5464.8  5448.8  5448.9  1207430
2017-10-13 00:01:00  13/10/17 0:01  5450.0  5450.9  5434.9  5435.0  1411973
2017-10-13 00:02:00  13/10/17 0:02  5434.4  5437.3  5430.0  5433.6  1537291
2017-10-13 00:03:00  13/10/17 0:03  5433.6  5443.7  5433.6  5442.3   825607
2017-10-13 00:04:00  13/10/17 0:04  5440.0  5444.4  5440.0  5440.0  1018440

I have the following code:

        r = {
            'open': previous['open'],
            'high': previous['high'],
            'low': previous['low'],
            'close': previous['close'],
            'volume': previous['volume'],
            'date': datetime_to_epoch(now),
            'human_date': now.strftime("%Y-%m-%d %H:%M:%S")}
        print(f'fixing {now} with {r}')
        c1 = len(minute_data)
        minute_data.append(pd.DataFrame(r, index=['date']))
        c2 = len(minute_data)
        print(f'data len from {c1} to {c2}')

c1 is always equal to c2, so I don't know how to insert a row...

also there is a warning that I do now understand:

FutureWarning: Sorting because non-concatenation axis is not aligned. A future version of pandas will change to not sort by default.

To accept the future behavior, pass 'sort=False'.

To retain the current behavior and silence the warning, pass 'sort=True'.

sort=sort,

In typical pandas form: 'non-concatenation axis is not aligned'.. super clear :D

but, the question is: how can I insert a row? the data is missing some times and I need to fill it up so no time frame is missing.

Edit: following Valdi_Bo's answer, I did this:

          r = {
                'open': previous['open'],
                'high': previous['high'],
                'low': previous['low'],
                'close': previous['close'],
                'volume': previous['volume'],
                'human_date': hDate
            }

            print(f'fixing {now} with {r}')
            c1 = len(minute_data)
            minute_data.append(pd.DataFrame(r, index=[indDate]))
            c2 = len(minute_data)

c1 is still equal to c2 after.

I implemented Jezrael's answer:

           r = {
                'open': previous['open'],
                'high': previous['high'],
                'low': previous['low'],
                'close': previous['close'],
                'volume': previous['volume'],
                'human_date': now.strftime("%Y-%m-%d %H:%M:%S")
            }

            df1 = pd.DataFrame(r)
            df1.index = [datetime_to_epoch('now')]

            print(f'fixing {now} with {r}')
            c1 = len(minute_data)
            minute_data = minute_data.append(df1)
            c2 = len(minute_data)
            print(f'data len from {c1} to {c2}')

and got this:

ValueError: If using all scalar values, you must pass an index

during the call to append.

Valdi_Bo · Accepted Answer · 2019-09-02T13:20:54.963

Look at minute_data.append(pd.DataFrame(r, index=['date'])) in your code.

You create a temporary DataFrame. The content of the row looks OK, but as the index value you attempt to insert 'date' - a string, whereas it should be of DateTime type.

My suggestion:

Get the current Timestamp, calling pd.now().
Convert it to human date (strftime(...)), almost as you did, but:
- without seconds / miliseconds (note that previous rows are just on full minutes),
- with format like in existing rows.
Convert this text to Timestamp (so it will be also without seconds), to be used as the index value.
Create the temporary DataFrame.
Append it to minute_data.

So the code should be:

now = pd.now()
hDate = now.strftime("%d/%m/%y %H:%M") + ":00"
indDate = pd.to_datetime(hDate, dayfirst=True)
r = { ... 
      'human_date': hDate }
minute_data.append(pd.DataFrame(r, index=[indDate]))

almost: minute_data = minute_data.append(...), while minute_data.append(...) doesn't work. I didn't know that append returned a new object. — Thomas, Sep 02 '19 at 16:22

can't insert a row in a dataframe where index is a datetime

1 Answers1