Rearrange the rows as column variables

1 visualizzazione (ultimi 30 giorni)
Man Lap Fung
Man Lap Fung il 5 Giu 2022
Modificato: Stephen23 il 6 Giu 2022
I have a large txt file with 41MB. It consists of 72 sensors measurements over the time. In case, I want to rearrange the table so that the 72 sensors are set as the column headings while the change of the "R" values of each of the sensors are shown under each column. For each row, the recording time of the #0 sensor will be put as each time step.
The resulting table would be like this:
As the file is too large and cannot be attached, below I show the first 3 sets of 72 sensor measurements. There are more than 10,000 sets in total. Thank you in advance.
# R Time
0 -2.1 03.11.2021 11:14:31
1 -7.04 03.11.2021 11:14:34
2 -11.85 03.11.2021 11:14:37
3 16 03.11.2021 11:14:41
4 12.13 03.11.2021 11:14:44
5 3.64 03.11.2021 11:14:47
6 -5.85 03.11.2021 11:14:50
7 37.43 03.11.2021 11:14:53
8 30.75 03.11.2021 11:14:57
9 23.03 03.11.2021 11:15:00
10 13.36 03.11.2021 11:15:03
11 60.76 03.11.2021 11:15:06
12 58.52 03.11.2021 11:15:09
13 56.85 03.11.2021 11:15:13
14 55.63 03.11.2021 11:15:16
15 87.26 03.11.2021 11:15:19
16 89.2 03.11.2021 11:15:22
17 104.1 03.11.2021 11:15:26
18 151.12 03.11.2021 11:15:29
19 113.1 03.11.2021 11:15:32
20 116.69 03.11.2021 11:15:35
21 140.12 03.11.2021 11:15:38
22 -5.56 03.11.2021 11:15:42
23 -17.22 03.11.2021 11:15:45
24 -27.46 03.11.2021 11:15:48
25 14.65 03.11.2021 11:15:51
26 3.89 03.11.2021 11:15:55
27 -15.5 03.11.2021 11:15:58
28 -53.9 03.11.2021 11:16:01
29 29.31 03.11.2021 11:16:04
30 19.07 03.11.2021 11:16:08
31 -5.17 03.11.2021 11:16:11
32 -60.37 03.11.2021 11:16:14
33 52.99 03.11.2021 11:16:17
34 41.11 03.11.2021 11:16:20
35 17.9 03.11.2021 11:16:24
36 -4.89 03.11.2021 11:16:27
37 80.25 03.11.2021 11:16:30
38 84.29 03.11.2021 11:16:33
39 81.63 03.11.2021 11:16:37
40 58.14 03.11.2021 11:16:40
41 110.88 03.11.2021 11:16:43
42 115.76 03.11.2021 11:16:46
43 110.73 03.11.2021 11:16:49
44 117.21 03.11.2021 11:16:53
45 -8.89 03.11.2021 11:16:56
46 -30.44 03.11.2021 11:16:59
47 -75.54 03.11.2021 11:17:02
48 9.1 03.11.2021 11:17:06
49 -0.3 03.11.2021 11:17:09
50 -40.23 03.11.2021 11:17:12
51 -141.76 03.11.2021 11:17:15
52 27.14 03.11.2021 11:17:18
53 14.53 03.11.2021 11:17:22
54 -31.13 03.11.2021 11:17:25
55 -291.18 03.11.2021 11:17:28
56 51.57 03.11.2021 11:17:31
57 40.84 03.11.2021 11:17:35
58 3.42 03.11.2021 11:17:38
59 -109.39 03.11.2021 11:17:41
60 72.22 03.11.2021 11:17:44
61 66.68 03.11.2021 11:17:48
62 43.86 03.11.2021 11:17:51
63 24.17 03.11.2021 11:17:54
64 99.99 03.11.2021 11:17:57
65 95.01 03.11.2021 11:18:00
66 83.49 03.11.2021 11:18:04
67 84.02 03.11.2021 11:18:07
68 -138.91 03.11.2021 11:18:10
69 -110.91 03.11.2021 11:18:13
70 -109.58 03.11.2021 11:18:16
71 -107.61 03.11.2021 11:18:20
0 -2.08 03.11.2021 14:15:03
1 -7.02 03.11.2021 14:15:07
2 -11.92 03.11.2021 14:15:10
3 16.04 03.11.2021 14:15:13
4 12.18 03.11.2021 14:15:16
5 3.72 03.11.2021 14:15:20
6 -5.92 03.11.2021 14:15:23
7 37.35 03.11.2021 14:15:26
8 30.59 03.11.2021 14:15:29
9 23.13 03.11.2021 14:15:33
10 13.46 03.11.2021 14:15:36
11 60.91 03.11.2021 14:15:39
12 58.53 03.11.2021 14:15:42
13 56.95 03.11.2021 14:15:45
14 55.87 03.11.2021 14:15:49
15 87.23 03.11.2021 14:15:52
16 89.47 03.11.2021 14:15:55
17 103.47 03.11.2021 14:15:58
18 151.15 03.11.2021 14:16:01
19 113.29 03.11.2021 14:16:05
20 116.45 03.11.2021 14:16:08
21 140.87 03.11.2021 14:16:11
22 -5.62 03.11.2021 14:16:14
23 -17.37 03.11.2021 14:16:18
24 -27.34 03.11.2021 14:16:21
25 14.6 03.11.2021 14:16:24
26 3.91 03.11.2021 14:16:27
27 -15.54 03.11.2021 14:16:30
28 -54.16 03.11.2021 14:16:34
29 29.26 03.11.2021 14:16:37
30 18.82 03.11.2021 14:16:40
31 -4.91 03.11.2021 14:16:43
32 -60.41 03.11.2021 14:16:46
33 52.75 03.11.2021 14:16:50
34 40.86 03.11.2021 14:16:53
35 17.73 03.11.2021 14:16:56
36 -4.76 03.11.2021 14:16:59
37 80.4 03.11.2021 14:17:03
38 84.38 03.11.2021 14:17:06
39 81.41 03.11.2021 14:17:09
40 58.67 03.11.2021 14:17:12
41 110.93 03.11.2021 14:17:15
42 115.56 03.11.2021 14:17:19
43 110.8 03.11.2021 14:17:22
44 116.95 03.11.2021 14:17:25
45 -8.28 03.11.2021 14:17:28
46 -30.01 03.11.2021 14:17:32
47 -74.32 03.11.2021 14:17:35
48 9.26 03.11.2021 14:17:38
49 -0.23 03.11.2021 14:17:41
50 -40.28 03.11.2021 14:17:44
51 -142.73 03.11.2021 14:17:48
52 27.11 03.11.2021 14:17:51
53 14.56 03.11.2021 14:17:54
54 -31.44 03.11.2021 14:17:57
55 -294.58 03.11.2021 14:18:00
56 51.62 03.11.2021 14:18:04
57 41.02 03.11.2021 14:18:07
58 3.26 03.11.2021 14:18:10
59 -108.46 03.11.2021 14:18:13
60 72.29 03.11.2021 14:18:17
61 66.25 03.11.2021 14:18:20
62 43.66 03.11.2021 14:18:23
63 24.59 03.11.2021 14:18:26
64 100.69 03.11.2021 14:18:29
65 95 03.11.2021 14:18:33
66 84.54 03.11.2021 14:18:36
67 84.67 03.11.2021 14:18:39
68 -103.68 03.11.2021 14:18:42
69 -4.82 03.11.2021 14:18:46
70 -9.55 03.11.2021 14:18:49
71 -16.63 03.11.2021 14:18:52
0 -2.08 03.11.2021 14:30:03
1 -6.93 03.11.2021 14:30:06
2 -11.77 03.11.2021 14:30:09
3 15.95 03.11.2021 14:30:12
4 12.1 03.11.2021 14:30:16
5 3.61 03.11.2021 14:30:19
6 -5.83 03.11.2021 14:30:22
7 37.47 03.11.2021 14:30:25
8 30.61 03.11.2021 14:30:28
9 23.19 03.11.2021 14:30:32
10 13.46 03.11.2021 14:30:35
11 60.85 03.11.2021 14:30:38
12 58.32 03.11.2021 14:30:41
13 57.15 03.11.2021 14:30:45
14 55.74 03.11.2021 14:30:48
15 87.05 03.11.2021 14:30:51
16 89.12 03.11.2021 14:30:54
17 103.73 03.11.2021 14:30:57
18 151.31 03.11.2021 14:31:01
19 113.31 03.11.2021 14:31:04
20 116.4 03.11.2021 14:31:07
21 140.44 03.11.2021 14:31:10
22 -5.69 03.11.2021 14:31:13
23 -17.52 03.11.2021 14:31:17
24 -27.84 03.11.2021 14:31:20
25 14.29 03.11.2021 14:31:23
26 4.04 03.11.2021 14:31:26
27 -15.75 03.11.2021 14:31:30
28 -53.91 03.11.2021 14:31:33
29 29 03.11.2021 14:31:36
30 18.93 03.11.2021 14:31:39
31 -5.1 03.11.2021 14:31:42
32 -60.53 03.11.2021 14:31:46
33 52.97 03.11.2021 14:31:49
34 40.82 03.11.2021 14:31:52
35 17.52 03.11.2021 14:31:55
36 -4.83 03.11.2021 14:31:59
37 80.38 03.11.2021 14:32:02
38 84.13 03.11.2021 14:32:05
39 81.79 03.11.2021 14:32:08
40 58.01 03.11.2021 14:32:11
41 110.93 03.11.2021 14:32:15
42 115.43 03.11.2021 14:32:18
43 110.78 03.11.2021 14:32:21
44 117.2 03.11.2021 14:32:24
45 -9.37 03.11.2021 14:32:27
46 -31.7 03.11.2021 14:32:31
47 -78.47 03.11.2021 14:32:34
48 8.42 03.11.2021 14:32:37
49 -0.78 03.11.2021 14:32:40
50 -41.45 03.11.2021 14:32:44
51 -142.92 03.11.2021 14:32:47
52 26.08 03.11.2021 14:32:50
53 13.67 03.11.2021 14:32:53
54 -32.11 03.11.2021 14:32:56
55 -296.35 03.11.2021 14:33:00
56 51.13 03.11.2021 14:33:03
57 40.15 03.11.2021 14:33:06
58 2.87 03.11.2021 14:33:09
59 -109.49 03.11.2021 14:33:13
60 71.65 03.11.2021 14:33:16
61 65.62 03.11.2021 14:33:19
62 42.51 03.11.2021 14:33:22
63 23.27 03.11.2021 14:33:25
64 99.31 03.11.2021 14:33:29
65 94.27 03.11.2021 14:33:32
66 82.23 03.11.2021 14:33:35
67 83.96 03.11.2021 14:33:38
68 -3.89 03.11.2021 14:33:41
69 -5.14 03.11.2021 14:33:45
70 -9.38 03.11.2021 14:33:48
71 -16.86 03.11.2021 14:33:51
  1 Commento
