Merging two files based on the values of one columm

Question

I have two .csv files which share one column header, which is WELL POSITION, and I would like to merge both files into one.

Also, one of the files have two columns in which there are individual values separated by a vertical line (|). I would need to format that .csv file so that I have each of that values in a single row.

I send attached the two files I mention.

meta.csv

BASELINESUBTRACTED.csv

Ben_BarrIngh · Answer

Hi Pilar,

Realised I didn't add part two:

Once you've got the 'Meta' and 'Baseline' files in, you can join them together by using the fact they both have columns describing the 'Well' - go to the Baseline table, and go Tables>Update. You can select to update it with the 'Meta' table, and just match the columns that show the wells together (see below)

This will stick the metadata from one table, to the other table via the identification of 'Well'

Thanks,
Ben

Ben_BarrIngh · Answer

Hi Pilar,

What you have here is called 'pipe-delimited' text with the X and Y columns holding the data as "1 | 2 | 3" and so on. There is an option in JMP in Cols > Utilities > Text to Columns that can help. I'll give you a full guide of what I did below, I've also created a workflow with workflow builder for you to follow along.

First you want to import both the baseline and meta file, you can do this with File>Open or just drag/drop into a JMP window. Starting with the baseline:

We want to remove the X and Y separators and create separate columns for each entry, that we will later 'stick together' with JMP. For both the X and Y columns we want to click the column, go to Cols>Utilities>Text to Columns. Once the window pops up, add the '|' as your delimiter, then hit okay. Do the same for your Y column.

The data table will look like this:

We don't want to keep the original 'X' and 'Y' column, so we can delete them both.

Now we want to create two columns from the data, one for the X, another for the Y. To do this go to Table > Stack, then select all of your X and Y columns. So JMP can recognise these are two distinct groups that are being stacked, click 'Multiple Series Stack', set the series to '2' and hit 'contiguous' (see below).

The resulting table will have your X and Y table correctly stacked and lined up, you can delete the 'Label' and 'Label2' columns, rename the 'Data' columns to x and Y, and then it's done.

As I mentioned, I created a workflow from this, and just added a step to pick the excel file, so this should work for all your files, guided that they follow the same format (I added a graphing step at the end just to show how you can also automating graphing).

Hope that helps!
Thanks,

Ben

JMP Community - Joining Excel Example from plates.jmpflow