import geopandas as gpd
import os
import pandas as pd

# Specify the full file paths
shapefile_path = os.path.abspath(r'C:\CSVT\Core 2\NO_B19 - Base Year Networks - Zoning Data.shp')
csv_path = os.path.abspath(r'C:\CSVT\Core 2\NRTM DMC 2061xlsx_4.1.3.csv')

# Check file existence
if not os.path.exists(shapefile_path):
 raise FileNotFoundError(f"Shapefile not found at: {shapefile_path}")

if not os.path.exists(csv_path):
 raise FileNotFoundError(f"CSV file not found at: {csv_path}")

# Read the shapefile and CSV file into GeoPandas GeoDataFrames
shapefile = gpd.read_file(shapefile_path)
csv_data = gpd.read_file(csv_path)

# Print unique values in all columns
for column in csv_data.columns:
 unique_values = csv_data[column].unique()
 print(f"Unique values in '{column}' column:", unique_values)

# Exclude columns with geometric data from conversion
non_geometric_columns = [col for col in csv_data.columns if csv_data[col].dtype != 'geometry']
csv_data[non_geometric_columns] = csv_data[non_geometric_columns].apply(pd.to_numeric, errors='coerce')

# Drop rows with NaN values (non-convertible)
csv_data = csv_data.dropna()

# Print the data types of the columns after conversion
print("CSV Data Data Types (after conversion):", csv_data.dtypes)

# Convert the common attribute to the same data type (e.g., object) in both GeoDataFrames
common_attribute = 'ZoneNo' # Replace with the actual common attribute
shapefile[common_attribute] = shapefile[common_attribute].astype(str)
csv_data[common_attribute] = csv_data[common_attribute].astype(str)

# Perform the merge based on the common attribute
merged_data = shapefile.merge(csv_data, on=common_attribute)

# Set 'geometry_x' as the active geometry column
merged_data = merged_data.set_geometry('geometry_x')

# Save the merged data as a new shapefile with the specified output path
output_path = os.path.abspath(r'C:\CSVT\Core 2\output_shapefile.shp')
merged_data.to_file(output_path)


****The python console with error I recieved****

# Python Console
# Use iface to access QGIS API interface or type help(iface) for more info
# Security warning: typing commands from an untrusted source can harm your computer
exec(Path('C:/Users/INSE06~1/AppData/Local/Temp/tmp0qdd6i8u.py').read_text())
Unique values in 'ZoneNo' column: ['10001' '10002' '10003' '51143' '51144' '51145']
Unique values in 'Sim_Buf' column: ['Simulation' 'Buffer']
Unique values in 'ZoneType' column: ['Geographic Zone' 'Special Generator']
Unique values in 'SpGenType' column: ['' 'NotApplicable' 'Airport' 'Port']
Unique values in 'ZoneDesc' column: ['' 'Sellafield' 'NuGen' 'Newcastle Intl Airport' 'Tyne Port'
 'Metrocentre' 'Teesside Park' 'Teesport' 'Metro Park West'
 'Durham Tees Valley Airport']
Unique values in 'SectorNo' column: ['7' '8' '10' '11' '9' '5' '1' '2' '3' '4' '6' '12' '13']
Unique values in 'MegaSctrNo' column: ['7' '8' '10' '11' '9' '5' '1' '2' '3' '4' '6' '12' '13']
Unique values in 'AM_Tot_Arr' column: ['56.27' '466.48' '147.39' '106.87' '125.98' '85.32']
Unique values in 'AM_Tot_Dep' column: ['600.99' '368.37' '272.04' '141.43' '158.58' '78.91']
Unique values in 'AM_Tot_Tot' column: ['657.26' '834.85' '419.43' '248.3' '284.56' '164.23']
Unique values in 'IP_Tot_Arr' column: ['54.8' '472.41' '143.3' '97.25' '128.47' '68.08']
Unique values in 'IP_Tot_Dep' column: ['602.97' '358.94' '251.73' '110.24' '125.89' '78.42']
Unique values in 'IP_Tot_Tot' column: ['657.77' '831.35' '395.03' '776.97' '207.49' '254.36']
Unique values in 'PM_Tot_Arr' column: ['56.4' '463.32' '152.24' '105.93' '156.89' '76.32']
Unique values in 'PM_Tot_Dep' column: ['527.46' '374.41' '232.95' '108.43' '132.38' '92.16']
Unique values in 'PM_Tot_Tot' column: ['583.86' '837.73' '385.19' '214.36' '289.27' '168.48']
Unique values in '12_Tot_Arr' column: ['666.81' '5623.86' '1758.69' '1221.9' '1619.43' '893.4']
Unique values in '12_Tot_Dep' column: ['7003.17' '4381.98' '3025.35' '1411.02' '1628.22' '983.73']
Unique values in '12_Tot_Tot' column: ['7669.98' '10005.84' '4784.04' '2632.92' '3247.65' '1877.13']
Unique values in 'AM_HDV_Arr' column: ['7.86' '101.46' '22.2' '95.18' '29.15' '9.85']
Unique values in 'AM_HDV_Dep' column: ['101.81' '54.51' '82.45' '71.47' '12.97' '10.52']
Unique values in 'AM_HDV_Tot' column: ['109.67' '155.97' '104.65' '61.67' '14.96' '20.37']
Unique values in 'IP_HDV_Arr' column: ['8.17' '104.86' '22.9' '29.72' '2.12' '8.53']
Unique values in 'IP_HDV_Dep' column: ['96.61' '52.24' '78.89' '68.57' '10.84' '9.97']
Unique values in 'IP_HDV_Tot' column: ['104.78' '157.1' '101.79' '56.47' '12.96' '18.5']
Unique values in 'PM_HDV_Arr' column: ['4.72' '59.89' '13.15' '2.15' '59.55' '16.61']
Unique values in 'PM_HDV_Dep' column: ['55.22' '29.96' '45.1' '13.77' '39.94' '5.83']
Unique values in 'PM_HDV_Tot' column: ['50.43' '27.36' '41.19' '12.86' '37.31' '5.45']
Unique values in '12_HDV_Arr' column: ['86.76' '1113.21' '243.45' '315.6' '22.32' '95.82']
Unique values in '12_HDV_Dep' column: ['1050.75' '566.85' '855.99' '303.3' '745.65' '109.2']
Unique values in '12_HDV_Tot' column: ['1137.51' '1680.06' '1099.44' '616.56' '143.76' '205.02']
Unique values in 'geometry' column: <GeometryArray>
[]
Length: 0, dtype: geometry
Traceback (most recent call last):
 File "C:\PROGRA~1\QGIS32~1.4\apps\Python39\lib\code.py", line 90, in runcode
 exec(code, self.locals)
 File "<input>", line 1, in <module>
 File "<string>", line 26, in <module>
 File "<string>", line 26, in <listcomp>
TypeError: data type 'geometry' not understood