I have data structured like this:
Location Sample HeV NiV CedV GhV MenV Mock `Total Events`
74(1,B10) FSS27681 93 100 93 94 169 96 2392
75(1,C10) FSS26893 108 110 104 113 137 100 2084
76(1,D10) FSS27851 100 91 96 160 129 79 3148
77(1,E10) FSS27852 96 92 280 103 155 81 1982
78(1,F10) FSS27854 182 124.5 163 400.5 234 92 2548
79(1,G10) FSS27875 99 94 89 118 163 96 2094
80(1,H10) Background0 100.5 103 94 97 123 108 1713
81(1,A11) FSS28147 194 250 157 169 253.5 195 1978
82(1,B11) FSS28148 82 91 133 139 192 84.5 2363
83(1,C11) FSS28149 189 141 173 488 278 100 1991
84(1,D11) FSS28150 143 106 145 365 189 108 2212
85(1,E11) FSS27828 87 91 90 95 102 93.5 2119
86(1,F11) FSS28051 73 79 74 84 84 76 2767
87(1,G11) FSS27886 88 93 83 89 105 77 1768
88(1,H11) Pos 1428 8472 86 90 107 90 1556
89(1,A12) FSS28161 114 102 106 186 137 84 1915
90(1,B12) FSS28165 2514.5 339 108 196 279 129 1906
91(1,C12) FSS27986 104 90 152 137 127 75 2212
92(1,D12) FSS28191 269.5 140 115 111 122 210 2207
93(1,E12) FSS27894 104 90 107 159 110 85 2185
94(1,F12) FSS28223 98 93 106 91.5 211 96 2085
95(1,G12) FSS28426 86 91 94 84 168 82 2568
96(1,H12) Neg 72 77 71 71.5 84 74 2598
I would like to filter() the rows that include Pos, Neg and Background0 and use those values to mutate() new columns. The first value in the filtered rows would be paired with the HeV column and so on. It seems a bit confusing in text so I will post the data frame that I am trying to achieve.
Location Sample HeV HeV_Pos HeV_Neg HeV_Background0 NiV NiV_Pos NiV_Neg NiV_Background0 CedV GhV MenV Mock Total Events
74(1,B10) FSS27681 93 1428 72 100.5 100 8472 77 103 93 94 169 96 2392
75(1,C10) FSS26893 108 1428 72 100.5 110 8472 77 103 104 113 137 100 2084
76(1,D10) FSS27851 100 1428 72 100.5 91 8472 77 103 96 160 129 79 3148
77(1,E10) FSS27852 96 1428 72 100.5 92 8472 77 103 280 103 155 81 1982
78(1,F10) FSS27854 182 1428 72 100.5 124.5 8472 77 103 163 400.5 234 92 2548
79(1,G10) FSS27875 99 1428 72 100.5 94 8472 77 103 89 118 163 96 2094
81(1,A11) FSS28147 194 1428 72 100.5 250 8472 77 103 157 169 253.5 195 1978
82(1,B11) FSS28148 82 1428 72 100.5 91 8472 77 103 133 139 192 84.5 2363
83(1,C11) FSS28149 189 1428 72 100.5 141 8472 77 103 173 488 278 100 1991
84(1,D11) FSS28150 143 1428 72 100.5 106 8472 77 103 145 365 189 108 2212
85(1,E11) FSS27828 87 1428 72 100.5 91 8472 77 103 90 95 102 93.5 2119
86(1,F11) FSS28051 73 1428 72 100.5 79 8472 77 103 74 84 84 76 2767
87(1,G11) FSS27886 88 1428 72 100.5 93 8472 77 103 83 89 105 77 1768
89(1,A12) FSS28161 114 1428 72 100.5 102 8472 77 103 106 186 137 84 1915
90(1,B12) FSS28165 2514.5 1428 72 100.5 339 8472 77 103 108 196 279 129 1906
91(1,C12) FSS27986 104 1428 72 100.5 90 8472 77 103 152 137 127 75 2212
92(1,D12) FSS28191 269.5 1428 72 100.5 140 8472 77 103 115 111 122 210 2207
93(1,E12) FSS27894 104 1428 72 100.5 90 8472 77 103 107 159 110 85 2185
94(1,F12) FSS28223 98 1428 72 100.5 93 8472 77 103 106 91.5 211 96 2085
95(1,G12) FSS28426 86 1428 72 100.5 91 8472 77 103 94 84 168 82 2568
I didn't fill in the mutated columns for CedV, GhV, MenV, Mock, Total Events for clarity. I tried some combinations of filter and pivot_longer but couldn't get anything to work. Thanks in advance for your help.
you can use bind_cols along with pivot_wider
dplyr::bind_cols(
df |> dplyr::filter(!Sample %in% c("Background0", "Pos", "Neg")),
df |>
dplyr::filter(Sample %in% c("Background0", "Pos", "Neg")) |>
dplyr::select(-Location) |>
tidyr::pivot_wider(names_from = "Sample",values_from = HeV:TotalEvents)
)