Is it possible to combine columns and rows of a matrix to become one column in R?

Question

I have a matrix formatted like this:

           EeBi3      EeBi4     EeBi18     EeBi19    EeBi20      EeBi23     EeBi27    EeBi34     KbFk2      KbFk4     KbFk6      PeHa8     PeHa10
EeBi3  0.0000000 0.30663054 0.30939201 0.31964525 0.0159719 0.200989592 0.29318178 0.1162526 0.2489781 0.09343830 0.4376092 0.28687875 0.35159621
EeBi4  0.3066305 0.00000000 0.02987622 0.11442012 0.3137519 0.333896798 0.08233566 0.3839975 0.4120018 0.30765367 0.2593018 0.15041463 0.27422173
EeBi18 0.3093920 0.02987622 0.00000000 0.08480880 0.3151130 0.318278424 0.05511679 0.3789155 0.4316341 0.30132272 0.2884377 0.12382093 0.24602851
EeBi19 0.3196452 0.11442012 0.08480880 0.00000000 0.3212185 0.274155334 0.03894638 0.3640993 0.4838800 0.28618071 0.3731422 0.05429169 0.16382697
EeBi20 0.0159719 0.31375186 0.31511302 0.32121855 0.0000000 0.188280009 0.29625886 0.1005033 0.2628903 0.08056380 0.4516085 0.28621750 0.34553757
EeBi23 0.2009896 0.33389680 0.31827842 0.27415533 0.1882800 0.000000000 0.27230274 0.1439996 0.4494438 0.10773002 0.5530131 0.22094569 0.20590374
EeBi27 0.2931818 0.08233566 0.05511679 0.03894638 0.2962589 0.272302737 0.00000000 0.3479591 0.4467920 0.26956435 0.3412793 0.06870414 0.19191949
EeBi34 0.1162526 0.38399750 0.37891551 0.36409933 0.1005033 0.143999586 0.34795908 0.0000000 0.3469285 0.07839490 0.5480826 0.31866487 0.34135589
KbFk2  0.2489781 0.41200179 0.43163407 0.48387998 0.2628903 0.449443831 0.44679200 0.3469285 0.0000000 0.34232527 0.3783952 0.47407413 0.57176468
KbFk4  0.0934383 0.30765367 0.30132272 0.28618071 0.0805638 0.107730017 0.26956435 0.0783949 0.3423253 0.00000000 0.4877729 0.24203774 0.27817792
KbFk6  0.4376092 0.25930176 0.28843765 0.37314224 0.4516085 0.553013136 0.34127927 0.5480826 0.3783952 0.48777287 0.0000000 0.40799603 0.53306173
PeHa8  0.2868788 0.15041463 0.12382093 0.05429169 0.2862175 0.220945691 0.06870414 0.3186649 0.4740741 0.24203774 0.4079960 0.00000000 0.12569638
PeHa10 0.3515962 0.27422173 0.24602851 0.16382697 0.3455376 0.205903745 0.19191949 0.3413559 0.5717647 0.27817792 0.5330617 0.12569638 0.00000000
PeHa11 0.3266313 0.19913019 0.17049466 0.08777039 0.3238718 0.225254817 0.11719478 0.3420208 0.5260750 0.26935327 0.4583761 0.05591193 0.07605928
PeHa12 0.3367256 0.25685361 0.22895587 0.14776433 0.3311734 0.199071506 0.17451819 0.3311436 0.5544202 0.26576569 0.5153881 0.10762639 0.01867100
PeHa17 0.3226647 0.22348872 0.19556777 0.11480606 0.3185359 0.204898410 0.14115379 0.3284669 0.5319505 0.25865025 0.4821449 0.07477337 0.05098563
PeHa22 0.3192500 0.18051405 0.15192028 0.06971327 0.3173386 0.229989867 0.09858663 0.3408997 0.5129427 0.26656850 0.4397559 0.03963477 0.09435762
PeHa25 0.3499101 0.18520962 0.15549736 0.07084135 0.3483912 0.259937765 0.10726345 0.3728101 0.5380142 0.29849906 0.4434609 0.06489490 0.10213880
PeHa26 0.1877320 0.32395308 0.30917395 0.26804570 0.1753025 0.014166801 0.26442013 0.1362758 0.4359037 0.09476091 0.5403134 0.21548450 0.20874388
PeHa30 0.1956270 0.32984662 0.31455875 0.27161616 0.1830246 0.005714928 0.26905891 0.1407653 0.4439777 0.10246103 0.5478803 0.21864639 0.20693512
PeHa43 0.3358969 0.20661476 0.17768612 0.09399550 0.3329394 0.230353071 0.12508568 0.3494262 0.5361312 0.27739154 0.4659164 0.06557053 0.07056457
PeHa45 0.3025261 0.20539838 0.17835277 0.10133805 0.2989947 0.196441684 0.12334593 0.3138035 0.5091367 0.24196955 0.4630190 0.05508370 0.07192619
PeHa47 0.2138750 0.33400149 0.31728296 0.26975857 0.2014510 0.014273766 0.26989353 0.1581370 0.4619302 0.12090088 0.5575339 0.21603364 0.19424960
PeHa50 0.2468650 0.36568309 0.34748133 0.29434339 0.2336665 0.047023708 0.29810038 0.1785470 0.4956838 0.15342675 0.5934922 0.24008087 0.19860087
PeHa51 0.1730793 0.33592434 0.32283678 0.28635167 0.1596276 0.031828605 0.28029671 0.1125863 0.4220459 0.07977824 0.5442145 0.23473247 0.23297788
PeHa52 0.3030116 0.18863513 0.16123152 0.08357227 0.3004033 0.208501727 0.10638561 0.3207493 0.5031884 0.24716364 0.4468453 0.03909335 0.08660320
PeHa58 0.2069011 0.33127083 0.31505394 0.26918808 0.1944690 0.008221430 0.26832527 0.1520985 0.4549954 0.11391921 0.5529698 0.21571158 0.19799795
PeHa67 0.3270270 0.23218661 0.20422008 0.12315449 0.3225332 0.203926993 0.14985361 0.3299117 0.5384731 0.26116032 0.4908683 0.08346663 0.04226329

