Python Forum
How to extract previous and current rows if their value is equal
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to extract previous and current rows if their value is equal
#1
Hi,

I want to extract rows if the previous row of Name column equal to 'MM' and current row of Name column is 'MA'

import pandas as pd
import numpy as np
df=pd.DataFrame({'Name':['MM','MA','CS','MM','MM','TH','MM','MA'],'Rank':[1,3,3,1,2,3,2,2],'Score':[120,20,12,15,2,83,22,32]})
for i,(index,row) in enumerate(df.iterrows()):
    if i == 0: continue
    df[(df.Name.loc[i]=='MA') & (df.Name.loc[i-1]=='MM')]
Reply
#2
df={'Name':["MM","MA","CS","MM","MM","TH","MM","MA"],'Rank':[1,3,3,1,2,3,2,2],'Score':[120,20,12,15,2,83,22,32]}
filtered_df = {'Name': [], 'Rank': [], 'Score': []}
for i in range(len(df['Name'])):
    try:
        if df['Name'][i] == 'MM' and df['Name'][i+1] == 'MA':
            filtered_df['Name'].append(df['Name'][i])
            filtered_df['Name'].append(df['Name'][i+1])

            filtered_df['Rank'].append(df['Rank'][i])
            filtered_df['Rank'].append(df['Rank'][i+1])

            filtered_df['Score'].append(df['Score'][i])
            filtered_df['Score'].append(df['Score'][i+1])
    except:
        pass
        
print(filtered_df)
Reply
#3
I still want to put the data in pandas dataframe
Reply
#4
I'm not sure if I understood what you're asking for, maybe this:

import pandas as pd

df=pd.DataFrame({'Name':["MM","MA","CS","MM","MM","TH","MM","MA"],'Rank':[1,3,3,1,2,3,2,2],'Score':[120,20,12,15,2,83,22,32]})
filtered_df = {'Name': [], 'Rank': [], 'Score': []}
for i in range(len(df['Name'])):
    try:
        if df['Name'][i] == 'MM' and df['Name'][i+1] == 'MA':
            filtered_df['Name'].append(df['Name'][i])
            filtered_df['Name'].append(df['Name'][i+1])

            filtered_df['Rank'].append(df['Rank'][i])
            filtered_df['Rank'].append(df['Rank'][i+1])

            filtered_df['Score'].append(df['Score'][i])
            filtered_df['Score'].append(df['Score'][i+1])
    except:
        pass

filtered_pd_df = pd.DataFrame(filtered_df)
print(filtered_pd_df)
Reply
#5
I used as below:
pd.DataFrame.from_dict(filtered_df),

but your comment also works, Many many thanks
Reply
#6
(Jun-18-2018, 02:49 PM)SriRajesh Wrote: Hi,

I want to extract rows if the previous row of Name column equal to 'MM' and current row of Name column is 'MA'

import pandas as pd
import numpy as np
df=pd.DataFrame({'Name':['MM','MA','CS','MM','MM','TH','MM','MA'],'Rank':[1,3,3,1,2,3,2,2],'Score':[120,20,12,15,2,83,22,32]})
for i,(index,row) in enumerate(df.iterrows()):
    if i == 0: continue
    df[(df.Name.loc[i]=='MA') & (df.Name.loc[i-1]=='MM')]

Output:
print(list(df.iterrows())) [(0, Name MM Rank 1 Score 120 Name: 0, dtype: object), (1, Name MA Rank 3 Score 20 Name: 1, dtype: object), (2, Name CS Rank 3 Score 12 Name: 2, dtype: object), (3, Name MM Rank 1 Score 15 Name: 3, dtype: object), (4, Name MM Rank 2 Score 2 Name: 4, dtype: object), (5, Name TH Rank 3 Score 83 Name: 5, dtype: object), (6, Name MM Rank 2 Score 22 Name: 6, dtype: object), (7, Name MA Rank 2 Score 32 Name: 7, dtype: object)]
Enumerate was redundant
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  RNG; One variable must not be equal to another DavidRobinsons 12 5,733 Oct-10-2018, 02:43 AM
Last Post: micseydel

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020