How can I export dataset to excel with names

Question

dataset=Dataset[{
<|"a"->1,"b"->"x","c"->{1}|>,
<|"a"->2,"b"->"y","c"->{2,3}|>,
<|"a"->3,"b"->"z","c"->{3}|>,
<|"a"->4,"b"->"x","c"->{4,5}|>,
<|"a"->5,"b"->"y","c"->{5,6,7}|>,
<|"a"->6,"b"->"z","c"->{}|>}]

Export["test.xls", 
Prepend[List @@@ Normal@dataset, Keys@Normal[dataset][[1]]]] like SemanticImport/SemanticExport

or a pivot table with column names and row names and a table_name in left corner.

titanic = ExampleData[{"Dataset", "Titanic"}]

titanic[GroupBy["sex"], GroupBy["class"], ratio, "survived"]

Are there some more stupid/better methods to do this?

Answer 1

I'm going to answer this question even though it was posted 3 years and 4 months ago.

titanic = ExampleData[{"Dataset", "Titanic"}]

Inserting "Missing" in empty cells of titanic Dataset:

fillmissingentries = List @@@ Normal@titanic /. Missing[] -> "Missing";

Look at the resulting Dataset:

Dataset[fillmissingentries]

Re-inserting the column headings in the above fillmissingentries Dataset :

colnames = {"class", "age", "sex", "survived"};

titanic = Dataset@Map[AssociationThread[colnames, #] &]@Dataset[fillmissingentries]

Group the passengers by Sex and then Class :

groupedbyClassAndSextable = titanic[GroupBy["sex"], GroupBy["class"]]

Keys[groupedbyClassAndSextable]

The next entries deal with building the tables pertaining to Female:

key1 = Keys[groupedbyClassAndSextable][[1]]

All classes - Female table:

groupedbyClassAndSextable[key1]

First Class - Female table:

firstclassfemaletable = groupedbyClassAndSextable[key1][[1]]

Second Class - Female table:

secondclassfemaletable = groupedbyClassAndSextable[key1][[2]]

Third Class - Female table:

thirdclassfemaletable = groupedbyClassAndSextable[key1][[3]]

The next entries deal with building the tables pertaining to Male:

key2 = Keys[groupedbyClassAndSextable][[2]]

All classes - Male table:

groupedbyClassAndSextable[key2]

First Class - Male table:

firstclassmaletable = groupedbyClassAndSextable[key2][[1]]

Second Class - Male table:

secondclassmaletable = groupedbyClassAndSextable[key2][[2]]

Third Class - Male table:

thirdclassmaletable = groupedbyClassAndSextable[key2][[3]]

Now we proceed to export the above tables as Excel workbooks:

setDir := Quiet@Check[SetDirectory@DirectoryName@$InputFileName, SetDirectory@NotebookDirectory[]]

Verify your working directory:

setDir

Exporting First Class - Female table :

entries1 = List @@@ Normal@firstclassfemaletable;

xlsxDataSet1 = PrependTo[entries1, colnames];

Export["First-Class-Female-Table.xlsx", xlsxDataSet1]

Exporting Second Class - Female table:

entries2 = List @@@ Normal@secondclassfemaletable;

xlsxDataSet2 = PrependTo[entries2, colnames];

Export["Second-Class-Female-Table.xlsx", xlsxDataSet2]

Exporting Third Class - Female table:

entries3 = List @@@ Normal@thirdclassfemaletable;

xlsxDataSet3 = PrependTo[entries3, colnames];

Export["Third-Class-Female-Table.xlsx", xlsxDataSet3]

Exporting First Class - Male table:

entries4 = List @@@ Normal@firstclassmaletable;

xlsxDataSet4 = PrependTo[entries4, colnames];

Export["First-Class-Male-Table.xlsx", xlsxDataSet4]

Exporting Second Class - Male table:

entries5 = List @@@ Normal@secondclassmaletable;

xlsxDataSet5 = PrependTo[entries5, colnames];

Export["Second-Class-Male-Table.xlsx", xlsxDataSet5]

Exporting Third Class - Male table:

entries6 = List @@@ Normal@thirdclassmaletable;

xlsxDataSet6 = PrependTo[entries6, colnames];

Export["Third-Class-Male-Table.xlsx", xlsxDataSet6]