0

I asked the same question on stackoverflow, but did not get a satisfactory answer.

I have a directory tree like this:

bacteria > species(num subdirs = 1300) > kmer(num subsudirs = 9)

The directory and subdirectories are agruped this way:

bacteria/specie1/kmer2/csv
                    kmer3/csv
                    .
                    .
                    kmer9/csv

         specie2/kmer2/csv
                    kmer3/csv
                    .
                    .
                    kmer9/csv

         specie1300/kmer2/csv
                    kmer3/csv
                    .
                    .
                    kmer9/csv

In the kmer subdirectory there are the csv files and depend on the species it has 1 or many files.

The csv files are simple, two coulmns with the kmer and it counts. I really dont know if all bigger kmers (like 6 or upper) will have the same kmer keys, because this I am counting them.

For example:

bacteria/species1/kmer2/example_csv_k2_count.csv

The csv example_csv_k2_count.csv has this structure:

kmer,count
AA, 10
AC, 20
.
.
.

For each species and kmer counts I need to concatenate the csv files in one master csv for each kmer count (2-9). For example for subdiretories of all species with kmer2 counts I need

specie1.csv1
kmer, count
aa, 22
at, 21...

specie1.csv2
kmer, count
aa, 31 
at, 18...

For bacteria/species2/kmer2:

specie2.csv1
kmer, count
aa, 22
at, 21...

specie2.csv2
kmer, count
aa, 31 
at, 18...

I need a make a master kmer2_count.csv for each species and they kmer counts:

kmer, count
    aa, 22, 31,...
    at, 21, 18,...

I tried many codes and commands that I search in stack, google, but I always get they stacked:

kmer, count
    aa, 31 
    at, 18...
kmer, count
    aa, 22
    at, 21...

Example of shell commands I tried:

    $ cat */kmer2/*.csv | datamash -t , transpose
    datamash: transpose input error: line 275 has 1 fields (previous lines had 2);

Ex2:

 awk '
            FNR==1 && NR!=1 { while (/^<header>/) getline; }
            1 {print}
        ' */kmer9/*.csv > ex.csv

Now I am using python/pandas and got the job working but for kmer counts bigger than 6, ex 7 my notebook is working for 3 days in a roll. I am trying to merge this to make a mean of counts because many species had they genome sequenced many times or because they are subspecies or strains. Than I will make a master file with the mean of all sequenced genomes.

Because this I would love a shell solution.

Thanks, Paulo

PS = Sorry about the bad text explaining my needs. Hope now it get more clear. My apologies

Improve this question
6
  • 3
    I don't understand the numbers: I see 1300, 9 and 1-12000. Two questions at the CSV output level: [A] Will all files have exactly the same kmer keys (aa, at, etc), or what happens when some files have an extra one like aq, or are missing an aa (which would cause ragged lines -- not all rows having the same number of columns). That's where your transpose error is arising. [B] Are you really proposing to output CSV rows with 12,000 columns? What would you expect to use to process those?
    – Paul_Pedant
    Dec 18, 2020 at 19:31
  • 3
    Are the fields in your CSV files really delimited by a comma and a space? Why have you added spaces to the first fields in the resulting CSV files? Joining (not stacking) CSV files is easy with csvjoin, but it only handles properly formatted CSV files. Also address Paul_Pendant's issues.
    – Kusalananda ♦
    Dec 18, 2020 at 19:44
  • 3
    This question is not clear, and needs some editing.
    – ctrl-alt-delor
    Dec 18, 2020 at 20:15
  • 1
    your output file seems to be bogus: if the header is kmer,count, you should have exactly 2 columns, not one per input file.
    – umläute
    Dec 18, 2020 at 20:23
  • 1
    And to be able to use tools that deal with proper CSV formats, you could first pre-process all the files to format the properly.
    – dirkt
    Dec 18, 2020 at 21:19

1 Answer 1

Reset to default
2

I'm not sure to have understood your goal.

If you need to pass from

cat input1.csv
kmer,count
aa,22
at,21

cat input2.csv
kmer,count
aa,31 
at,18

to

something like

kmer,count
aa,22;31
at,21;18

you can use Miller and run

mlr --csv nest --implode --values --across-records -f count input1.csv input2.csv
Improve this answer
7

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.