private static Dictionary<string, Dictionary<string, double>> ExtractData(FileInfo f)
{
List<string> headers = new List<string>();
List<string> data = new List<string>();
Dictionary<string, Dictionary<string, double>> output = new Dictionary<string, Dictionary<string, double>>();
using (CsvReader r = new CsvReader(f.FullName))
{
data = r.ParseRecord();
for (int i = 1; i < data.Count - 1; i++)
{
headers.Add(data[i]);
}
while (!r.EndOfStream)
{
data = r.ParseRecord();
output.Add(data[0], new Dictionary<string, double>());
for (int i = 1; i < headers.Count; i++)
{
output[data[0]].Add(headers[i], double.Parse(data[i]));
}
}
}
return output;
}
private static List<List<string>> GetData(string file)
{
List<List<string>> ret = new List<List<string>>();
using (CsvReader r = new CsvReader(file))
{
while (!r.EndOfStream)
{
List<string> data = r.ParseRecord();
double result = new double();
ret.Add(data.Where(x => x != "" && !double.TryParse(x, out result)).ToList<string>());
_Totals.Add(result);
}
}
return ret;
}
private static void WriteIntensities(int year)
{
List<List<object>> o = new List<List<object>>();
using (CsvReader r = new CsvReader(string.Format(@"E:\Dropbox\matlabFiles\VariableRecords\IOModel{0}.csv", year)))
{
int i = 0;
while (!r.EndOfStream)
{
o.Add(new List<object>());
List<string> thing = r.ParseRecord();
o[i].Add(year);
foreach (string t in thing)
{
o[i].Add(t);
}
i++;
}
}
DB.LoadToTable("Intensity", o);
}
private static void CreateModel(object year)
{
Dictionary<string, Dictionary<string, double>> ASic = new Dictionary<string, Dictionary<string, double>>();
Dictionary<string, Dictionary<string, string>> description = new Dictionary<string, Dictionary<string, string>>();
List<string> sicNasty = new List<string>();
List<string> headers = new List<string>();
using (CsvReader r = new CsvReader(string.Format(@"E:\Dropbox\IO Model source data\A{0}.csv", year)))
{
foreach (string s in r.ParseRecord())
{
if (s.Trim() != "")
{
headers.Add(s.Trim());
ASic.Add(s.Trim(), new Dictionary<string, double>());
}
}
r.ParseRecord();
description.Add("A", new Dictionary<string, string>());
while (!r.EndOfStream)
{
List<string> records = new List<string>();
foreach (string s in r.ParseRecord())
{
records.Add(s.Trim());
}
sicNasty.Add(records[0].Trim());
description["A"].Add(records[0], records[1]);
for (int i = 2; i < records.Count - 1; i++)
{
string thing = headers[i - 2];
thing = records[i];
thing = records[0];
Dictionary<string, double> t = ASic[thing];
// We want the proportion of each element in each row divided by the row total.
ASic[records[0]].Add(headers[i - 2], double.Parse(records[i] == "" ? "0" : records[i]));
}
}
}
List<List<object>> a = new List<List<object>>();
int j = 0;
foreach (string s in ASic.Keys)
{
foreach (string v in ASic[s].Keys)
{
a.Add(new List<object>());
a[j].Add(year);
a[j].Add(s);
a[j].Add(v);
a[j++].Add(ASic[s][v]);
}
}
List<List<object>> aHeaders = new List<List<object>>();
j = 0;
foreach (List<object> o in a)
{
if (!ContainsObject(aHeaders, o[0]))
{
aHeaders.Add(new List<object>());
aHeaders[j++].Add(o[0]);
}
}
List<List<object>> b = new List<List<object>>();
using (CsvReader r = new CsvReader(string.Format(@"E:\Dropbox\IO Model source data\B{0}.csv", year)))
{
int k = 0;
r.ParseRecord();
description.Add("B", new Dictionary<string, string>());
while (!r.EndOfStream)
{
List<string> records = r.ParseRecord();
sicNasty.Add(records[0]);
b.Add(new List<object>());
b[k].Add(year);
b[k].Add(records[0].Replace("\r", ""));
b[k++].Add(records[2].Replace("\r", ""));
if (!description["A"].ContainsKey(records[0]))
{
description["B"].Add(records[0], records[1]);
}
if (!headers.Contains(records[0]))
{
headers.Add(records[0]);
}
}
}
List<List<object>> desc = new List<List<object>>();
using (CsvReader r = new CsvReader(@"E:\Dropbox\IO Model source data\Description.csv"))
{
int k = 0;
r.ParseRecord();
while (!r.EndOfStream)
{
List<string> records = r.ParseRecord();
sicNasty.Add(records[0]);
desc.Add(new List<object>());
desc[k].Add(records[0].Replace("\r", ""));
desc[k++].Add(records[1].Replace("\r", ""));
}
}
List<List<object>> abMap = new List<List<object>>();
using (CsvReader r = new CsvReader(@"E:\Dropbox\IO Model source data\ABMap.csv"))
{
r.ParseRecord();// Skip column headers.
j = 0;
while (!r.EndOfStream)
{
List<string> record = r.ParseRecord();
if (!IsNull(record))
{
abMap.Add(new List<object>());
for (int i = 0; i < record.Count; i++)
{
abMap[j].Add(record[i].Replace("\r", ""));
}
j++;
}
}
}
List<List<object>> f = new List<List<object>>();
using (CsvReader r = new CsvReader(string.Format(@"E:\Dropbox\IO Model source data\F{0}.csv", year)))
{
r.ParseRecord();
j = 0;
while (!r.EndOfStream)
{
List<string> record = r.ParseRecord();
f.Add(new List<object>());
f[j].Add(year);
for (int i = 0; i < record.Count; i++)
{
f[j].Add(record[i].Replace("\r", ""));
}
j++;
}
}
List<List<object>> d = new List<List<object>>();
int kay = new int();
foreach (string jay in description.Keys)
{
foreach (string key in description[jay].Keys)
{
d.Add(new List<object>());
d[kay].Add(key);
d[kay].Add(description[jay][key].Replace("'", "''"));
d[kay++].Add(jay);
}
}
List<List<object>> map = new List<List<object>>();
using (CsvReader r = new CsvReader(@"E:\Dropbox\IO Model source data\IOModel_c76.csv"))
{
r.ParseRecord();
j = 0;
while (!r.EndOfStream)
{
List<string> records = r.ParseRecord();
foreach (string s in records[1].Split(','))
{
map.Add(new List<object>());
map[j].Add(records[0]);
map[j++].Add(s.Replace("\r", ""));
}
}
}
if (Restart)
{
DB.CreateDatabase("IOModel", @"E:\SQL server Data\", @"D:\SQL logs\");
DB.RunFile("IOModel", new FileInfo(@"E:\GitHub\Research\SQL\IOModel\000-Tables.sql"));
DB.LoadToTable("Category", desc);
DB.LoadToTable("SicDescription", d);
DB.LoadToTable("ABMap", abMap);
DB.LoadToTable("IOModel_Censa76", map);
List<List<object>> y = new List<List<object>>();
for (int i = 1997; i < 2012; i++)
{
y.Add(new List<object>());
y[i - 1997].Add(i);
}
DB.LoadToTable("ModelYear", y);
Restart = false;
}
DB.LoadToTable("A", a);
DB.LoadToTable("B", b);
DB.LoadToTable("F", f);
}
private static Dictionary<string, string> GetClasSystem(FileInfo f, int sysId, int description)
{
Dictionary<string, string> data = new Dictionary<string, string>();
using (CsvReader r = new CsvReader(f.FullName))
{
List<string> record = new List<string>();
// Skip header row.
if (string.IsNullOrEmpty(_Sys0))
{
_Sys0 = r.ParseRecord()[description];
}
else
{
_Sys1 = r.ParseRecord()[description];
}
while (!r.EndOfStream)
{
record = r.ParseRecord();
if (!string.IsNullOrEmpty(record[description]))
{
data.Add(record[sysId], record[description]);
}
}
}
return data;
}