0
$\begingroup$

I've used SemanticImport to import to a "TSV" spreadsheet into a Dataset. Each row starts with a year name and then has 12 values, one for each month. SemanticImport correctly interpreted the column names as months, and the first column as year, but the row entries under year are interpreted as data rather than names. Is there a way to turn them into names for the rows?

$\endgroup$
3
  • $\begingroup$ Can you post (a subset of) your CSV data in Pastebin? $\endgroup$ Aug 31, 2017 at 16:24
  • $\begingroup$ I've never used Pastebin and am not sure how to show you the data but I did post a subset at: pastebin.com/vihHYQsf $\endgroup$
    – user5091
    Aug 31, 2017 at 18:19
  • $\begingroup$ Actually you file is more like "TSV" rather than "CSV". So I correct the question accordingly. $\endgroup$ Sep 1, 2017 at 4:39

2 Answers 2

5
$\begingroup$

Perhaps you can use GroupBy on the "Year" column, and then drop the "Year" column:

import = SemanticImport["https://pastebin.com/raw/vihHYQsf"];

ds = GroupBy[import, "Year", First][All, KeyDrop["Year"]]

enter image description here

Then, we can extract the Jul, 2010 value using:

ds[Key[2010], "Jul"]

560.7

$\endgroup$
4
$\begingroup$

I don't believe SemanticImport[] has the flexibility you want, so I'll use plain Import[] instead (which is much faster) and then do some post-processing:

raw = Import["https://pastebin.com/raw/vihHYQsf", "Table"];
ds = Dataset[AssociationThread[IntegerString /@ raw[[2 ;;, 1]],
                               AssociationThread[raw[[1, 2 ;;]], #] & /@
                               raw[[2 ;;, 2 ;;]]]]

dataset

where I needed to use IntegerString[] to converts integers to proper labels.

For example,

ds["2009", "Oct"]
   585.
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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