insert row with datetime index to dataframe

Question

I have a dataframe with datetime index:

time                count  day hour  minute  label
2018-06-07 00:25:00 207    7.0 0.0   25.0    177.0
2018-06-07 00:55:00 187    7.0 0.0   55.0    150.0
2018-06-07 01:25:00 194    7.0 1.0   25.0    165.0
2018-06-07 01:55:00 176    7.0 1.0   55.0    175.0
2018-06-07 02:25:00 195    7.0 2.0   25.0    172.0
-> add new datetime record record here

and I'm trying to add some new records but I get:

[DatetimeIndex(['2018-06-07 01:55:00'], dtype='datetime64[ns]', name='time', freq=None)] not an index
# this happen even if row exists or not

I just want to add a 30 minutes interval record, my code is something like

last_date = recent_posts.iloc[[-1]].index
last_date = last_date + timedelta(minutes=30)

recent_posts.iloc[[last_date]] = # bla #bla

# What I may be doing wrong?

You are looking for last_date DatetimeIndex which is not in the dataframe. — harpan
– harpan, Commented Jun 13, 2018 at 15:19
it is! when "recent_posts.iloc[[-1]].index" gives the last element, however, I think should matter is index doest not exists, I want to insert a new row! correct me if I'm wrong — Freddy
– Freddy, Commented Jun 13, 2018 at 15:23

harpan · Accepted Answer · 2018-06-13 15:31:16Z

19

The correct way to insert a new record would be:

df.append(pd.DataFrame(index=[last_date]))

Example:

print(df)

Output:

                   count    day hour    minute  label
time                    
2018-06-07 00:25:00 207 7.0 0.0 25.0    177.0
2018-06-07 00:55:00 187 7.0 0.0 55.0    150.0

To add an element, use .append():

df.append(pd.DataFrame(index=[last_date]))

Output:

                     count  day  hour  label  minute
2018-06-07 00:25:00  207.0  7.0   0.0  177.0    25.0
2018-06-07 00:55:00  187.0  7.0   0.0  150.0    55.0
2018-06-07 01:25:00    NaN  NaN   NaN    NaN     NaN

As you can see, it adds a new record with the defined index and since we did not specify values for other columns, they are NaN

You can specify the values for one or more columns with a dict like this:

data = {'hour':10} 
df.append(pd.DataFrame(data, index=[last_date]))

Output:

                     count  day  hour  label  minute
2018-06-07 00:25:00  207.0  7.0   0.0  177.0    25.0
2018-06-07 00:55:00  187.0  7.0   0.0  150.0    55.0
2018-06-07 01:25:00    NaN  NaN   10.0    NaN     NaN

edited Jun 13, 2018 at 15:31

answered Jun 13, 2018 at 15:25

harpan

8,6412 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Freddy Over a year ago

Super thansk Harv! Examples I've seen almost always use concat or altering the index directly

harpan Over a year ago

Glad I could help. @Freddy

Confounded Over a year ago

I have seen in few places that append make a copy of the dataframe, and that a more efficient way to do it is with .loc[], but I get an error ValueError: cannot set a row with mismatched columns when I use df.loc[last_date] = data. Do you know how to get it working this way? Thank you

Confounded Over a year ago

@PS It seems that you need to assign the object created with .append to another variable as .append does do in-place modification so data is not saved. This was not clear from your post.

harpan Over a year ago

@Confounded I have added the link of the official docs. It is impossible to put every aspect of the function in the answer, hence the official doc link. Glad you figured that one out yourself.

|

Collectives™ on Stack Overflow

insert row with datetime index to dataframe

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related