3
$\begingroup$
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?

$\endgroup$
1
$\begingroup$

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]
$\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.