I have two large tab-delimited files (>10GB) and I know that when they're sorted, they're identical in content.
However, I'm interested in the order of rows and the index of the swapped ones when they share the same "key" (key here being defined as rows grouped based on
In other words, rows between these two files should be only compared against each other when they come from the same group (i.e. when they share the same Source and Location).
So for example, in the example below, rows 4, 5, 6 from
file1.tsv should be compared against 4, 5, 6 from
Note: files are normal TSV. Additional spaces are only added here to make columns center- and right-aligned for better visibility. These spaces are not part of the original files
Identifier Position Source Location AY1:2301 87 ch1 14 BC1U:4010 105 ch1 14 AC44:1230 90 ch1 15 AJC:93410 83 ch1 16 ABYY:0001 101 ch1 16 ABC:01 42 ch1 16 HH:A9CX 413 ch1 17 LK:9310 2 ch1 17 JFNE:3410 132 ch1 18 MKASDL:11 14 ch1 18 MKDFA:9401 18 ch1 18 MKASDL1:011 184 ch2 50 LKOC:AMC02 18 ch2 50 POI:1100 900 ch2 53 MCJE:09HA 11 ch2 53 ABYCI:1123 15 ch2 53 MNKA:410 1 ch2 53
Identifier Position Source Location AY1:2301 87 ch1 14 BC1U:4010 105 ch1 14 AC44:1230 90 ch1 15 ABC:01 42 ch1 16 ABYY:0001 101 ch1 16 AJC:93410 83 ch1 16 HH:A9CX 413 ch1 17 LK:9310 2 ch1 17 MKASDL:11 14 ch1 18 JFNE:3410 132 ch1 18 MKDFA:9401 18 ch1 18 MKASDL1:011 184 ch2 50 LKOC:AMC02 18 ch2 50 MNKA:410 1 ch2 53 POI:1100 900 ch2 53 ABYCI:1123 15 ch2 53 MCJE:09HA 11 ch2 53
I want to do something similar to a "diff" but at the 'group' level (where rows are only compared when they share the same
I want to extract the original "row numbers" when the order of rows are 'swapped' within the same "Source/Location" "group" (or key).
The whole row should match in terms of content.
But I have no idea how to go about this. I can only think of writing a for loop which would be extremely inefficient when my original dataset has millions of rows.
Group_Source:Location df1.index df2.index ch1:16 4 6 ch1:16 6 4 ch1:18 9 10 ch1:18 10 9 ch2:53 14 15 ch2:53 15 17 ch2:53 17 14
- Both dataframes have the same number of rows
- Both dataframes are identical (only order of rows are swapped, so if both are sorted by Source, then Location and then Position and then Identifier, then they will be exactly identical)
- 'Swapped' rows always match exactly in terms of content in all columns
Location. There are several tools like this, one example is q.
ABCis a HUGO gene name, so I assumed the others were just names I wasn't familiar with, thanks.