How to import a file into math and refer to a column by header name

Question

I have a TSV file with many columns, for example:

genename X1 X100 X103 X105 X115 X117 X120 X122 X123 Gene20728 0.415049 0.517868 0.820183 0.578081 0.30997 0.395181 

I would like to import it into Mathematica, and then extract and sort the column.

ie, I want to extract the column ["X117"] and sort it and display a sorted list.

Answer 1

 table = Import["file.csv", "Table"]; x117 = Drop[table[[All, 7]], 1]; sorted = Sort[x117]; 

Answer 2

I don’t think there is a built-in method of achieving a smart structure that you seem to be asking for. Below is what, in my opinion, is the most direct implementation of the various possible methods.

 stringdata = "h1\th2\n1\t2\n3\t4\n5" h1 h2 1 2 5 4 3 Clear[ImportColumnsByName]; ImportColumnsByName[filename_] := Module[{data, headings, columns, struc}, data = ImportString[filename, "TSV"]; headings = data[[1]]; columns = Transpose[PadRight[data[[2 ;; -1]]]]; MapThread[(struc[#1] = #2) &, {headings, columns}]; struc ] Clear[test]; test = ImportColumnsByName[stringdata]; test["h1"] test["h2"] Sort[test["h1"]] 

outputs:

 {1, 3, 5} {2, 4, 0} {1, 3, 5} 

Based on the ragfield solution, this is a more dynamic method, but each call to this structure calls up a position and part call.

 Clear[ImportColumnsByName]; ImportColumnsByName[filename_] := Module[{data, temp}, data = PadRight@ImportString [filename, "Table"]; temp[heading_] := Rest[data[[All, Position[data[[1]], heading][[1, 1]]]]]; temp ] Clear[test]; test = ImportColumnsByName[stringdata]; test["h1"] test["h2"] Sort[test["h1"]] 

outputs:

 {1, 3, 5} {2, 4, 0} {1, 3, 5} 

Answer 3

Starting with ragfield code:

 table = Import["file.csv", "Table"]; colname = "X117" x117 = Drop[table[[All, Position[tb[[1, All]], colname]//Flatten]], 1]//Flatten; sorted = Sort[x117]; 

Answer 4

To process Excel files from different sites, I make variations on this:

 data = {{"h1", "h2"}, {1, 2}, {3, 4}, {5, ""}}; find[x_String] := Cases[Transpose[data], {x, __}] In[]=find["h1"] Out[]={{"h1", 1, 3, 5}} 

If this is fuzzy data, you can usually easily overlay it to make it suitable for transposition. In addition, some of my sources are lazy with formatting, sometimes changing headings, sometimes there is an empty line in front of the heading, etc.:

 find2[x_String,data_List] := Cases[Transpose[data], {___, y_String /; StringMatchQ[StringTrim[y], x, IgnoreCase -> True], __}] In[]=find2["H1",data] Out[]={{"h1", 1, 3, 5}} data2 = {{"", ""}, {"H1 ", "h2"}, {1, 2}, {3, 4}, {5, ""}}; In[]=find2["h1",data2] Out[]={{,"H1 ", 1, 3, 5}}