3 Working with Tables¶

Row-wise¶

To merge Tables row-wise we can use the Table method ``stack_tables``. The method requires that all tables must have columns of the same name, type, and format in the same order. When processing samples with the same workflow this is given in most of the cases. However, the given example peaks_table and sample table t have different columns. A straightforward way to compare the columns is transforming the column names into sets:

[20]:

set(t.col_names) - set(peaks_table.col_names)

[20]:

{'c_uM'}

[21]:

set(peaks_table.col_names) - set(t.col_names)

[21]:

set()

Obviously, column c_uM is missing in peaks_table. There are now different ways to cope with the missing column. We could simply add the missing column to our peaks table using peaks_table.add_column_with_constant_value, we could remove the column from the sample t using t.drop_columns, or we can extract the common columns from the sample table using t.extract_columns. The latter method is not in place and hence we do not change the original Table.

[22]:

sample = t.extract_columns(*peaks_table.col_names)

Some explanations:

The command ~~~ t.extract_column(col_name_1, col_name_2, …, col_name_n) ~~~ returns a new Table with all columns whose names are listed as function arguments. If the number of columns to be extracted is long, there is an elegant way to unpack lists or tuples as function arguments using *. Since the Table attribute col_names returns a tuple with column names in the correct order, we can unpack those names directly.

Another issue which comes along with table stacking: after stacking the tables together we do no longer know which row belongs to which sample. We need an identifier for the sample. We can again use the source of the peak map.

[23]:

def update_source(t, insert_after="compound"):
    pm = t.peakmap.unique_value()
    source = pm.meta_data["source"]
    t.add_column_with_constant_value(
        "source", source, str, insert_after=insert_after
    )

Finally we can execute following commands:

[24]:

update_source(sample)
update_source(peaks_table)
t_comp = emzed.Table.stack_tables([sample, peaks_table])

When inspecting the results, we observe that identical amino acids differ in retention times. This is because both samples were measured in different batches with different columns and with slightly modifier methods. However, RT alignment should fix that problem. We will come to this later.

Column-wise¶

Alternatively, we can use the Table.join or Table.left_join method:

[25]:

t_comp = peaks_table.join(
    sample, peaks_table.compound == sample.compound
)

The resulting column looks like follows

At first glance, horizontal merging via join seems to be much more efficient. Each row contains the result of a single amino acid for all samples. However, with each join you will double the number of columns. To keep the column names unique postfixes __0, __1, ... are added to each column name which requires postfix handling for further processing. Hence, for comparison of multiple samples vertical merging in combination with row indexing is much better suited. Nevertheless, joining of two tables is a powerful tool for more sophisticated table comparisons.

Note that you can also display the chromatogram and mass spectrum for the two peakmaps simultaneously by selecting both peakmaps in the plot spectra box (hold Ctrl/Cmd while selecting). This is a handy way to directly compare the peaks of two (or more) samples.

Column operations¶

You can directly apply functions to one or more table columns with ``t.apply()``, an extremely powerful the method featuring sophisticated operations on tables. It is used in combination with add_column(), replace_column(), or add_or_replace_column(). Let’s create an example table:

[26]:

t = emzed.to_table("a", ["apple"], str)
t.add_column("b", ["juice"], str)
t

[26]:

We can now define a function that combines two strings with a delimiter:

[27]:

def combine(a, b, delimiter):
    return a + delimiter + b

Now we can add a new column combined to the table with the command:

[28]:

t.add_or_replace_column(
    "combined", t.apply(combine, t.a, t.b, "_"), str
)
t

[28]:

We add or replace column combined to the table t, yielding the corresponding values with the method apply. apply takes the function we want to apply as first argument, followed by the function arguments. In given example, t.apply calls the function combine with its arguments a, b, link handing over t.a as argument a, t.b as argument b, and the string '_' as argument link. As you might have noticed, it is possible to combine Table columns with any other object accepted as argument by the function called.

A second, more relevant example. Currently peak_table has the columns rtmin and rtmax, but the retention time column ‘rt’ is missing. We could simply estimate the value by (rtmin + rtmax) / 2. However, this is not completely accurate since the peak apex is not always placed in the middle of the RT window. A better way would be to determine the retention time at max intensity, which can be obtained from peak integration. The column model contains the required peak information.

[29]:

import matplotlib.pyplot as plt  # from matplotlib library

# we get the model object in the first row
model = peaks_table.model.to_list()[0]

# we extract retention times and intensities
rts, ints = model.graph()