that I want to put into this dataframe :

            Pair Count TC      CF/TC
1     KbFk6/KbFk2     2 27 0.07407407
2     KbFk4/KbFk2     1 27 0.03703704
3    PeHa58/KbFk2     1 27 0.03703704
4    PeHa12/KbFk2     1 27 0.03703704
5    PeHa10/KbFk2     1 27 0.03703704
6    PeHa22/KbFk2     1 27 0.03703704
7    PeHa50/KbFk2     1 27 0.03703704
8    PeHa11/KbFk2     1 27 0.03703704
9    PeHa26/KbFk2     1 27 0.03703704
10   PeHa51/KbFk2     1 27 0.03703704
11   PeHa67/KbFk2     1 27 0.03703704
12    PeHa8/KbFk2     1 27 0.03703704
13   PeHa25/KbFk2     1 27 0.03703704
14   PeHa30/KbFk2     1 27 0.03703704
15   PeHa47/KbFk2     1 27 0.03703704
16   PeHa45/KbFk2     1 27 0.03703704
17   PeHa52/KbFk2     1 27 0.03703704
18   PeHa17/KbFk2     1 27 0.03703704
19   PeHa43/KbFk2     1 27 0.03703704
20   PeHa58/KbFk6     1 27 0.03703704
21   PeHa12/KbFk6     1 27 0.03703704
22   PeHa10/KbFk6     1 27 0.03703704
23   PeHa22/KbFk6     1 27 0.03703704
24   PeHa50/KbFk6     1 27 0.03703704
25   PeHa11/KbFk6     1 27 0.03703704
26   PeHa26/KbFk6     1 27 0.03703704
27   PeHa51/KbFk6     1 27 0.03703704
28   PeHa67/KbFk6     1 27 0.03703704
29    PeHa8/KbFk6     1 27 0.03703704
30   PeHa25/KbFk6     1 27 0.03703704

Im trying to get the value found for each pair in the matrix to be added to that same pair in the dataframe, and said value to be a column in the data frame. The pairs also need to be unique and cant be repeated ie A/B and B/A. How can I go about it?

akrun · Accepted Answer · 2019-11-25T22:14:22.387

We can split the 'pair' column at the delimiter / and then pivot it to wide format with pivot_wider

library(dplyr)
library(tidyr)
df1 %>%
   group_by(Pair) %>%
   mutate(rn = row_number()) %>% 
   separate(Pair, into = c("Pair1", "Pair2")) %>% 
   select(-TC, -Count) %>% 
   pivot_wider(names_from = Pair2, values_from = CF.TC)

Or with xtabs from base R

df2 <- transform(read.table(text = df1$Pair, sep="/", header = FALSE),
              CFTC = df1$CF.TC)
lvls <- sort(unique(unlist(df2[1:2])))
df2[1:2] <- lapply(df2[1:2], factor, levels = lvls)
xtabs(CFTC ~ ., df2)

If it is the opposite direction

library(reshape2)
melt(longdf) %>%
     mutate(Pair = str_c(V1, V2, sep="/"))

I edited my post for clarity, but I think trying to do the opposite of what you suggest. Id like the matrix row and variable to be paired up in a similar fashion as the data frame with the accompanying value in a new column of the dataframe. — bobbytreefish, Nov 25 '19 at 22:12

Is it possible to combine columns and rows of a matrix to become one column in R?

1 Answers1