Merge multiple files

Question

[Najmeh]

I have several separate Excel files that containing a sample and its gene expression. How can I combine these files and have a complete table containing all the samples and their gene expression?

Answer 1

[Mensur Dlakic]

Presumably all the files share at least one identical column, or else it is unlikely to work.

I don't know if Excel can merge the files directly, and that may be the first thing to try because it may not involve conversions I will suggest below.

Questions similar to yours get asked all the time, the main difference being that most people are trying to merge plain-text tables rather than Excel files. In your case that would mean converting the files into .csv or .tsv formats (comma-separated or tab-delimited). From that point there are many solutions on this website that you can find by searching for merge table or join table. Pandas library in python has several function for this purpose, and the same is true for dplyr in R.

Answer 2

[M__]

VB is what you are wanting, its just no one knows how to code it (rather its not worth the effort to learn).

I agree to export to CSV - this removes all the Windows tags(super important). Python will read a csv file directly into a pandas dataframa, via.

df = pd.read_csv ('file_name.csv') 

However, it needs a knowledge of Python, moreover pandas which isn't trivial.

If you have access to Linux or Unix concatenating might be all you require, viz.

cat fileA.csv fileB.csv fileC.csv > fileABC.csv

Then open it up in Excel. You can loop through it in bash on Linux or MacOSX,

for f in *csv; do cat $f >> fileABC; done

If you''ve loads of Excel files the above will be quicker (you have to be in the same directory as your files). On a Mac is you click 'Terminal' (under Utilities) and as soon as the window opens type bash ... away you go. By default OSX uses Zsh (Z shell), I would guess it has the same functionality but I don't know.

I think >> will be needed for the bash loop, using > in this one liner gives you just one file.

If you want to 'concatenate' horizontally (in rows) the Linux/Unix command is paste.

If you're super cool this could be done via awk from a command line one liner, but you'd need a knowledge of each spreadsheet column to do that.