Convert timetable with some mixed text representing numbers to all numeric for retime function

The sample timetable below needs to be aggregated hourly with the retime function. The text numbers which sometimes appear in my data feed cause an error at the retime function. How do I convert the timetable to all numeric data?

The source is a ThingSpeak channel. There is 5 gigabytes of this data to process. Mathworks has no facility to fix or change data already recorded within the feed. The timetable is created with the Mathworks command thingspeakread, so I don't have table input options. I'm hoping that there is some command that applies to the whole time table, preserves the numeric data, and converts the text numbers to numeric data.

10×7 timetable

         Timestamps         x1stFloor    x2ndFloor    x3rdFloor    Slab    EarthTube    SlabCoolingIn    SlabCoolingOut
    ____________________    _________    _________    _________    ____    _________    _____________    ______________

    06-Apr-2013 08:20:55    '103.72'     100          NaN          NaN     NaN          NaN              NaN           
    06-Apr-2013 08:21:10    '103.72'     101          NaN          NaN     NaN          NaN              NaN           
    06-Apr-2013 08:21:26    '102.84'     102          NaN          NaN     NaN          NaN              NaN           
    06-Apr-2013 08:21:41    '102.84'     103          NaN          NaN     NaN          NaN              NaN

Answer 1

[Matt Cossalman]

For everyone else trying to solve this problem, here's what I came up with:

      % Convert text numbers
      strVars = varfun(@iscellstr,data,'output','uniform');
      display(strVars);
      for varnum = 1:length(strVars)
          if strVars(varnum)
              celldata = table2cell(data(:,varnum));
              celldata = str2double(celldata);
              data.(varnum)= celldata;
          end
      end

[Peter Perkins]

Matt, you are correct, my answer did not account for putting the converted data back into the table. One way to do that would be to horzcat the output of varfun with the subtable that contains only the non-text vars.

Your solution is also a good way. you could perhaps loop only over find(strVars), but that's just polish.

Answer 2

[Peter Perkins]

Matt, you have not said where that timetable came from. I think you want to fix this at the cause. Typically, you'll get text where you expect numeri8c when you read a file and the file has some garbage in that field. Impossible to say exactly what went wrong, but you might look at thing like the 'TreatAsEmpty' parameter to readtable, or look into using detectimportoptions.

[Matt Cossalman]

Thanks, Peter. The source is a ThingSpeak channel. There is 5 gigabytes of this data to process. Mathworks has no facility to fix or change data already recorded within the feed. The timetable is created with the Mathworks command thingspeakread, so I don't have table input options. I'm hoping that there is some command that applies to the whole time table, preserves the numeric data, and converts the text numbers to numeric data.

[Peter Perkins]

Perhaps something along the lines of

t = varfun(@str2double,t,'InputVariables',@iscellstr)

would do, but it's hard to say if that will do exactly what you want. If it's really just one variable, then

t.x1stFloor = str2double(t.x1stFloor)

would also be a possibility. But at some point, you'll probably want to fix up whatever garbage I suspect is in those variables.

[Matt Cossalman]

Thanks again. The varfun() yields an empty timetable when there are no textual numbers. So we are not there yet. How can I preserve the numeric data AND convert the textual numbers. I need an automatic process that will crunch 5 gigs of this data. This is the fix for the garbage permanently recorded in the data feed.