How to convert column wise data from CSV file into a matrix format data CSV in R?

Hello,

I have a CSV file containing [309291 rows x 3 columns]. A snapshot of my data is given below

query    target    Similarity
  DB00091  DB00502    0.363395
  DB00091  DB00706    0.193460
  DB00091  DB01435    0.368020
  DB00091  DB01095    0.274538
  DB00091  DB00608    0.357236
       ...      ...         ...
  DB13879  DB00502    0.461665
  DB13879  DB00706    0.466264
  DB13879  DB01435    0.388672
  DB13879  DB01095    0.380627
  DB13879  DB00608    0.300343

Please, notice this that, I have similarity values for DB00091 with 5 different target like DB00502, DB00706, DB01435, DB01095, DB00608 ,etc,.

Now, I want to change this CSV into a matrix format CSV. For example,

        DB00502 DB00706 DB01435 DB01095 DB00608
DB00091 0.363395 0.193460 0.368020 0.274538 0.357236
DB13879 0.461665 0.466264 0.388672 0.380627 0.300343

Could you tell me how can I do it? Any kind of suggestion is appreciable.

I would use the pivot_wider function from the tidyr pacakage.

library(tidyr)
DF <- read.csv("~/R/Play/Dummy.csv")
DF
#>      query  target Similarity
#> 1  DB00091 DB00502   0.363395
#> 2  DB00091 DB00706   0.193460
#> 3  DB00091 DB01435   0.368020
#> 4  DB00091 DB01095   0.274538
#> 5  DB00091 DB00608   0.357236
#> 6  DB13879 DB00502   0.461665
#> 7  DB13879 DB00706   0.466264
#> 8  DB13879 DB01435   0.388672
#> 9  DB13879 DB01095   0.380627
#> 10 DB13879 DB00608   0.300343
DFwide <- pivot_wider(DF,names_from = target,values_from = Similarity)
DFwide
#> # A tibble: 2 x 6
#>   query   DB00502 DB00706 DB01435 DB01095 DB00608
#>   <chr>     <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
#> 1 DB00091   0.363   0.193   0.368   0.275   0.357
#> 2 DB13879   0.462   0.466   0.389   0.381   0.300

Created on 2020-11-12 by the reprex package (v0.3.0)

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

@FJCC Thank you very much