Python: Find string from list in Excel column-CodePudding

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.

CodePudding user response：

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