# we split the peak
plt.plot(rts, ints)
plt.show()

Hence, we can get the retention time at peak appex from the model graph. Here is a possible function returning the retention time at appex.

[30]:

def get_rt(model, rtmin, rtmax):
    rts, ints = model.graph()  # iterable of (rt, intensity) pairs
    # find rt with highest intensity
    if len(rts):
        rt, _ = max(zip(rts, ints), key=lambda v: v[1])
        return rt
    # in case the peak does not exist
    return (rtmin + rtmax) / 2

We can now define a more sophisticated function update_retention_time(t), which adds or replaces a column rt to the table.

[31]:

def update_retention_time(t):
    t.add_or_replace_column(
        "rt", t.apply(get_rt, t.model, t.rtmin, t.rtmax), emzed.RtType
    )

Column names as string representation¶

So far, we directly accessed a column by the corresponding table attribute, e.g.:

Grouping and splitting¶

Grouping rows is another important Table feature. There exists many possible use cases and it plays an important role in workflow routines. Most grouping routines can be adressed with Table method ``group_by``. Again, we can take our amino analysis results to learn how to use it. For instance: Which amino acid is most abundant in which sample?

To answer the question let us load a Table containing the amino acid concentations of Caulobacter and Arabidopsis samples.

[32]:

t = emzed.io.load_table(path_aa_conc_table)
print(t[:5])

id   source                      compound       c_uM
int  str                         str            float
---  --------------------------  -------------  --------
  0  AA_sample_caulobacter.mzML  Phenylalanine  4.09e-01
  1  AA_sample_caulobacter.mzML  Leucine        1.08e+00
  2  AA_sample_caulobacter.mzML  Tryptophane    1.09e-01
  3  AA_sample_caulobacter.mzML  Isoleucine     1.23e+00
  4  AA_sample_caulobacter.mzML  Methionine     1.32e-01

We can now group the rows by combound name

[33]:

grouped = t.group_by(t.compound)

We obtain a ‘grouped_by’ object named ‘grouped’ where Table rows are grouped by the column ‘compound’. The group_by objects provides a number of methods like e.g. min, max, std, mean, median, sum, … Since we are interested in the compound-wise maximal amino acid concentration:

[34]:

t.add_or_replace_column(
    "c_max_uM", grouped.max(t.c_uM), float, "%.2e"
)
print(t[:5])  # only the first 5 rows

id   source                      compound       c_uM      c_max_uM
int  str                         str            float     float
---  --------------------------  -------------  --------  --------
  0  AA_sample_caulobacter.mzML  Phenylalanine  4.09e-01  4.09e-01
  1  AA_sample_caulobacter.mzML  Leucine        1.08e+00  1.08e+00
  2  AA_sample_caulobacter.mzML  Tryptophane    1.09e-01  2.92e-01
  3  AA_sample_caulobacter.mzML  Isoleucine     1.23e+00  1.23e+00
  4  AA_sample_caulobacter.mzML  Methionine     1.32e-01  1.32e-01

Finally, we can use the again Table.filter to obtain the most intense amino acids

[35]:

most_intense = t.filter(t.c_uM.approx_equal(t.c_max_uM, 1e-6, 0))
print(most_intense[:5])

id   source                      compound       c_uM      c_max_uM
int  str                         str            float     float
---  --------------------------  -------------  --------  --------
  0  AA_sample_caulobacter.mzML  Phenylalanine  4.09e-01  4.09e-01
  1  AA_sample_caulobacter.mzML  Leucine        1.08e+00  1.08e+00
  3  AA_sample_caulobacter.mzML  Isoleucine     1.23e+00  1.23e+00
  4  AA_sample_caulobacter.mzML  Methionine     1.32e-01  1.32e-01
  5  AA_sample_caulobacter.mzML  Valine         2.33e+00  2.33e+00

Remarks:

• Intuitively, for filtering one would compare whether values in the column ‘c_uM’ and ‘c_max_uM’ are equal. However, comparing numerical values via ‘==’ is critical when values were calculated. One solution is to use the column expression approx_equal(t[col_name], atol, rtol) with atol as absolute and rtol as relative tolerance.

• We separated the group_by operation into 2 logical steps. Here, the single line command:

• In above example we extract the sample with maximal concentration for each amino acid with filter and we loose the concentration in all other samples. Alternatively, we can add a new column max_sample_name showing the name of the sample with the maximal concentration of each amino acid. This is possible with method group_by.aggregate, which allows applying a user defined function to the ‘group_by’ object.

