CODEWITHSUNDEEP
c#excellistoledb
DoIt
DoIt

Reputation: 3438

Counting number times a particular value is repeated in a C# list

I have a list with 7 columns in it with several rows of data which are imported from an Excel sheet using Oledb. Now I wanted to go through the list and from column 1 and check the value in first row of first column and find out how many times it occurs in the list and if it occurs more than once I would like to store all the other column values along with the first column in another list for some operations on it

For Example I have the following rows of data in the list 

Time        CC   Month  prc      str    tx      qq
14:46:00    NP  14-Dec                 4.0000   6,000
14:46:00    LN  14-Dec  6.00    Put     2.0090  1,500
14:46:00    LN  14-Dec  6.00    Call    0.0095  1,500
14:42:57    LN  14-Sep  3.85    Put     0.0860  50
14:42:57    LN  14-Sep  3.85    Call    0.1200  50
14:34:00    LN  14-Sep  3.25    Put     0.0025  200
14:34:00    LN  14-Sep  3.50    Put     0.0100  100
14:32:00    NP  14-Dec                  4.0000  2,000
14:32:00    LN  14-Dec  6.00    Put     2.0090  500
14:32:00    LN  14-Dec  6.00    Call    0.0095  500
14:27:00    LN  15-Mar  4.50    Call    0.2230  100
14:26:00    LN  15-Mar  4.50    Call    0.2210  200

In the above rows of data in the list I have first column as time and in time column first row has 14:46:00, now would like to check for number times 14:46:00 is repeated and insert all the rows and columns associated with 14:46:00 and perform some arithmetic operation on them and move onto next unique value of time that is 14:42:57 and repeat the process

Example List1 for Time 14:46:00 would be as follows

14:46:00    NP  14-Dec                  4.0000  6,000
14:46:00    LN  14-Dec  6.00    Put     2.0090  1,500
14:46:00    LN  14-Dec  6.00    Call    0.0095  1,500

Once Some operation are done on the above list it can be overwritten by next set of values for time 14:42:57

I have imported Data from Excel Sheet to list using following lines of code

var fileName = string.Format("{0}\\AxiomParser.xlsx", Directory.GetCurrentDirectory());

            var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 12.0;", fileName);

            var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$] ", connectionString);
            var ds = new DataSet();

            adapter.Fill(ds, "axiomData");

            DataTable data = ds.Tables["axiomData"];


            List<DataRow> listAxiomData = data.AsEnumerable().ToList();
            List<AxiomDS> resultAxiomData = new List<AxiomDS>();

            foreach (var row in listAxiomData)
            {
                var entries = new AxiomDS();
                entries.time = Convert.ToDateTime(row[0]);
                entries.CC = row[1].ToString();
                entries.term = row[2].ToString();
                if (row[3].ToString() != "")
                {
                    entries.strike = Convert.ToDouble(row[3]);
                }

                entries.strategy = row[4].ToString();

                if (row[5].ToString() != "")
                {
                    entries.price = Convert.ToDouble(row[5]);
                }
                entries.quantity = Convert.ToInt32(row[6]);

                resultAxiomData.Add(entries);
            }

Upvotes: 0

Views: 651

Answers (3)

Clever Neologism
Clever Neologism

Reputation: 1332

Using LINQ-to-objects:

public class Row {
    DateTime Time {get; set;}
    string CC { get; set; }
    string Month {get; set;}
    double PRC {get; set;}
    string Str {get; set;}
    double TX {get; set;}
    int QQ { get; set; }
}

var allEntries = new List<Row>();

//Load data...

var allEntriesDict = allEntries.Select(entry => entry.Time)
                               .Distinct()
                               .ToDictionary(time => time, 
                                             time => allEntries.Where(entry => entry.Time == time).ToList());
foreach(var kvp in allEntriesDictionary) {
    //kvp.Key is the Time
    //kvp.Value is a List<Row>
    DoCalculations(kvp.Value);
}

Upvotes: 0

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107566

It sounds like you want to group your entries by time, and then perform some work on each group that has more than one record. LINQ makes this easy:

var groupedData = from result in resultAxiomData
                  group result by result.time into resultGroup
                  where resultGroup.Count() > 1
                  order by resultGroup.Key
                  select resultGroup;

Now you can loop over the grouped results:

foreach (var timeGroup in groupedData)
{
    // timeGroup.Key is the time
    foreach (var entry in timeGroup)
    {
        // process the entry
    }
}

Upvotes: 1

Jon
Jon

Reputation: 3255

public class AxiomDS
{
    public DateTime time { get; set; }
    public string CC { get; set; }
    public string term { get; set; }
}

public class Program
{
    static void Main(string[] args)
    {
        List<AxiomDS> resultAxiomData = new List<AxiomDS>();

        var uniqueTimes = resultAxiomData.Select(a => a.time).Distinct();

        foreach (var uniqueTime in uniqueTimes)
        {
            // Find all records that have this time
            var recordsToProcess = resultAxiomData.Where(r => r.time == uniqueTime);

            // TODO:
            foreach (var record in recordsToProcess)
            {
                // Do something with this list
            }
        }
    }
}

Upvotes: 1

Related Questions