pandas dataframe drop problem, want to delete specific rows?

Question

I have a problem to delete specific rows from my dataframe. I want to delete rows that are by matching account number. Here is code:

def main():
    
    # Collecting data from .csv
    df1 = pd.read_csv("./2018/Member last activited.csv", 
    sep=";", dtype={"Account Number": str}, encoding='ISO-8859-1', engine = 'python')

    accountnum = df1["Account Number"]

    # Collecting data from .csv
    df2 = pd.read_csv("./2019/Member last activited062019.csv", 
    sep=";", dtype={"Account Number": str, "Phone Number": str}, encoding='ISO-8859-1', engine = 'python')

    accountnum2 = df2["Account Number"]

    # comparing account numbers and removing them if matched
 
    tmp2 = {"ID": "0",
        "ACCOUNTNUM": "0"}
    tmplist = []
    for x, y in accountnum.items():
        for z, w in accountnum2.items():
            if y == w:
               tmp2 = {"ID": z, "ACCOUNTNUM": w }
               tmplist.append(tmp2)
    index = 0
    for x in df2["Account Number"]:
        if x == tmplist[index]["ACCOUNTNUM"]:
            df2.drop(index, inplace=True)
        index += 1

accountnum2 = df1["Account Number"], I think you meant to use df2 here. Please provide more background about the problem and target here. — Niv Dudovitch
– Niv Dudovitch, Commented Sep 2, 2021 at 9:37
now it states that list index is out of range. Is there a better way to do this? — Levsha
– Levsha, Commented Sep 2, 2021 at 9:41

Divyesh Peshavaria · Accepted Answer · 2021-09-02 09:40:13Z

1

You can use the .isin() method of pandas Series

df2["Account Number"].isin(df1["Account Number"])

This will give you Series of boolean values which will be true for all rows where Account Number in df2 is present in df1 as well. Since, you want to discard those rows, you can use series indexing along with ~ (negation operator) like this:

df3 = df2[~df2["Account Number"].isin(df1["Account Number"])]

answered Sep 2, 2021 at 9:40

Divyesh Peshavaria

5991 gold badge5 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

pandas dataframe drop problem, want to delete specific rows?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related