public void TestMethod1()
{
string fn = Path.Combine(TestData.DataPath, "SimpleResponseFunction.xls");
var diversion = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "Input");
diversion.Read();
var expected = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "ExpectedResponse");
expected.Read();
var responseFunction = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "ResponseFunction");
responseFunction.Read();
Console.WriteLine(" responseFunction has " + responseFunction.Count + " points");
Console.WriteLine("diversion has " + diversion.Count);
Series lag = Math.RoutingWithLags(diversion, responseFunction.Values);
Assert.AreEqual(7, lag.Count);
lag.WriteToConsole();
if (lag.Count != expected.Count)
Console.WriteLine("Error: expected count not the same as lag count");
double sumDiff = System.Math.Abs(Math.Sum(expected - lag));
Console.WriteLine("Sum of difference = " + sumDiff);
Assert.AreEqual(0,sumDiff,0.0000001);
}
public void TestMethod1()
{
string fn = Path.Combine(TestData.DataPath, "SimpleResponseFunction.xls");
var diversion = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "Input");
diversion.Read();
var expected = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "ExpectedResponse");
expected.Read();
var responseFunction = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "ResponseFunction");
responseFunction.Read();
Console.WriteLine(" responseFunction has " + responseFunction.Count + " points");
Console.WriteLine("diversion has " + diversion.Count);
Series lag = Math.RoutingWithLags(diversion, responseFunction.Values);
Assert.AreEqual(7, lag.Count);
lag.WriteToConsole();
if (lag.Count != expected.Count)
{
Console.WriteLine("Error: expected count not the same as lag count");
}
double sumDiff = System.Math.Abs(Math.Sum(expected - lag));
Console.WriteLine("Sum of difference = " + sumDiff);
Assert.AreEqual(0, sumDiff, 0.0000001);
}
public void PartialDay()
{
string fn = Path.Combine(TestData.DataPath, "wilson.xls");
var s = new ExcelDataReaderSeries(fn, "wilson", "A", "B");
s.Read();
Series avg = Math.TimeWeightedDailyAverage(s);
}
private void AddExcelSeries(out Series s, out int c, out int sdi)
{
var fn = excelFileName;
s = new ExcelDataReaderSeries(fn, "NoDuplicates", "Date Sampled", "Field Temp C");
s.Read();
c = s.Count;
Console.WriteLine("Count = " + c);
Assert.IsTrue(s.Count > 0);
sdi = db.AddSeries(s);
}
public void SevenDay()
{
string fn = Path.Combine(TestData.DataPath, "sevendayavg.xls");
var s = new ExcelDataReaderSeries(fn, "Sheet1", "A", "B");
s.Read();
Assert.AreEqual(2738, s.Count);
var s7 = Math.WeeklyAverageSimple(s);
s7.WriteToConsole();
Assert.AreEqual(DateTime.Parse("2004-02-12 23:59:59.9"), s7[0].DateTime);
Assert.AreEqual(2.17, s7[0].Value, 0.01);
Assert.AreEqual(101.32, s7[1].Value, 0.01);
}
public void EstimateDaily()
{
string fn = TestData.DataPath + "\\EstimateDailyFromMonthly.xlsx";
var daily = new ExcelDataReaderSeries(fn, "daily", "Date", "Value");
daily.Read();
var monthly = new ExcelDataReaderSeries(fn, "monthly", "Date", "Farm_Own");
monthly.Read();
Series s = Math.EstimateDailyFromMonthly(daily, monthly);
}
public void SevenDayMovingInstantSparse()
{
string fn = TestData.DataPath + "\\temp example 7 day max.xls";
var s = new ExcelDataReaderSeries(fn, "sparse", "C", "D");
s.Read();
Series s2 = Reclamation.TimeSeries.Math.SevenDADMAX(s);
Series expected = new ExcelDataReaderSeries(fn, "7dadmax", "A", "B");
expected.Read();
Assert.AreEqual(expected[0].DateTime.Date, s2[0].DateTime.Date);
Assert.AreEqual(PointFlag.Missing, s2[0].Flag); //
}
public void ReadOneYearFiltering()
{
string filename = Path.Combine(TestData.DataPath, "UnregulationUpperSnake.xls");
Assert.IsTrue(File.Exists(filename), "missing file " + filename);
var s = new ExcelDataReaderSeries(filename, "data", "Date", "HEII QD");
s.Read(DateTime.Parse("2005-01-01"), DateTime.Parse("2005-12-31"));
for (int i = 0; i < s.Messages.Count; i++)
{
Console.WriteLine(s.Messages[i]);
}
Assert.AreEqual(365, s.Count, "wrong number of points from " + filename);
}
public void SimpleRead()
{
string filename = Path.Combine(TestData.DataPath, "SpecificationTestData.xls");
Assert.IsTrue(File.Exists(filename), "missing file " + filename);
var s = new ExcelDataReaderSeries(filename, "Sheet1", "Date", "JulianDay");
s.Read();
for (int i = 0; i < s.Messages.Count; i++)
{
Console.WriteLine(s.Messages[i]);
}
Assert.IsTrue(s.Count > 1000, "not enough data in " + filename);
}
private void ReadFromFile(DateTime t1, DateTime t2)
{
this.Clear();
if (!File.Exists(xlsFilename))
{
Logger.WriteLine("Missing file:" + xlsFilename, "ui");
}
var s = new ExcelDataReaderSeries(xlsFilename, sheetName, dateColumn, valueColumn);
s.TimeInterval = Reclamation.TimeSeries.TimeInterval.Daily;
s.Read(t1, t2);
Logger.WriteLine("Read " + s.Count + " items from " + xlsFilename);
this.Add(s);
}
public void HeiseResidualTest()
{
var fn = Path.Combine(TestData.DataPath, "ResidualCalcs.xls");
ExcelDataReaderSeries heii_qu = new ExcelDataReaderSeries(fn, "unregulation", "Date", "HEII_QU", "cfs");
heii_qu.Read();
var heii_resid = Math.WaterYearResidual(heii_qu,7);
heii_resid.WriteToConsole();
var expected = new ExcelDataReaderSeries(fn, "unregulation", "Date", "HEII_RESID", "cfs");
expected.Read();
var diff = Math.Sum(heii_resid - expected);
Assert.IsTrue(System.Math.Abs(diff) < 0.01);
}
public void LargeRead()
{
Performance perf = new Performance();
string filename = Path.Combine(TestData.DataPath, "UnregulationUpperSnake.xls");
Assert.IsTrue(File.Exists(filename), "missing file " + filename);
ExcelDataReaderSeries s = new ExcelDataReaderSeries(filename, "data", "Date", "HEII QD");
s.Read();
for (int i = 0; i < s.Messages.Count; i++)
{
Console.WriteLine(s.Messages[i]);
}
Assert.AreEqual(28942, s.Count, "wrong number of points from " + filename);
perf.Report("large read took "); // 5.7 seconds ExcelOle (full startup)
}
public void SevenDayMovingInstant()
{
string fn = TestData.DataPath + "\\temp example 7 day max.xls";
var s = new ExcelDataReaderSeries(fn, "457373", "C", "D");
s.Read();
Series s2 = Reclamation.TimeSeries.Math.SevenDADMAX(s);
var expected = new ExcelDataReaderSeries(fn, "7dadmax", "A", "B");
expected.Read();
for (int i = 0; i < expected.Count; i++)
{
Assert.AreEqual(expected[i].Value, s2[i].Value, 0.001);
}
}
public void SevenDayMovingDaily()
{
string fn = Path.Combine(TestData.DataPath, "SpecificationTestData.xls");
Series s = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "JulianDay");
Series expected = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "SevenDayMovingAverage");
s.Read();
expected.Read();
Series s2 = Reclamation.TimeSeries.Math.SevenDayMovingAverage(s);
Assert.AreEqual(2798, s2.Count);
expected.RemoveMissing();
Assert.AreEqual(expected.Count, s2.Count);
Series diff = expected - s2;
double d = Reclamation.TimeSeries.Math.Sum(diff);
Assert.AreEqual(0, d, 0.001);
}
public void HeiseResidualTest()
{
var fn = Path.Combine(TestData.DataPath, "ResidualCalcs.xls");
ExcelDataReaderSeries heii_qu = new ExcelDataReaderSeries(fn, "unregulation", "Date", "HEII_QU", "cfs");
heii_qu.Read();
var heii_resid = Math.WaterYearResidual(heii_qu, 7);
heii_resid.WriteToConsole();
var expected = new ExcelDataReaderSeries(fn, "unregulation", "Date", "HEII_RESID", "cfs");
expected.Read();
var diff = Math.Sum(heii_resid - expected);
Assert.IsTrue(System.Math.Abs(diff) < 0.01);
}
public void ConvertToCelcius()
{
var fn = Path.Combine(TestData.DataPath, "CalculationTests.xlsx");
Series s = new ExcelDataReaderSeries(fn, "ConvertToCelcius", "Date", "value");
s.Units = "degrees C";
s.Name = "series1";
CalculationSeries c = new CalculationSeries();
c.Parser.VariableResolver.Add("series1", s);
c.Expression = "5/9*(series1-32)";
c.Read();
// c.WriteToConsole();
c.Clear();
c.Calculate(); //t,t.AddDays(3));
c.WriteToConsole();
Assert.AreEqual(0, c[0].Value, 0.01);
Assert.AreEqual(100, c[1].Value, 0.01);
}
public void MaxMin()
{
string fn = Path.Combine(TestData.DataPath, "temp example 7 day max.xls");
var s = new ExcelDataReaderSeries(fn, "457373", "C", "D");
s.Read();
Series max = Math.DailyMax(s);
max.WriteCsv(@"c:\temp\a.csv");
Assert.AreEqual(7, max[0].DateTime.Day);
Assert.AreEqual(14.68, max[0].Value, 0.01);
Assert.AreEqual(17.21, max["8/5/2004"].Value, 0.01);
Assert.AreEqual(1965.0, max[max.Count - 1].Value, .001);
Series min = Math.DailyMin(s);
Assert.AreEqual(7, min[0].DateTime.Day);
Assert.AreEqual(12.98, min[0].Value, 0.01);
Assert.AreEqual(15.31, min["8/5/2004"].Value, 0.01);
Assert.AreEqual(1965.0, min[min.Count - 1].Value, 0.001);
}
public void SevenDayMovingDaily()
{
string fn = TestData.DataPath + "\\SpecificationTestData.xls";
Series s = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "JulianDay");
Series expected = new ExcelDataReaderSeries(fn, "Sheet1", "Date", "SevenDayMovingAverage");
s.Read();
expected.Read();
Series s2 = Reclamation.TimeSeries.Math.SevenDayMovingAverage(s);
Assert.AreEqual(2798, s2.Count);
expected.RemoveMissing();
Assert.AreEqual(expected.Count, s2.Count);
Series diff = expected - s2;
double d = Reclamation.TimeSeries.Math.Sum(diff);
Assert.AreEqual(0, d, 0.001);
}
public void UnregulatedFlow()
{
var fn = Path.Combine(TestData.DataPath, "CalculationTests.xlsx");
Series af = new ExcelDataReaderSeries(fn, "jck_qu", "Date", "jck af");
Series qd = new ExcelDataReaderSeries(fn, "jck_qu", "Date", "jck qd");
var qu = new CalculationSeries();
qu.Parser.VariableResolver.Add("jck_af", af);
qu.Parser.VariableResolver.Add("jck_qd", qd);
qu.Expression = "(jck_af[t]-jck_af[t-1])/1.98347+jck_qd";
DateTime t1 = DateTime.Parse("12/25/2010");
DateTime t2 = DateTime.Parse("12/27/2010");
qu.Calculate(t1, t2);
qu.WriteToConsole();
Assert.AreEqual(674.39, qu["12/26/2010"].Value, .01);
}
public void MaxMin()
{
string fn = Path.Combine(TestData.DataPath, "temp example 7 day max.xls");
var s = new ExcelDataReaderSeries(fn, "457373", "C", "D");
s.Read();
Series max = Math.DailyMax(s);
max.WriteCsv(@"c:\temp\a.csv");
Assert.AreEqual(7, max[0].DateTime.Day);
Assert.AreEqual(14.68, max[0].Value, 0.01);
Assert.AreEqual(17.21, max["8/5/2004"].Value, 0.01);
Assert.AreEqual(1965.0, max[max.Count - 1].Value, .001);
Series min = Math.DailyMin(s);
Assert.AreEqual(7, min[0].DateTime.Day);
Assert.AreEqual(12.98, min[0].Value, 0.01);
Assert.AreEqual(15.31, min["8/5/2004"].Value, 0.01);
Assert.AreEqual(1965.0, min[min.Count - 1].Value, 0.001);
}
static void Main(string[] args)
{
Console.WriteLine("connecting to database");
var svr = MySqlServer.GetMySqlServer("140.215.104.92", "timeseries", "");
TimeSeriesDatabase db = new TimeSeriesDatabase(svr);
Console.WriteLine("reading site catalog");
var sites = db.GetSiteCatalog();
sites.AddsitecatalogRow("schwind", "Schwind", "CA",
"39.34752", "-121.80084", "", "", "", "", "", 0, "", "", "", "", "", "");
sites.AddsitecatalogRow("cassidy", "Cassidy", "CA",
"39.33243", "-121.78971", "", "", "", "", "", 0, "", "", "", "", "", "");
sites.AddsitecatalogRow("risingriver", "Rising River", "CA",
"39.33097", "-121.76636", "", "", "", "", "", 0, "", "", "", "", "", "");
svr.SaveTable(sites);
Console.WriteLine("reading series catalog");
var series = db.GetSeriesCatalog();
int grayLodge = series.GetOrCreateFolder("water.usbr.gov", "MP", "Gray Lodge");
string fn = @"U:\water.usbr.gov\2016\GrayLodgeData.xlsx";
var s = new ExcelDataReaderSeries(fn, "Rising River", "A", "B", "cfs");
s.Name = "Flow";
s.Table.TableName = "mpgraylodge_instant_rising_" + s.Name;;
s.Read();
// db.AddSeries()
svr.SaveTable(series);
Console.WriteLine();
}
private void ReadFromFile(DateTime t1, DateTime t2)
{
this.Clear();
if (!File.Exists(xlsFilename))
{
Logger.WriteLine("Missing file:" + xlsFilename, "ui");
}
var s = new ExcelDataReaderSeries(xlsFilename, sheetName, dateColumn, valueColumn);
s.TimeInterval = Reclamation.TimeSeries.TimeInterval.Daily;
s.Read(t1, t2);
Logger.WriteLine("Read " + s.Count + " items from " + xlsFilename);
this.Add(s);
}
private void AddExcelSeries(out Series s, out int c, out int sdi)
{
var fn = excelFileName;
s = new ExcelDataReaderSeries(fn, "NoDuplicates", "Date Sampled", "Field Temp C");
s.Read();
c = s.Count;
Console.WriteLine("Count = "+c);
Assert.IsTrue(s.Count > 0);
sdi = db.AddSeries(s);
}
public void SevenDayMovingInstant()
{
string fn = Path.Combine(TestData.DataPath, "temp example 7 day max.xls");
var s = new ExcelDataReaderSeries(fn, "457373", "C", "D");
s.Read();
Series s2 = Reclamation.TimeSeries.Math.SevenDADMAX(s);
var expected = new ExcelDataReaderSeries(fn, "7dadmax", "A", "B");
expected.Read();
for (int i = 0; i < expected.Count; i++)
{
Assert.AreEqual(expected[i].Value, s2[i].Value, 0.001);
}
}
public void SevenDayMovingInstantSparse()
{
string fn = Path.Combine(TestData.DataPath, "temp example 7 day max.xls");
var s = new ExcelDataReaderSeries(fn, "sparse", "C", "D");
s.Read();
Series s2 = Reclamation.TimeSeries.Math.SevenDADMAX(s);
Series expected = new ExcelDataReaderSeries(fn, "7dadmax", "A", "B");
expected.Read();
Assert.AreEqual(expected[0].DateTime.Date, s2[0].DateTime.Date);
Assert.AreEqual(PointFlag.Missing, s2[0].Flag); //
}
public void SevenDay()
{
string fn = Path.Combine(TestData.DataPath, "sevendayavg.xls");
var s = new ExcelDataReaderSeries(fn, "Sheet1", "A", "B");
s.Read();
Assert.AreEqual(2738, s.Count);
var s7 = Math.WeeklyAverageSimple(s);
s7.WriteToConsole();
Assert.AreEqual(DateTime.Parse("2004-02-12 23:59:59.9"), s7[0].DateTime);
Assert.AreEqual(2.17, s7[0].Value, 0.01);
Assert.AreEqual(101.32, s7[1].Value, 0.01);
}
public void PartialDay()
{
string fn = Path.Combine(TestData.DataPath, "wilson.xls");
var s = new ExcelDataReaderSeries(fn, "wilson", "A", "B");
s.Read();
Series avg = Math.TimeWeightedDailyAverage(s);
}