Let’s find the name of the sample with maximal concentration and add it as a new column:

[36]:

# the function required for agregate
def name_of_max_sample(concs, names):
    # we create a list of tuples -> [(conc1, source1), ...]
    pairs = zip(concs, names)
    # max considers first element first, then second:
    max_pair = max(pairs)
    # return only the sample name
    return max_pair[1]

[37]:

t = emzed.io.load_table(path_aa_conc_table)
grouped = t.group_by(t.compound)
t.add_or_replace_column(
    "max_sample_name",
    grouped.aggregate(name_of_max_sample, t.c_uM, t.source),
    str,
)
print(t[:5])

id   source                      compound       c_uM      max_sample_name
int  str                         str            float     str
---  --------------------------  -------------  --------  --------------------------
  0  AA_sample_caulobacter.mzML  Phenylalanine  4.09e-01  AA_sample_caulobacter.mzML
  1  AA_sample_caulobacter.mzML  Leucine        1.08e+00  AA_sample_caulobacter.mzML
  2  AA_sample_caulobacter.mzML  Tryptophane    1.09e-01  AA_sample_arabidopsis.mzML
  3  AA_sample_caulobacter.mzML  Isoleucine     1.23e+00  AA_sample_caulobacter.mzML
  4  AA_sample_caulobacter.mzML  Methionine     1.32e-01  AA_sample_caulobacter.mzML

As you might have noticed the grouped_by method aggregate and the table method apply are highly similar. Whereas apply always operates row-wise aggregate features column-wise operations on grouped rows. Hence, clever row indexing enables highly flexible table operations.

To split a table into several sub-tables by column(s), Table provides the splitting methods split_by returning a list of Tables and split_by_iter returning a memory efficient generator object. As a general rule, you might use split_by when expecting few tables and split_by_iter when expecting many tables after the split process. Let us split the table by max_sample_name:

[38]:

tables = t.split_by("max_sample_name")
for sub in tables:
    sub.print_(4)
    print("\n")

id   source                      compound       c_uM      max_sample_name
int  str                         str            float     str
---  --------------------------  -------------  --------  --------------------------
  0  AA_sample_caulobacter.mzML  Phenylalanine  4.09e-01  AA_sample_caulobacter.mzML
...  ...                         ...                 ...  ...
 19  AA_sample_arabidopsis.mzML  Arginine       0.00e+00  AA_sample_caulobacter.mzML
 20  AA_sample_arabidopsis.mzML  Lysine         0.00e+00  AA_sample_caulobacter.mzML


id   source                      compound     c_uM      max_sample_name
int  str                         str          float     str
---  --------------------------  -----------  --------  --------------------------
  2  AA_sample_caulobacter.mzML  Tryptophane  1.09e-01  AA_sample_arabidopsis.mzML
...  ...                         ...               ...  ...
 16  AA_sample_arabidopsis.mzML  Asparagine   3.09e+01  AA_sample_arabidopsis.mzML
 17  AA_sample_arabidopsis.mzML  Aspartate    1.92e+01  AA_sample_arabidopsis.mzML


id   source                      compound  c_uM   max_sample_name
int  str                         str       float  str
---  --------------------------  --------  -----  ---------------
 12  AA_sample_caulobacter.mzML  Glycine       -  -
 12  AA_sample_arabidopsis.mzML  Glycine       -  -

We get 3 tables by splitting, one table for each sample and one table where max_sample_name was None, thus containing the amino acids which have not been detected in any sample.

Adding and replacing columns between tables¶

You can add values that are linked by a shared identifier from one table to another with the column expression lookup:

The method requires a row identifier column in both the source and the target table where each row has a unique id and for each target id a source id exists. You can use lookup together with the table expressions add_column, replace_column, and add_or_replace_column. An example:

[39]:

t1 = emzed.to_table("a", ["here", "comes", "sunshine"], str)
t1.add_enumeration()
t1

[39]:

[40]:

t2 = emzed.to_table("b", ["it`s", "rain", "enough!"], str)
t2.add_enumeration()
t2

[40]:

If we want sunshine in table t2 we can replace values of column b by values of column t1.a:

[41]:

t2.replace_column("b", t2.id.lookup(t1.id, t1.a), str)
t2

[41]:

The expression lookup is extremely useful if you just require a limited number of columns from another table with corresponding row identifiers, since it avoids cumbersome column removing and renaming after Table.join operations.