4

PROBLEM:

I have a CSV file with 16,382 columns with data that looks like this:

+-------------+-----------+------+-------+------+------+------+-----+-------+-----+
| PATIENT_ID  | DIAGNOSIS |  1   |   2   |  3   |  9   |  10  | 13  |  14   | ... |
+-------------+-----------+------+-------+------+------+------+-----+-------+-----+
| X764_130520 | 0         | 0.35 | 9.68  | 0.11 | 0.04 | 0.03 | 0   | 32.54 |     |
| X800_130701 | 0         | 2.24 | 32.04 | 0.13 | 0.34 | 0.04 | 0   | 39.47 | ... |
| X164_120423 | 6         | 3.12 | 24.08 | 0.04 | 0.1  | 0.08 | 0   | 73.47 | ... |
| X218_120425 | 6         | 2.48 | 20.62 | 0    | 0.22 | 0    | 0   | 59.06 | ... |
| ...         | ...       | ...  | ...   | ...  | ...  | ...  | ... | ...   |     |
| X824_130725 | 1         | 0.76 | 44.77 | 0.1  | 0.28 | 0.13 | 0   | 45.35 |     |
+-------------+-----------+------+-------+------+------+------+-----+-------+-----+

RAW FORMAT:

PATIENT_ID,DIAGNOSIS,1,2,3,9,10,13,14,15,16,18,19,20,21,22,23,24,25,...
X764_130520,0,0.35,9.68,0.11,0.04,0.03,0,32.54,0.13,49.73,33.34,0.77,..
X800_130701,0,2.24,32.04,0.13,0.34,0.04,0,39.47,0.51,44.92,...
X218_120425,6,2.48,20.62,0,0.22,0,0,59.06,0.11,86.48,62.63,1.09,110.83,...
X266_120430,6,2.66,19.95,0.05,0.13,0.1,0,39.72,0.32,41.78,40.78,1.03,...
X276_120430,6,2.45,14.64,0,0.06,0.1,0,60.77,0.34,88.92,59.66,...
X411_120503,6,3.33,28.23,0,0.19,0.04,0,54.92,0.52,64.29,53.62,0.7,...
X448_120507,6,2.48,22.98,0,0.08,0,0,46.58,0.25,67.85,57.76,...
X517_120515,6,3.5,30.19,0.07,0.07,0.03,0.02,51.38,0.25,67.89,56.2,1.07,...

Except for the first two columns, everything else afterward are 16,380 columns of floating points. The heading for these floating points are integer IDs of human genes and are not contiguous natural numbers.

WHAT I WANT:

I am using PostgreSQL 9.5.6 on 64-bit Linux based on Ubuntu 16.04.4

I want to put the 16,380 numbers into a REAL array of size 16,380 in PostgreSQL, but I am lost as to how to do this.

I want the table in PostgreSQL to look like this:

+------------+-----------+---------------+
| patient_id | diagnosis |    gene_id    |
+------------+-----------+---------------+
| ...        | ...       | {...,...,...} |
| ...        | ...       | {...,...,...} |
| ...        | ...       | {...,...,...} |
+------------+-----------+---------------+

I created the table above with this code:

CREATE TABLE rosmap_rnaseq_entrez (
    patient_id VARCHAR(20),
    diagnosis INTEGER,
    gene_id REAL[16380]
);

I just need to find a proper SQL command to import the CSV file into the table above.

2

Perl

Not golfing, and not trying to write the best perl script, here is a one-liner quick transform script that does what you want.

perl -wnlE'next if /^$/ or $. == 1; s/,\.+$//; my ($p,$d,$s) = (split /,/,$_,3); say join ",", $p, $d,qq["{$s}"]'

I'll break it down..

# skips empty lines and the first line (header)
next if /^$/ or $. == 1;

# replaces ,... at the end of every file (don't use ... in samples)
s/,\.+$//;

# patientid, diag, stuff to stuff in array.
my ($p, $d, $s) = (split /,/, $_, 3);

# join the first two and output the last one. wrapped in array.
say join ",", $p, $d, qq["{$s}"];

Output

X764_130520,0,"{0.35,9.68,0.11,0.04,0.03,0,32.54,0.13,49.73,33.34,0.77}"
X800_130701,0,"{2.24,32.04,0.13,0.34,0.04,0,39.47,0.51,44.92}"
X218_120425,6,"{2.48,20.62,0,0.22,0,0,59.06,0.11,86.48,62.63,1.09,110.83}"
X266_120430,6,"{2.66,19.95,0.05,0.13,0.1,0,39.72,0.32,41.78,40.78,1.03}"

Execution

perl -wnE'above transform' ./dat.txt  > out.txt
COPY rosmap_rnaseq_entrez FROM '/tmp/out.txt' CSV DELIMITER ',';

TABLE rosmap_rnaseq_entrez ;
 patient_id  | diagnosis |                          gene_id                           
