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?
2 Answers
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"]]
Then, we can extract the Jul, 2010 value using:
ds[Key[2010], "Jul"]
560.7
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 ;;]]]]
where I needed to use IntegerString[]
to converts integers to proper labels.
For example,
ds["2009", "Oct"]
585.
"TSV"
rather than"CSV"
. So I correct the question accordingly. $\endgroup$