# How to Export ragged data to a spreadsheet with missing variables in a way that the Mean calculations in both Mathematica and the Spreadsheet match

This is a multi-dimensional question. It is both about the way Mathematica handles missing values AND the difference in how Mathematica and a spreadsheet separately handle missing values.

I have an array with many missing variables, sometimes on the beginning and ends of rows as well as the end of columns. I need to be able to transpose this data and export it to CSV in rectangular form even if some elements are missing. There the mean of both columns and rows will be calculated. How do I fill the slots with something that makes the array rectangular without the added elements (zero) affecting the Mean in the calculations made in either Mathematica or the spreadsheet?

Using zero where blank is intended affects the mean

TrueQ[N[Mean[Range[10]]] == N[Join[Range[10], ConstantArray[0, 10]]]]


(*False*) for the obvious reason that the denominator is different

Using "PadRight" or "Nothing" or "None" or "Null, or "Missing" prevents calculating the mean

(*{PadRight[Range[10],20,x],Join[Range[10],ConstantArray[Nothing,10]],Join[Range[10],ConstantArray[None,10]],Join[Range[10],ConstantArray[Null,10]],Join[Range[10],ConstantArray[Missing,10]]},N[Mean[%]]*)


This...

 ragged = {{2, Null, Null, 4, 5, 6, 7}, {1, 2, 3, 4}, {1, 2, 3, 4, 5,
6, 7, 8, 9}, {, , 1, 2, 3, 4, 5}}


....needs to look like this for transpose and export...

sets = {Join[{2, 0, 0, 4, 5, 6, 7}, ConstantArray[0, 3]],Join[Range[4], ConstantArray[0, 6]],Join[Range[9], ConstantArray[0, 1]],Join[ConstantArray[0, 2], Range[5], ConstantArray[0, 3]]} tbl = Transpose[sets] // TableForm


Mean of the rows (changes when the zeros become missing values because the denominator changes)

Mean[sets]

(*{1, 1, 7/4, 7/2, 13/4, 4, 19/4, 2, 9/4, 0}*)


Mean of the columns (also changes when the zeros become missing values because the denominator changes)

Total[sets[[#]]] & /@ Range[4]
N[%/10]

(*{24, 10, 45, 15}*)
(*{2.4, 1., 4.5, 1.5}*)


The zeros need to be gone when it hits the spreadsheet file and calculations are done. I know I can do the calculations in Mathematica but I am working with a colleague who does not have Mathematica. (I know you are thinking "Get rid of that colleague")

This gets it right for Export...

sets2 = {Join[{2, "", "", 4, 5, 6, 7}, ConstantArray["", 3]], Join[Range[4], ConstantArray["", 6]], Join[Range[9], ConstantArray["", 1]], Join[ConstantArray["", 2], Range[5], ConstantArray["", 3]]}  tbl2 = Transpose[sets2];  % // TableForm


...and works for the spreadsheet but not in Mathematica.

N[Mean[sets2]]

(*{0.25 (4. + ""), 0.25 (4. + 2. ""), 0.25 (7. + ""), 3.5,
0.25 (13. + ""), 0.25 (16. + ""), 0.25 (19. + ""), 0.25 (8. + 3. ""),
0.25 (9. + 3. ""), ""}*)


I am looking for a better way to do it so it works smoothly in both.

### In Mathematica

Assume I have a file like below:

data1.csv
---------
1,2,3
0,,4
,,2


If you import the following file, it will replace empty cells with "", which became an obstacle in your calculations. You can create a simple function that disappears them before the calculation.

removeMissing = ReplaceAll["" -> Nothing];

(* Mean of each row *)
Mean@*removeMissing /@ data

(* Out: {2, 2, 2} *)

(* Mean of each column *)
Mean@*removeMissing /@ Transpose[data]

(* Out: {1/2, 2, 3} *)


### From Mathematica

Let's assume we have the following data:

data = {{1, 2, 3}, {0, Missing[], 4}, {Missing[], Missing[], 2}};


You can use ExportString and then replace the "Missing":

Export["C:\\test2.csv",
StringReplace[ExportString[data /. _?MissingQ -> "", "CSV"],
"\"\"" -> ""], "String"];


test2.csv file contains:

1,2,3
0,,4
,,2


We are back to square one.

I hope you're not involve in complex/scientific format, since I could not verify the code above works in those cases (but be sure there is a solution).

x = {{0, Missing[], 10}, {1, 2, 3, 4, Missing[], 6}};
Mean[DeleteMissing[#]] & /@ x
(* {5, 16/5} *)

Export["missing.xlsx", x]


This makes it so that your Excel colleague just does the standard stuff in Excel and you in Mathematica just need to use DeleteMissing when needed.

The gist of your question seems to be how to find an identical representation for your data that is interpreted the same way in both Mathematica and some unidentified spreadsheet application.

First comment/question: Spreadsheet apps that I'm familiar with handle ragged data from csv files--are you sure this is actually a problem? Maybe your colleague can configure the import function in some fashion.

Second comment: A far better strategy than trying to make both representations identical would be to build transformation functions between the two apps. Given that Mathematica is probably the much more flexible of the two apps in terms of manipulating representations, I'd suggest you let your colleague define the representation for missing datum--probably just some token that can easily be filtered on when calculating mean. Then in Mathematica you define a function that cleans the data imported from the spreadsheet and another function that fills the positions where missing data should be indicated with your colleague's preferred token before export.

Third comment: As for representing missing data in Mathematica, I would suggest you use Missing["Nonexistent"] (or whatever reason makes sense in your context). It's not clear to me that you actually need this or if you only need to indicate missing data because of the spreadsheet dependency. But if you have explicit Missings in your data, it's easy to use DeleteMissing to clean the data before doing calculations. Per the comment above, if you do need this then your prep-for-export function just replaces the Missing expressions with the token your colleague needs.

Fourth comment: I'm not sure what transposing data has to do with anything, unless you're struggling with transposing data that's not rectangular. You can use Missing to make your data rectangular, so this seems like a non-issue.