4
$\begingroup$

I have two list: in one of them the name of certain compounds, and in the other one the properties of each compounds. So I want to merge them to have for each compound name the relative properties. Imagine you have two list of data and you want merge them, in this way:

 compounds = {{"N", "Name", "CAS", "family", "subfamily"}, {1., "METHANE","74-82-8",
"n-Alkanes", ""}, {2., "ETHANE", "74-84-0", "N-ALKANES", ""}, {3.,
"PROPANE", "74-98-6", "N-ALKANES", ""}, {4., "ISOBUTANE", 
"75-28-5", "METHYLALKANES", "2-methylalkanes"}, {5., "n-BUTANE", 
"106-97-8", "N-ALKANES", ""}, {6., "1,1,3-TRIMETHYLCYCLOHEXANE", 
"3073-66-3", "Alkylcyclohexanes", ""}, {7., "n-PENTANE", 
"109-66-0", "N-ALKANES", ""}, {8., "ISOPENTANE", "78-78-4", 
"METHYLALKANES", "2-METHYLALKANES"}, {9., "NEOPENTANE", "463-82-1",
"Dimethylalkanes", "2,2-dimethylalkanes"}, {11., "n-HEXANE", 
"110-54-3", "N-ALKANES", ""}, {12., "2-METHYLPENTANE", "107-83-5", 
"METHYLALKANES", "2-METHYLALKANES"}, {13., "3-METHYLPENTANE", 
"96-14-0", "METHYLALKANES", "3-METHYLALKANES"}, {14., 
"2,2-DIMETHYLBUTANE", "75-83-2", "DIMETHYLALKANES", 
"2,2-DIMETHYLALKANES"}};

 properties = {{93.15, 0.000202, 3287., 1., "LVS", 25244., 
"Experimental", "< 25%", "R"}, {113.15, 0.000138, 3287., 1., 
"LVS", 25244., "Experimental", "< 25%", "R"}, {233.15, 0.000094, 
8798., 2., "LVS", 25233., "Experimental", "< 5%", "N"}, {253.15, 
0.000078, 8798., 2., "LVS", 25233., "Experimental", "< 5%", 
"N"}, {273.15, 0.00006, 8798., 2., "LVS", 25233., "Experimental", 
"< 5%", "N"}, {173.15, 0.00042, 13089., 3., "LVS", 25238., 
"Experimental", "< 10%", "N"}, {193.15, 0.00034, 13089., 3., 
"LVS", 25238., "Experimental", "< 10%", "N"}, {233.15, 0.00022, 
13089., 3., "LVS", 25238., "Experimental", "< 10%", "N"}, {273.15,
 0.000195, 13089., 3., "LVS", 25238., "Experimental", "< 10%", 
"N"}, {273.15, 0.000351, 32270., 12., "LVS", 25227., 
"Experimental", "< 3%", "A"}, {293.15, 0.000286, 32270., 12., 
"LVS", 25227., "Experimental", "< 3%", "A"}, {313.15, 0.000237, 
32270., 12., "LVS", 25227., "Experimental", "< 3%", "A"}, {273.75,
 0.000369, 32271., 12., "LVS", 25227., "Experimental", "< 3%", 
"A"}, {278.15, 0.00035, 32271., 12., "LVS", 25227., 
"Experimental", "< 3%", "A"}, {283.15, 0.000332, 32271., 12., 
"LVS", 25227., "Experimental", "< 3%", "A"}};

