getting error while joining 2 file size of 2 mb and 3 GB

Question

naadiya khudabux 2016-10-9

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/306420-getting-error-while-joining-2-file-size-of-2-mb-and-3-gb

评论： Walter Roberson 2016-11-13

hello Everyone!!

i have to join two files, i am using innerjoin with one key column 'XDRID' but am getting the following error.can anybody help me in knowing to why am getting this error and how to solve.

    Error using table/innerjoin (line 78)
Left and right key variables 'XDRID' and 'XDRID' are not comparable
because one is a non-cell.

9 个评论
显示 7更早的评论隐藏 7更早的评论

naadiya khudabux 2016-10-9

编辑：Walter Roberson 2016-10-9

在 MATLAB Online 中打开

'160a01184201c500'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304572016.00  1467304690756.00  1  5  17  NaN  NaN  NaN  NaN  0  NaN  80  889  532  14  4  0  0  0  0  33  100  0  0  1  40  NaN  NaN  2  0  1  6  200  40  40  69  'data.video.qiyi.com'  NaN  NaN  NaN  'text/plain'  NaN  NaN  143  0  3  0  40  0  NaN
'160a011842034200'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304573036.00  1467304573246.00  1  5  17  NaN  NaN  NaN  NaN  0  NaN  80  804  279  4  3  0  0  0  0  27  69  0  0  90  28  NaN  NaN  1  0  1  6  200  28  28  49  'mbdlog.iqiyi.com'  NaN  NaN  NaN  'text/html'  NaN  NaN  0  0  3  0  28  0  NaN
'160a01184204f700'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304574196.00  1467304574341.00  1  5  17  NaN  NaN  NaN  NaN  0  NaN  80  858  279  4  3  0  0  0  0  33  60  0  0  29  33  NaN  NaN  1  0  1  6  200  33  33  55  'mbdlog.iqiyi.com'  NaN  NaN  NaN  'text/html'  NaN  NaN  0  0  3  0  33  0  NaN
'160a01184209c800'  NaN  7.17462676157379e+18  7.63062400670183e+16  4.97705383067421e+18  NaN  NaN  29547  118294295  'CMNET'  103  1467304577391.00  1467304577590.00  1  3  2  NaN  NaN  NaN  NaN  0  NaN  80  892  1086  6  5  0  1  0  0  30  79  0  0  10  50  NaN  NaN  1  0  1  6  200  50  50  70  'api.weibo.cn'  NaN  NaN  NaN  'application/json'  NaN  NaN  0  0  1  0  50  0  3
'160a0118421bcb00'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304589045.00  1467304619236.00  1  6  36  NaN  NaN  NaN  NaN  0  NaN  80  564  2590  8  10  0  0  0  0  2  60  0  0  10  4  NaN  NaN  1  0  1  6  200  4  4  24  'p1.music.126.net'  NaN  NaN  NaN  'image/jpg'  NaN  NaN  1800  0  3  0  4  0  NaN
'160a0118421bcc00'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304589006.00  1467304589225.00  1  6  36  NaN  NaN  NaN  NaN  0  NaN  80  448  2207  6  5  0  0  0  0  2  60  0  0  10  109  NaN  NaN  1  0  1  6  200  109  109  149  'p1.music.126.net'  NaN  NaN  NaN  'image/jpg'  NaN  NaN  1666  0  3  0  109  0  NaN
'160a011842376000'  NaN  1.59887515923291e+18  4.90256020833500e+18  8.61871928453489e+18  NaN  NaN  29547  117726722  'CMNET'  103  1467304608515.00  1467304608789.00  1  1  9  5  0  NaN  NaN  0  NaN  80  1142  6381  9  9  0  0  0  0  23  40  0  0  50  95  NaN  NaN  1  0  1  5  200  95  95  159  'short.weixin.qq.com'  NaN  NaN  NaN  'application/octet-stream'  NaN  NaN  5909  0  3  0  95  0  1
'160a0118423b6400'  NaN  8.67272675720160e+18  6.73500442591658e+18  3.26382062659079e+18  NaN  NaN  29633  252422019  'CMNET'  103  1467304611735.00  1467304618733.00  1  18  10078  NaN  NaN  NaN  NaN  0  NaN  80  1075  264  6  6  0  0  0  0  4  30  0  0  6934  0  NaN  NaN  1  0  1  6  1001  0  0  0  'q.dmzj.com'  NaN  NaN  NaN  ''  NaN  NaN  0  0  3  1  0  0  NaN

