fio-l1-nyc-csvwkt-addresses

Rerun

# /// script
# requires-python = ">=3.12"
# dependencies = [
#     "pandas",
#     "geopandas",
#     "shapely",
#     "pyarrow",
#     "pyproj"
# ]
# ///

import pandas as pd
import geopandas as gpd
from shapely import wkt
import pyarrow as pa
import pyarrow.parquet as pq

def convert_to_geoparquet(input_csv, output_parquet):
    # Load CSV
    df = pd.read_csv(input_csv)

    # Convert geometry_wkt to shapely objects and create GeoDataFrame
    df['geometry'] = df['geometry_wkt'].apply(wkt.loads)
    gdf = gpd.GeoDataFrame(df, geometry='geometry', crs="EPSG:4326")

    # Drop the original WKT column as it is now redundant and we want a proper GeoParquet
    gdf = gdf.drop(columns=['geometry_wkt'])

    # Convert recorded_at to datetime (timestamp[us])
    gdf['recorded_at'] = pd.to_datetime(gdf['recorded_at'])

    # Convert unit_count to int32
    gdf['unit_count'] = gdf['unit_count'].astype('int32')

    # Convert all other columns (except geometry and the typed ones) to string
    # Identify columns to be strings
    typed_cols = ['geometry', 'recorded_at', 'unit_count']
    for col in gdf.columns:
        if col not in typed_cols:
            gdf[col] = gdf[col].astype(str)

    # Ensure correct schema for Parquet via PyArrow to be strict
    # We'll use GeoParquet specification: geometry is a special column.
    # In GeoPandas, writing to parquet usually handles this if pyarrow is installed.
    
    # Reorder columns to ensure geometry is handled correctly if needed, 
    # but GeoPandas write_parquet handles it.
    
    gdf.to_parquet(output_parquet, engine='pyarrow', index=False)
    print(f"Successfully converted {input_csv} to {output_parquet}")

if __name__ == "__main__":
    convert_to_geoparquet('nyc_addresses.csv', 'addresses.geoparquet')

# /// script
# requires-python = ">=3.12"
# dependencies = [
#     "pandas",
#     "geopandas",
#     "shapely",
#     "pyarrow",
#     "pyproj"
# ]
# ///

import pandas as pd
import geopandas as gpd
from shapely import wkt
import pyarrow as pa
import pyarrow.parquet as pq

def convert_to_geoparquet(input_csv, output_parquet):
    # Load CSV
    df = pd.read_csv(input_csv)

    # Convert geometry_wkt to shapely objects
    df['geometry'] = df['geometry_wkt'].apply(wkt.loads)
    
    # Prepare the GeoDataFrame
    # Note: we MUST ensure the columns are of the requested types before creating/saving
    
    # 1. Convert recorded_at to datetime
    df['recorded_at'] = pd.to_datetime(df['recorded_at'])
    
    # 2. Convert unit_count to int32
    df['unit_count'] = df['unit_count'].astype('int32')
    
    # 3. Convert all other columns (excluding geometry_wkt, geometry, recorded_at, unit_count) to string
    # Actually, the prompt says "all other columns as string". 
    # This includes columns that were originally in the CSV.
    
    # Let's identify the columns we want to keep and their types.
    # The columns in CSV: "id","country","postcode","street","number","unit","postal_city","recorded_at","unit_count","geometry_wkt"
    
    # The target columns are:
    # - geometry (point, EPSG:4326)
    # - recorded_at (timestamp[us])
    # - unit_count (int32)
    # - everything else (string)
    
    # First, create the GeoDataFrame from the dataframe with geometry
    gdf = gpd.GeoDataFrame(df, geometry='geometry', crs="EPSG:4326")
    
    # Drop the original WKT column
    gdf = gdf.drop(columns=['geometry_wkt'])
    
    # Get the list of columns in gdf
    cols = gdf.columns.tolist()
    
    # We want to force specific types. 
    # Geometry is handled by GeoPandas.
    # recorded_at and unit_count are already set.
    # The rest should be string.
    
    for col in cols:
        if col == 'geometry':
            continue
        elif col == 'recorded_at':
            # Force timestamp[us] if possible, though pandas handles this via datetime64[ns]
            # Parquet/PyArrow will convert datetime64[ns] to timestamp[us] or [ns] 
            # depending on the engine. To be safe with 'timestamp[us]', we can use pyarrow.
            continue
        elif col == 'unit_count':
            continue
        else:
            gdf[col] = gdf[col].astype(str)
            
    # Now, let's refine the schema explicitly using pyarrow for the save to be certain.
    # However, gdf.to_parquet typically respects the pandas dtypes.
    
    # Check types
    print("Column dtypes before saving:")
    print(gdf.dtypes)

    gdf.to_parquet(output_parquet, engine='pyarrow', index=False)
    print(f"Successfully converted {input_csv} to {output_parquet}")

if __name__ == "__main__":
    convert_to_geoparquet('nyc_addresses.csv', 'addresses.geoparquet')

# /// script
# requires-python = ">=3.12"
# dependencies = [
#     "pandas",
#     "geopandas",
#     "shapely",
#     "pyarrow",
#     "pyproj"
# ]
# ///

import pandas as pd
import geopandas as gpd
from shapely import wkt
import pyarrow as pa
import pyarrow.parquet as pq

def convert_to_geoparquet(input_csv, output_parquet):
    # Load CSV
    df = pd.read_csv(input_csv)

    # 1. Convert recorded_at to datetime64[us]
    df['recorded_at'] = pd.to_datetime(df['recorded_at'], utc=True).dt.floor('us')

    # 2. Convert unit_count to int32
    df['unit_count'] = df['unit_count'].astype('int32')

    # 3. Convert all other non-geometry, non-date, non-int columns to string
    # Important: the prompt says "all other columns as string". 
    # This includes 'id', 'country', 'postcode', 'street', 'number', 'unit', 'postal_city'.
    # We'll exclude 'geometry_wkt' as it will be replaced.
    cols_to_stringify = ['id', 'country', 'postcode', 'street', 'number', 'unit', 'postal_city']
    for col in cols_to_stringify:
        if col in df.columns:
            df[col] = df[col].astype(str)

    # 4. Handle geometry
    df['geometry'] = df['geometry_wkt'].apply(wkt.loads)
    gdf = gpd.GeoDataFrame(df, geometry='geometry', crs="EPSG:4326")
    
    # Drop the old WKT column
    gdf = gdf.drop(columns=['geometry_wkt'])

    # Now let's ensure the Parquet schema is exactly as requested using pyarrow.
    # We'll construct the pyarrow table manually to guarantee the types.
    
    # Define schema
    # recorded_at -> timestamp[us]
    # unit_count -> int32
    # others -> string
    # geometry -> handled by geo own logic?
    # GeoParquet requires the geometry column to be present.
    
    # Let's use gdf.to_parquet first and see if it works.
    # If we want to be absolutely sure about timestamp[us], we can cast the table.
    
    table = pa.Table.from_pandas(gdf, preserve_index=False)
    
    # We can check the schema of the table
    print("Current Table Schema:")
    print(table.schema)

    # Write the table to parquet
    # For GeoParquet, we should use the geopandas way to ensure metadata is correct
    gdf.to_parquet(output_parquet, engine='pyarrow', index=False)
    
    print(f"Successfully converted {input_csv} to {output_parquet}")

if __name__ == "__main__":
    convert_to_geoparquet('nyc_addresses.csv', 'addresses.geoparquet')