[939] Generate a new shapefile based on a list of records and query polygons from a large shapefile

ref: arcpy.management.MakeFeatureLayer(in_features, out_layer, {where_clause}, {workspace}, {field_info})

ref: arcpy.management.SelectLayerByAttribute(in_layer_or_view, {selection_type}, {where_clause}, {invert_where_clause})

---

# 1. Records are saved at ".csv" files
# 2. To match each record with the polygon in the file "QLD"
# 3. Remove duplicate records
# 4. Generate a new shapefile
 
import os, arcpy
import geopandas as gpd
 
root_dir = r"D:\Bingnan_Li\01_Tasks\11_20231109_PDF_reading\Planning_LGA"
for i in range(len(os.listdir(root_dir))):
    folder = os.listdir(root_dir)[i] 
    
    for file in os.listdir(os.path.join(root_dir, folder)):
        if file.find("csv") > -1:
            file_path = os.path.join(root_dir, folder, file)
            print("  --" + file_path) 
            print() 
            
            df = pd.read_csv(file_path)
            arcpy.env.workspace = os.path.join(root_dir, folder)
            
            # Create a feature layer for the input shapefile
            arcpy.MakeFeatureLayer_management("U:\\ADMINISTRATIVE\\Cadastral\\AUS_Cadastre.gdb\\QLD", "Templayer")
            
            # Iterate through the list of lot records and select features
            for i in range(len(df)):
                lotplan = df.loc[i, "lotplan"]
                query = f"lotplan = '{lotplan}'"
                # ADD_TO_SELECTION—The resulting selection will be added to the current selection if one exists. 
                arcpy.SelectLayerByAttribute_management("Templayer", "ADD_TO_SELECTION", query)
                
            # Write the selected features to a new feature class
            arcpy.CopyFeatures_management("Templayer", "feature_result_new.shp")
            
            # Use DeleteIdentical to remove duplicates based on specified fields
            arcpy.management.DeleteIdentical("feature_result_new.shp", ["lotplan"])
            
            # Delete the temporary feature layer
            arcpy.Delete_management("Templayer")
            
            os.chdir(os.path.join(root_dir, folder))
            gdf = gpd.read_file("feature_result_new.shp")
            arcpy.Delete_management("feature_result_new.shp")
            gdf_new = gdf[['geometry', 'LOT', 'PLAN', 'LOTPLAN']]
            gdf_new_copy = gdf_new.copy()
            gdf_new_copy['LGA_NAME'] = [folder] * len(gdf_new_copy)
                        
            gdf_new_copy.to_file("NEW" + file.replace("csv", "shp"))