I want to merge the two list. When the program read 1. in the second list (properties) I want that he writes the name, the cas number, the family and subfamily. At the end this is what I wish...

  whatIneed = {{93.15, 0.000202, 3287., "METHANE", "LVS", 25244., 
"Experimental", "< 25%", "R", "74-82-8", "n-Alkanes"}, {113.15, 
0.000138, 3287., "METHANE", "LVS", 25244., "Experimental", 
"< 25%", "R", "74-82-8", "n-Alkanes"}, {233.15, 0.000094, 8798., 
"ETHANE", "LVS", 25233., "Experimental", "< 5%", "N", "74-84-0", 
"N-ALKANES"}, {253.15, 0.000078, 8798., "ETHANE", "LVS", 25233., 
"Experimental", "< 5%", "N", "74-84-0", "N-ALKANES"}, {273.15, 
0.00006, 8798., "ETHANE", "LVS", 25233., "Experimental", "< 5%", 
"N", "74-84-0", "N-ALKANES"}, {173.15, 0.00042, 13089., "PROPANE",
 "LVS", 25238., "Experimental", "< 10%", "N", "74-98-6", 
"N-ALKANES"}, {193.15, 0.00034, 13089., "PROPANE", "LVS", 25238., 
"Experimental", "< 10%", "N", "74-98-6", "N-ALKANES"}, {233.15, 
0.00022, 13089., "PROPANE", "LVS", 25238., "Experimental", 
"< 10%", "N", "74-98-6", "N-ALKANES"}, {273.15, 0.000195, 13089., 
"PROPANE", "LVS", 25238., "Experimental", "< 10%", "N", "74-98-6",
 "N-ALKANES"}, {273.15, 0.000351, 32270., "2-METHYLPENTANE", 
"LVS", 25227., "Experimental", "< 3%", "A", "107-83-5", 
"METHYLALKANES"}, {293.15, 0.000286, 32270., "2-METHYLPENTANE", 
"LVS", 25227., "Experimental", "< 3%", "A"}, {313.15, 0.000237, 
32270., "2-METHYLPENTANE", "LVS", 25227., "Experimental", "< 3%", 
"A", "107-83-5", "METHYLALKANES"}, {273.75, 0.000369, 32271., 
"2-METHYLPENTANE", "LVS", 25227., "Experimental", "< 3%", "A", 
"107-83-5", "METHYLALKANES"}, {278.15, 0.00035, 32271., 
"2-METHYLPENTANE", "LVS", 25227., "Experimental", "< 3%", "A", 
"107-83-5", "METHYLALKANES"}, {283.15, 0.000332, 32271., 
"2-METHYLPENTANE", "LVS", 25227., "Experimental", "< 3%", "A", 
"107-83-5", "METHYLALKANES"}};

I tried to solve my problem with this answer: Merging two lists with possible different keys. But in my opinion there is an error in the Caitlin J. Ramsey (he explains in a clear way all the procedure) answer because he does not define the "values" variable, but with my score I can't write a comment. So here I'm. Thanks.

$\endgroup$
  • 2
    $\begingroup$ values is merged in that answer, please have a try. $\endgroup$ – happy fish Mar 30 '17 at 13:01
  • 2
    $\begingroup$ FYI you'd probably be better using Association or Dataset to handle this stuff, but here is a quick one liner which assumes all lists are in the correct order: MapAt[# /. ((#[[1]] -> #[[2]]) & /@ Rest@compounds) &, properties, {All, {4}}] that will get your whatIneed. Also, you have a typo in compounds, the headers aren't separated from the first entry i.e. ` ..."subfamily", 1., "METHANE"...` should be ..."subfamily"},{1., "METHANE"... $\endgroup$ – N.J.Evans Mar 30 '17 at 14:04
  • 1
    $\begingroup$ @N.J.Evans thanks a lot. This is a very nice way, but in this way I can't have also the other required values such as Cas, subfamily...and so on. $\endgroup$ – Giorgy Tisni Mar 30 '17 at 14:23
  • 1
    $\begingroup$ @GiorgyTisni OK, I will write a solution then. Before that I want to confirm one thing: is the number to replace in the second list always on the 4th position? If not, there is a problem if you have two 1s (another one may be some other data that shouldn't be replaced) $\endgroup$ – happy fish Mar 30 '17 at 15:05
  • 1
    $\begingroup$ Oh I missed the other fields, does this work? MapAt[# /. ((#[[1]] -> Sequence @@ (#[[2;;-2]])) & /@ Rest@compounds) &, properties, {All, {4}}] $\endgroup$ – N.J.Evans Mar 30 '17 at 16:47
4
$\begingroup$

You may use JoinAcross with Dataset or Association. I show with Dataset below. To perform with Association remove the Postfix and Dataset from dsCompounds and dsProperties. Also, you may find the ChemicalData and ElementData functions of interest.

dsCompounds = 
 AssociationThread[First@compounds, #] & /@ Rest@compounds // Dataset

Mathematica graphics

dsProperties = 
 AssociationThread[
     Insert["N", 4]@Table["P" <> ToString@i, {i, 8}], #] & /@ properties // Dataset

Mathematica graphics

Then use JoinAcross on Key "N"

JoinAcross[dsProperties, dsCompounds, "N"]

Mathematica graphics

Hope this helps.

$\endgroup$
2
$\begingroup$

I am assuming your replacement keys are at the 4th position, for the reason above in the comment. If you continue to work with lists,

asso = Association[Rest@compounds /. {key_?NumericQ, rest___} :> (key -> List@rest)];
f[{pre : Repeated[_, {3}], key_, rest___}] := Join[{pre, rest}, asso[key]];
f /@ properties
$\endgroup$
  • $\begingroup$ now it works perfectly... $\endgroup$ – Giorgy Tisni Apr 2 '17 at 8:07

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.