MATLAB: Need help transforming data from a table

csvdatatransformation

I had transaction data in a .CSV where each instance of a sale is a row of data with sku, quantity, and date are columns.
I want to create a new table with the same three columns, but so that the quantity is the sum of all instances for sales of the specific sku for that specific day. For instance, right now we would see two seperate rows for two sales of sku 1234 on March 13. I want the table to have only one row for that sku while the quantity column reads "2" as it is the sum of all sales for that sku on that day.
Thank you

Best Answer

Zac, if I understand correctly what you want to do, it's way simpler than all that.
I assume you know that your data file is kind of non-standard. It's hard to tell what you intend as what, but I took a guess. I recommend you take a look at detectimportoptions to deal with it, what I've shjown here is quick and dirty but not the most flexible way of reading that file.
>> fmt = '%f%f%s%s%s%s%f%f%{dd-MMM-yy}D%{dd-MMM-yy}D%{dd-MMM-yy}D%s%s%s';
>> t = readtable('sampleDataSet.csv','Format',fmt,'Delimiter','~','ReadVariableNames',false);
>> t = t(:,[3 7 9]);
>> t.Properties.VariableNames = {'SKU' 'Quantity' 'Date'};
>> t.SKU = categorical(t.SKU)
t =
  20×3 table
      SKU      Quantity      Date   
    _______    ________    _________
    9303W          1       18-Mar-13
    2031727        1       30-Apr-13
    2030117        1       30-Apr-13
    2038036        1       30-Apr-13
    2038036        1       30-Apr-13
    2032445        1       30-Apr-13
    2035549        1       30-Apr-13
    2046881        2       30-Apr-13
    38H5           1       26-Apr-13
    52C2           1       26-Apr-13
    53Y8K          1       26-Apr-13
    43Z38          1       26-Apr-13
    9595           1       26-Apr-13
    9595           1       26-Apr-13
    2031510        1       26-Apr-13
    53Y8          15       26-Apr-13
    53Y8K         15       26-Apr-13
    0001           6       26-Apr-13
    1940           1       26-Apr-13
    4001           1       26-Apr-13
Once you're read the file, there are several ways to do the grouped calculation you want, including findgroups/splitapply and groupsummary. The following uses varfun. It's one line:
  >> varfun(@sum,t,'GroupingVariables',{'SKU' 'Date'})
  ans =
  17×4 table
      SKU        Date       GroupCount    sum_Quantity
    _______    _________    __________    ____________
    0001       26-Apr-13        1               6     
    1940       26-Apr-13        1               1     
    2030117    30-Apr-13        1               1     
    2031510    26-Apr-13        1               1     
    2031727    30-Apr-13        1               1     
    2032445    30-Apr-13        1               1     
    2035549    30-Apr-13        1               1     
    2038036    30-Apr-13        2               2     
    2046881    30-Apr-13        1               2     
    38H5       26-Apr-13        1               1     
    4001       26-Apr-13        1               1     
    43Z38      26-Apr-13        1               1     
    52C2       26-Apr-13        1               1     
    53Y8       26-Apr-13        1              15     
    53Y8K      26-Apr-13        2              16     
    9303W      18-Mar-13        1               1     
    9595       26-Apr-13        2               2
Related Question