I have a number of large data sets in Excel and .dat from OMEdit Modelica that then need to import some or all of it to Mathematica for manipulation and plotting. Importing the data is easy enough using Import were I only need the first sheet.

data = Import[filePath/Name.xls, {"Data", 1}];

I then use this to get a list of the column number and header names:

dims = Dimensions[data] ;

DataNumber = Table[{i, data[[1, i]]}, {i, 1, dims[[2]]}] // Grid

This yields:

1 header1 2 header2 … 99 header99

This is useful, but with larger list, is tedious.

I am searching for a method to give me the column number given a certain column header name, such as header57. Much better would be to be able to give part of the name, such as a wildcard part of the header. Note that the data is not always in the same order, hence the search.

For example, one data column might be ‘Expander.medium.h’ and would like to be able to just find the column number. I might also want to find all the columns that have ‘medium’, so would like to have all the column that have ‘medium’ or similar syntax.

I have tried Cases[] and a few others without luck.

Cases[data[[1, All]], “header87”]

Any suggestions would be most appreciated.


1 Answer 1

data = Import["ExampleData/elements.xls", {"Data", 1}]

enter image description here

headers = data[[1]]
 {"AtomicNumber", "Abbreviation", "Name", "AtomicWeight"}

You can use PositionIndex and Lookup as follows:

posindex = Map[First] @ PositionIndex[headers]
 <|"AtomicNumber" -> 1, "Abbreviation" -> 2, "Name" -> 3, "AtomicWeight" -> 4|>
Lookup[posindex, Flatten @ Select[StringMatchQ["*eigh*"]] @ Keys @ posindex]
  • $\begingroup$ Thank - that works perfectly. - many hours saved... $\endgroup$
    – Chris B
    May 5, 2020 at 10:30

Your Answer

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

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