CODEWITHSUNDEEP
rggplot2
GuessWhoAm1
GuessWhoAm1

Reputation: 1

How to plot many lines from data in Excel in R

I'm kinda new to R, is there any way to plot ColA as X-axes, ColB as y-axis, ColC as X-axis and ColD as the y-axis and so on. And all the paired cols have same name (e.g., ColA=Dis and Colb = hard, ColC=Dis and COLd= hard and so on). Basically what I have done is that I have merged all the Dis and hard from different sheets in different excels into one datasheet, then I thought I could plot it with excel, however, the excel can only display 255 curves in one figure, which I probably have one thousand curves. Then my dataset looks like this:

A B C D E F G H ...

Dis hard Dis hard Dis hard Dis hard ...

1 3 4 6 9 11 15 20

3 4 6 9 11 22 25 30

and so on I have tried the following code:

nwb1<-read.xlsx("newresult.xlsx")
nwb1<-as.data.frame(nwb1)
ggplot(data=nwb1, aes(x=displacement, y=hardness)) + geom_line() + geom_point( size=4, shape=21, fill="white")

the error message told me that "data` must be uniquely named but has duplicate columns"

Upvotes: 0

Views: 195

Answers (1)

StupidWolf
StupidWolf

Reputation: 46968

Let's try something like this, I do not have your data, so for illustration purpose, I make 3 excel files first, each having a column call Dis and another call hard:

library(openxlsx)
for(i in 1:3){
df = data.frame(Dis=1:10,hard=rpois(10,i)) 
write.xlsx(df,file=paste("file",i,".xlsx",sep=""))
}

Now we start with making a vector of all the excel files you want to plot:

fl = c("file1.xlsx","file2.xlsx","file3.xlsx")
df = lapply(fl,function(i){
x = read.xlsx(i)
x$sample=sub(".xlsx","",i)
x
})

df = do.call(rbind,df)
head(df)
  Dis hard sample
1   1    1  file1
2   2    0  file1
3   3    0  file1
4   4    2  file1
5   5    2  file1
6   6    2  file1

The dataframe df is concatenated from all your excel files and has an extra column call sample to denote the file where it came from. You can also make a table like this in excel if you have more problems reading / combining files. Once you have something like that, we plot:

ggplot(data=df, aes(x=Dis, y=hard,col=sample)) + geom_line() + 
geom_point( size=4, shape=21, fill="white")

enter image description here

In the event you have merged your data with excel, you will read in a very odd data.frame, and you can try the following, not recommended :

values = do.call(cbind,lapply(1:5,function(i)cbind(1:10,1:10+rnorm(10))))
df = data.frame(rbind(rep(c("Dis","hard"),5),values))
colnames(df)=LETTERS[1:10]
head(df)
    A                B   C                 D   E                 F   G
1 Dis             hard Dis              hard Dis              hard Dis
2   1 1.09836250501178   1 0.350206285061174   1 0.620196066920137   1
3   2 1.81400395465058   2   4.2990376623795   2  1.00810320999903   2
4   3 3.94001753647332   3  3.32736042411927   3  3.23285030270875   3
5   4 3.93795305230344   4  4.14948397718842   4  3.88849871990867   4
6   5 5.08952019766558   5  5.18257115670042   5  4.72275692563252   5
                 H   I                J
1             hard Dis             hard
2 1.08603311982134   1 0.51876628213101
3 1.38614529438877   2 1.73020370187464
4 2.70650988128661   3 4.65143843701136
5 3.26676976653313   4 5.17606099966858
6 5.00453246607507   5 6.72671659884557

newdf = data.frame(
Dis=as.numeric(unlist(lapply(df[-1,df[1,] == "Dis"],as.character))),
hard=as.numeric(unlist(lapply(df[-1,df[1,] == "hard"],as.character))),
group = rep(1:(ncol(df)/2),each=nrow(df)-1)
)
ggplot(newdf,aes(x=Dis,y=hard,group=group))+geom_line()

Upvotes: 1

Related Questions