5
$\begingroup$

Good Morning!

I have a data set that looks like this:

DataTable={{"    ","111011","11011.03","111021","112011","112021","112022","112031","113011"},
 {"2.C.1.a", 5.49, 4.285, 4.78, 4.25, 4.325, 4.26, 4.35, 4.005},
 {"2.C.1.b", 3.08, 3.11, 3.605, 3.48, 3.555, 3.48, 3.66, 5.025},
 {"2.C.1.c", 4.03, 3.28, 3.655, 2.095, 3.325, 3.295, 2.775, 3.815},
 {"2.C.1.d", 3.565, 4.17, 3.76, 4.305, 5.45, 5.33, 4.98, 3.1},
 {"2.C.1.e", 4.82, 4.27, 4.505, 4.13, 4.35, 4.775, 5.215, 4.75},
 {"2.C.1.f", 4.56, 3.4, 4.095, 2.76, 3.13, 3.94, 3.87, 3.91},
 {"2.C.2.a", 2.775, 2.29, 3.865, 3.315, 2.69, 2., 2.125, 2.265},
 {"2.C.2.b", 0.715, 1.5, 0.915, 0.77, 0.7, 0.67, 0.515, 1.225},
 {"2.C.3.a", 2.385, 3.165, 3.665, 3.85, 3.78, 3.095, 3.47, 3.86},
 {"2.C.3.b", 1.67, 3.775, 2.365, 0.975, 2.62, 1.595, 0.895, 1.315},
 {"2.C.3.c", 2.045, 4.26, 2.52, 3.135, 3.35, 1.25, 1.54, 1.765},
 {"2.C.3.d", 2.055, 4.005, 2.565, 0.655, 0.805, 0.955, 0.805, 1.605},
 {"2.C.3.e", 1.36, 2.7, 2.81, 0.76, 0.935, 1.595, 0.715, 1.3},
 {"2.C.4.a", 3.815, 3.635, 3.86, 2.965, 3.955, 3.74, 2.5, 3.56},
 {"2.C.4.b", 0.82, 2.72, 1.61, 0.615, 0.775, 0.98, 0.53, 0.715},
 {"2.C.4.c", 1.19, 2.7, 1.845, 0.66, 0.82, 1., 0.53, 0.85},
 {"2.C.4.d", 0.95, 2.76, 1.2, 0.65, 0.65, 0.88, 0.53, 0.68},
 {"2.C.4.e", 3.52, 3.06, 2.75, 2.79, 2.655, 3.5, 2.525, 2.06},
 {"2.C.4.f", 2.8, 2.87, 1.555, 1.605, 2.235, 2.355, 2.39, 1.395},
 {"2.C.4.g", 1.44, 3.135, 1.72, 1.81, 2.375, 1.93, 1.46, 1.395},
 {"2.C.5.a", 1.04, 0.885, 1.205, 0.5, 0.85, 0.99, 1.36, 1.05},
 {"2.C.5.b", 2.25, 0.885, 1.625, 0.915, 1.365, 1.43, 1.2, 1.24},
 {"2.C.6", 3.845, 4.335, 3.42, 2.63, 3.59, 3.83, 3.085, 2.495},
 {"2.C.7.a", 4.315, 4.145, 3.695, 4.705, 4.76, 4.02, 5.08, 4.07},
 {"2.C.7.b", 1.17, 1.14, 1.375, 1.485, 1.5, 1., 1.41, 0.865},
 {"2.C.7.c", 1.15, 1.17, 0.815, 3.205, 1.51, 0.825, 1.335, 0.795},
 {"2.C.7.d", 1.285, 1.84, 0.82, 0.99, 1.385, 1., 1.55, 0.91},
 {"2.C.7.e", 1.76, 2.135, 1.19, 1.265, 1.38, 1.325, 1.7, 1.17},
 {"2.C.8.a", 3.455, 2.89, 3.11, 1.5, 2.38, 1.57, 1.99, 2.21},
 {"2.C.8.b", 3.96, 2.98, 3.035, 1.8, 2.895, 2.715, 2.34, 2.105},
 {"2.C.9.a", 1.675, 1.195, 2.415, 2.985, 2.71, 2.24, 2.075, 2.23},
 {"2.C.9.b", 3.025, 2.88, 2.635, 4.815, 4.005, 2.715, 4.86, 2.32},
 {"2.C.10", 2.06, 2.45, 2.255, 1.495, 1.605, 1.595, 1.2, 1.775}
};

TableForm[DataTable]

Notice that:

  1. The upper-Left corner of the DataTable has no entry in it.
  2. The items in the first row are SOCs (Standard Occupational Classification) codes.
  3. The items in the first column are attributes from a survey form.

Typically, these arrays(not including the first row and first column) come in dimensions: 33 X 954; 35 X 954; 41 X 954; 57 X 954 and 1 X 954. In other words; the SOcs in the first row are always 954 but, the attributes in the five types of surveys might change, i.e.; {33, 35, 41, 57 and 1 respectively}.

