Defining sets and parameters from .csv file

I have a CSV file that has an index (hours) and two parameters (consumption and insolation). I want AMPL to create another index Day that consists of 24 consecutive hours of each parameter date so I can find the max value of each parameter for each day. I've tried two ways:

#.mod
set Hour;
set Day;
param consumption{Hour} >=0;
let DAYS := ceil(card(HOURS)/24); 
param max_consumption:= max{i in Hour} consumption[i];
#.run
model csvtest.mod;
table hourpower IN "amplcsv" "testload.csv": Hour<-[Hour], consumption, insolation;
read table hourpower;
display max_consumption, Hour;

This works to display the max consumption and does correctly calculate the number of days, but doesn't store values.

The second way I tried was to add a column called days to the CSV file, but when I tried to make the table, I got a syntax error

set Hour;
set Day;
table hourpower IN "amplcsv" "testload.csv": Day<-[Day, Hour<-[Hour], consumption, insolation;

It seems like AMPL won't let me use the brackets to define two indices.

I'm trying to get to a point where I can have a parameter that stores each max value for each day like:

daily_insolation_max[i,j]
daily_consumption_max[i,j]

Where each of these stores a max value for each day depending on how many hours of data there are.

When I try to upload the csv to ampl this is the error I get: context: table hourpour IN "amplcsv" "testload.csv": Day <-[Day], >>> Hour <<< <-[Hour], consumption; csv snipping is here: Day Hour consumption insolation 1 1 3358.3 0 1 2 3628.7 10 1 3 4837.1 35 2 1 3016.4 0 2 2 3723.1 13 2 3 4443.1 40
Day and Hour are the indicies consumption and insolation are the parameters

I feel like this shouldn't be too difficult, but I've been trying all day so any help would be greatly appreciated. EDIT: Using python to extract data works (added a line to strip any invisible spaces from column). I want to confirm the now-defined parameters can be used in ampl. Python code below includes additional parameters and ampl .mod file has a total cost calculation I want to use as a test

import pandas as pd
from amplpy import AMPL

file_path = r"C:\Users\mcaba\Dissertation model\data.csv"  # replace with your actual file path
data = pd.read_csv(file_path, sep=',') # using tab as separator
data.columns = data.columns.str.strip()
print(type(data))
print(data)
days = sorted(data['day'].unique().tolist())
hours = sorted(data['hour'].unique().tolist())

consumption_data = {}
insolation_data = {}
for _, row in data.iterrows():
    day, hour, consumption, insolation = row['day'], row['hour'], row['consumption'], row['insolation']
    consumption_data[day,hour] = consumption
    insolation_data[day,hour] = insolation

max_consumption = {}
max_insolation = {}
sum_consumption = {}
for day in days:
    daily_consumption = [consumption_data[(day, hour)] for hour in hours if (day, hour) in consumption_data]
    daily_insolation = [insolation_data[(day, hour)] for hour in hours if (day, hour) in insolation_data]
    #daily_grid_purchase
    # Store the max values for consumption and insolation for each day
    max_consumption[day] = max(daily_consumption)
    max_insolation[day] = max(daily_insolation)
    sum_consumption[day] = sum(daily_consumption)

print(f"Total Cost: {total_cost}")
# Output the max values for each day
print("Max Consumption per Day:", max_consumption)
print("Max Insolation per Day:", max_insolation)
print("Total Consumption per Day:", sum_consumption)
ampl = AMPL()
ampl.read(r"C:\Users\mcaba\Dissertation model\model.mod")

ampl.set['DAYS'] = days
ampl.set['HOURS'] = hours
ampl.param['consumption'] = consumption_data
ampl.param['insolation'] = insolation_data

ampl.display('consumption, insolation')
ampl.param['max_consumption'] = max_consumption
ampl.param['max_insolation'] = max_insolation
ampl.param['sum_consumption'] = sum_consumption
ampl.param['P_cost'] = total_cost
ampl.display('max_consumption')
ampl.display('max_insolation')
ampl.display('sum_consumption')

AMPL code with total cost parameter

  set DAYS;
  set HOURS;
  param consumption{DAYS, HOURS};
  param insolation{DAYS, HOURS};
  param max_consumption{DAYS};
  param max_insolation{DAYS};
  param sum_consumption{DAYS};
 
  param Total_cost;
  

 Total_Cost:= 400*sum{d in DAYS}max_insolation-(200*sum{d in DAYS}sum_consumption + 300*sum{d in DAYS}max_consumption);

I tried running it but python returned a nameError: anme 'total cost' is not defined.

Solution

If possible, I'd rather go with the Python api for Ampl. There it's much easier to read csv or do data/output manipulation.

Assuming your model has

    set DAYS;
    set HOURS;
    param consumption{DAYS, HOURS};
    param insolation{DAYS, HOURS};

The following code works:

import pandas as pd
from amplpy import AMPL

file_path = "tmp.csv"  # replace with your actual file path
data = pd.read_csv(file_path, sep='\t') # using tab as separator

print(type(data))
print(data)
days = sorted(data['Day'].unique().tolist())
hours = sorted(data['Hour'].unique().tolist())

consumption_data = {}
insolation_data = {}
for _, row in data.iterrows():
    day, hour, consumption, insolation = row['Day'], row['Hour'], row['consumption'], row['insolation']
    consumption_data[day,hour] = consumption
    insolation_data[day,hour] = insolation

ampl = AMPL()
ampl.read("model.mod")

ampl.set['DAYS'] = days
ampl.set['HOURS'] = hours
ampl.param['consumption'] = consumption_data
ampl.param['insolation'] = insolation_data

ampl.display('consumption, insolation')

Writing to csv is also easier that way rather than using ampl's table statement.

consumption_max = ampl.param['consumption_max'].to_dict()
consumption_max_df = pd.DataFrame(consumption_max)
consumption_max_df.to_csv(output_file, index=False)