Import Data from a file or MySql

Jul 12, 2015 at 8:38 PM
Hi all,

I am trying to develop an application which will forecast prices of agricultural products.

I have made the connection between C# and MySql. (Actually i can load data from MySql and save them in a listbox)
I also have made the connection between C# and R through R.NET

but now, in order to perform some statistical analysis and charts i need your help to create a Chart by loading the two values from the listbox OR direct from my database.
The two values are Date,AnnualPrice in my db table.
string constring = "datasource=localhost;port=3306;username=root;password=;";//connect to server
            MySqlConnection conDataBase = new MySqlConnection(constring);
            MySqlCommand cmdDataBase = new MySqlCommand("Select Date,AnnualPrice from db_og.historicprices;", conDataBase);

try
            {
                conDataBase.Open();
                MySqlDataReader myReader = cmdDataBase.ExecuteReader();
                while (myReader.Read())
                {
                   
                    DataPrices DataPrice = new DataPrices();

                   

                    ListViewItem item = new ListViewItem(myReader["Date"].ToString());
                    item.SubItems.Add(myReader["AnnualPrice"].ToString());

                    listView1.Items.Add(item);
                    engine.SetSymbol("plot1", listView1);
                    engine.Evaluate("plot(item$Date, item$AnnualPrice)");
                }//end while   

            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);

            }
            
Developer
Jul 15, 2015 at 8:45 AM
You must convert your data in C# from arrays to simpler array formats; R.NET cannot magically figure out what a ListView of other very specific constructs. Even arrays of DateTime have pitfalls and ambiguities preventing reliable conversion all the time (calendars; time zones, daylight savings, and so on)

To give an example, based on what your data seems to be:

int[] daysSinceStart;
double[] values;
int startyear, startmonth, startday;


engine.SetSymbol("startyear" , engine.CreateIntegerVector(new[]{startyear } ));
engine.SetSymbol("startmonth", engine.CreateIntegerVector(new[]{startmonth} ));
engine.SetSymbol("startday"  , engine.CreateIntegerVector(new[]{startday  } ));
engine.SetSymbol("daysSinceOrigin", engine.CreateIntegerVector(daysSinceStart));
engine.SetSymbol("dvalues", engine.CreateNumericVector(values));

engine.Evaluate(@"origin <- ISOdate(startYear, startMonth, startDay, tz='UTC')
library(lubridate)
myDates <- origin + days(daysSinceOrigin)
# something that builds a time series, e.g. x <- xts(...)
");
engine.Evaluate("plot.zoo(x)");
Jul 15, 2015 at 1:28 PM
Hi may i ask you a question

my data are type DateTime and double

Date Price
112/9/1990 15.30
12/9/1990 15.30
12/9/1990 15.30
12/9/1990 15.30

what if i save them in a 2D ArrayList
ArrayList DataAnnualPrice = new ArrayList();
then Create a Nummeric Matrix
var v = engine.CreateNumericMatrix(DataAnnualPrice );
ngine.SetSymbol("v",  v );
engine.Evaluate("require('ggplot2')");
    engine.Evaluate("library('ggplot2')");
    engine.Evaluate("my_data <- data.frame(v)");
engine.Evaluate("myChart <- ggplot(my_data, aes(x=Price, y=Date));
        engine.Evaluate("print(myChart)");