I have a list with certificate numbers:
prod_cmvp_data = ['3914', '3907', '3197', '4272', '4271', '4254', '3784', '3946', '3888', '4174', '4222', '3613', '3125', '3140', '3197', '3196', '3644', '3615', '3651', '3918', '3946', '4271', '3888', '4174', '4222', '3613', '3125', '3140']
I also have a df object and one of the columns is "Certificate_Number". My goal is to iterate through it and only return the rows where the "Certificate_Number" matches with the certificate numbers from the list prod_cmvp_data
Here's my code:
def main():
certs_url = "https://csrc.nist.gov/projects/cryptographic-module-validation-program/validated-modules/search/all"
prod_cmvp_data = get_prod_cmvp_data()
data = get_html(certs_url)
cmvp_data = get_cmvp_data(data, prod_cmvp_data)
sunset_date(cmvp_data, prod_cmvp_data)
write_file('txt', cmvp_data)
def get_prod_cmvp_data():
# parse excel sheet for client CMVP data and remove empty cells
prod_cmvp_data = pd.read_excel('_Request ID__Client Cryptographic Module List Template.xlsx', header = 4)
drop_na = prod_cmvp_data.dropna(subset=['CMVP #'])
# convert df obj to list
to_list = drop_na['CMVP #'].values.tolist()
# remove '#' and blank spaces from list values - only CMVP numbers remain
rmv_excess_chars = [item.strip('# ') for item in to_list]
return rmv_excess_chars
def get_html(url):
# make request to URL and convert to BS obj
req = requests.get(url)
soup = BeautifulSoup(req.content, 'html.parser')
return soup
def get_cmvp_data(cmvp_content, prod_data):
# --- Build CMVP dataframe ---
search_tbl = cmvp_content.find_all('table', id='searchResultsTable')
# convert HTML table to df obj
cert_table = pd.read_html(str(search_tbl))
df = cert_table[0]
# print(df)
# column headers - sub spaces w/'_'
df.columns = [column.replace(" ", "_") for column in df.columns]
# print(df.columns)
df_new = df[df['Certificate_Number'].isin(prod_data)]
# sort data - vendor name descending
# df_filtered = df_filtered.sort_values(by=['Vendor_Name', 'Certificate_Number'], ascending=True)
return df_new
def sunset_date(cmvp_data, prod_cmvp_data):
condensed_cmvp_data = cmvp_data.get(["Certificate_Number", "Validation_Date"])
print(prod_cmvp_data)
print(cmvp_data)
Running this will give me this output:
Empty DataFrame
Columns: [Certificate_Number, Vendor_Name, Module_Name, Module_Type, Validation_Date]
Index: []
For context, if I run print(df) this is what the df object is:
Certificate Number Vendor Name Module Name Module Type Validation Date
0 4293 Qualcomm Technologies, Inc. Qualcomm® Secure Processing Unit Hardware 09/06/2022
1 4292 Canonical Ltd. Ubuntu 20.04 OpenSSL Cryptographic Module Software 09/06/2022
2 4291 Extreme Networks, Inc. Extreme Networks SLX 9540 and SLX 9740 Switches Hardware 09/01/2022
3 4290 LogRhythm LogRhythm 7.8.0 System Monitor Agent Software 08/31/2022
4 4289 LogRhythm LogRhythm 7.8.0 Platform Manager Software 08/31/2022
.. ... ... ... ... ...
816 1938 SafeLogic Inc. CryptoComply for Mobile Software 04/30/201311/08/201304/23/201401/25/201602/10/...
817 1592 L3Harris Technologies, Inc. Harris Unified Audio Card Hardware 08/22/201104/13/201607/20/201808/30/2021
818 1363 Progress Software, Inc. MOVEit Crypto Software 07/12/201004/26/201604/27/201601/24/201801/30/...
819 1324 Comtech Mobile Datacom Corporation Transceiver Cryptographic Module (TCM) Hardware 07/12/201004/18/201605/08/201810/16/2020
820 911 L3Harris Technologies, Inc. Harris Corporation Wireless Systems Cryptograp... Software 02/07/200807/02/201004/10/201504/27/201603/01/...
I want to iterate through my df object and only return the rows that contain one of the certificate numbers from my prod_cmvp_data list. I know it only shows a few of the items in my df object, but the first number in my list '3914' is also in my df object, along with many of those other numbers. None of them are being returned and instead, it's just returning an empty list.