Moving data in columns left or right
321 views
Skip to first unread message
fintan macmahon
Jan 6, 2016, 7:45:02 AM1/6/16
to OpenRefine
Thad Guidry
Jan 6, 2016, 9:35:09 AM1/6/16
to openrefine
Nope.
But if or when we get operation macros, you could easily do that. Bounties accepted on issue here: https://github.com/OpenRefine/OpenRefine/issues/251
--
You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Tom Morris
Jan 6, 2016, 4:13:21 PM1/6/16
to openr...@googlegroups.com
How about deleting the extra date column and renaming the others? Would that create the effect that you want?
Tom
Owen Stephens
Jan 7, 2016, 3:24:49 AM1/7/16
to OpenRefine
Hi Fintan,
It looks like the two example rows you have given are shifted by a different number of columns - is that correct? If not, I'd agree with Tom's suggestion. However if I've got this right, correcting this all in one go is likely to be difficult.
* The first thing I'd look at is how the problem occurred in the first place - is it something that could be fixed by starting over from the original source (and how much work would that be)
* If that isn't practical, or isn't going to help, you might consider exporting the data into separate csv files. You'd need to find a way of filtering to rows that have been shifted by a certain number of places, and then do an export for each of these different sets of records. You could then fix the 'shift' by adding appropriate extra column headers in the first line of each file, then merge these files together (outside OpenRefine), before bringing them back into OpenRefine with the shift fixed.
* If you want/need to keep this entirely within OpenRefine, then I think you'd have to once again filter down to a set of records with a consistent shift, then populate the data from the old (wrong) column to a new (corrected) column. I'd probably setup a whole new set of 'correct' columns for this and use transforms like "cells["shifted col name"].value" to populate the new cols from the old
If none of these appeal, the other approach that occurred to me is using the 'Transpose' function in OpenRefine. This would allow you to move the column headings + values into rows with a key/value pair (one col for the header, one for the value). You could then write transforms which would fix headers, and then transpose back. This could potentially be quite efficient, but when you transpose back you have to know that there will be the same number of rows to transpose back for each record.
Hope that helps with some ideas
Owen
fintan macmahon
Jan 7, 2016, 10:08:11 AM1/7/16
to OpenRefine
Yeah Owen, that's correct. I was given the data in it's current form. I think I'll go for option 2. Thanks for the replies.
fintan
fintan macmahon
Jan 7, 2016, 10:55:26 AM1/7/16
to OpenRefine
Ok, came up with a slightly different solution. I exported the data using the templating feature after refining the filter. Moved the values up one. I'm going to try importing them again. Thanks
fintan
{
"Path" : {{jsonize(cells["Path"].value)}},
"Filename" : {{jsonize(cells["Filename"].value)}},
"Folder" : {{jsonize(cells["Folder"].value)}},
"Link" : {{jsonize(cells["Link"].value)}},
"MSCID" : {{jsonize(cells["MSCID"].value)}},
"Title1" : {{jsonize(cells["Title1"].value)}},
"XMP-xmp:Description" : {{jsonize(cells["XMP-xmp:Description"].value)}},
"Drawing No" : {{jsonize(cells["Drawing No"].value)}},
"CreateDate" : {{jsonize(cells["CreateDate"].value)}},
"Scale" : {{jsonize(cells["Scale"].value)}},
"Creator" : {{jsonize(cells["Creator"].value)}},
"Survey By" : {{jsonize(cells["Survey By"].value)}},
"Original Revision" : {{jsonize(cells["Original Revision"].value)}},
"Other" : {{jsonize(cells["Other"].value)}},
"Nat Mon No" : {{jsonize(cells["County"].value)}},
"County" : {{jsonize(cells["Sitename"].value)}},
"Sitename" : {{jsonize(cells["Townland"].value)}},
"Townland" : {{jsonize(cells["Primary SMR No"].value)}},
"Primary SMR No" : {{jsonize(cells["ClassType Monument"].value)}},
"ClassType Monument" : {{jsonize(cells["ITM EASTING"].value)}},
"ITM EASTING" : {{jsonize(cells["ITMNORTHING"].value)}},
"ITMNORTHING" : {{jsonize(cells["IRISHGRIDEASTING"].value)}},
"IRISHGRIDEASTING" : {{jsonize(cells["IRISHGRIDNORTHING"].value)}},
"IRISHGRIDNORTHING" : {{jsonize(cells["Longitude"].value)}},
"Longitude" : {{jsonize(cells["Latitude"].value)}},
"Latitude" : {{jsonize(cells["Column 27"].value)}}
Owen Stephens
Jan 12, 2016, 12:52:30 PM1/12/16
to OpenRefine
Neat! Hope that worked for you
Owen
Reply all
Reply to author
Forward
0 new messages