1
$\begingroup$

I'm attempting to do a code for Wolfram Mathematica

I am uploading an Excel file that has a huge number of characters and multiple sheets to enter.

The issue is the import is set up as a table/grid which is fine, but in this table/grid there are a variety of non-integer values (null values) that don't show up as null values, I need to find a way to make all the null values disappear and to delete whatever row they are in (across).

For example, suppose there is a null value in column 7 row 13, I want to automatically delete that whole row because it taints the data set. I'd prefer to have an automatic way to do this because the 7 sheets have about 2300 ~ 4000 rows each, and it would be difficult to carry out the deletion by hand. The rows with no input values are specified as non-0 and non-values as well, which makes getting any sort of mathematical analysis an issue.

Mycode:

Boston01a = Import["Boston-v01.xlsx", {"Data", 1}];

textStyle @ 
  Row[{"Number of entries in ", " file: ", Length[Boston01a]}]
timesStyle10 @
  Pane[
    Grid[Boston01a, Frame -> All], 
    ImageSize -> {All, 200}, Scrollbars -> True]

(*Boston data set 1 data specification*)
Bo1 = Drop[Boston01a, 1, 2];
timesStyle10 @
  Pane[
    Grid[Bo1, Frame -> All], 
    ImageSize -> {All, 200}, Scrollbars -> True];
Improve this question
$\endgroup$
2
  • 1
    $\begingroup$ Welcome to Mathematica.StackExchange. It is a good habit here to provide an example file that can be downloaded and experimented with. For the moment I can advise you to have a look at Select. You merely have to create a function that eats a row and spits out wether the row should be selected (return value of function should be True) or it should be ignored (return value False). Maybe NumericQ can also be of help. $\endgroup$ – Henrik Schumacher Mar 23 '18 at 20:04
  • 1
    $\begingroup$ I have found the opposite approach much more reliable in the past: select just those rows in the data which are containing what you expect. That is somewhat inefficient for huge datasets but for a few thousend rows should work fine. That will get rid of the question which types of NULL or invalid entries there could eventually be... $\endgroup$ – Albert Retey Mar 23 '18 at 21:38
1
$\begingroup$

This may not be an elegant solution but it works if you know the drop character to search for, such as Null/Blank, or any other unique character (or number) that you can assign to variable dropchar. In addition, the code drops every row that has one or more non-integer cell value at any position.

The code reads data from an Excel file, searches for dropchar at any position in each row and checks to make sure all cells are integers, then deletes each row that contains dropchar or has non-integer characters, and creates the resultant array newdata.

(* Read data from Excel file, drop all rows that contain dropchar at any position in the row, and create the resultant array newdata *)

data=Import["Boston-v01.xlsx"];
dim=Dimensions[data] (* Check dimension of original data *)
rows=dim[[2]];
dropchar=""; (* Assign the drop variable as a Null/Blank cell value *)
n=0;j=1;newdata={};
While[j<=rows,If[Position[data[[1, j, All]], dropchar] == {}&&  IntegerQ[Total[IntegerPart[data[[1, j, All]]]]], {newdata = 
Append[newdata, data[[1, j, All]]];j+=1},  j+=1;n+=1;  ] ]
Print["Dropped ",n," data rows!"];
Dimensions[newdata] (* Check dimension of newdata *)
Improve this answer
$\endgroup$
3
  • $\begingroup$ Using Append will be unnecessarily slow because it needs bazillions of copy operations. Better use Select. Also checking if a list is empty may be done qicker with Length[list] == 0. $\endgroup$ – Henrik Schumacher Mar 24 '18 at 7:12
  • $\begingroup$ @ Henrik Schumacher : I see your point. I opted to read the file in one shot in order to speed up the reading operation which would have slowed down the operation if we read the file line by line and checked it that way. Anyway, I also added a check to make sure that all cells are indeed integers, in addition to the check for the unique drop character which might come in handy. Thanks. $\endgroup$ – Vixillator Mar 24 '18 at 7:22
  • $\begingroup$ @ Henrik Schumacher : By the way, I just run my code with a larger dataset (4320 rows x 22 columns) and it completed in about 2.6 seconds, including the file read. $\endgroup$ – Vixillator Mar 24 '18 at 8:10
0
$\begingroup$

XLS/XLSX have an EmptyField option so these cells can be clearly and definitely identified. Then we can use DeleteCases with a level spec of {2} so that the pattern tests at the row level. Here's an example using ExampleData from 11.3:

With[{uu=CreateUUID[]}, 
    DeleteCases[
        Import["ExampleData/mrrogers.xlsx","EmptyField"->uu]
        ,
        _?(MemberQ[#, uu] &)
        ,
        {2}
    ]
]
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.