5
$\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$
7
  • 2
    $\begingroup$ values is merged in that answer, please have a try. $\endgroup$
    – vapor
    Mar 30, 2017 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, 2017 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$ Mar 30, 2017 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$
    – vapor
    Mar 30, 2017 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, 2017 at 16:47

2 Answers 2

7
$\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$
1
  • $\begingroup$ now it works perfectly... $\endgroup$ Apr 2, 2017 at 8:07

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .