Parse specific text from CSV rows into a new column using Python

Question

I have a csv file that has the block of text in a field that I would like to extract to a new column. For example my csv looks like this below:

house, paint, status-text house1, green, this house is nice it gets a status of result: PASS this is good house2, red, this house is not too nice it gets a status of result: FAIL this is bad house3, blue, this house is the best it gets a status of result: PASS this is great,

I would like to run a simple regex to pull out the (result: PASS) or (result: FAIL) into a new column so the CSV would now look like below:

house, paint, status-text, status house1, green, this house is nice it gets a status of result: PASS this is good, PASS house2, red, this house is not too nice it gets a status of result: FAIL this is bad, FAIL house3, blue, this house is the best it gets a status of result: PASS this is great, PASS

I was thinking of using a Pandas data frame but not sure how to parse out the (PASS/FAIL) and move it to its own column for the 3 rows, and potentially having this scale to hundreds of rows. Any example of how to do this as a small sample would be greatly appreciated.

D. Seah · Accepted Answer · 2020-05-22 04:22:23Z

1

you can load the csv to pandas dataframe and then do this

    conditions = [
        df["status-text"].str.contains("result: PASS "),
        df["status-text"].str.contains("result: FAIL "),
    ]

    choices = ["PASS", "FAIL"]

    df["status"] = numpy.select(conditions, choices, default=None)

    print(df)

answered May 22, 2020 at 4:22

D. Seah

4,7121 gold badge15 silver badges27 bronze badges

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

1 Comment

Tbalz Over a year ago

Wow this was a great, easy to understand and elegant solution. I was able to integrate it into my poc. Thanks so much!

Dishin Goyani · Accepted Answer · 2020-05-22 04:24:11Z

1

You can use np.where

df['status'] = np.where(df["status-text"].str.contains('PASS'), 'PASS', 'FAIL')
df
    house   paint                                        status-text status
0  house1   green   this house is nice it gets a status of result...   PASS
1  house2     red   this house is not too nice it gets a status o...   FAIL
2  house3    blue   this house is the best it gets a status of re...   PASS

answered May 22, 2020 at 4:24

Dishin Goyani

7,7533 gold badges33 silver badges42 bronze badges

Collectives™ on Stack Overflow

Parse specific text from CSV rows into a new column using Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related