library(dplyr)
> library(formattable)
> library(reshape2)
> library(data.table)
> library(tidyr)
> cent<-read.csv("d:/TEMP/centre1.csv", header=TRUE)
> cent
First.Name Last.Name X1.1 X1.1.1 X2.1 X2.1.1 X3.1 X3.1.1 X4.1 X4.1.1 X5.1 X5.1.1 X6.1 X6.1.1 X7.1 X7.1.1 X8.1
1 a x 1 GFU 1 GFU 4 LTC 4 LTC 2 GFU 2 GFU 2 GFU 2
2 b y 0 0 0 0 0 0 0 0
3 c v 1 INSP 1 INSP 4 LTC 4 LTC 2 INSP 2 INSP 2 3D 2
4 d w 4 CO 4 CO 4 CO 4 CO 4 FREE 4 FREE 3 RATU 3
5 e s 1 AMI 1 AMI 1 AMI 1 AMI 1 AMI 3 AMI 3 AMI 3
6 f p 1 RATU 1 RATU 1 RATU 1 RATU 1 RATU 4 FREE 2 RATU 2
7 g q 3 ET 3 ET 3 ET 3 ET 3 ET 4 FREE 2 ET 2
X8.1.1 X9.1 X9.1.1 X10.1 X10.1.1 X11.1 X11.1.1 X12.1 X12.1.1 X13.1 X13.1.1 X14.1 X14.1.1 X15.1 X15.1.1 X16.1
1 GFU 2 GFU 4 LTC 3 GFU 3 GFU 3 GFU 3 GFU 3 GFU 4
2 0 0 0 4 FREE 4 FREE 1 OJT 1 OJT 1
3 INSP 2 INSP 4 LTC 3 INSP 3 INSP 3 INSP 3 INSP 3 INSP 4
4 RATU 3 RATU 3 RATU 3 RATU 3 RATU 4 FREE 2 RATU 2 RATU 2
5 AMI 3 AMI 3 AMI 3 AMI 4 FREE 2 AMI 2 AMI 2 AMI 2
6 RATU 2 RATU 2 RATU 2 RATU 4 FREE 4 FREE 1 RATU 1 RATU 1
7 ET 2 ET 2 ET 2 ET 2 DETE 4 FREE 4 ET 1 ET 1
X16.1.1 X17.1 X17.1.1 X18.1 X18.1.1 X19.1 X19.1.1 X20.1 X20.1.1 X21.1 X21.1.1 X22.1 X22.1.1 X23.1 X23.1.1 X24.1
1 LTC 4 LTC 1 GFU 1 GFU 1 GFU 1 GFU 1 GFU 4 LTC 4
2 OJT 1 OJT 1 OJT 4 FREE 4 FREE 1 OJT 1 OJT 1 OJT 1
3 LTC 4 LTC 1 INSP 1 INSP 1 INSP 1 INSP 1 INSP 4 LTC 4
4 RATU 2 RATU 2 RATU 2 RATU 4 FREE 1 RATU 1 RATU 1 RATU 1
5 AMI 2 AMI 2 AMI 2 AMI 4 FREE 1 AMI 1 AMI 1 AMI 1
6 RATU 1 RATU 1 RATU 4 FREE 4 FREE 3 RATU 3 RATU 3 RATU 3
7 ET 1 ET 1 ET 1 ET 4 FREE 3 ET 3 ET 3 ET 3
X24.1.1 X25.1 X25.1.1 X26.1 X26.1.1 X27.1 X27.1.1 X28.1 X28.1.1 X29.1 X29.1.1 X30.1 X30.1.1 X31.1 X31.1.1
1 LTC 2 GFU 4 CO 2 GFU 2 GFU 2 GFU 4 LTC 3 GFU
2 OJT 1 OJT 4 FREE 4 FREE 3 OJT 3 OJT 3 OJT 3 OJT
3 LTC 2 INSP 2 INSP 2 INSP 2 INSP 2 INSP 4 LTC 3 INSP
4 RATU 1 RATU 1 RATU 4 FREE 3 RATU 3 RATU 3 RATU 3 RATU
5 AMI 1 AMI 1 AMI 4 FREE 3 AMI 3 AMI 3 AMI 3 AMI
6 RATU 3 RATU 4 FREE 4 FREE 1 RATU 1 RATU 1 RATU 1 RATU
7 ET 3 ET 3 DETE 4 FREE 2 ET 2 ET 2 ET 2 ET
> cent1 <- cent%>%
+ select(c(First.Name, Last.Name,starts_with("X"),-ends_with('.1.1')))
> head(cent1)
First.Name Last.Name X1.1 X2.1 X3.1 X4.1 X5.1 X6.1 X7.1 X8.1 X9.1 X10.1 X11.1 X12.1 X13.1 X14.1 X15.1 X16.1 X17.1
1 a x 1 1 4 4 2 2 2 2 2 4 3 3 3 3 3 4 4
2 b y 0 0 0 0 0 0 0 0 0 0 0 4 4 1 1 1 1
3 c v 1 1 4 4 2 2 2 2 2 4 3 3 3 3 3 4 4
4 d w 4 4 4 4 4 4 3 3 3 3 3 3 4 2 2 2 2
5 e s 1 1 1 1 1 3 3 3 3 3 3 4 2 2 2 2 2
6 f p 1 1 1 1 1 4 2 2 2 2 2 4 4 1 1 1 1
X18.1 X19.1 X20.1 X21.1 X22.1 X23.1 X24.1 X25.1 X26.1 X27.1 X28.1 X29.1 X30.1 X31.1
1 1 1 1 1 1 4 4 2 4 2 2 2 4 3
2 1 4 4 1 1 1 1 1 4 4 3 3 3 3
3 1 1 1 1 1 4 4 2 2 2 2 2 4 3
4 2 2 4 1 1 1 1 1 1 4 3 3 3 3
5 2 2 4 1 1 1 1 1 1 4 3 3 3 3
6 1 4 4 3 3 3 3 3 4 4 1 1 1 1
> cent2<-cent%>%
+ select(c(First.Name, Last.Name,ends_with(".1.1")))
> cent2
First.Name Last.Name X1.1.1 X2.1.1 X3.1.1 X4.1.1 X5.1.1 X6.1.1 X7.1.1 X8.1.1 X9.1.1 X10.1.1 X11.1.1 X12.1.1
1 a x GFU GFU LTC LTC GFU GFU GFU GFU GFU LTC GFU GFU
2 b y FREE
3 c v INSP INSP LTC LTC INSP INSP 3D INSP INSP LTC INSP INSP
4 d w CO CO CO CO FREE FREE RATU RATU RATU RATU RATU RATU
5 e s AMI AMI AMI AMI AMI AMI AMI AMI AMI AMI AMI FREE
6 f p RATU RATU RATU RATU RATU FREE RATU RATU RATU RATU RATU FREE
7 g q ET ET ET ET ET FREE ET ET ET ET ET DETE
X13.1.1 X14.1.1 X15.1.1 X16.1.1 X17.1.1 X18.1.1 X19.1.1 X20.1.1 X21.1.1 X22.1.1 X23.1.1 X24.1.1 X25.1.1 X26.1.1
1 GFU GFU GFU LTC LTC GFU GFU GFU GFU GFU LTC LTC GFU CO
2 FREE OJT OJT OJT OJT OJT FREE FREE OJT OJT OJT OJT OJT FREE
3 INSP INSP INSP LTC LTC INSP INSP INSP INSP INSP LTC LTC INSP INSP
4 FREE RATU RATU RATU RATU RATU RATU FREE RATU RATU RATU RATU RATU RATU
5 AMI AMI AMI AMI AMI AMI AMI FREE AMI AMI AMI AMI AMI AMI
6 FREE RATU RATU RATU RATU RATU FREE FREE RATU RATU RATU RATU RATU FREE
7 FREE ET ET ET ET ET ET FREE ET ET ET ET ET DETE
X27.1.1 X28.1.1 X29.1.1 X30.1.1 X31.1.1
1 GFU GFU GFU LTC GFU
2 FREE OJT OJT OJT OJT
3 INSP INSP INSP LTC INSP
4 FREE RATU RATU RATU RATU
5 FREE AMI AMI AMI AMI
6 FREE RATU RATU RATU RATU
7 FREE ET ET ET ET
> x1<-cent1 %>%
+ pivot_longer(cols =-c(First.Name,Last.Name),
+ names_to='year',
+ values_to='points'
+ )
> x1
# A tibble: 217 x 4
First.Name Last.Name year points
<chr> <chr> <chr> <int>
1 a x X1.1 1
2 a x X2.1 1
3 a x X3.1 4
4 a x X4.1 4
5 a x X5.1 2
6 a x X6.1 2
7 a x X7.1 2
8 a x X8.1 2
9 a x X9.1 2
10 a x X10.1 4
# i 207 more rows
# i Use `print(n = ...)` to see more rows
> x2<-cent2 %>%
+ pivot_longer(cols = -c(First.Name,Last.Name),
+ names_to='year',
+ values_to='points'
+ )
> x2
# A tibble: 217 x 4
First.Name Last.Name year points
<chr> <chr> <chr> <chr>
1 a x X1.1.1 GFU
2 a x X2.1.1 GFU
3 a x X3.1.1 LTC
4 a x X4.1.1 LTC
5 a x X5.1.1 GFU
6 a x X6.1.1 GFU
7 a x X7.1.1 GFU
8 a x X8.1.1 GFU
9 a x X9.1.1 GFU
10 a x X10.1.1 LTC
# i 207 more rows
# i Use `print(n = ...)` to see more rows
> total <- cbind(x1, x2$points)
> total
First.Name Last.Name year points x2$points
1 a x X1.1 1 GFU
2 a x X2.1 1 GFU
3 a x X3.1 4 LTC
4 a x X4.1 4 LTC
5 a x X5.1 2 GFU
6 a x X6.1 2 GFU
7 a x X7.1 2 GFU
8 a x X8.1 2 GFU
9 a x X9.1 2 GFU
10 a x X10.1 4 LTC
11 a x X11.1 3 GFU
12 a x X12.1 3 GFU
13 a x X13.1 3 GFU
14 a x X14.1 3 GFU
15 a x X15.1 3 GFU
16 a x X16.1 4 LTC
17 a x X17.1 4 LTC
18 a x X18.1 1 GFU
19 a x X19.1 1 GFU
20 a x X20.1 1 GFU
21 a x X21.1 1 GFU
22 a x X22.1 1 GFU
23 a x X23.1 4 LTC
24 a x X24.1 4 LTC
25 a x X25.1 2 GFU
26 a x X26.1 4 CO
27 a x X27.1 2 GFU
28 a x X28.1 2 GFU
29 a x X29.1 2 GFU
30 a x X30.1 4 LTC
31 a x X31.1 3 GFU
32 b y X1.1 0
33 b y X2.1 0
34 b y X3.1 0
35 b y X4.1 0
36 b y X5.1 0
37 b y X6.1 0
38 b y X7.1 0
39 b y X8.1 0
40 b y X9.1 0
41 b y X10.1 0
42 b y X11.1 0
43 b y X12.1 4 FREE
44 b y X13.1 4 FREE
45 b y X14.1 1 OJT
46 b y X15.1 1 OJT
47 b y X16.1 1 OJT
48 b y X17.1 1 OJT
49 b y X18.1 1 OJT
50 b y X19.1 4 FREE
51 b y X20.1 4 FREE
52 b y X21.1 1 OJT
53 b y X22.1 1 OJT
54 b y X23.1 1 OJT
55 b y X24.1 1 OJT
56 b y X25.1 1 OJT
57 b y X26.1 4 FREE
58 b y X27.1 4 FREE
59 b y X28.1 3 OJT
60 b y X29.1 3 OJT
61 b y X30.1 3 OJT
62 b y X31.1 3 OJT
63 c v X1.1 1 INSP
64 c v X2.1 1 INSP
65 c v X3.1 4 LTC
66 c v X4.1 4 LTC
67 c v X5.1 2 INSP
68 c v X6.1 2 INSP
69 c v X7.1 2 3D
70 c v X8.1 2 INSP
71 c v X9.1 2 INSP
72 c v X10.1 4 LTC
73 c v X11.1 3 INSP
74 c v X12.1 3 INSP
75 c v X13.1 3 INSP
76 c v X14.1 3 INSP
77 c v X15.1 3 INSP
78 c v X16.1 4 LTC
79 c v X17.1 4 LTC
80 c v X18.1 1 INSP
81 c v X19.1 1 INSP
82 c v X20.1 1 INSP
83 c v X21.1 1 INSP
84 c v X22.1 1 INSP
85 c v X23.1 4 LTC
86 c v X24.1 4 LTC
87 c v X25.1 2 INSP
88 c v X26.1 2 INSP
89 c v X27.1 2 INSP
90 c v X28.1 2 INSP
91 c v X29.1 2 INSP
92 c v X30.1 4 LTC
93 c v X31.1 3 INSP
94 d w X1.1 4 CO
95 d w X2.1 4 CO
96 d w X3.1 4 CO
97 d w X4.1 4 CO
98 d w X5.1 4 FREE
99 d w X6.1 4 FREE
100 d w X7.1 3 RATU
101 d w X8.1 3 RATU
102 d w X9.1 3 RATU
103 d w X10.1 3 RATU
104 d w X11.1 3 RATU
105 d w X12.1 3 RATU
106 d w X13.1 4 FREE
107 d w X14.1 2 RATU
108 d w X15.1 2 RATU
109 d w X16.1 2 RATU
110 d w X17.1 2 RATU
111 d w X18.1 2 RATU
112 d w X19.1 2 RATU
113 d w X20.1 4 FREE
114 d w X21.1 1 RATU
115 d w X22.1 1 RATU
116 d w X23.1 1 RATU
117 d w X24.1 1 RATU
118 d w X25.1 1 RATU
119 d w X26.1 1 RATU
120 d w X27.1 4 FREE
121 d w X28.1 3 RATU
122 d w X29.1 3 RATU
123 d w X30.1 3 RATU
124 d w X31.1 3 RATU
125 e s X1.1 1 AMI
126 e s X2.1 1 AMI
127 e s X3.1 1 AMI
128 e s X4.1 1 AMI
129 e s X5.1 1 AMI
130 e s X6.1 3 AMI
131 e s X7.1 3 AMI
132 e s X8.1 3 AMI
133 e s X9.1 3 AMI
134 e s X10.1 3 AMI
135 e s X11.1 3 AMI
136 e s X12.1 4 FREE
137 e s X13.1 2 AMI
138 e s X14.1 2 AMI
139 e s X15.1 2 AMI
140 e s X16.1 2 AMI
141 e s X17.1 2 AMI
142 e s X18.1 2 AMI
143 e s X19.1 2 AMI
144 e s X20.1 4 FREE
145 e s X21.1 1 AMI
146 e s X22.1 1 AMI
147 e s X23.1 1 AMI
148 e s X24.1 1 AMI
149 e s X25.1 1 AMI
150 e s X26.1 1 AMI
151 e s X27.1 4 FREE
152 e s X28.1 3 AMI
153 e s X29.1 3 AMI
154 e s X30.1 3 AMI
155 e s X31.1 3 AMI
156 f p X1.1 1 RATU
157 f p X2.1 1 RATU
158 f p X3.1 1 RATU
159 f p X4.1 1 RATU
160 f p X5.1 1 RATU
161 f p X6.1 4 FREE
162 f p X7.1 2 RATU
163 f p X8.1 2 RATU
164 f p X9.1 2 RATU
165 f p X10.1 2 RATU
166 f p X11.1 2 RATU
167 f p X12.1 4 FREE
168 f p X13.1 4 FREE
169 f p X14.1 1 RATU
170 f p X15.1 1 RATU
171 f p X16.1 1 RATU
172 f p X17.1 1 RATU
173 f p X18.1 1 RATU
174 f p X19.1 4 FREE
175 f p X20.1 4 FREE
176 f p X21.1 3 RATU
177 f p X22.1 3 RATU
178 f p X23.1 3 RATU
179 f p X24.1 3 RATU
180 f p X25.1 3 RATU
181 f p X26.1 4 FREE
182 f p X27.1 4 FREE
183 f p X28.1 1 RATU
184 f p X29.1 1 RATU
185 f p X30.1 1 RATU
186 f p X31.1 1 RATU
187 g q X1.1 3 ET
188 g q X2.1 3 ET
189 g q X3.1 3 ET
190 g q X4.1 3 ET
191 g q X5.1 3 ET
192 g q X6.1 4 FREE
193 g q X7.1 2 ET
194 g q X8.1 2 ET
195 g q X9.1 2 ET
196 g q X10.1 2 ET
197 g q X11.1 2 ET
198 g q X12.1 2 DETE
199 g q X13.1 4 FREE
200 g q X14.1 4 ET
[ reached 'max' / getOption("max.print") -- omitted 17 rows ]
> names(total)
[1] "First.Name" "Last.Name" "year" "points" "x2$points"
> colnames(total)
[1] "First.Name" "Last.Name" "year" "points" "x2$points"
> names(total)[5] <- "masina"
> names(total)[4] <- "schimb"
> tail(total)
First.Name Last.Name year schimb masina
212 g q X26.1 3 DETE
213 g q X27.1 4 FREE
214 g q X28.1 2 ET
215 g q X29.1 2 ET
216 g q X30.1 2 ET
217 g q X31.1 2 ET
> total1<-total%>%
+ filter(total$masina%in% c('GFU','RATU','INSP','DETE','3D','AMI','ET','ISTE','ITAL','LEAL','LIDR','MANI','MPI','DATE'))
> total1
First.Name Last.Name year schimb masina
1 a x X1.1 1 GFU
2 a x X2.1 1 GFU
3 a x X5.1 2 GFU
4 a x X6.1 2 GFU
5 a x X7.1 2 GFU
6 a x X8.1 2 GFU
7 a x X9.1 2 GFU
8 a x X11.1 3 GFU
9 a x X12.1 3 GFU
10 a x X13.1 3 GFU
11 a x X14.1 3 GFU
12 a x X15.1 3 GFU
13 a x X18.1 1 GFU
14 a x X19.1 1 GFU
15 a x X20.1 1 GFU
16 a x X21.1 1 GFU
17 a x X22.1 1 GFU
18 a x X25.1 2 GFU
19 a x X27.1 2 GFU
20 a x X28.1 2 GFU
21 a x X29.1 2 GFU
22 a x X31.1 3 GFU
23 c v X1.1 1 INSP
24 c v X2.1 1 INSP
25 c v X5.1 2 INSP
26 c v X6.1 2 INSP
27 c v X7.1 2 3D
28 c v X8.1 2 INSP
29 c v X9.1 2 INSP
30 c v X11.1 3 INSP
31 c v X12.1 3 INSP
32 c v X13.1 3 INSP
33 c v X14.1 3 INSP
34 c v X15.1 3 INSP
35 c v X18.1 1 INSP
36 c v X19.1 1 INSP
37 c v X20.1 1 INSP
38 c v X21.1 1 INSP
39 c v X22.1 1 INSP
40 c v X25.1 2 INSP
41 c v X26.1 2 INSP
42 c v X27.1 2 INSP
43 c v X28.1 2 INSP
44 c v X29.1 2 INSP
45 c v X31.1 3 INSP
46 d w X7.1 3 RATU
47 d w X8.1 3 RATU
48 d w X9.1 3 RATU
49 d w X10.1 3 RATU
50 d w X11.1 3 RATU
51 d w X12.1 3 RATU
52 d w X14.1 2 RATU
53 d w X15.1 2 RATU
54 d w X16.1 2 RATU
55 d w X17.1 2 RATU
56 d w X18.1 2 RATU
57 d w X19.1 2 RATU
58 d w X21.1 1 RATU
59 d w X22.1 1 RATU
60 d w X23.1 1 RATU
61 d w X24.1 1 RATU
62 d w X25.1 1 RATU
63 d w X26.1 1 RATU
64 d w X28.1 3 RATU
65 d w X29.1 3 RATU
66 d w X30.1 3 RATU
67 d w X31.1 3 RATU
68 e s X1.1 1 AMI
69 e s X2.1 1 AMI
70 e s X3.1 1 AMI
71 e s X4.1 1 AMI
72 e s X5.1 1 AMI
73 e s X6.1 3 AMI
74 e s X7.1 3 AMI
75 e s X8.1 3 AMI
76 e s X9.1 3 AMI
77 e s X10.1 3 AMI
78 e s X11.1 3 AMI
79 e s X13.1 2 AMI
80 e s X14.1 2 AMI
81 e s X15.1 2 AMI
82 e s X16.1 2 AMI
83 e s X17.1 2 AMI
84 e s X18.1 2 AMI
85 e s X19.1 2 AMI
86 e s X21.1 1 AMI
87 e s X22.1 1 AMI
88 e s X23.1 1 AMI
89 e s X24.1 1 AMI
90 e s X25.1 1 AMI
91 e s X26.1 1 AMI
92 e s X28.1 3 AMI
93 e s X29.1 3 AMI
94 e s X30.1 3 AMI
95 e s X31.1 3 AMI
96 f p X1.1 1 RATU
97 f p X2.1 1 RATU
98 f p X3.1 1 RATU
99 f p X4.1 1 RATU
100 f p X5.1 1 RATU
101 f p X7.1 2 RATU
102 f p X8.1 2 RATU
103 f p X9.1 2 RATU
104 f p X10.1 2 RATU
105 f p X11.1 2 RATU
106 f p X14.1 1 RATU
107 f p X15.1 1 RATU
108 f p X16.1 1 RATU
109 f p X17.1 1 RATU
110 f p X18.1 1 RATU
111 f p X21.1 3 RATU
112 f p X22.1 3 RATU
113 f p X23.1 3 RATU
114 f p X24.1 3 RATU
115 f p X25.1 3 RATU
116 f p X28.1 1 RATU
117 f p X29.1 1 RATU
118 f p X30.1 1 RATU
119 f p X31.1 1 RATU
120 g q X1.1 3 ET
121 g q X2.1 3 ET
122 g q X3.1 3 ET
123 g q X4.1 3 ET
124 g q X5.1 3 ET
125 g q X7.1 2 ET
126 g q X8.1 2 ET
127 g q X9.1 2 ET
128 g q X10.1 2 ET
129 g q X11.1 2 ET
130 g q X12.1 2 DETE
131 g q X14.1 4 ET
132 g q X15.1 1 ET
133 g q X16.1 1 ET
134 g q X17.1 1 ET
135 g q X18.1 1 ET
136 g q X19.1 1 ET
137 g q X21.1 3 ET
138 g q X22.1 3 ET
139 g q X23.1 3 ET
140 g q X24.1 3 ET
141 g q X25.1 3 ET
142 g q X26.1 3 DETE
143 g q X28.1 2 ET
144 g q X29.1 2 ET
145 g q X30.1 2 ET
146 g q X31.1 2 ET
> fapt<-total1%>%
+
+ group_by(First.Name,Last.Name,masina)%>%
+ count(masina)%>%
+ arrange( Last.Name,First.Name)
> fapt
# A tibble: 8 x 4
# Groups: First.Name, Last.Name, masina [8]
First.Name Last.Name masina n
<chr> <chr> <chr> <int>
1 f p RATU 24
2 g q DETE 2
3 g q ET 25
4 e s AMI 28
5 c v 3D 1
6 c v INSP 22
7 d w RATU 22
8 a x GFU 22
> fapt1<-total1%>%
+ group_by(First.Name,Last.Name,masina)%>%
+ count(schimb)%>%
+ arrange( Last.Name,First.Name)
As it stands, it is just about impossible to see what you are doing. We need to see your code and we need some sample data. We do not need to see intermediate output.
You say you want to "group by multiple columns. Can you explain a bit more?
If I have read your post correctly this is what you are doing
library(dplyr)
library(formattable)
library(reshape2)
library(data.table)
library(tidyr)
cent<-read.csv("d:/TEMP/centre1.csv", header=TRUE)
cent1 <- cent %>% select(c(First.Name, Last.Name,starts_with("X"),-ends_with('.1.1')))
cent2<-cent%>% select(c(First.Name, Last.Name,ends_with(".1.1")))
x1 <- cent1 %>% pivot_longer(cols =-c(First.Name,Last.Name), names_to='year',
values_to='points' )
x2<-cent2 %>% pivot_longer(cols = -c(First.Name,Last.Name),
names_to='year', values_to='points' )
total <- cbind(x1, x2$points)
total1 <- total %>% filter(total$masina%in% c('GFU','RATU','INSP','DETE','3D','AMI',
'ET','ISTE','ITAL', 'LEAL','LIDR','MANI','MPI','DATE'))
fapt<-total1 %>% group_by(First.Name,Last.Name,masina) %>%
count(masina) %>% arrange( Last.Name,First.Name)
fapt1<-total1 %>% group_by(First.Name,Last.Name,masina) %>%
count(schimb) %>% arrange( Last.Name,First.Name)
Is this correct?
I think we need to see your data. A handy way to supply data is to use the dput() function. Do dput(mydata) where "mydata" is the name of your dataset. For really large datasets probably dput(head(mydata, 100)) will do. Paste the output between
```
```
1 Like
`dput(cent)
structure(list(First.Name = c("a", "b", "c", "d", "e", "f", "g"
), Last.Name = c("x", "y", "v", "w", "s", "p", "q"), X1.1 = c(1L,
0L, 1L, 4L, 1L, 1L, 3L), X1.1.1 = c("GFU", "0", "INSP", "CO",
"AMI", "RATU", "ET"), X2.1 = c(1L, 0L, 1L, 4L, 1L, 1L, 3L), X2.1.1 = c("GFU",
"0", "INSP", "CO", "AMI", "RATU", "ET"), X3.1 = c(4L, 0L, 4L,
4L, 1L, 1L, 3L), X3.1.1 = c("LTC", "0", "LTC", "CO", "AMI", "RATU",
"ET"), X4.1 = c(4L, 0L, 4L, 4L, 1L, 1L, 3L), X4.1.1 = c("LTC",
"0", "LTC", "CO", "AMI", "RATU", "ET"), X5.1 = c(2L, NA, 2L,
4L, 1L, 1L, 3L), X5.1.1 = c("GFU", "", "INSP", "FREE", "AMI",
"RATU", "ET"), X6.1 = c(2L, NA, 2L, 4L, 3L, 4L, 4L), X6.1.1 = c("GFU",
"", "INSP", "FREE", "AMI", "FREE", "FREE"), X7.1 = c(2L, NA,
2L, 3L, 3L, 2L, 2L), X7.1.1 = c("GFU", "", "3D", "RATU", "AMI",
"RATU", "ET"), X8.1 = c(2L, NA, 2L, 3L, 3L, 2L, 2L), X8.1.1 = c("GFU",
"0", "INSP", "RATU", "AMI", "RATU", "ET"), X9.1 = c(2L, 0L, 2L,
3L, 3L, 2L, 2L), X9.1.1 = c("GFU", "0", "INSP", "RATU", "AMI",
"RATU", "ET"), X10.1 = c(4L, 4L, 4L, 3L, 3L, 2L, 2L), X10.1.1 = c("LTC",
"FREE", "LTC", "RATU", "AMI", "RATU", "ET"), X11.1 = c(3L, 4L,
3L, 3L, 3L, 2L, 2L), X11.1.1 = c("GFU", "FREE", "INSP", "RATU",
"AMI", "RATU", "ET"), X12.1 = c(3L, 1L, 3L, 3L, 4L, 4L, 2L),
X12.1.1 = c("GFU", "OJT", "INSP", "RATU", "FREE", "FREE",
"DETE"), X13.1 = c(3L, 1L, 3L, 4L, 2L, 4L, 4L), X13.1.1 = c("GFU",
"OJT", "INSP", "FREE", "AMI", "FREE", "FREE"), X14.1 = c(3L,
1L, 3L, 2L, 2L, 1L, 4L), X14.1.1 = c("GFU", "", "INSP", "RATU",
"AMI", "RATU", "ET"), X15.1 = c(3L, NA, 3L, 2L, 2L, 1L, 1L
), X15.1.1 = c("GFU", "", "INSP", "RATU", "AMI", "RATU",
"ET"), X16.1 = c(4L, NA, 4L, 2L, 2L, 1L, 1L), X16.1.1 = c("LTC",
"OJT", "LTC", "RATU", "AMI", "RATU", "ET"), X17.1 = c(4L,
1L, 4L, 2L, 2L, 1L, 1L), X17.1.1 = c("LTC", "OJT", "LTC",
"RATU", "AMI", "RATU", "ET"), X18.1 = c(1L, 1L, 1L, 2L, 2L,
1L, 1L), X18.1.1 = c("GFU", "OJT", "INSP", "RATU", "AMI",
"RATU", "ET"), X19.1 = c(1L, 4L, 1L, 2L, 2L, 4L, 1L), X19.1.1 = c("GFU",
"FREE", "INSP", "RATU", "AMI", "FREE", "ET"), X20.1 = c(1L,
4L, 1L, 4L, 4L, 4L, 4L), X20.1.1 = c("GFU", "FREE", "INSP",
"FREE", "FREE", "FREE", "FREE"), X21.1 = c(1L, 1L, 1L, 1L,
1L, 3L, 3L), X21.1.1 = c("GFU", "OJT", "INSP", "RATU", "AMI",
"RATU", "ET"), X22.1 = c(1L, 1L, 1L, 1L, 1L, 3L, 3L), X22.1.1 = c("GFU",
"OJT", "INSP", "RATU", "AMI", "RATU", "ET"), X23.1 = c(4L,
1L, 4L, 1L, 1L, 3L, 3L), X23.1.1 = c("LTC", "OJT", "LTC",
"RATU", "AMI", "RATU", "ET"), X24.1 = c(4L, 1L, 4L, 1L, 1L,
3L, 3L), X24.1.1 = c("LTC", "OJT", "LTC", "RATU", "AMI",
"RATU", "ET"), X25.1 = c(2L, 1L, 2L, 1L, 1L, 3L, 3L), X25.1.1 = c("GFU",
"OJT", "INSP", "RATU", "AMI", "RATU", "ET"), X26.1 = c(4L,
4L, 2L, 1L, 1L, 4L, 3L), X26.1.1 = c("CO", "FREE", "INSP",
"RATU", "AMI", "FREE", "DETE"), X27.1 = c(2L, 4L, 2L, 4L,
4L, 4L, 4L), X27.1.1 = c("GFU", "FREE", "INSP", "FREE", "FREE",
"FREE", "FREE"), X28.1 = c(2L, 3L, 2L, 3L, 3L, 1L, 2L), X28.1.1 = c("GFU",
"OJT", "INSP", "RATU", "AMI", "RATU", "ET"), X29.1 = c(2L,
3L, 2L, 3L, 3L, 1L, 2L), X29.1.1 = c("GFU", "OJT", "INSP",
"RATU", "AMI", "RATU", "ET"), X30.1 = c(4L, 3L, 4L, 3L, 3L,
1L, 2L), X30.1.1 = c("LTC", "OJT", "LTC", "RATU", "AMI",
"RATU", "ET"), X31.1 = c(3L, 3L, 3L, 3L, 3L, 1L, 2L), X31.1.1 = c("GFU",
"OJT", "INSP", "RATU", "AMI", "RATU", "ET")), class = "data.frame", row.names = c(NA,
-7L))
> `
#######
library(dplyr)
library(formattable)
library(reshape2)
library(data.table)
library(tidyr)
cent<-read.csv("d:/TEMP/centre1.csv", header=TRUE)
cent
cent1 <- cent %>% select(c(First.Name, Last.Name,starts_with("X"),-ends_with('.1.1')))
cent2<-cent%>% select(c(First.Name, Last.Name,ends_with(".1.1")))
x1 <- cent1 %>% pivot_longer(cols =-c(First.Name,Last.Name), names_to='year',
values_to='points' )
x2<-cent2 %>% pivot_longer(cols = -c(First.Name,Last.Name),
names_to='year', values_to='points' )
total <- cbind(x1, x2$points)
names(total)[5] <- "masina"
names(total)[4] <- "schimb"
total1 <- total %>% filter(total$masina%in% c('GFU','RATU','INSP','DETE','3D','AMI',
'ET','ISTE','ITAL', 'LEAL','LIDR','MANI','MPI','DATE'))
fapt<-total1 %>% group_by(First.Name,Last.Name,masina) %>%
count(masina) %>% arrange( Last.Name,First.Name)
fapt1<-total1 %>% group_by(First.Name,Last.Name,masina) %>%
count(schimb) %>% arrange( Last.Name,First.Name)
#I dont know why dput add after each number L:Ex:X1.1 SHOULD BE=c(1,0,1,4,1,1,3)
Thanks, the data came through with no problem. I'll have to think about what you are doing.
Here's a toy example of how you can pivot and summarize a dataframe that has multiple pairs of columns:
library(tidyverse)
tribble(
~name, ~item_2010, ~value_2010, ~item_2011, ~value_2011,
"Alice", "pencils", 3, "pencils", 6,
"Bob", "markers", 8, "pencils", 2,
"Charlie", "highlighters", 5, "pencils", 4
) %T>% print() %>%
pivot_longer(
cols = -name,
names_to = c(".value", "year"),
names_pattern = "(.*)_(.*)"
) %T>% print() %>%
summarise(
.by = c(name, item), count = sum(value)
)
which gives:
# A tibble: 3 × 5
name item_2010 value_2010 item_2011 value_2011
<chr> <chr> <dbl> <chr> <dbl>
1 Alice pencils 3 pencils 6
2 Bob markers 8 pencils 2
3 Charlie highlighters 5 pencils 4
# A tibble: 6 × 4
name year item value
<chr> <chr> <chr> <dbl>
1 Alice 2010 pencils 3
2 Alice 2011 pencils 6
3 Bob 2010 markers 8
4 Bob 2011 pencils 2
5 Charlie 2010 highlighters 5
6 Charlie 2011 pencils 4
# A tibble: 5 × 3
name item count
<chr> <chr> <dbl>
1 Alice pencils 9
2 Bob markers 8
3 Bob pencils 2
4 Charlie highlighters 5
5 Charlie pencils 4
is that similar to what you were trying to do?
YEs something similar .I dont know how ro use names of columns (X1.1 X1.1.1 X2.1 X2.1.1 X3.1 X3.1.1 X4.1 X4.1.1 X5.1 X5.1.1 X6.)
I'd rename to the column names to something easier, perhaps
fix_colname <- function(x) {
print (x)
if (grepl("X(\\d)\\.(\\d)$", x)) {
str_replace(string = x, pattern = "X(\\d)\\.(\\d)", replacement = "item_\\1") }
else if(grepl("X(\\d)\\.(\\d)\\.(\\d)", x)) {
str_replace(x, "X(\\d)\\.(\\d)\\.(\\d)", "value_\\1")
} else {
x
}
}
colnames(df) <- map_chr(colnames(df), fix_colname)
Yes function are working but only till X.9.1 and X9.1.1 .
Function fix_colname dont replace ex:X10.1 with item _10 and X10.1.1 with value_10 and so one
That's easy to fix. In stead of matching a single digit with \d in the pattern, use \d+
fix_colname <- function(x) {
print (x)
if (grepl("X(\\d+)\\.(\\d)$", x)) {
str_replace(string = x, pattern = "X(\\d+)\\.(\\d)", replacement = "item_\\1") }
else if(grepl("X(\\d+)\\.(\\d)\\.(\\d)", x)) {
str_replace(x, "X(\\d+)\\.(\\d)\\.(\\d)", "value_\\1")
} else {
x
}
}