naadiya khudabux 2016-10-9

consider this as my 2nd table. i am trying to join on the base of 'XDRID'.

naadiya khudabux 2016-10-9

在 MATLAB Online 中打开

if true
 K>> drop.VariableNames
ans = 
Columns 1 through 6
  'XDRID'    'RAT'    'IMSI'    'IMEI'    'MSISDN'    'MachineIPAddType'
Columns 7 through 12
  'SGWGGSNIPAdd'    'TAC'    'ECI'    'APN'    'AppTypeCode'    'RequestTime'
Columns 13 through 16
  'ProcedureEndTime'    'ProtocolType'    'AppType'    'AppSubType'
Columns 17 through 21
  'AppContent'    'AppStatus'    'USER_IPv4'    'UserPort'    'L4Protocal'
Columns 22 through 25
  'AppServerIP_IPv4'    'AppServerPort'    'ULTraffic'    'DLTraffic'
Columns 26 through 29
  'ULIPPacket'    'DLIPPacket'    'ULTCPOoOPacket'    'DLTCPOoOPacket'
Columns 30 through 32
  'ULTCPRetransPacket'    'DLTCPRetransPacket'    'TCPSYNAtteDelay'
Columns 33 through 35
  'TCPSYNComfirmDelay'    'ULIPFRAGPACKETS'    'DLIPFRAGPACKETS'
Columns 36 through 39
  'TCPSYNSuccFirstRe…'     'FirstReqToFirstRe…'     'WindowSize'    'MSSSize'
Columns 40 through 43
  'TCPSYNAtte'    'TCPConnStatus'    'SessionIsEnd'    'EventType'
Columns 44 through 46
  'HTTPWAPStatus'    'FirstHTTPResPacke…'     'LastHTTPPacketDelay'
Columns 47 through 51
  'LastACKPacketDelay'    'HOST'    'URI'    'XOnlineHost'    'UserAgent'
Columns 52 through 55
  'HTTP_content_type'    'refer_URI'    'Cookie'    'ContentLength'
Columns 56 through 59
  'DestBeha'    'OperBehaIden'    'OperFinishIden'    'OperDelay'
Columns 60 through 61
  'Browser'    'PortalAppCollection'
end

These are the labels for all the columns

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Walter Roberson 2016-10-9

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/306420-getting-error-while-joining-2-file-size-of-2-mb-and-3-gb#answer_238191

The error message you are getting would be consistent with the first column of the second table being named XDRID, the same as the third column of the first table. However, the third column of the first table is numeric, while the first column of the second table is character vector.

When you do a table join, the fields that have the same name in the two tables must also have the same data type.

50 个评论
显示 48更早的评论隐藏 48更早的评论

Walter Roberson 2016-10-9

In the first table, the values such as 1000077641156912666 are 19 digit numbers, with values exceeding the maximum that can be represented in a double precision number, but within range of what can be represented as an uint64, so if the data was read in carefully there is a possibility that all digits are meaningful.

In the second table, the values such as '160a01184201c500' are 16 characters that are hex digits. Those might represent unsigned 64 bit numbers; if so then it is important to know the byte order to do the decoding. I cannot tell at the moment what the intended order is. The hex representation of the values in the first table, the ones starting with 1000*, all come out starting with '0d' hex, which is a byte that does not appear in any of the hex values in the second table, suggesting that if they do represent 64 bit integers then they are in a different range.

