r - Somthing is wrong with using pivot_wider and pivot_longer to gather data(I finished it by myself.It was solved.)

Question

I used this method to gather mean and sd result successly before here .And then, I tried to use this methond to gather my gene counts DEG data with "logFC","cil","cir","ajustP_value" .But I failed because something wrong with my result.

Just like this:

 data_1<-data.frame(matrix(sample(1:1200,1200,replace = T),48,25))
    names(data_1) <- c(paste0("Gene_", 1:25))
    rownames(data_1)<-NULL
    
    head(data_1)
    A<-paste0(1:48,"_logFC")
    data_logFC<-data.frame(A=A,data_1)
    #
    data_2<-data.frame(matrix(sample(1:1200,1200,replace = T),48,25))
    names(data_2) <- c(paste0("Gene_", 1:25))
    rownames(data_1)<-NULL
    B_L<-paste0(1:48,"_CI.L")
    data_CIL<-data.frame(A=B_L,data_2)
    data_CIL[1:48,1:6]
    #
    data_3<-data.frame(matrix(sample(1:1200,1200,replace = T),48,25))
    names(data_3) <- c(paste0("Gene_", 1:25))
    rownames(data_3)<-NULL
    C_R<-paste0(1:48,"_CI.R")
    data_CIR<-data.frame(A=C_R,data_3)
    data_CIR[1:48,1:6]
    #
    data_4<-data.frame(matrix(sample(1:1200,1200,replace = T),48,25))
    names(data_4) <- c(paste0("Gene_", 1:25))
    rownames(data_4)<-NULL
    D<-paste0(1:48,"_adj.P.Val")
    data_ajustP<-data.frame(A=D,data_4)
    data_ajustP[1:48,1:6]
    #  combine data_logFC  data_CIL  data_CIR  data_ajustP
    data <- bind_rows(list(
      logFC = data_logFC,
      CIL = data_CIL,
      CIR =data_CIR,
      AJSTP=data_ajustP
    ), .id = "stat")
    
    data[1:10,1:6]
    data_DEG<- data %>%
      pivot_longer(-c(stat,A), names_to = "Gene", values_to = "value") %>%pivot_wider(names_from = "stat", values_from = "value")
    
    head(data_DEG,100)
    str(data_DEG$CIL)
> head(data_DEG,100)
# A tibble: 100 x 6
   A       Gene    logFC   CIL   CIR AJSTP
   <chr>   <chr>   <int> <int> <int> <int>
 1 1_logFC Gene_1    504    NA    NA    NA
 2 1_logFC Gene_2    100    NA    NA    NA
 3 1_logFC Gene_3    689    NA    NA    NA
 4 1_logFC Gene_4    779    NA    NA    NA
 5 1_logFC Gene_5    397    NA    NA    NA
 6 1_logFC Gene_6   1152    NA    NA    NA
 7 1_logFC Gene_7    780    NA    NA    NA
 8 1_logFC Gene_8    155    NA    NA    NA
 9 1_logFC Gene_9    142    NA    NA    NA
10 1_logFC Gene_10  1150    NA    NA    NA
# … with 90 more rows

Why is there so many NAs ? Can somebody help me ? Vary thankful.

EDITE: I confused the real sample group of my data. So I reshape my data without a right index.

Here is my right method:

data[1:10,1:6]
data<-separate(data,A,c("Name","stat2"),"_")
data<-data[,-3]
data_DEG<- data %>%
  pivot_longer(-c(stat,Name), names_to = "Gene", values_to = "value") %>%pivot_wider(names_from = "stat", values_from = "value")

head(data_DEG,10)
tail(data_DEG,10)

> head(data_DEG,10)
# A tibble: 10 x 6
   Name  Gene    logFC   CIL   CIR AJSTP
   <chr> <chr>   <int> <int> <int> <int>
 1 1     Gene_1    504  1116   774   278
 2 1     Gene_2    100   936   448   887
 3 1     Gene_3    689   189   718   933
 4 1     Gene_4    779   943   690    19
 5 1     Gene_5    397   976    40   135
 6 1     Gene_6   1152   304   343   647
 7 1     Gene_7    780  1076   796  1024
 8 1     Gene_8    155   645   469   180
 9 1     Gene_9    142   256   889  1047
10 1     Gene_10  1150   976  1194   670
> tail(data_DEG,10)
# A tibble: 10 x 6
   Name  Gene    logFC   CIL   CIR AJSTP
   <chr> <chr>   <int> <int> <int> <int>
 1 48    Gene_16   448   633  1080  1122
 2 48    Gene_17    73   772    14   388
 3 48    Gene_18   652   999   699   912
 4 48    Gene_19   600  1163   512   241
 5 48    Gene_20   428  1119  1142   348
 6 48    Gene_21    66   553   240    82
 7 48    Gene_22   753  1119   630   117
 8 48    Gene_23  1017   305  1120   447
 9 48    Gene_24   432  1175   447   670
10 48    Gene_25   482   394   371   696

It's a perfect result!!

question from:https://stackoverflow.com/questions/65937139/somthing-is-wrong-with-using-pivot-wider-and-pivot-longer-to-gather-datai-finis

Answer 1

Waitting for answers