Python: Find string from list in Excel column

Question

I have a list of urls to scrape (from a txt file) and an Excel file with the scraped data, including the url. I regularly add new urls to the txt file and want to be able to run the code after each time, only on the newly added urls (first column, named 'URLS'). I figured I'd do this by letting it check whether the url from the list is already in the Excel and only do something if it isn't, but I'm stuck on how to do this (I've tried multiple options using openpyxl and pandas).

My setup for pandas looks like this:

import pandas as pd

df = pd.read_excel('scrapeddata.xlsx')
pd.set_option('display.max_colwidth', None) #otherwise it would cut off the urls

with open('urls.txt', 'r') as f:
    urls = f.readlines()
    urls = [url.strip() for url in urls]  #strip `\n`

for url in urls:

And for openpyxl like this:

from openpyxl import load_workbook

wb = openpyxl.load_workbook('articles.xlsx')
ws = wb.active

with open('urls.txt', 'r') as f:
    urls = f.readlines()
    urls = [url.strip() for url in urls]  #strip `\n`

for url in urls:

Then I think I need some kind of if clause that matches the url with the content of the 'URLS' column in the Excel. All the options I've tried have errored (they are too many to name here I'm afraid). Any help is appreciated much as I'm still very new to this.

HedgeHog · Accepted Answer · 2022-03-16 11:34:41Z

1

You could compare the urls between list and series and operate on the delta:

list(set(urls) - set(df['URLS'].to_list()))

Example

import pandas as pd

urls = ['https://www.google.com','https://www.google.at','https://www.google.de','https://www.yahoo.de']

data = {'SITE': ['google','google','yahoo'],
        'URLS': ['https://www.google.com','https://www.google.de','https://www.yahoo.de']
        }
df = pd.DataFrame(data)

delta = list(set(urls) - set(df['URLS'].to_list()))

for url in delta:
    print(url)

Output

https://www.google.at

answered Mar 16, 2022 at 11:34

HedgeHog

25.4k5 gold badges18 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

Python: Find string from list in Excel column

1 Answer 1

Example

Output

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Example

Output

Comments

Your Answer

Sign up or log in

Post as a guest

Related