I am trying to create a matrix of plant traits and plant species. There are 2,912,746 rows in the data and 3 columns. There are different numbers of traits for each species, and not every species has every trait. The data format is tab delimited.

Current format--

  Species   Trait      Value
  Species_1 SLA        4
  Species_1 Photopath  C3
  Species_1 Mycorrhiza AMF
  Species_2 SLA        3 
  Species_2 Growth     10

Desired format--

          SLA Photopath Mycorrhiza Growth
Species_1 4   C3        AMF
Species_2 3                        10

Any help with this would be OH SO appreciated. It has been a quite the challenge, and I'm not sure where to begin.

Thank you!!!!

~Mark Anthony

  • These sort of manipulations is what SQL was designed for. You could use PostgreSQL or something lightweight like sqlite. Other alternatives that could probably do what you want include R (with data frames) and the Python pandas library. – Faheem Mitha Feb 8 '16 at 8:41

To begin I recommend you to have all data inside a text/plain file, where as you wrote, all data is tab delimited.

Then you can try to experiment how to filter the columns using cut


#get first column of a tab delimited file
> cut -f 1 -d $'\t' file

After that, I recommend you to install a database engine, as for example mysql-server and a workbench as mysql-workbench.

Then if you want, I can help you to build an indexed database with some insert functions, which will help you to insert, get and analyze all data you need now and in the future with ease.

Other choice

Is to change the extention of that text/plain file to .csv and open it with LibreOffice Calc. After opening it, select the tabulator as delimiter. You will be able to analyze the data using pivot tables, but I am not sure how can you achieve the output you want.

Bash solution

First, let's make a new directory to work with.

> mkdir test

Then copy your source file to that new directory.

> cp source test/file

Then enter to the directory

> cd test

Now, remove the first line of the file (the column names row)

> nano file
press ctrl+k, ctrl+x and y

Then sort the file

> sort file > file.sort

Get all column names

> cut -f 2 -d $'\t' file > cols

Make a directory for columns

> mkdir c

Split all data by columns (ignore errors)

> while read i ; do grep "$i" file | cut -f 1,3 -d $'\t' > "c/$i" ; done < cols

Join all data and delete repeats

> cut -f 1 -d $'\t' file.sort > result
> for f in c/* ; do join result "$f" > tmp ; join -v 1 result "$f" | sed -e 's/$/ -/g' >> tmp ; sort tmp > result ; done
> uniq result
  • I have one solution for you to get the desired output. I can't write it now, but I'll come back in short... – ncomputers Feb 7 '16 at 23:00
  • Hi ncomputers- thank you very much for the response. I've been playing around considerably with column and row matching with various awk and sed commands, as well as cutting out specific columns and trying to sort them in notepad++. I think your suggestion of a database may be my best next route. If you are able/willing to discuss further your offer of support getting going with sql, I would be beyond thankful! – Mark Anthony Feb 7 '16 at 23:48
  • @MarkAnthony To have a databse could help you a lot if this data is going to grow. I have posted you a bash solution, which requrie some linux common commands... I'll retest it again in a few minutes.... It should work for what you want... – ncomputers Feb 8 '16 at 0:52
  • @MarkAnthony I've tested it again: it works.... all fields should be delimited by tabulator. The performance could be slow for 2,912,746 rows.... – ncomputers Feb 8 '16 at 1:03
  • @MarkAnthony for the design of the database, sure I am open to help you. Rather than designing the database, I'd like to help you learn to design the database ... :D – ncomputers Feb 8 '16 at 6:50

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.