How to import text data with different column lengths without having NaN

6 views (last 30 days)
GB
GB on 15 Mar 2024
Answered: Stephen23 on 15 Mar 2024
I have a CSV data file with time and data values, each pairing having different numbers of rows. I want to read each column as a variable without any NaN values. With readtable, the shorter column vectors are populated with NaN. What options do I have to read the text file and ignore all NaN so I have column vectors of different lenghts with all valid data?
For example:
Time,current,Time1,speed,Time2,temp,Time3,pressure,Time4,out
2246.6426,0,7187.7838,0,7187.8113,42.960379,2246.6476,0.059509242,7187.7838,16666.699
2246.6526,0,7188.7839,0,7188.8114,42.919011,2246.6576,0.059738124,7188.7839,16666.699
2246.6626,0,7189.7839,0,7189.8114,42.906844,2246.6676,0.05966183,7189.7839,16666.699
2246.6726,0,7190.7839,0,7190.8114,42.921445,2246.6776,0.059738124,7190.7839,16666.699
2246.6826,0,7191.7839,0,7191.8114,42.889811,2246.6876,0.059509242,7191.7839,16666.699
2246.6926,0,7192.7839,0,7192.8114,42.87521,2246.6976,0.059585536,7192.7839,16666.699
2246.7026,0,7193.7839,0,7193.8114,42.870343,2246.7076,0.059509242,7193.7839,16666.699
2246.7126,0,7194.7839,0,7194.8114,42.86791,2246.7176,0.05966183,7194.7839,16666.699
2246.7226,0,7195.7839,0,7195.8114,42.850876,2246.7276,0.059432948,7195.7839,16666.699
2246.7326,0, , , , ,2246.7376,0.059890712, ,
2246.7426,0, , , , ,2246.7476,0.05966183, ,
2246.7526,0, , , , ,2246.7576,0.05966183, ,
2246.7626,0, , , , ,2246.7676,0.059738124, ,
2246.7726,0, , , , ,2246.7776,0.059738124, ,
2246.7826,0, , , , ,2246.7876,0.059738124, ,
2246.7926,0, , , , ,2246.7976,0.059738124, ,
2246.8026,0, , , , ,2246.8076,0.059814418, ,
2246.8126,0, , , , ,2246.8176,0.05966183, ,
2246.8226,0, , , , ,2246.8276,0.059738124, ,
2246.8326,0, , , , ,2246.8376,0.059738124, ,
2246.8426,0, , , , ,2246.8476,0.059585536, ,
Readtable gives:
22×10 table
x_Time MAIF_WRP_CURRENT_A_ Time MAIF_PWM_SPEED_RPM_ Time_1 MAIF_WRP_TEMP_C_ Time_2 MAIF_MP_OUT_V_ Time_3 MAIF_MP_PRESS_mTorr_
______ ___________________ ______ ___________________ ______ ________________ ______ ______________ ______ ____________________
2246.6 0 7183.8 0 7183.8 42.99 2246.6 0.059586 7183.8 16667
2246.6 0 7184.8 0 7184.8 42.958 2246.6 0.059586 7184.8 16667
2246.6 0 7185.8 0 7185.8 42.951 2246.6 0.059662 7185.8 16667
2246.6 0 7186.8 0 7186.8 42.943 2246.6 0.059586 7186.8 16667
2246.6 0 7187.8 0 7187.8 42.96 2246.6 0.059509 7187.8 16667
: : : : : : : : : :
2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN
2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN
2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN
2246.8 0 NaN NaN NaN NaN 2246.8 0.059814 NaN NaN
2246.8 0 NaN NaN NaN NaN 2246.8 0.059662 NaN NaN
All math functions on the shorter variables return NaN values.

Sign in to answer this question.

Accepted Answer

Voss
Voss on 15 Mar 2024
Ran in:
Here's a method you may find useful:
Read the file into a table:
T = readtable('test.txt')
T = 21×10 table
Time current Time1 speed Time2 temp Time3 pressure Time4 out ______ _______ ______ _____ ______ ______ ______ ________ ______ _____ 2246.6 0 7187.8 0 7187.8 42.96 2246.6 0.059509 7187.8 16667 2246.7 0 7188.8 0 7188.8 42.919 2246.7 0.059738 7188.8 16667 2246.7 0 7189.8 0 7189.8 42.907 2246.7 0.059662 7189.8 16667 2246.7 0 7190.8 0 7190.8 42.921 2246.7 0.059738 7190.8 16667 2246.7 0 7191.8 0 7191.8 42.89 2246.7 0.059509 7191.8 16667 2246.7 0 7192.8 0 7192.8 42.875 2246.7 0.059586 7192.8 16667 2246.7 0 7193.8 0 7193.8 42.87 2246.7 0.059509 7193.8 16667 2246.7 0 7194.8 0 7194.8 42.868 2246.7 0.059662 7194.8 16667 2246.7 0 7195.8 0 7195.8 42.851 2246.7 0.059433 7195.8 16667 2246.7 0 NaN NaN NaN NaN 2246.7 0.059891 NaN NaN 2246.7 0 NaN NaN NaN NaN 2246.7 0.059662 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059662 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN
Then convert table into a scalar struct:
S = table2struct(T,'ToScalar',true)
S = struct with fields:
Time: [21×1 double] current: [21×1 double] Time1: [21×1 double] speed: [21×1 double] Time2: [21×1 double] temp: [21×1 double] Time3: [21×1 double] pressure: [21×1 double] Time4: [21×1 double] out: [21×1 double]
Finally remove the NaNs from each field of the struct:
% if you want to remove only trailing NaNs from each column:
% S = structfun(@(x)x(1:find(~isnan(x),1,'last')),S,'UniformOutput',false)
% if you want to remove all NaNs from each column:
S = structfun(@(x)x(~isnan(x)),S,'UniformOutput',false)
S = struct with fields:
Time: [21×1 double] current: [21×1 double] Time1: [9×1 double] speed: [9×1 double] Time2: [9×1 double] temp: [9×1 double] Time3: [21×1 double] pressure: [21×1 double] Time4: [9×1 double] out: [9×1 double]

More Answers (2)

Walter Roberson
Walter Roberson on 15 Mar 2024
There is no option for producing column vectors of different lengths.
After you readtable, you can
newTable = varfunc(@(DATA) {rmmissing(DATA)}, TheTable);
this will give you a table that contains 1 x 1 cell arrays that have the individual column data.
This will likely be pretty much useless.
Stephen23
Stephen23 on 15 Mar 2024
Ran in:
Keeping the corresponding pairs of columns together:
T = readtable('test.txt', 'Delimiter',',')
T = 21×10 table
Time current Time1 speed Time2 temp Time3 pressure Time4 out ______ _______ ______ _____ ______ ______ ______ ________ ______ _____ 2246.6 0 7187.8 0 7187.8 42.96 2246.6 0.059509 7187.8 16667 2246.7 0 7188.8 0 7188.8 42.919 2246.7 0.059738 7188.8 16667 2246.7 0 7189.8 0 7189.8 42.907 2246.7 0.059662 7189.8 16667 2246.7 0 7190.8 0 7190.8 42.921 2246.7 0.059738 7190.8 16667 2246.7 0 7191.8 0 7191.8 42.89 2246.7 0.059509 7191.8 16667 2246.7 0 7192.8 0 7192.8 42.875 2246.7 0.059586 7192.8 16667 2246.7 0 7193.8 0 7193.8 42.87 2246.7 0.059509 7193.8 16667 2246.7 0 7194.8 0 7194.8 42.868 2246.7 0.059662 7194.8 16667 2246.7 0 7195.8 0 7195.8 42.851 2246.7 0.059433 7195.8 16667 2246.7 0 NaN NaN NaN NaN 2246.7 0.059891 NaN NaN 2246.7 0 NaN NaN NaN NaN 2246.7 0.059662 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059662 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN
for k = width(T):-2:1
T = mergevars(T,k-1:k,'MergeAsTable',true);
end
display(T)
T = 21×5 table
Var1 Var3 Var5 Var7 Var9 Time current Time1 speed Time2 temp Time3 pressure Time4 out _________________ _______________ ________________ __________________ _______________ 2246.6 0 7187.8 0 7187.8 42.96 2246.6 0.059509 7187.8 16667 2246.7 0 7188.8 0 7188.8 42.919 2246.7 0.059738 7188.8 16667 2246.7 0 7189.8 0 7189.8 42.907 2246.7 0.059662 7189.8 16667 2246.7 0 7190.8 0 7190.8 42.921 2246.7 0.059738 7190.8 16667 2246.7 0 7191.8 0 7191.8 42.89 2246.7 0.059509 7191.8 16667 2246.7 0 7192.8 0 7192.8 42.875 2246.7 0.059586 7192.8 16667 2246.7 0 7193.8 0 7193.8 42.87 2246.7 0.059509 7193.8 16667 2246.7 0 7194.8 0 7194.8 42.868 2246.7 0.059662 7194.8 16667 2246.7 0 7195.8 0 7195.8 42.851 2246.7 0.059433 7195.8 16667 2246.7 0 NaN NaN NaN NaN 2246.7 0.059891 NaN NaN 2246.7 0 NaN NaN NaN NaN 2246.7 0.059662 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059662 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN 2246.8 0 NaN NaN NaN NaN 2246.8 0.059738 NaN NaN
S = table2struct(T,'ToScalar',true);
S = structfun(@rmmissing,S,'uni',0)
S = struct with fields:
Var1: [21×2 table] Var3: [9×2 table] Var5: [9×2 table] Var7: [21×2 table] Var9: [9×2 table]
S.Var1
ans = 21×2 table
Time current ______ _______ 2246.6 0 2246.7 0 2246.7 0 2246.7 0 2246.7 0 2246.7 0 2246.7 0 2246.7 0 2246.7 0 2246.7 0 2246.7 0 2246.8 0 2246.8 0 2246.8 0 2246.8 0 2246.8 0
S.Var3
ans = 9×2 table
Time1 speed ______ _____ 7187.8 0 7188.8 0 7189.8 0 7190.8 0 7191.8 0 7192.8 0 7193.8 0 7194.8 0 7195.8 0

Categories

Find more on Matrices and Arrays in Help Center and File Exchange

Tags

Products


Release

R2023b

Community Treasure Hunt

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

Start Hunting!