Referring to the above DataTable array then;

SOCs = {"111011","11011.03","111021","112011","112021","112022","112031","113011"};

contains the complete set of SOC codes.

What I need is a Mathematica code; call it: SubMatrix[ ] such that if I have a subset of SOCs, say:

SOCsToMatch = {"11011.03","112011","112021","112031"};

then, evaluating:

SubMatrix[SOCsToMatch]

gives:

{{"11011.03", "112011", "112021", "112031"}, {4.285, 4.25, 4.325, 4.35},
 {3.11, 3.48, 3.555, 3.66}, {3.28, 2.095, 3.325, 2.775}, {4.17, 4.305, 5.45, 4.98}, 
 {4.27, 4.13, 4.35, 5.215}, {3.4, 2.76, 3.13, 3.87}, {2.29, 3.315, 2.69, 2.125}, 
 {1.5, 0.77, 0.7, 0.515}, {3.165, 3.85, 3.78, 3.47}, {3.775, 0.975, 2.62, 0.895}, 
 {4.26, 3.135, 3.35, 1.54}, {4.005, 0.655, 0.805, 0.805}, {2.7, 0.76, 0.935, 0.715}, 
 {3.635, 2.965, 3.955, 2.5}, {2.72, 0.615, 0.775, 0.53}, {2.7, 0.66, 0.82, 0.53}, 
 {2.76, 0.65, 0.65, 0.53}, {3.06, 2.79, 2.655, 2.525}, {2.87, 1.605, 2.235, 2.39}, 
 {3.135, 1.81, 2.375, 1.46}, {0.885, 0.5, 0.85, 1.36}, {0.885, 0.915, 1.365, 1.2}, 
 {4.335, 2.63, 3.59, 3.085}, {4.145, 4.705, 4.76, 5.08}, {1.14, 1.485, 1.5, 1.41}, 
 {1.17, 3.205, 1.51, 1.335}, {1.84, 0.99, 1.385, 1.55}, {2.135, 1.265, 1.38, 1.7}, 
 {2.89, 1.5, 2.38, 1.99}, {2.98, 1.8, 2.895, 2.34}, {1.195, 2.985, 2.71, 2.075}, 
 {2.88, 4.815, 4.005, 4.86}, {2.45, 1.495, 1.605, 1.2}};

Thank you!

$\endgroup$
8
$\begingroup$

Select and MemberQ are easy to use for this

subMatrix[data_, socs_] := 
  Select[data // Transpose, MemberQ[socs, #[[1]]] &] // Transpose;
$\endgroup$
5
$\begingroup$

Use an Association:

assoc = AssociationThread[DataTable[[1, 2 ;;]] -> Transpose@DataTable[[2 ;;, 2 ;;]]];

Then, note that, e.g.

assoc["111011"]
(* {5.49, 3.08, 4.03, 3.565, 4.82, 4.56, 2.775, 0.715, 2.385, 1.67, 2.045,
    2.055, 1.36, 3.815, 0.82, 1.19, 0.95, 3.52, 2.8, 1.44, 1.04, 2.25, 3.845,
    4.315, 1.17, 1.15, 1.285, 1.76, 3.455, 3.96, 1.675, 3.025, 2.06} *)

For your desired format, define

subMatrix[keys_List] := Join[{Keys@#}, Transpose@Values@#] &@assoc[[keys]]

Then, you can do

subMatrix[{"11011.03", "112011", "112021", "112031"}]
$\endgroup$
  • $\begingroup$ Thank you for your excellent approaches in answering my question N.J.Evans! You don't have an idea about the amount of "perspiration" that you are saving me! Thank you!! $\endgroup$ – Gilmar Rodriguez Pierluissi Sep 27 '16 at 18:01
  • $\begingroup$ @GilmarRodriguezPierluissi. I think you meant to post this comment on the other answer! :) $\endgroup$ – march Sep 27 '16 at 18:02
3
$\begingroup$

This is a straight-forward application of Cases, though you might also think about different data structures.(Association, Dataset, etc.)

subMatrix[data_,socs_]:= Transpose[
   Cases[
     Transpose@data,
     {soc_,___}/;Or@@@Thread[StringMatchQ[soc,socs]
    ]
   ]

First I transpose the data so that each row represents an entry, the first row is all of your attributes which will not be included in the final array, as per your spec. Then I simply select all Cases that match a pattern. In this case I look for rows of the form {soc,any-other-stuff} such that /; the condition that soc matches one of the values you want is satisfied. Then I transpose the result to get back to the column format you started with.

to apply this simply do:

subMatrix[dataTable,socsToMatch]

A few caveats, this depends on your soc's being strings, and having exactly the position they do in the dataset. If these are met, you're fine. Also, I've changed leading uppercase letters to lowercase, which you should always do. MMA starts all functions with uppercase and this will keep you from maddening conflicts in the future.

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