import pandas as pd

# Read the Excel files
df1 = pd.read_excel('Downloads/UUIDs for Farmonaut Data Extraction.xlsx')
df2 = pd.read_excel('Downloads/ifpri_uuids.xlsx')

# Initialize an empty list to store the matching IDs
matching_ids = []

# Iterate through the rows of excel sheet 2
for index, row in df2.iterrows():
    # Get the value from column C and split it by comma
    description_values = str(row['UUID']).split(',')
    
    # Get the first element from the split values
    
    first_element = description_values[0].strip()
    try:
        first_element = float(first_element)
    except:
        first_element = first_element
    
    # Compare the first element with the values in column D of excel sheet 1
    matching_row = df1[df1['UUID Final'] == first_element]
    print(matching_row)
    
    if not matching_row.empty:
        # If a match is found, add the value of column B from excel sheet 1 to the list
        matching_ids.append(matching_row['Field ID'].values[0])

# Save the resulting array to a new Excel sheet
result_df = pd.DataFrame(matching_ids, columns=['Matching IDs'])
result_df.to_excel('ifpri_matching_ids.xlsx', index=False)