-------------+-----------+------------------------------------------------------------
 X764_130520 |         0 | {0.35,9.68,0.11,0.04,0.03,0,32.54,0.13,49.73,33.34,0.77}
 X800_130701 |         0 | {2.24,32.04,0.13,0.34,0.04,0,39.47,0.51,44.92}
 X218_120425 |         6 | {2.48,20.62,0,0.22,0,0,59.06,0.11,86.48,62.63,1.09,110.83}
 X266_120430 |         6 | {2.66,19.95,0.05,0.13,0.1,0,39.72,0.32,41.78,40.78,1.03}
(4 rows)

Golf

Using this method you could even go for something more compact

perl -pwnlE'$_=sprintf(q[%s,%s,"{%s}"],split/,/,$_,3)'
  • @EvanCaroll The length of the array might be causing an error. I can store the shorter array literal, but when I run it on the actual array of size 16,380, I am getting "Error: malformed array literal: "{2.45,...} SQL state: 22P02 Detail: Unexpected "," character." – Dobob Mar 11 '17 at 4:06
  • It' is adding a new line just after the array ends, just before "}"" character, causing the error. – Dobob Mar 11 '17 at 4:28
  • nevermind, I wrote some python and fixed it. – Dobob Mar 11 '17 at 6:44
3

Couldn't resist :-)

<data.txt perl -pe 's/((.*?,){2})(.*)/\1"{\3}"/'

X764_130520,0,"{0.35,9.68,0.11,0.04,0.03,0,32.54,0.13,49.73,33.34,0.77,..}"
X800_130701,0,"{2.24,32.04,0.13,0.34,0.04,0,39.47,0.51,44.92,...}"
X218_120425,6,"{2.48,20.62,0,0.22,0,0,59.06,0.11,86.48,62.63,1.09,110.83,...}"
X266_120430,6,"{2.66,19.95,0.05,0.13,0.1,0,39.72,0.32,41.78,40.78,1.03,...}"
X276_120430,6,"{2.45,14.64,0,0.06,0.1,0,60.77,0.34,88.92,59.66,...}"
X411_120503,6,"{3.33,28.23,0,0.19,0.04,0,54.92,0.52,64.29,53.62,0.7,...}"
X448_120507,6,"{2.48,22.98,0,0.08,0,0,46.58,0.25,67.85,57.76,...}"
X517_120515,6,"{3.5,30.19,0.07,0.07,0.03,0.02,51.38,0.25,67.89,56.2,1.07,...}"
3

You don't need any auxiliary tools. This works on any platform.
COPY to a simple temporary ancillary table:

CREATE TEMP TABLE tmp(txt text);

COPY tmp FROM '/path/to/your_file.csv' (FORMAT csv, ENCODING 'utf8', DELIMITER E'\b');

I chose backspace as delimiter (E'\b') which never occurs. This way you get one text column.
Assuming UTF8 encoding. Adapt if necessary.
If you can't use SQL COPY, consider the psql meta command \copy instead:

Create the target table by parsing the row in the next step:

CREATE TABLE target AS
SELECT split_part(txt, ',', 1) AS patient_id
     , split_part(txt, ',', 2) AS diagnosis
     , string_to_array(substring(txt, '^(?:[^,]*,){2}(.*)'), ',')::real[] AS arr
FROM   tmp;

Simple and fast.

The temporary table dies at the end of the session automatically.
The first line of integer values can be stored in real[], too.
About split_part():

The regular expression in substring(): '^(?:[^,]*,){2}(.*)' - it basically says: omit the first two fields and take the rest.
This assumes no white space between values else you might want to use trim(). Related answer with more explanation and links for regular expressions:

Since an array does not seem useful for your purpose, you might instead just keep the comma-separated list:

     substring(txt, '^(?:[^,]*,){2}(.*)') AS list
  • This is the cleanest solution. It would be interesting to know if it's faster than the other solutions, but my guess is that Perl is faster on string manipulation. – pietrop Mar 11 '17 at 6:32
  • 1
    @pietrop: It's pretty fast, but Perl is the master of regular expressions. That part will be faster in Perl. OTOH, if the file isn't huge, the overhead of involving another piece of software will cost more. Also: with different kinds of code: more complications, more points of failure. And: if we need to optimize performance, we can do without the regular expression. I did not bother, because the OP did not mention performance. – Erwin Brandstetter Mar 11 '17 at 13:53
  • @pietrop Massively slower. – Evan Carroll Mar 11 '17 at 18:17
  • 1
    @pietrop Even if you turned off all the durability options I can't see this being even close to the speed, and it has nothing to with regular expressions (though yes perl is faster there too). It's creating rows as an intermediary storage. That's perfectly fine and likely not an issue, but I doubt it's even close to the same speed. Perl arrays are faster than extending than PostgreSQL tables, but here we're not even doing that. We're just transforming a stream. Nothing touches the disk and gets materialized except the final copy. – Evan Carroll Mar 11 '17 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.