make separate table for sets of lat lon that appears x times in a big table.

1 view (last 30 days)
SChow
SChow on 13 Oct 2023
Answered: Star Strider on 13 Oct 2023
Hi I have a big table 'A' (attached) and I am trying to make multiple tables out of it. Each for a set of lat and long that appears 10 times(for the years from 2010-2019) in the big table. So the new tables will be 10x 'n' (where n is the number of columns in A).
Sorry I do not have a systematic code to put up here.
Many thanks
  1 Comment
Harald
Harald on 13 Oct 2023
Hi,
it is not clear to me what result you are looking for.
A = readtable("who_ambient_air_quality_database_version_2023_(v6.0).xlsx", "Sheet", "Update 2023 (V6.0)");
A = sortrows(A, ["latitude", "longitude", "year"]);
sequenceOf10 = find(A.latitude(1:end-9) == A.latitude(10:end) & ...
A.longitude(1:end-9) == A.longitude(10:end))
From the code, you can see that not all set appear 10 times, and some appear more often (when sequenceOf10 contains consecutive numbers). So, would you like to have a table for...
  • each set of lan and lot (there's more than 10000 of them)
  • each set of lan and lot with exactly 10 observations
  • each set of lan and lot with exactly 10 observations for years 2010 to 2019
  • each set of lan and lot with at least 10 observations
If I think about it more, I could probably come up with additional interpretations of the question.
Best wishes,
Harald

Sign in to comment.

Sign in to answer this question.

Accepted Answer

Star Strider
Star Strider on 13 Oct 2023
Ran in:
Use accumarray for this, however we may not be discussing the same file —
T1 = readtable('who_ambient_ai...3_(v6.0).xlsx', 'Sheet',3)
T1 = 41364×20 table
who_region iso3 country_name city year version pm10_concentration pm25_concentration no2_concentration pm10_tempcov pm25_tempcov no2_tempcov type_of_stations reference web_link population population_source latitude longitude who_ms __________ _______ ____________ _____________ ____ ______________________________ __________________ __________________ _________________ ____________ ____________ ___________ ________________ ____________________________________________________________________________________________________________________________________________ ________________________________________________________________________________________________________________________________________________________________________ ____________ _________________ ________ _________ ______ {'3_Sear'} {'IND'} {'India'} {'Chennai' } 2018 {'version 2022' } {'NA'} {'30' } NaN {'NA'} {'91'} NaN {'NA' } {'U.S. Department of State, United States Environmental Protection Agency.' } {'https://www.airnow.gov/index.cfm?action=airnow.global_summary' } {'9890427' } {'NA'} 13.088 80.278 1 {'3_Sear'} {'IND'} {'India'} {'Solapur' } 2016 {'version 2022, version 2018'} {'NA'} {'39' } NaN {'NA'} {'99'} NaN {'NA' } {'Central Pollution Control Board India, Environmental Data Bank, CPCB' } {'NA' } {'985568' } {'NA'} 17.66 75.906 1 {'3_Sear'} {'IND'} {'India'} {'Chennai' } 2019 {'version 2022' } {'NA'} {'39' } NaN {'NA'} {'85'} NaN {'NA' } {'U.S. Department of State, United States Environmental Protection Agency.' } {'[[["EPA AirNow DOS","http://airnow.gov/index.cfm?action=airnow.global_summary"]]]' } {'9890427' } {'NA'} 13.088 80.278 1 {'3_Sear'} {'IND'} {'India'} {'Hyderabad'} 2019 {'version 2022' } {'NA'} {'42' } NaN {'NA'} {'87'} NaN {'NA' } {'U.S. Department of State, United States Environmental Protection Agency.' } {'[[["EPA AirNow DOS","http://airnow.gov/index.cfm?action=airnow.global_summary"]]]' } {'8943523' } {'NA'} 17.384 78.456 1 {'3_Sear'} {'IND'} {'India'} {'Pune' } 2017 {'version 2022' } {'NA'} {'43' } NaN {'NA'} {'NA'} NaN {'NA' } {'Central Pollution Control Board India, Environmental Data Bank' } {'http://www.cpcb.gov.in/CAAQM/' } {'5727530' } {'NA'} 18.505 73.824 1 {'3_Sear'} {'IND'} {'India'} {'Mumbai' } 2019 {'version 2022' } {'NA'} {'43' } NaN {'NA'} {'90'} NaN {'NA' } {'U.S. Department of State, United States Environmental Protection Agency.' } {'[[["EPA AirNow DOS","http://airnow.gov/index.cfm?action=airnow.global_summary"]]]' } {'21042538'} {'NA'} 19.073 72.883 1 {'3_Sear'} {'IND'} {'India'} {'Chennai' } 2017 {'version 2022' } {'NA'} {'48' } NaN {'NA'} {'76'} NaN {'NA' } {'Central Pollution Control Board India, Environmental Data Bank, U.S. Department of State, United States Environmental Protection Agency.'} {'[[["Central Pollution Control Board","http://www.cpcb.gov.in/CAAQM"]]], http://www.cpcb.gov.in/CAAQM/, https://www.airnow.gov/index.cfm?action=airnow.global_summary'} {'9890427' } {'NA'} 13.077 80.248 1 {'3_Sear'} {'IND'} {'India'} {'Panchkula'} 2017 {'version 2022' } {'NA'} {'52' } NaN {'NA'} {'61'} NaN {'NA' } {'Central Pollution Control Board India, Environmental Data Bank, U.S. Department of State, United States Environmental Protection Agency.'} {'[[["Central Pollution Control Board","http://www.cpcb.gov.in/CAAQM"]]], http://www.cpcb.gov.in/CAAQM/' } {'211355' } {'NA'} 30.7 76.857 1 {'3_Sear'} {'IND'} {'India'} {'Panchkula'} 2016 {'version 2022, version 2018'} {'NA'} {'54' } NaN {'NA'} {'85'} NaN {'NA' } {'Central Pollution Control Board India, Environmental Data Bank, CPCB' } {'http://www.cpcb.gov.in/CAAQM/' } {'211355' } {'NA'} 30.702 76.856 1 {'3_Sear'} {'IND'} {'India'} {'Hyderabad'} 2018 {'version 2022' } {'NA'} {'59' } NaN {'NA'} {'97'} NaN {'NA' } {'U.S. Department of State, United States Environmental Protection Agency.' } {'https://www.airnow.gov/index.cfm?action=airnow.global_summary' } {'8943523' } {'NA'} 17.384 78.456 1 {'3_Sear'} {'IND'} {'India'} {'Ahmedabad'} 2015 {'version 2022, version 2018'} {'NA'} {'65' } NaN {'NA'} {'53'} NaN {'NA' } {'Central Pollution Control Board India, Environmental Data Bank, CPCB' } {'NA' } {'7342850' } {'NA'} 22.996 72.6 1 {'3_Sear'} {'IND'} {'India'} {'Kolkata' } 2019 {'version 2022' } {'NA'} {'68' } NaN {'NA'} {'83'} NaN {'NA' } {'U.S. Department of State, United States Environmental Protection Agency.' } {'[[["EPA AirNow DOS","http://airnow.gov/index.cfm?action=airnow.global_summary"]]]' } {'14864919'} {'NA'} 22.563 88.363 1 {'3_Sear'} {'IND'} {'India'} {'Kolkata' } 2016 {'version 2022, version 2018'} {'NA'} {'77' } NaN {'NA'} {'92'} NaN {'2 Urban'} {'AirNow DOS, U.S. Department of State, United States Environmental Protection Agency.' } {'https://www.airnow.gov/index.cfm?action=airnow.global_summary' } {'14864919'} {'NA'} 22.555 88.355 1 {'3_Sear'} {'IND'} {'India'} {'Jodhpur' } 2017 {'version 2022' } {'NA'} {'83' } NaN {'NA'} {'NA'} NaN {'NA' } {'Central Pollution Control Board India, Environmental Data Bank' } {'http://www.cpcb.gov.in/CAAQM/' } {'1284142' } {'NA'} 26.473 74.641 1 {'3_Sear'} {'IND'} {'India'} {'Jodhpur' } 2016 {'version 2022, version 2018'} {'NA'} {'97' } NaN {'NA'} {'78'} NaN {'NA' } {'Central Pollution Control Board India, Environmental Data Bank, CPCB' } {'http://www.cpcb.gov.in/CAAQM/' } {'1284142' } {'NA'} 26.352 73.572 1 {'3_Sear'} {'IND'} {'India'} {'Jaipur' } 2016 {'version 2022, version 2018'} {'NA'} {'105'} NaN {'NA'} {'73'} NaN {'NA' } {'Central Pollution Control Board India, Environmental Data Bank, CPCB' } {'NA' } {'3460701' } {'NA'} 26.974 75.774 1
tic
[LatLonu,ix1,ix2] = unique([T1.latitude T1.longitude], 'stable','rows'); % Unique Lat & Lon Combinations
LatLonT = accumarray(ix2, (1:numel(ix2))', [], @(x){T1(x,:)}) % Collect Results
LatLonT = 14678×1 cell array
{2×20 table} {1×20 table} {2×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table} {1×20 table}
toc
Elapsed time is 2.697595 seconds.
TableRowSizes = cellfun(@(x)size(x,1), LatLonT, 'Unif',0);
[MinRows,MaxRows] = bounds([TableRowSizes{:}])
MinRows = 1
MaxRows = 13
Rows10Idx = find([TableRowSizes{:}]==10) % Indices Of Tables With Exactly 10 Rows
Rows10Idx = 1×79
215 218 2898 2899 2900 3015 3285 3290 3479 3488 3552 3608 3610 3772 3774 4043 4046 4053 4066 4097 4100 4175 4294 4324 4425 4500 4524 4527 4571 4576
Rows10 = numel(Rows10Idx) % Number Of Tables With Exactly 10 Rows
Rows10 = 79
LatLonT{1} % First Table
ans = 2×20 table
who_region iso3 country_name city year version pm10_concentration pm25_concentration no2_concentration pm10_tempcov pm25_tempcov no2_tempcov type_of_stations reference web_link population population_source latitude longitude who_ms __________ _______ ____________ ___________ ____ ________________ __________________ __________________ _________________ ____________ ____________ ___________ ________________ ____________________________________________________________________________ _____________________________________________________________________________________ ___________ _________________ ________ _________ ______ {'3_Sear'} {'IND'} {'India'} {'Chennai'} 2018 {'version 2022'} {'NA'} {'30'} NaN {'NA'} {'91'} NaN {'NA'} {'U.S. Department of State, United States Environmental Protection Agency.'} {'https://www.airnow.gov/index.cfm?action=airnow.global_summary' } {'9890427'} {'NA'} 13.088 80.278 1 {'3_Sear'} {'IND'} {'India'} {'Chennai'} 2019 {'version 2022'} {'NA'} {'39'} NaN {'NA'} {'85'} NaN {'NA'} {'U.S. Department of State, United States Environmental Protection Agency.'} {'[[["EPA AirNow DOS","http://airnow.gov/index.cfm?action=airnow.global_summary"]]]'} {'9890427'} {'NA'} 13.088 80.278 1
LatLonT{end} % Last Table
ans = 4×20 table
who_region iso3 country_name city year version pm10_concentration pm25_concentration no2_concentration pm10_tempcov pm25_tempcov no2_tempcov type_of_stations reference web_link population population_source latitude longitude who_ms __________ _______ ________________ __________________ ____ ________________ ____________________ __________________ _________________ ____________ ____________ ___________ ________________ ___________________________________________________ ________ __________ _________________ ________ _________ ______ {'5_Emr'} {'SAU'} {'Saudi Arabia'} {'Al-Jouf region'} 2022 {'version 2023'} {'320.26' } {'NA'} NaN {'NA'} {'NA'} NaN {'NA'} {'Ministry of Environment, Water, and Agriculture'} {'NA'} {'32613'} {'NA'} 29.817 39.863 1 {'5_Emr'} {'SAU'} {'Saudi Arabia'} {'Al-Jouf region'} 2021 {'version 2023'} {'52.2101437914371'} {'NA'} NaN {'NA'} {'NA'} NaN {'NA'} {'Ministry of Environment, Water, and Agriculture'} {'NA'} {'32613'} {'NA'} 29.817 39.863 1 {'5_Emr'} {'SAU'} {'Saudi Arabia'} {'Al-Jouf region'} 2020 {'version 2023'} {'90' } {'NA'} NaN {'NA'} {'NA'} NaN {'NA'} {'Ministry of Environment, Water, and Agriculture'} {'NA'} {'32613'} {'NA'} 29.817 39.863 1 {'5_Emr'} {'SAU'} {'Saudi Arabia'} {'Al-Jouf region'} 2019 {'version 2023'} {'NA' } {'NA'} NaN {'NA'} {'NA'} NaN {'NA'} {'Ministry of Environment, Water, and Agriculture'} {'NA'} {'32613'} {'NA'} 29.817 39.863 1
The accumarray function in general makes life easier for these types of problems!
.

More Answers (0)

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!