Skip to content
Advertisement

Pandas: Conditionally insert rows into DataFrame while iterating through rows in the middle

I have a dataframe and need to insert rows in the middle based on a condition, if the condition is met, I need to add rows based on previous and next row values. So far I have this

for index, row in df.iterrows():
    last_row = df.iloc[index-1]
    next_row = df.iloc[index+1]

    new_row = {
    'SystemID' : last_row.SystemID,
    'StartDateTime' : last_row.EndDateTime,
    'EndDateTime' : next_row.StartDateTime,
    'Date' : last_row.Date,
    'Duration':0,
    'EventCodeType':"PL",
    'Shift':last_row.Shift,
    'ProductCode':last_row.ProductCode,
    'Machine&Fault':"Producing"
    }
    new_row = {k:v for k,v in new_row.items()}
    if index == last:
        break
    else:
        if index != 0 :
            #condition 
            if (df.iloc[index]['StartDateTime'] != df.iloc[index - 1]['EndDateTime']):
                df.loc[index+1] = new_row # This line is giving error


    continue

But when I’m trying to insert row(last line of code), it is throwing an error IndexError: single positional indexer is out-of-bounds for the code(next_row = df.iloc[index+1])

the data looks like this

[![enter image description here][1]][1]

Update: I used the below logic as suggested in one of the answers

for index, row in df.iterrows():
    try:
        if index == df.shape[0]:
            break
        else:           
            last_row = df.iloc[index-1]
            next_row = df.iloc[index]
            new_row = {
            'SystemID' : last_row.SystemID,
            'StartDateTime' : last_row.EndDateTime,
            'EndDateTime' : next_row.StartDateTime,
            'Date' : last_row.Date,
            'Duration':0 ,
            'EventCodeType':"PL",
            'Shift':last_row.Shift,
            'ProductCode':last_row.ProductCode,
            'Machine&Fault':"Producing"
            }
            new_row = {k:v for k,v in new_row.items()}
            if index != 0 and index!=df.shape[0]:
                if (df.iloc[index]['StartDateTime'] != df.iloc[index - 1]['EndDateTime']):
                    df1 = df1.append(new_row, ignore_index=True)

            continue
    except:
        continue

Put the code in a try catch exception and append the new rows in a new dataframe and concat the two dataframes [1]: https://i.stack.imgur.com/mFVBT.png

Advertisement

Answer

You are trying to add a row beyond the dataframe’s scope (size/capacity, so to say). You can get the the size of dataframe by using dataframe.shape().

If you need to, you would have to extend the index of the dataframe using set_index() when you try to add a row at the end/after the last row. This should solve your issue.

You could also use dataframe.append() to add new rows.

Another possible solution would be to use integer slicing with iloc. iloc doesn’t give an error with slicing, but again, going beyond the scope of the dataframe would still be an issue you have to fix before appending anyhting.

Advertisement