MATLAB: What kind of join will work to merge these two tables

MATLAB

I have table T1

date city vistors
---------------------
January 1 2020 NY 100
January 2 2020 NY 200
January 1 2020 SF 150
January 4 2020 SF 50

I have table T2

city state
----------
NY New York
SF Calfiornia

I want Matlab to create T3

date city vistors state 
---------------------
January 1 2020 NY 100 New York
January 2 2020 NY 200 New York 
January 1 2020 SF 150 California
January 4 2020 SF 50  California

Best Answer

Try

T3 = join(T1,T2,'Keys','City')

Related Solutions

MATLAB: From discrete dates to continuous dates (revised)

Here's a sample timetable.

dt = datetime(2020, 1, [1; 5; 12]);
T = timetable(dt, [100; 40; 300], 'VariableNames', "visitors");
T.Properties.DimensionNames{1} = 'date'
T = 3x1 timetable
       date        visitors
    ___________    ________

    01-Jan-2020      100   
    05-Jan-2020       40   
    12-Jan-2020      300

retime it either filling rows not appearing in the original with missing (the default) or with the constant 0

T_fillWithMissing = retime(T, 'daily')
T_fillWithMissing = 12x1 timetable
       date        visitors
    ___________    ________

    01-Jan-2020      100   
    02-Jan-2020      NaN   
    03-Jan-2020      NaN   
    04-Jan-2020      NaN   
    05-Jan-2020       40   
    06-Jan-2020      NaN   
    07-Jan-2020      NaN   
    08-Jan-2020      NaN   
    09-Jan-2020      NaN   
    10-Jan-2020      NaN   
    11-Jan-2020      NaN   
    12-Jan-2020      300   
T_fillWith0 = retime(T, 'daily', 'fillwithconstant', 'Constant',0)
T_fillWith0 = 12x1 timetable
       date        visitors
    ___________    ________

    01-Jan-2020      100   
    02-Jan-2020        0   
    03-Jan-2020        0   
    04-Jan-2020        0   
    05-Jan-2020       40   
    06-Jan-2020        0   
    07-Jan-2020        0   
    08-Jan-2020        0   
    09-Jan-2020        0   
    10-Jan-2020        0   
    11-Jan-2020        0   
    12-Jan-2020      300

MATLAB: Time series for nutrient data

Try this:

T1 = readtable('emily bristow D352_CTD_NUTRIENTS.xlsx',  'VariableNamingRule','preserve'); 
T2 = T1(:,16);
DayLabels = T2{~ismissing(T2),:};
UniqueDayLabels = unique(DayLabels);
T2 = fillmissing(T2, 'previous', 'DataVariables',{('Data Collected')});
T1.('Data Collected') = T2;
DayGrps = findgroups(T1.('Data Collected'));
MeanNO2NO3 = splitapply(@nanmean,T1.('Nitrate + Nitrite (μM)'),DayGrps);
NutrientByDate = table(UniqueDayLabels, MeanNO2NO3, 'VariableNames',{'Data Collected','Mean Nitrate + Nitrite (μM)'})
DayNr = regexp(NutrientByDate{:,1},'\d*','match');
[~,NutrientByDateSortedIdx] = sort(str2double([DayNr{:}]));
NuO2NO3ByDateSorted = NutrientByDate(NutrientByDateSortedIdx,:)

producing:

NutrientByDate =
  15×2 table
    Data Collected    Mean Nitrate + Nitrite (μM)
    ______________    ___________________________
    {'10th June'}                6.4595          
    {'11th June'}                5.0186          
    {'12th June'}                6.1182          
    {'13th June'}                4.7663          
    {'16th June'}                4.2824          
    {'17th June'}                 4.079          
    {'19th June'}                 3.945          
    {'20th June'}                6.9626          
    {'3rd June' }               0.20307          
    {'4th June' }                4.2168          
    {'5th June' }                5.9074          
    {'6th June' }                6.3682          
    {'7th June' }                6.0618          
    {'8th June' }                6.3556          
    {'9th June' }                5.4644

Sorting them takes a bit more effort:

DayNr = regexp(NutrientByDate{:,1},'\d*','match');
[~,NutrientByDateSortedIdx] = sort(str2double([DayNr{:}]));
NutrientByDateSorted = NutrientByDate(NutrientByDateSortedIdx,:)

producing:

NutrientByDateSorted =
  15×2 table
    Data Collected    Mean Nitrate + Nitrite (μM)
    ______________    ___________________________
    {'3rd June' }               0.20307          
    {'4th June' }                4.2168          
    {'5th June' }                5.9074          
    {'6th June' }                6.3682          
    {'7th June' }                6.0618          
    {'8th June' }                6.3556          
    {'9th June' }                5.4644          
    {'10th June'}                6.4595          
    {'11th June'}                5.0186          
    {'12th June'}                6.1182          
    {'13th June'}                4.7663          
    {'16th June'}                4.2824          
    {'17th June'}                 4.079          
    {'19th June'}                 3.945          
    {'20th June'}                6.9626

Doing Phosphate separately:

T2 = T1(:,16);
DayLabels = T2{~ismissing(T2),:};
UniqueDayLabels = unique(DayLabels);
T2 = fillmissing(T2, 'previous', 'DataVariables',{('Data Collected')});
T1.('Data Collected') = T2;
DayGrps = findgroups(T1.('Data Collected'));
MeanPO4 = splitapply(@nanmean,T1.('phosphate  (μM)'),DayGrps);
PhosphateByDate = table(UniqueDayLabels, MeanPO4, 'VariableNames',{'Data Collected','Mean phosphate  (μM)'})
DayNr = regexp(PhosphateByDate{:,1},'\d*','match');
[DayNrSorted,PhospahteByDateSortedIdx] = sort(str2double([DayNr{:}]));
PhosphateByDateSorted = PhosphateByDate(PhospahteByDateSortedIdx,:)

produces:

NutrientByDateSorted =
  15×2 table
    Data Collected    Mean phosphate  (μM)
    ______________    ____________________
    {'3rd June' }           0.10776       
    {'4th June' }           0.36712       
    {'5th June' }               NaN       
    {'6th June' }            0.4977       
    {'7th June' }           0.50817       
    {'8th June' }           0.48635       
    {'9th June' }            0.4276       
    {'10th June'}           0.44522       
    {'11th June'}           0.36299       
    {'12th June'}           0.42828       
    {'13th June'}           0.35977       
    {'16th June'}           0.35638       
    {'17th June'}            0.3191       
    {'19th June'}            0.2708       
    {'20th June'}           0.45897

And putting it all together:

DateVar = compose('%2d June', DayNrSorted);
NO2No3PO4 = table(datetime(DateVar(:), 'InputFormat','dd MMMM', 'Format','dd MMMM'), NuO2NO3ByDateSorted{:,2}, PhosphateByDateSorted{:,2}, 'VariableNames',{'Data Collected','Nitrate + Nitrite (μM)','Mean phosphate  (μM)'})
VarNames = NO2No3PO4.Properties.VariableNames;
figure
plot(NO2No3PO4{:,1}, NO2No3PO4{:,2:3})
xlabel(VarNames{1})
ylabel('Concentration')
legend(VarNames{[2 3]}, 'Location','E')
grid
set(gca, 'XTickLabelRotation',60)

with the plot:

This is definitely not a trivial problem!

EDIT — (5 Dec 2020 at 20:08)

Added consolidated table ‘NO2No3PO4’ and plot figure.

Best Answer

Related Solutions

MATLAB: From discrete dates to continuous dates (revised)

MATLAB: Time series for nutrient data

Related Question