If you read your data into R, it is simple to compute the percentages. Use Save
As in Excel to save your data as a .csv (comma separated variables) file. Then
use read.csv() to create a data frame in R as Jim indicated. Put it in the
default directory that R is using (this depends on what operating system you
are using). Then import the file with
raw_data <- read.csv("YourData.csv")
You may need to add some arguments in read.csv() depending on if you have
column headings or not. Blank fields in Excel will be interpreted as missing
values, not zeros, but you did not give us any of your data (even just the
first 10, rows and columns) so it is impossible to be more specific. Once you
have the data frame (and have replaced the missing values with zeros if
necessary), the process is simple:
pct_data <- prop.table(as.matrix(raw_data), 2) * 100
will produce a matrix with percentages down each column and store it as a
matrix object (variable) called pct_data. R uses different methods to store
different kinds of data. The read.csv() function creates a data frame which can
handle a mixture of character and numeric data, but the prop.table() function
only accepts a matrix of numeric data and returns a matrix of numeric data. The
data you described is all numeric so it is easy to switch the data frame to a
matrix (and then back again if you want). If you are going to use R, you will
need to spend some time reading about how it works, but as you can see, that
time invested will make some operations much simpler than Excel and will allow
you to conduct analyses that Excel does not even attempt.
You can get details on these three functions by running the following commands
in R:
?read.csv
?prop.table
?as.matrix
-------------------------------------
David L Carlson
Department of Anthropology
Texas A&M University
College Station, TX 77840-4352
-----Original Message-----
From: R-help [mailto:r-help-boun...@r-project.org] On Behalf Of Jim Lemon
Sent: Tuesday, March 7, 2017 2:24 AM
To: Thilini Maddegoda Vidanelage <tmad...@aucklanduni.ac.nz>; r-help mailing
list <r-help@r-project.org>
Subject: Re: [R] FOR TAKING PERCENTAGES of OTUS in each column (n=2910 COLUMNs)
Hi Thilini,
It is fairly simple in R once you have imported the data. Say you have
a data frame obtained by exporting the Excel table to CSV and then
importing it with "read.csv". I'm not sure whether you have a number
in each cell or just a 0/1 absent/present value, but it may not
matter. Assume the data frame is named "tjdf"
for(column in 1:dim(tjdf)[2])
tjdf[,paste("pct",column,sep="")]<-100*tjdf[,column]/sum(tjdf[,column])
Alternatively, you could create a new data frame with just the percentages.
Jim
On Tue, Mar 7, 2017 at 12:16 PM, Thilini Maddegoda Vidanelage
<tmad...@aucklanduni.ac.nz> wrote:
> Hi,
> I am analyzing a huge excel table with OTUs. In the table, I have 2910
> columns and 365 rows.Each column represents one individual (n=2910). Rows
> represent microbial species (n=365).
> I have the total of all OTUs of microbial species under each column. Then I
> need to get the percentages of each species in each individual.I started to
> do this in excel but I have to repeat this for 2910 times which is going to
> be very time-consuming. I am sure there should be a smart way to do this
> and just wondering whether there is any R script to do this.Any help is
> much appreciated.
> Many thanks, Thilini
>
> *Thilini Jayasinghe*
> PhD Candidate
> Liggins Institute
> The University of Auckland
> Building 503/201, 85 Park Road, Grafton, Auckland 2023
> Mobile: +64 220211604
> Email: tmad...@aucklanduni.ac.nz
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
