Im looking to transpose a file from long format using either an awk statement or Python.
My input file would look like this;
ID Chr_Position Geno
111 1_1234 0
111 1_12345 1
111 1_2345 0
111 2_23245 2
and my required output would like to be (header not necessarily needed)
ID 1_1234 1_12345 2_2345
111 0 1 0 2
112 1 1 1 1
113 1 1 0 2
They are big files, over 100,000 IDs with over 10,000 rows each but can split into smaller if needed. I have previous code that will transpose rows and columns but not sure how to take it from long format.
idk how to generate the output you posted given the input file you posted but maybe this'll help get you on the track:
$ cat tst.awk
NR==1 { next }
$1 != prev { if (line!="") print prev line; line=""; prev=$1 }
{ line = line OFS $NF }
END { if (line!="") print prev line }
$ awk -f tst.awk file
111 0 1 0 2
if your data is well structured (the same number of columns/rows in the same order, consistent separators) you can try this
$ sed 1d file | pr -4ats' ' | cut -d' ' -f1,3,6,9,12
111 0 1 0 2
112 1 0 4 3
for the test data file
$ cat file
ID Chr_Position Geno
111 c1 0
111 c2 1
111 c3 0
111 c4 2
112 c1 1
112 c2 0
112 c3 4
112 c4 3