# Missing values and importing Excel file

**URL:** <https://community.wolfram.com/t/missing-values-and-importing-excel-file/1347>\
**Category:** Questions\
**Tags:** business-analytics, mathematica, import-and-export\
**Created:** [January 23, 2014, 3:22am UTC](https://community.wolfram.com/t/missing-values-and-importing-excel-file/1347 "2014-01-23T03:22:44Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mccarthy](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@mccarthy](https://community.wolfram.com/u/mccarthy)\
**Post date:** [January 23, 2014, 3:22am UTC](https://community.wolfram.com/t/missing-values-and-importing-excel-file/1347/1 "2014-01-23T03:22:44Z")

</div>

Hi, I’m trying to import an Excel file in which some data have missing values (represented as a period (.)). I import the file with no problem but when I try to get descriptive statistics, the procedure doesn’t ignore the missing values. I’ve tried adding “.” → Sequence[] but this adds zeros and the descriptive statistics include these in the calculations. In searching, I’ve yet to find a way or an option that skips these missing values when calculating descriptive statistics.

Any suggestions would be most appreciated. Pat

---

<div class="post-metadata">

**Author:** ![mthiel](https://yyz1.discourse-cdn.com/wolfram/user_avatar/community.wolfram.com/mthiel/32/42877_2.png) [@mthiel](https://community.wolfram.com/u/mthiel)\
**Post date:** [January 23, 2014, 6:45am UTC](https://community.wolfram.com/t/missing-values-and-importing-excel-file/1347/2 "2014-01-23T06:45:59Z")

</div>

Dear Pat,

this might work:

```auto
list = {1, 2, 3, 4, 5, ".", ".", "."}
DeleteCases[list, "."]

```

It should give:

```auto
{1, 2, 3, 4, 5}

```

Best wishes,  
Marco

---

<div class="post-metadata">

**Author:** ![mccarthy](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@mccarthy](https://community.wolfram.com/u/mccarthy)\
**Post date:** [January 24, 2014, 6:10pm UTC](https://community.wolfram.com/t/missing-values-and-importing-excel-file/1347/3 "2014-01-24T18:10:39Z")

</div>

Dear Marco, thanks for your reply and I now have a related problem. Below is code that successfully reads 22 columns of data from data matrix trydta. The command Mean correctly calculates the mean for data in column 7, which has both missing and non-missing data values.

colmean = Mean[Select[trydta[[All, 7]], NumberQ]] // N

But I would like to get a table of means for all 22 variables. Do you know how I need to modify the command to tell mathematica that I would like means not only for column 7 but for columns 1 - 22?

Thanks again for your help.

Pat

---

<div class="post-metadata">

**Author:** ![mthiel](https://yyz1.discourse-cdn.com/wolfram/user_avatar/community.wolfram.com/mthiel/32/42877_2.png) [@mthiel](https://community.wolfram.com/u/mthiel)\
**Post date:** [January 24, 2014, 8:32pm UTC](https://community.wolfram.com/t/missing-values-and-importing-excel-file/1347/4 "2014-01-24T20:32:17Z")

</div>

Dear Pat,

sure. You already answered the question yourself. You want a table of the results for all 22 columns, so try:

```auto
colmean = 
 Table[Mean[Select[trydta[[All, m]], NumberQ]] // N, {m, 1, 22}]

```

Cheers,  
M.

---

<div class="post-metadata">

**Author:** ![caitlinjoramsey](https://yyz1.discourse-cdn.com/wolfram/user_avatar/community.wolfram.com/caitlinjoramsey/32/30633_2.png) [@caitlinjoramsey](https://community.wolfram.com/u/caitlinjoramsey)\
**Post date:** [January 13, 2015, 1:43am UTC](https://community.wolfram.com/t/missing-values-and-importing-excel-file/1347/5 "2015-01-13T01:43:31Z")

</div>

As an alternative, you could do something like this:

```
in:=sampleData = 
  SemanticImport["ExampleData/elements.xls", Automatic, 
   "NamedColumns"];
in:=Select[#, NumberQ] & /@ Values[sampleData];
in:=Mean[#] & /@ %

out:={5., Mean[{}], Mean[{}], 10.3094}

```

This and the previous answer may be equally convenient if your goal is to get a single result for each consecutive column. But the resulting output will be formatted as a list, rather than a table.

On the other hand, if you want to operate on specific columns in an arbitrary order, you could also do this:

```
in:=cols = Keys[sampleData];
in:=KeyTake[sampleData, {cols[[1]], cols[[4]]}];
in:=Select[#, NumberQ] & /@ Values[%];
in:=Mean[#] & /@ %

out:={5., 10.3094}

```