In any case, what I speculated earlier is still the case: in one table the values for XDRID are numeric and in the other table they are string. You cannot join tables on a field if the representations for the fields are different between the tables.

naadiya khudabux 2016-10-11

编辑：Walter Roberson 2016-11-13

在 MATLAB Online 中打开

'160a01184201c500'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304572016.00  1467304690756.00  1  5  17  NaN  NaN  NaN  NaN  0  NaN  80  889  532  14  4  0  0  0  0  33  100  0  0  1  40  NaN  NaN  2  0  1  6  200  40  40  69  'data.video.qiyi.com'  NaN  NaN  NaN  'text/plain'  NaN  NaN  143  0  3  0  40  0  NaN
'160a011842034200'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304573036.00  1467304573246.00  1  5  17  NaN  NaN  NaN  NaN  0  NaN  80  804  279  4  3  0  0  0  0  27  69  0  0  90  28  NaN  NaN  1  0  1  6  200  28  28  49  'mbdlog.iqiyi.com'  NaN  NaN  NaN  'text/html'  NaN  NaN  0  0  3  0  28  0  NaN
'160a01184204f700'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304574196.00  1467304574341.00  1  5  17  NaN  NaN  NaN  NaN  0  NaN  80  858  279  4  3  0  0  0  0  33  60  0  0  29  33  NaN  NaN  1  0  1  6  200  33  33  55  'mbdlog.iqiyi.com'  NaN  NaN  NaN  'text/html'  NaN  NaN  0  0  3  0  33  0  NaN
'160a01184209c800'  NaN  7.17462676157379e+18  7.63062400670183e+16  4.97705383067421e+18  NaN  NaN  29547  118294295  'CMNET'  103  1467304577391.00  1467304577590.00  1  3  2  NaN  NaN  NaN  NaN  0  NaN  80  892  1086  6  5  0  1  0  0  30  79  0  0  10  50  NaN  NaN  1  0  1  6  200  50  50  70  'api.weibo.cn'  NaN  NaN  NaN  'application/json'  NaN  NaN  0  0  1  0  50  0  3
'160a0118421bcb00'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304589045.00  1467304619236.00  1  6  36  NaN  NaN  NaN  NaN  0  NaN  80  564  2590  8  10  0  0  0  0  2  60  0  0  10  4  NaN  NaN  1  0  1  6  200  4  4  24  'p1.music.126.net'  NaN  NaN  NaN  'image/jpg'  NaN  NaN  1800  0  3  0  4  0  NaN
'160a0118421bcc00'  NaN  3.63391677599642e+18  6.04918968728055e+18  7.30444913473469e+18  NaN  NaN  29547  121298690  'CMNET'  103  1467304589006.00  1467304589225.00  1  6  36  NaN  NaN  NaN  NaN  0  NaN  80  448  2207  6  5  0  0  0  0  2  60  0  0  10  109  NaN  NaN  1  0  1  6  200  109  109  149  'p1.music.126.net'  NaN  NaN  NaN  'image/jpg'  NaN  NaN  1666  0  3  0  109  0  NaN
'160a011842376000'  NaN  1.59887515923291e+18  4.90256020833500e+18  8.61871928453489e+18  NaN  NaN  29547  117726722  'CMNET'  103  1467304608515.00  1467304608789.00  1  1  9  5  0  NaN  NaN  0  NaN  80  1142  6381  9  9  0  0  0  0  23  40  0  0  50  95  NaN  NaN  1  0  1  5  200  95  95  159  'short.weixin.qq.com'  NaN  NaN  NaN  'application/octet-stream'  NaN  NaN  5909  0  3  0  95  0  1
'160a0118423b6400'  NaN  8.67272675720160e+18  6.73500442591658e+18  3.26382062659079e+18  NaN  NaN  29633  252422019  'CMNET'  103  1467304611735.00  1467304618733.00  1  18  10078  NaN  NaN  NaN  NaN  0  NaN  80  1075  264  6  6  0  0  0  0  4  30  0  0  6934  0  NaN  NaN  1  0  1  6  1001  0  0  0  'q.dmzj.com'  NaN  NaN  NaN  ''  NaN  NaN  0  0  3  1  0  0  NaN

