Hello!
Thank you for your responses, I hope this topic will help me.
I need to format the data in Table 1 according to the template in Table 2 while preserving the data from Table 1. Is there any way to automate this, or will I have to manually transfer the data?
Can you give an example of what the table 1 and table 2 look like? It's hard to answer this without a clear example of how the data is or could be structured
Thanks @Aleksandr_Izmailov . Given a full row of data in Table 1, how would that map to Table 2? I mean, where would (for example) the "Email" from Table 1 go in Table 2? Would several columns from Table 1 be used to populate a single column in Table 2?
Thank you for your concern! I have already done it. Maybe not perfectly, but the result is satisfactory and achieved.A person familiar with Excel formulas helped.
Hi @ostephens and @Aleksandr_Izmailov ,
I came across the same question and I'm wondering if there is an easy way to do it inside OpenRefine. On one hand, I have a fixed, empty template of column headers (names) defining standard biological sample characteristics (Reference template). On the other hand, I frequently get sample data from lab with messy but relevant header names containing values in cells (tsv format).
Now I want to first import the reference template into OpenRefine (which has standard column headers but empty values) and then import my sample data so that let say "Age" column in my reference template gets populated by values from "Age group" column in my sample file or "Sex" column values get populated under the "Sex" column in the Reference template.
Reference template
Sample data from lab
Strange request as OpenRefine has no "import by name" option.
Normal way is to format the required columns and names before importing the data.
When you need [Sex] [Disease] and [Age] just use the spreadsheet to arrange them this way.
You could use a JSON script after importing like:
[
{
"op": "core/column-rename",
"oldColumnName": "Sex",
"newColumnName": "Age group",
"description": "Rename column Age to Age group"
}
{.... etc ...}
]
from the Apply option menu, but this would require a script for each combination of input columns and even creation a dummy input column here when the disease is missing:-(
Using the spreadsheet to prepare the colums as needed would be my recommandation.
@Erfanesi On the CSV/TSV importer, during the import preview, we have option to specify custom column names [separated by commas] , if that helps you?
Thanks @Nico_Altink ! Just wanted to examine the possibilities.
Thanks @thadguidry ! I'm aware of that option. However, when you have a large number of columns with total or partial header matches with your reference column names, you don't wanna do this manually. So, my conclusion is that this should be done outside OpenRefine.
