Unable to use inner join function

조회 수: 5(최근 30일)
Dion Theunissen
Dion Theunissen 2022년 8월 10일
편집: Stephen23 2022년 8월 10일
I try to join 2 tables together based on an common ID.
When i use the innerjoin function I do not get an error but just a new table without rows.
%% import trips
opts = delimitedTextImportOptions("NumVariables", 28);
% Specify range and delimiter
opts.DataLines = [2, Inf];
opts.Delimiter = ",";
% Specify column names and types
opts.VariableNames = ["id", "country_id", "location_id", "age_group_id", "trip_name", "seo_url", "short_details", "is_featured", "trip_letter", "show_trip_letter", "trip_type", "trip_main_image", "trip_thumb_image", "start_date", "duration", "trip_fee", "trip_discount", "original_fee", "trip_seats_status", "marketing_text", "is_not_bookable", "meta_title", "meta_description", "meta_keyword", "archive", "is_full", "status", "ts"];
opts.VariableTypes = ["double", "double", "double", "double", "categorical", "string", "string", "double", "categorical", "double", "categorical", "string", "string", "datetime", "double", "double", "double", "double", "double", "string", "double", "string", "string", "string", "double", "double", "double", "datetime"];
% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
% Specify variable properties
opts = setvaropts(opts, ["seo_url", "short_details", "trip_main_image", "trip_thumb_image", "marketing_text", "meta_title", "meta_description", "meta_keyword"], "WhitespaceRule", "preserve");
opts = setvaropts(opts, ["trip_name", "seo_url", "short_details", "trip_letter", "trip_type", "trip_main_image", "trip_thumb_image", "marketing_text", "meta_title", "meta_description", "meta_keyword"], "EmptyFieldRule", "auto");
opts = setvaropts(opts, "start_date", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "ts", "InputFormat", "yyyy-MM-dd HH:mm:ss");
% Import the data
trips = readtable("/Users/simireizen/Documents/MATLAB/trips.csv", opts);
%% Import Bookings
opts = delimitedTextImportOptions("NumVariables", 130);
% Specify range and delimiter
opts.DataLines = [2, Inf];
opts.Delimiter = ",";
% Specify column names and types
opts.VariableNames = ["VarName1", "id", "VarName3", "trip_id", "VarName5", "client_id", "VarName7", "travel_agent_id", "VarName9", "travel_brand_id", "VarName11", "child_firstname", "VarName13", "child_lastname", "VarName15", "gender", "VarName17", "child_dob", "VarName19", "parent_name", "VarName21", "parent_email", "VarName23", "email", "VarName25", "address", "VarName27", "house_number", "VarName29", "city", "VarName31", "postcode", "VarName33", "telephone", "VarName35", "cellphone", "VarName37", "whatsapp_number", "VarName39", "location_pickup_id", "VarName41", "child_diet", "VarName43", "child_medication", "VarName45", "about_child", "VarName47", "date_added", "VarName49", "can_drive", "VarName51", "have_driving_license", "VarName53", "have_creditcard", "VarName55", "trip_fee", "VarName57", "discount_id", "VarName59", "discount_amount", "VarName61", "insurance", "VarName63", "cancellation_insurance", "VarName65", "travel_insurance", "VarName67", "cancellation_policy_number", "VarName69", "travel_policy_number", "VarName71", "survival_adventure_insurance", "VarName73", "insurance_admin_charges", "VarName75", "nature_disaster_insurance", "VarName77", "sgr_contribution", "VarName79", "insurnace_question_1", "VarName81", "insurnace_question_2", "VarName83", "total_amount", "VarName85", "paid_amount", "VarName87", "deleted", "VarName89", "payment_reminder_email_sent", "VarName91", "total_reminder_sent", "VarName93", "email_sent", "VarName95", "login_reminder_email_sent", "VarName97", "upsell_email_sent", "VarName99", "deposit_reminder_email_sent", "VarName101", "passport_reminder_email_sent", "VarName103", "display_name", "VarName105", "additional_address", "VarName107", "contact_person_name", "VarName109", "contact_person_extra_name", "VarName111", "contact_person_extra_cellphone", "VarName113", "travel_agent_email", "VarName115", "commission", "VarName117", "covid_option", "VarName119", "account_name", "VarName121", "account_number", "VarName123", "phone_reminder_email_sent", "VarName125", "status", "VarName127", "ts", "VarName129", "trip_date_before_42"];
opts.VariableTypes = ["double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "string", "string", "string", "string", "categorical", "categorical", "datetime", "datetime", "double", "string", "double", "string", "string", "string", "string", "string", "double", "double", "string", "string", "string", "string", "double", "double", "double", "double", "double", "double", "double", "double", "categorical", "categorical", "string", "string", "string", "string", "datetime", "datetime", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "categorical", "categorical", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "string", "string", "double", "string", "string", "string", "string", "string", "double", "double", "double", "string", "double", "double", "double", "double", "string", "string", "string", "string", "double", "double", "double", "double", "datetime", "datetime", "datetime", "datetime"];
% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
% Specify variable properties
opts = setvaropts(opts, ["VarName11", "child_firstname", "VarName13", "child_lastname", "parent_name", "parent_email", "VarName23", "email", "VarName25", "address", "VarName29", "city", "VarName31", "postcode", "VarName43", "child_medication", "VarName45", "about_child", "VarName103", "display_name", "additional_address", "VarName107", "contact_person_name", "VarName109", "contact_person_extra_name", "travel_agent_email", "VarName119", "account_name", "VarName121", "account_number"], "WhitespaceRule", "preserve");
opts = setvaropts(opts, ["VarName11", "child_firstname", "VarName13", "child_lastname", "VarName15", "gender", "parent_name", "parent_email", "VarName23", "email", "VarName25", "address", "VarName29", "city", "VarName31", "postcode", "VarName41", "child_diet", "VarName43", "child_medication", "VarName45", "about_child", "VarName61", "insurance", "VarName103", "display_name", "additional_address", "VarName107", "contact_person_name", "VarName109", "contact_person_extra_name", "travel_agent_email", "VarName119", "account_name", "VarName121", "account_number"], "EmptyFieldRule", "auto");
opts = setvaropts(opts, "VarName17", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "child_dob", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "VarName47", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "date_added", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "VarName127", "InputFormat", "yyyy-MM-dd HH:mm:ss");
opts = setvaropts(opts, "ts", "InputFormat", "yyyy-MM-dd HH:mm:ss");
opts = setvaropts(opts, "VarName129", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "trip_date_before_42", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, ["insurnace_question_1", "insurnace_question_2", "VarName105"], "TrimNonNumeric", true);
opts = setvaropts(opts, ["insurnace_question_1", "insurnace_question_2", "VarName105"], "ThousandsSeparator", ",");
% Import the data
bookings = readtable("/Users/simireizen/Documents/MATLAB/SimiScript/deleted_bookings.csv", opts);
%% join tables
T = innerjoin(bookings,trips);

채택된 답변

Stephen23
Stephen23 2022년 8월 10일
편집: Stephen23 2022년 8월 10일
Because the two tables use different names for the key variable, you will have to help INNERJOIN by telling it the names of the variables/columns that you want to use for the keys. For example:
T = innerjoin(bookings,trips,'LeftKeys','trip_id', 'RightKeys','id');

추가 답변(0개)

제품


릴리스

R2022a

Community Treasure Hunt

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

Start Hunting!

Translated by