Stephen23
Stephen23 il 6 Giu 2022
Modificato: Stephen23 il 6 Giu 2022
Keeping the data in three columns would probably make it easier to work with.
For example, three columns means you can use the standard methods for analyzing table data:
https://www.mathworks.com/help/matlab/matlab_prog/grouping-variables-for-splitting-data.html

Accedi per commentare.

Accedi per rispondere a questa domanda.

Risposta accettata

Voss
Voss il 5 Giu 2022
Ran in:
If I understand correctly, you only need the times corresponding to sensor #0, and the other times can be discarded/ignored. If that's the case, maybe something along these lines will work for you:
t = readtable('table.txt','PreserveVariableNames',true);
N_sensors = 72;
zero_times = t{t{:,'#'} == 0,'Time'};
R = num2cell(reshape(t.R,N_sensors,[]).',1);
t_new = table(zero_times,R{:},'VariableNames',['Time',sprintfc('%d',0:N_sensors-1)])
t_new = 3×73 table
Time 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 ___________________ _____ _____ ______ _____ _____ ____ _____ _____ _____ _____ _____ _____ _____ _____ _____ _____ _____ ______ ______ ______ ______ ______ _____ ______ ______ _____ ____ ______ ______ _____ _____ _____ ______ _____ _____ _____ _____ _____ _____ _____ _____ ______ ______ ______ ______ _____ ______ ______ ____ _____ ______ _______ _____ _____ ______ _______ _____ _____ ____ _______ _____ _____ _____ _____ ______ _____ _____ _____ _______ _______ _______ _______ 03.11.2021 11:14:31 -2.1 -7.04 -11.85 16 12.13 3.64 -5.85 37.43 30.75 23.03 13.36 60.76 58.52 56.85 55.63 87.26 89.2 104.1 151.12 113.1 116.69 140.12 -5.56 -17.22 -27.46 14.65 3.89 -15.5 -53.9 29.31 19.07 -5.17 -60.37 52.99 41.11 17.9 -4.89 80.25 84.29 81.63 58.14 110.88 115.76 110.73 117.21 -8.89 -30.44 -75.54 9.1 -0.3 -40.23 -141.76 27.14 14.53 -31.13 -291.18 51.57 40.84 3.42 -109.39 72.22 66.68 43.86 24.17 99.99 95.01 83.49 84.02 -138.91 -110.91 -109.58 -107.61 03.11.2021 14:15:03 -2.08 -7.02 -11.92 16.04 12.18 3.72 -5.92 37.35 30.59 23.13 13.46 60.91 58.53 56.95 55.87 87.23 89.47 103.47 151.15 113.29 116.45 140.87 -5.62 -17.37 -27.34 14.6 3.91 -15.54 -54.16 29.26 18.82 -4.91 -60.41 52.75 40.86 17.73 -4.76 80.4 84.38 81.41 58.67 110.93 115.56 110.8 116.95 -8.28 -30.01 -74.32 9.26 -0.23 -40.28 -142.73 27.11 14.56 -31.44 -294.58 51.62 41.02 3.26 -108.46 72.29 66.25 43.66 24.59 100.69 95 84.54 84.67 -103.68 -4.82 -9.55 -16.63 03.11.2021 14:30:03 -2.08 -6.93 -11.77 15.95 12.1 3.61 -5.83 37.47 30.61 23.19 13.46 60.85 58.32 57.15 55.74 87.05 89.12 103.73 151.31 113.31 116.4 140.44 -5.69 -17.52 -27.84 14.29 4.04 -15.75 -53.91 29 18.93 -5.1 -60.53 52.97 40.82 17.52 -4.83 80.38 84.13 81.79 58.01 110.93 115.43 110.78 117.2 -9.37 -31.7 -78.47 8.42 -0.78 -41.45 -142.92 26.08 13.67 -32.11 -296.35 51.13 40.15 2.87 -109.49 71.65 65.62 42.51 23.27 99.31 94.27 82.23 83.96 -3.89 -5.14 -9.38 -16.86
  2 Commenti
Man Lap Fung
Man Lap Fung il 5 Giu 2022
Yes. That's what I would like to create. Thank you!
Voss
Voss il 5 Giu 2022
You're welcome!

Accedi per commentare.

Più risposte (1)

Image Analyst
Image Analyst il 5 Giu 2022
Modificato: Image Analyst il 5 Giu 2022
Ran in:
Did you try readtable
t = readtable('dataTable.txt')
t = 216×3 table
Number R DateAndTime ______ ______ ___________________ 0 -2.1 03.11.2021 11:14:31 1 -7.04 03.11.2021 11:14:34 2 -11.85 03.11.2021 11:14:37 3 16 03.11.2021 11:14:41 4 12.13 03.11.2021 11:14:44 5 3.64 03.11.2021 11:14:47 6 -5.85 03.11.2021 11:14:50 7 37.43 03.11.2021 11:14:53 8 30.75 03.11.2021 11:14:57 9 23.03 03.11.2021 11:15:00 10 13.36 03.11.2021 11:15:03 11 60.76 03.11.2021 11:15:06 12 58.52 03.11.2021 11:15:09 13 56.85 03.11.2021 11:15:13 14 55.63 03.11.2021 11:15:16 15 87.26 03.11.2021 11:15:19
It doesn't look like for each unique time there are 72 readings, so what do you plan on doing about that?

Categorie

Scopri di più su Tables in Help Center e File Exchange

Tag

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by