Walter Roberson 2016-10-11

I examined '160a01184201c500' as a potential hex representation of a phone number. If we look at the second file, the phone numbers are shown in "leading-1" format, which is the format without the leading '+' that would be used for the North American Numbering Plan numbers for Canada, USA, some US territories, some Caribbean countries, and some British Overseas Territories . For each of those numbers the format is effectively '1' followed by a 3-digit area code, followed by a 3 digit Local Area Exchange, followed by a 4 digit local number. People now typically bundle the 3 digit Local Area Exchange and 4 digit local number together into a 7 digit number, and typically work in terms of 1 plus area code plus 7 digit number.

The valid 3 digit area codes are not permitted to begin with '1', so phone numbers range from about 12000000000 to 19999999999 . If you convert 120000000000 to hex you get 02CB417800 and if you convert 19999999999 to hex you get 04A817C7FF. Therefore if phone numbers are converted to hex, they require 5 bytes (10 hex digits.)

We can then ask "But suppose they left off the leading '1' in the hex representation: would that still need 5 bytes?". The answer to that is Yes, in that the hypothetical phone number 9999999999 (with no leading 1) is going to be relatively close to the 5 bytes required for 12000000000. We could then point out that no actual area codes begin with '999' and suggest that maybe it could fit. There is, though, an area code in Texas that is '979', so you need most of the range. Taking it a step further we can ask what the largest phone number without leading 1 is that could be represented in 4 bytes; it is hex2dec('ffffffff') which is only 4294967295, which is very definitely not enough to represent the 18797502608 phone number shown in the second file even if you remove the leading 1.

We have thus established that if the phone numbers are being represented in hex, that we need 5 bytes (10 hex digits).

I then took the sample entry '160a01184201c500' from the first line of your first table, and extracted all possible subsets of 5 bytes from it, all possible arrangements of 5 bytes taken from those 8 bytes, such as 0a01184201 and c501164216. All possible arrangements in order to take into account that I do not know the byte order that was used, or which 5 bytes out of 8 was used. I took all of the possibilities, and I converted to decimal. And then I looked for values that came out in the range 12000000000 to 19999999999, being consistent with the leading 1 shown in the second table. The result was that no arrangement of 5 bytes out of 8 of '160a01184201c500' converts to decimal with a value in the range 12000000000 to 19999999999. Therefore it is not possible that some subset of 5 bytes out of '160a01184201c500' encodes a North American Numbering Plan phone number that begins with a leading 1.

I then checked for values in the range 2000000000 to 9999999999, under the possibility that the encoded phone numbers were missing the leading '1'. 912 out of the 3720 unique possibilities matched that range, from 3305177354 to 7604410390. That is far too many for me to be able to deduce a subset or byte order pattern, so that path is not useful without either a much larger data sample with a known match, or specific information about how the bytes are laid out.

One thing I did not try was the possibility that an odd number of hex digits was used for the phone number encoding.

I proceeded to test the hypothesis that the 16 hex digits were 8 bytes of a standard IEEE 754 binary floating point number, just in some unknown number. I tried all 40320 possible arrangements. I weeded out the results that were negative or were not integers. 120 results remained, of which the smallest was 46188113177648 which is several digits too large to be a phone number.

At the moment, your problem it not that the data is big and that you might possibly have to use a tabular dataset and mapreduce: your problem at the moment is that you do not know how to decode the phone number supposedly encoded by '160a01184201c500' into something you can use to match on the second table.

The second table has the values in the range 10^18 in the third column. We could hypothesize that it is those values that have been encoded as hex to appear in the first table. Re-using the possible conversions as IEEE 754 binary floating point numbers, we find none of the potential arrangements of hex are within range (they were like 46188113177648 to 46386386305538). I then converted all of the possible byte reorderings of the hex into uint64 numbers, and checked to see if any of them are in a plausible range. The smallest came out as 1585268172810371781 and the largest as 4811278259851428096. Although those have the right number of digits to match the 18 digit numbers that begin with 1000*, they are too large to match those.

Are you certain that the match is on phone number, and not on "account"??? The 8th field of the first dataset, with values such as 29547, looks like a plausible match against the account numbers such as 268451 in the second table.

If you are certain that the phone number is encoded in that hex, then I need a sample record from each table in which the phone number is known to match between the two tables, so that I can try to figure out how it might be encoded.

Walter Roberson 2016-10-15

在 MATLAB Online 中打开

In the file with only three fields, gaoxiaotest2.txt the third field, there named XDRID, appears to be the field to match on.

In the file with 27 fields, disk1.txt, the 6th field, there named MSISDN, appears to be the field to match on. 412 of the entries match.

arcfmt = ['%u64', '%s', repmat('%u64',1,25)];
fid = fopen('disk1.txt','rt');
dc = textscan(fid, arcfmt, 'HeaderLines', 1, 'Delimiter', ',', 'EmptyValue', uint8(0), 'CollectOutput', 1);
fclose(fid);
arc_rows = [dc{1}, dc{3}];
arc_xdrid = dc{2};
acctfmt = repmat('%u64', 1, 3);
fid = fopen('gaoxiaotest2.txt', 'rt');
for part = 1 : 9
  %we read multiple times to account for bugs in the file
  ac{part} = textscan(fid, acctfmt, 'HeaderLines', 1, 'Delimiter', ',', 'EmptyValue', uint8(0), 'CollectOutput', 1);
fclose(fid);
acct_rows = cell2mat( vertcat(ac{:}) );

Now you can match on acct_rows(:,3) and arc_rows(:,5) . (Yes, I did say that it was column 6, but column 2 is a string so I have excluded it from the numeric array.)

My actual code for reading the file is more robust than the above.

The telephone numbers in the files sometimes have a hyphen to indicate a missing digit; sometimes it has a slash to indicate a missing digit; sometimes it has a space instead. You should be thinking about how you want to handle that situation.

naadiya khudabux 2016-10-18

编辑：Walter Roberson 2016-11-13

在 MATLAB Online 中打开

hello .. i want to ask for this kind of file which format specifier I am suppose to use

160a01184201c500    3.63392E+18  6.04919E+18  7.30445E+18                                                            
160a011842034200    3.63392E+18  6.04919E+18  7.30445E+18                                                            
160a01184204f700    3.63392E+18  6.04919E+18  7.30445E+18                                                            
160a01184209c800    7.17463E+18  7.63062E+16  4.97705E+18                                                            
160a0118421bcb00    3.63392E+18  6.04919E+18  7.30445E+18                                                            
160a0118421bcc00    3.63392E+18  6.04919E+18  7.30445E+18                                                            
160a011842376000    1.59888E+18  4.90256E+18  8.61872E+18                                                            
160a0118423b6400    8.67273E+18  6.735E+18  3.26382E+18                                                            
160a0118424ecd00    8.67273E+18  6.735E+18  3.26382E+18                                                            
160a0118424ece00    8.67273E+18  6.735E+18  3.26382E+18                                                            
160a0118424f3200    8.67273E+18  6.735E+18  3.26382E+18                                                            
160a0118424f4e00    8.67273E+18  6.735E+18  3.26382E+18                                                            
160a0118424f6a00    8.67273E+18  6.735E+18  3.26382E+18                                                            
160a0118424f7000    8.67273E+18  6.735E+18  3.26382E+18                                                            
160a0118424f7300    2.06908E+18  8.49562E+17  8.23739E+18                                                            
160a0118424f8d00    8.67273E+18  6.735E+18  3.26382E+18                                                            
160a0118424fb700    2.06908E+18  8.49562E+17  8.23739E+18                                                            
160a0118424fb800    2.06908E+18  8.49562E+17  8.23739E+18

naadiya khudabux 2016-11-12

编辑：Walter Roberson 2016-11-13

在 MATLAB Online 中打开

MSISDN column description: user number(TCBCD code) thorugh Gn/S11 interface signaling access. (is this line is hint to find out the key value?? or do i need to analyze and Interface column values with MSISDN coloum.)

      Interface          XDRID           RAT       IMSI          IMEI         MSISDN      ProcedureType    RequestTime     EndTime      Status    RequestCause    FailureCause    MMEUES1APID    OldMMEGroupID    OldMMECode    OldMTMSI    MMEGroupID    MMECode    MTMSI    TMSI    UserIPv4    MMEIP     TAC        ECI        OtherTAC    OtherECI    APN
      _________    __________________    ___    __________    __________    __________    _____________    ___________    __________    ______    ____________    ____________    ___________    _____________    __________    ________    __________    _______    _____    ____    ________    _____    _____    __________    ________    ________    ___
          '164c82d268df6200'    NaN    1.2351e+18    3.8966e+18    1.9813e+18    3                1.4673e+12     1.4673e+12    0         NaN             NaN             3.3738e+07     NaN              NaN           0           835           196        0        NaN     NaN         NaN      29633    1.1829e+08    NaN         0           NaN
          '164c82d2718abd00'    NaN    5.7829e+18    4.0201e+18    1.7189e+18    3                1.4673e+12     1.4673e+12    0         NaN             NaN             4.0267e+08     NaN              NaN           0           835           202        0        NaN     NaN         NaN      29521    1.2158e+08    NaN         0           NaN
          '164c82d294e6f300'    NaN    7.7102e+18    5.6221e+18    4.0734e+18    3                1.4673e+12     1.4673e+12    0         NaN             NaN              3.356e+08     NaN              NaN           0           835           202        0        NaN     NaN         NaN      29633    1.2148e+08    NaN         0           NaN
          '164c82d29ca4c700'    NaN    1.2351e+18    3.8966e+18    1.9813e+18    3                1.4673e+12     1.4673e+12    0         NaN             NaN             3.3624e+07     NaN              NaN           0           835           196        0        NaN     NaN         NaN      29633    1.1829e+08    NaN         0           NaN
          '164c82d2e39e2e00'    NaN    6.8662e+18    1.8811e+17      3.81e+18    3                1.4673e+12     1.4673e+12    0         NaN             NaN             3.5237e+08     NaN              NaN           0           835           202        0        NaN     NaN         NaN      29633    1.1829e+08    NaN         0           NaN
          '164c82d2f290be00'    NaN    1.0375e+18    2.1788e+18     8.775e+18    3                1.4673e+12     1.4673e+12    0         NaN             NaN             2.8524e+08     NaN              NaN           0           835           194        0        NaN     NaN         NaN      29633    1.1836e+08    NaN         0           NaN
          '164c82d2f57c6300'    NaN    1.3533e+17    4.4186e+18    5.3176e+18    3                1.4673e+12     1.4673e+12    0         NaN             NaN             2.8523e+08     NaN              NaN           0           832           218        0        NaN     NaN         NaN      58271    1.1761e+08    NaN         0           NaN
          '164c82d2f7d05f00'    NaN    5.5829e+18    4.8856e+18    2.3275e+18    3                1.4673e+12     1.4673e+12    0         NaN             NaN             1.6869e+07     NaN              NaN           0           832           218        0        NaN     NaN         NaN      58271    1.1781e+08    NaN         0           NaN

naadiya khudabux 2016-11-12

yeah i know i tokk alot of time of you ... Thank you so much

Walter Roberson 2016-11-13

I went back to my code, and to the disk1.txt and gaoxiaotest2.txt that you had posted. This time around, I can determine that there are no fields upon which those two files match. Every match was at a location where either the data was missing or the field was 0 or 1. I cross-checked all the combinations.

I do not know now how it is I found matches before; they are not there now when I look.

请先登录，再进行评论。