1

I have a file with a huge amount of data. Places where there is no information about prices are marked as NaN. I would like to delete all rows, where there are such names and delete all columns where there are a lot of missing data (because I need then proportional matrix).

I also have another string (AssetList) where there is information about all tickers. If column will be deleted, it’s necessary to delete according ticker there.

I would much appreciate any help.

Data:

6,41    16,51   x      x   69,78

6,22    16      x      x   68,48

6,17    15,61   x      x   69,46

x       x       x      x   x

x       x       x      x   x

x       x       x      x   x

5,83    15,14   x      x   69,85

6,4     17,64   x      x   71,03

6,07    16,04   x      x   68,64

5,91    17,09   x      x   68,92

6       18,19   x      x   68,72

x       x       x      x   x

x       x       x      x   x

5,58    17,17   x      x   69,02

5,3     16,83   x      x   67,69

5,66    19,65   x      x   68,64

5,65    20,86   x      x   69,45

5,43    20,46   x      x   68,94

x       x       x      x   x

x       x       x      x   x

5,58    2       0,16  x    68,73

AssetList:

FLWS   SRCE   FUBC   DDD   MMM
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Claude Monet
  • 143
  • 1
  • 1
  • 8
  • What exactly would you like the result to look like? If you simply delete all rows with a `x` nothing would be left. – Dennis Jaheruddin Jun 04 '13 at 09:05
  • I think he wants to delete rows with all NaNs? But I'm not sure what constitutes a lot of NaNs in the column... – Huguenot Jun 04 '13 at 09:05
  • 1
    This should get you started http://stackoverflow.com/questions/5202680/matlab-how-to-efficiently-remove-nan-elements-from-matrix – Huguenot Jun 04 '13 at 09:06

1 Answers1

0

I'll have to make some assumptions here, as I didn't fully understand your question.

The following first deletes all rows that exist of NaN exclusively, and continues by deleting all columns that contain at least one NaN:

M = [ ...
    6.41    16.51   NaN    NaN 69.78                           
    6.22    16      NaN    NaN 68.48                           
    6.17    15.61   NaN    NaN 69.46                           
    NaN     NaN     NaN    NaN NaN                           
    NaN     NaN     NaN    NaN NaN                           
    NaN     NaN     NaN    NaN NaN                           
    5.83    15.14   NaN    NaN 69.85                           
    6.4     17.64   NaN    NaN 71.03                           
    6.07    16.04   NaN    NaN 68.64                           
    5.91    17.09   NaN    NaN 68.92                           
    6       18.19   NaN    NaN 68.72                           
    NaN     NaN     NaN    NaN NaN                               
    NaN     NaN     NaN    NaN NaN                           
    5.58    17.17   NaN    NaN 69.02                           
    5.3     16.83   NaN    NaN 67.69                           
    5.66    19.65   NaN    NaN 68.64                           
    5.65    20.86   NaN    NaN 69.45                           
    5.43    20.46   NaN    NaN 68.94                           
    NaN     NaN     NaN    NaN NaN                           
    NaN     NaN     NaN    NaN NaN            
    5.58    2       0.16   NaN 68.73];

AssetList = {
    'FLWS'   'SRCE'   'FUBC'  'DDD'   'MMM' };

% Delete all-NaN rows
M(all(isnan(M),2),:) = [];

% Delete any-NaN columns
colsToBeDeleted = any(isnan(M));
M(:, colsToBeDeleted) = []
AssetList(colsToBeDeleted) = []

Result:

M =
    6.4100   16.5100   69.7800
    6.2200   16.0000   68.4800
    6.1700   15.6100   69.4600
    5.8300   15.1400   69.8500
    6.4000   17.6400   71.0300
    6.0700   16.0400   68.6400
    5.9100   17.0900   68.9200
    6.0000   18.1900   68.7200
    5.5800   17.1700   69.0200
    5.3000   16.8300   67.6900
    5.6600   19.6500   68.6400
    5.6500   20.8600   69.4500
    5.4300   20.4600   68.9400
    5.5800    2.0000   68.7300

AssetList = 
   'FLWS'    'SRCE'    'MMM'
Rody Oldenhuis
  • 37,726
  • 7
  • 50
  • 96