0

I have a DataFrame as shown in the picture:

dataframe

I want to compare the current row and the next, whether df['Time'] is the same, and df['MessageType'] is 'D' followed by 'A'. If the condition is satisfied, remove the row that contains 'D' and rename the value 'A' to 'AMEND'.

Something like... 

if df['Time'] (current) == df['Time'] (next) & df['MessageType'] (current) == 'D' is followed by df['MessageType'] (next) == 'A':
del current row
df['MessageType'] (next_row).rename({'A': 'AMEND'})
Dharman
  • 30,962
  • 25
  • 85
  • 135
Shaun Lim
  • 75
  • 1
  • 6
  • you can use the shift function in pandas , with `periods = -1` [pandas.DataFrame.shift](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.shift.html) – Shijith Dec 23 '19 at 05:49
  • show your sample df – Kenan Dec 23 '19 at 05:55

1 Answers1

0

You can load both of the columns from the data frame into different lists and iterate over them to check if the condition is followed & combine your updated lists to generate a new data frame.

This is a very brute force way to do this but will give you enough idea to come up with a more space-efficient approach iterating directly over the data frame. Coming up with the best answer yourself might cheer you up too.

import pandas as pd

Time = df['Time']
MessageType = df['MessageType']
Symbol = df['Symbol']

for i in range(len(Time)-1):
    if(Time[i] == Time[i+1] && MessageType[i] == "D" && MessageType[i+1] == "A"):
        del Time[i] # deleting the row
        del MessageType[i] # deleting the row
        del Symbol[i] # deleting the row
        MessageType[i+1] = "AMEND" # rename the message type

# creating a new dataframe with updated lists
new_df = pd.DataFrame({'Time':Time, 'MessageType':MessageType, 'Symbol':Symbol})
Dharman
  • 30,962
  • 25
  • 85
  • 135
Lavish Saluja
  • 221
  • 5
  • 10
  • 1
    Thank you. It does solve my problem, although the warning appears - "A value is trying to be set on a copy of a slice from a DataFrame See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy" – Shaun Lim Dec 24 '19 at 05:25
  • The SettingWithCopyWarning just ensures that the user knows they are operating on the copy of the main data frame and not the original one so changes might not reflect upon the original df. Try reading this answer, https://stackoverflow.com/questions/20625582/how-to-deal-with-settingwithcopywarning-in-pandas – Lavish Saluja Dec 25 '19 at 11:16