I need to average the values associated with a date string from a csv file , the format of the csv file is as follows
20/04/2009 -1.271
20/04/2009 0
20/04/2009 0.247
20/04/2009 1.337
20/04/2009 -0.438
21/04/2009 -0.134
21/04/2009 -0.096
21/04/2009 -0.418
21/04/2009 0.362
The value being a float.
The expected result would be a new csv containing
20/04/2009 , 0.025
21/04/2009 , 0.071
The language is java , and I am using the openCsv api.
I would like help in understanding how I would go down this large CSV file , and group the dates in a temporary collections so as to average them, and assign that average with the correct date.
This would be a one time operation.
public class SentimentScore {
private DateTime time;
private double score;
public SentimentScore(DateTime t, double s) {
time=t;
score=s;
}
public DateTime getTime() {
return time;
}
public double getScore() {
return score;
}
the rows are stored in the above class
public class Processor {
private String pathToFile = "C:\\Data\\scores.csv";
private List<SentimentScore> scores;
CSVReader reader;
DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
public Processor() {
}
public void loadScores() throws FileNotFoundException, IOException, ParseException {
reader = new CSVReader(new FileReader(pathToFile));
String[] record = null;
while ((record = reader.readNext()) != null) {
String dateString = record[0];
Date date = formatter.parse(dateString);
DateTime correctDate = new DateTime(date);
double value = Double.parseDouble(record[1]);
SentimentScore score = new SentimentScore(correctDate, value);
scores.add(score);
}
}
public void calculateAverages()
{
List<SentimentScore> newScores = new ArrayList<>();
Iterator scoreIterator = scores.iterator();
SentimentScore temp = (SentimentScore) scoreIterator.next();
DateTime currentDate = temp.getTime();
while(scoreIterator.hasNext())
{
SentimentScore currentRow = (SentimentScore) scoreIterator.next();
if(currentRow.getTime().equals(currentDate))
{
List<Double> values = new ArrayList<>();
values.add(currentRow.getScore());
double sum = 0;
for (Double value : values)
{
sum = sum + value;
}
double avg = sum/values.size();
SentimentScore averagedScore = new SentimentScore(currentRow.getTime(),avg);
newScores.add(averagedScore);
}
}
}
}
What I have tried:
I have tried the above code , but i think i am lost and would appreciate some help.