Left join produces extraneous rows
hjrnunes opened this issue · 1 comments
hjrnunes commented
Use these files to run the following code.
per-qid.csv.zip
per-val.csv.zip
(let [qids ["AGYSUB" "LOC" "AGELVL" "EDLVL"]
per-val (-> (tc/dataset "per-val.csv")
(vary-meta assoc :name "per-val"))
per-qid (-> (tc/dataset "per-qid.csv")
(vary-meta assoc :name "per-qid"))]
(println per-val)
(println per-qid)
(-> (tc/left-join per-val per-qid qids)
(tc/select-rows (fn [row]
(or (not= (get row "per-qid.AGYSUB") (get row "AGYSUB"))
(not= (get row "per-qid.LOC") (get row "LOC"))
(not= (get row "per-qid.AGELVL") (get row "AGELVL"))
(not= (get row "per-qid.EDLVL") (get row "EDLVL")))))
)
)This will print:
per-val [496552 6]:
| AGYSUB | LOC | AGELVL | EDLVL | SALLVL | per-val |
|--------------------------------------------------------------------------------------------|-------------------------|------------|---------------------------------------------------------------------|---------------------|--------:|
| VAAD-BOARD OF VETERANS APPEALS | 51-VIRGINIA | 55-59 | 17-MASTER'S DEGREE | $110,000 - $119,999 | 1 |
| TR93-INTERNAL REVENUE SERVICE | 47-TENNESSEE | 55-59 | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | $50,000 - $59,999 | 75 |
| DD07-DEFENSE LOGISTICS AGENCY | 15-HAWAII | 40-44 | 13-BACHELOR'S DEGREE | $100,000 - $109,999 | 1 |
| ARJA-JOINT ACTIVITIES | CO-COLOMBIA | 55-59 | 18-POST-MASTER'S | $100,000 - $109,999 | 1 |
| AF0M-HEADQUARTERS, AIR FORCE RESERVE COMMAND | 13-GEORGIA | 55-59 | 07-SOME COLLEGE - LESS THAN ONE YEAR | $80,000 - $89,999 | 2 |
| AF1L-AIR MOBILITY COMMAND | 37-NORTH CAROLINA | 50-54 | 17-MASTER'S DEGREE | $50,000 - $59,999 | 2 |
| HE36-FOOD AND DRUG ADMINISTRATION | 20-KANSAS | 40-44 | 13-BACHELOR'S DEGREE | $110,000 - $119,999 | 2 |
| TR93-INTERNAL REVENUE SERVICE | 37-NORTH CAROLINA | 40-44 | 17-MASTER'S DEGREE | $140,000 - $149,999 | 1 |
| VATA-VETERANS HEALTH ADMINISTRATION | 27-MINNESOTA | 50-54 | 21-DOCTORATE DEGREE | $140,000 - $149,999 | 2 |
| VABA-IMMEDIATE OFFICE OF THE ASSISTANT SECRETARY FOR HUMAN RESOURCES AND ADMINISTRATION | 11-DISTRICT OF COLUMBIA | 60-64 | 17-MASTER'S DEGREE | $130,000 - $139,999 | 1 |
| ... | ... | ... | ... | ... | ... |
| DN00-DEPARTMENT OF ENERGY | 08-COLORADO | 60-64 | 13-BACHELOR'S DEGREE | $90,000 - $99,999 | 1 |
| ARXA-UNITED STATES ARMY INSTALLATION MANAGEMENT COMMAND | 24-MARYLAND | 65 or more | 12-FOUR YEARS COLLEGE | $100,000 - $109,999 | 1 |
| DD12-DEFENSE COUNTERINTELLIGENCE AND SECURITY AGENCY | 24-MARYLAND | 50-54 | 17-MASTER'S DEGREE | $130,000 - $139,999 | 1 |
| ARXP-U.S. ARMY SECURITY ASSISTANCE COMMAND | 42-PENNSYLVANIA | 30-34 | 17-MASTER'S DEGREE | $70,000 - $79,999 | 1 |
| AF0M-HEADQUARTERS, AIR FORCE RESERVE COMMAND | 32-NEVADA | 50-54 | 01-NO FORMAL EDUCATION OR SOME ELEMENTARY SCHOOL - DID NOT COMPLETE | $60,000 - $69,999 | 1 |
| VADG-EXECUTIVE DIRECTOR, OFFICE OF ACQUISITION AND LOGISTICS | 11-DISTRICT OF COLUMBIA | 55-59 | 13-BACHELOR'S DEGREE | $120,000 - $129,999 | 1 |
| SM03-SMITHSONIAN INSTITUTION (EXCEPT UNITS ADMINISTERED UNDER SEPARATE BOARDS OF TRUSTEES) | 11-DISTRICT OF COLUMBIA | 65 or more | 08-ONE YEAR COLLEGE | $130,000 - $139,999 | 1 |
| AF0J-AIR EDUCATION AND TRAINING COMMAND | 04-ARIZONA | 50-54 | 17-MASTER'S DEGREE | $50,000 - $59,999 | 3 |
| DJ03-BUREAU OF PRISONS/FEDERAL PRISON SYSTEM | 11-DISTRICT OF COLUMBIA | 50-54 | 14-POST-BACHELOR'S | $170,000 - $179,999 | 2 |
| AFGS-AIR FORCE GLOBAL STRIKE COMMAND | 38-NORTH DAKOTA | 25-29 | 17-MASTER'S DEGREE | $60,000 - $69,999 | 2 |
| TD17-FEDERAL MOTOR CARRIER SAFETY ADMINISTRATION | 48-TEXAS | 50-54 | 08-ONE YEAR COLLEGE | $80,000 - $89,999 | 1 |
per-qid [195875 7]:
| AGYSUB | LOC | AGELVL | EDLVL | fj | [:l "SALLVL"] | per-eqc |
|-----------------------------------------------------------------|-------------------------|------------|-------------------------------------------------------------------------------------|----:|--------------:|--------:|
| NV27-U.S. MARINE CORPS | 15-HAWAII | 55-59 | 08-ONE YEAR COLLEGE | 3 | 3.0 | 3 |
| HE36-FOOD AND DRUG ADMINISTRATION | 37-NORTH CAROLINA | 40-44 | 17-MASTER'S DEGREE | 3 | 3.0 | 3 |
| DD35-DEFENSE FINANCE AND ACCOUNTING SERVICE | 12-FLORIDA | 40-44 | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | 1 | 1.0 | 1 |
| IN05-BUREAU OF LAND MANAGEMENT | 41-OREGON | 35-39 | 16-POST-FIRST PROFESSIONAL | 1 | 1.0 | 1 |
| VAEB-DEPUTY ASSISTANT SECRETARY FOR INFORMATION AND TECHNOLOGY | **-UNSPECIFIED | 50-54 | 01-NO FORMAL EDUCATION OR SOME ELEMENTARY SCHOOL - DID NOT COMPLETE | 1 | 1.0 | 1 |
| DD35-DEFENSE FINANCE AND ACCOUNTING SERVICE | 18-INDIANA | 35-39 | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | 1 | 1.0 | 1 |
| TR93-INTERNAL REVENUE SERVICE | 49-UTAH | 40-44 | 05-TERMINAL OCCUPATIONAL PROGRAM - DID NOT COMPLETE | 1 | 1.0 | 1 |
| AG16-NATURAL RESOURCES CONSERVATION SERVICE | 05-ARKANSAS | 30-34 | 11-THREE YEARS COLLEGE | 7 | 3.9 | 7 |
| AG22-NATIONAL INSTITUTE OF FOOD AND AGRICULTURE | 04-ARIZONA | 65 or more | 21-DOCTORATE DEGREE | 1 | 1.0 | 1 |
| ARSA-OFFICE OF THE SECRETARY OF THE ARMY | 11-DISTRICT OF COLUMBIA | 40-44 | 20-POST-SIXTH YEAR | 1 | 1.0 | 1 |
| ... | ... | ... | ... | ... | ... | ... |
| CM61-NATIONAL TELECOMMUNICATIONS AND INFORMATION ADMINISTRATION | 11-DISTRICT OF COLUMBIA | 25-29 | 14-POST-BACHELOR'S | 3 | 1.9 | 3 |
| AF4G-UNITED STATES SPACE FORCE | 35-NEW MEXICO | 65 or more | 07-SOME COLLEGE - LESS THAN ONE YEAR | 1 | 1.0 | 1 |
| VATA-VETERANS HEALTH ADMINISTRATION | 51-VIRGINIA | 45-49 | 10-ASSOCIATE DEGREE | 72 | 6.5 | 72 |
| EP00-ENVIRONMENTAL PROTECTION AGENCY | 11-DISTRICT OF COLUMBIA | 35-39 | 10-ASSOCIATE DEGREE | 3 | 3.0 | 3 |
| NV27-U.S. MARINE CORPS | 13-GEORGIA | 30-34 | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | 17 | 4.7 | 17 |
| AR2A-UNITED STATES ARMY CYBER COMMAND | 02-ALASKA | 40-44 | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | 4 | 4.0 | 4 |
| NV76-NAVAL EDUCATION AND TRAINING COMMAND | 23-MAINE | 20-24 | 17-MASTER'S DEGREE | 1 | 1.0 | 1 |
| TR95-OFFICE OF INSPECTOR GENERAL | 11-DISTRICT OF COLUMBIA | 50-54 | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | 1 | 1.0 | 1 |
| ARXA-UNITED STATES ARMY INSTALLATION MANAGEMENT COMMAND | KS-KOREA, REPUBLIC OF | 50-54 | 13-BACHELOR'S DEGREE | 14 | 6.5 | 14 |
| ARXG-UNITED STATES ARMY PRINT AND MEDIA DISTRIBUTION CENTER | 29-MISSOURI | 55-59 | 08-ONE YEAR COLLEGE | 1 | 1.0 | 1 |
| AF1M-AIR FORCE MATERIEL COMMAND | 02-ALASKA | 45-49 | 17-MASTER'S DEGREE | 2 | 2.0 | 2 |
=> left-outer-join [20 13]:
| per-qid.AGYSUB | AGYSUB | per-qid.LOC | LOC | per-qid.AGELVL | AGELVL | per-qid.EDLVL | EDLVL | SALLVL | per-val | fj | [:l "SALLVL"] | per-eqc |
|--------------------------------------------------------------------------|--------------------------------------------------------------------------|-------------------------|-------------------------|----------------|------------|-------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------|---------------------|--------:|---:|--------------:|--------:|
| AG07-RURAL DEVELOPMENT | DLAM-OFFICE OF THE ASSISTANT SECRETARY FOR ADMINISTRATION AND MANAGEMENT | 30-MONTANA | 42-PENNSYLVANIA | 50-54 | 60-64 | 10-ASSOCIATE DEGREE | 09-TWO YEARS COLLEGE | $100,000 - $109,999 | 1 | 1 | 1.0 | 1 |
| DLAM-OFFICE OF THE ASSISTANT SECRETARY FOR ADMINISTRATION AND MANAGEMENT | AG07-RURAL DEVELOPMENT | 42-PENNSYLVANIA | 30-MONTANA | 60-64 | 50-54 | 09-TWO YEARS COLLEGE | 10-ASSOCIATE DEGREE | $90,000 - $99,999 | 1 | 1 | 1.0 | 1 |
| ARMW-U.S. ARMY MILITARY DISTRICT OF WASHINGTON | VATA-VETERANS HEALTH ADMINISTRATION | 11-DISTRICT OF COLUMBIA | 29-MISSOURI | 45-49 | 60-64 | 17-MASTER'S DEGREE | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | $30,000 - $39,999 | 3 | 5 | 3.8 | 5 |
| ARMW-U.S. ARMY MILITARY DISTRICT OF WASHINGTON | VATA-VETERANS HEALTH ADMINISTRATION | 11-DISTRICT OF COLUMBIA | 29-MISSOURI | 45-49 | 60-64 | 17-MASTER'S DEGREE | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | $60,000 - $69,999 | 1 | 5 | 3.8 | 5 |
| ARMW-U.S. ARMY MILITARY DISTRICT OF WASHINGTON | VATA-VETERANS HEALTH ADMINISTRATION | 11-DISTRICT OF COLUMBIA | 29-MISSOURI | 45-49 | 60-64 | 17-MASTER'S DEGREE | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | $40,000 - $49,999 | 4 | 5 | 3.8 | 5 |
| ARMW-U.S. ARMY MILITARY DISTRICT OF WASHINGTON | VATA-VETERANS HEALTH ADMINISTRATION | 11-DISTRICT OF COLUMBIA | 29-MISSOURI | 45-49 | 60-64 | 17-MASTER'S DEGREE | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | $80,000 - $89,999 | 1 | 5 | 3.8 | 5 |
| ARMW-U.S. ARMY MILITARY DISTRICT OF WASHINGTON | VATA-VETERANS HEALTH ADMINISTRATION | 11-DISTRICT OF COLUMBIA | 29-MISSOURI | 45-49 | 60-64 | 17-MASTER'S DEGREE | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | $50,000 - $59,999 | 10 | 5 | 3.8 | 5 |
| VATA-VETERANS HEALTH ADMINISTRATION | ARMW-U.S. ARMY MILITARY DISTRICT OF WASHINGTON | 29-MISSOURI | 11-DISTRICT OF COLUMBIA | 60-64 | 45-49 | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | 17-MASTER'S DEGREE | $110,000 - $119,999 | 2 | 19 | 3.6 | 19 |
| VATA-VETERANS HEALTH ADMINISTRATION | ARMW-U.S. ARMY MILITARY DISTRICT OF WASHINGTON | 29-MISSOURI | 11-DISTRICT OF COLUMBIA | 60-64 | 45-49 | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | 17-MASTER'S DEGREE | $120,000 - $129,999 | 1 | 19 | 3.6 | 19 |
| VATA-VETERANS HEALTH ADMINISTRATION | ARMW-U.S. ARMY MILITARY DISTRICT OF WASHINGTON | 29-MISSOURI | 11-DISTRICT OF COLUMBIA | 60-64 | 45-49 | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | 17-MASTER'S DEGREE | $80,000 - $89,999 | 1 | 19 | 3.6 | 19 |
| VATA-VETERANS HEALTH ADMINISTRATION | ARMW-U.S. ARMY MILITARY DISTRICT OF WASHINGTON | 29-MISSOURI | 11-DISTRICT OF COLUMBIA | 60-64 | 45-49 | 06-TERMINAL OCCUPATIONAL PROGRAM - CERTIFICATE OF COMPLETION, DIPLOMA OR EQUIVALENT | 17-MASTER'S DEGREE | $90,000 - $99,999 | 1 | 19 | 3.6 | 19 |
| ARXA-UNITED STATES ARMY INSTALLATION MANAGEMENT COMMAND | AG03-AGRICULTURAL RESEARCH SERVICE | 49-UTAH | 37-NORTH CAROLINA | 55-59 | 65 or more | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | 13-BACHELOR'S DEGREE | $80,000 - $89,999 | 1 | 7 | 3.6 | 7 |
| ARXA-UNITED STATES ARMY INSTALLATION MANAGEMENT COMMAND | AG03-AGRICULTURAL RESEARCH SERVICE | 49-UTAH | 37-NORTH CAROLINA | 55-59 | 65 or more | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | 13-BACHELOR'S DEGREE | $70,000 - $79,999 | 1 | 7 | 3.6 | 7 |
| AG03-AGRICULTURAL RESEARCH SERVICE | ARXA-UNITED STATES ARMY INSTALLATION MANAGEMENT COMMAND | 37-NORTH CAROLINA | 49-UTAH | 65 or more | 55-59 | 13-BACHELOR'S DEGREE | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | $50,000 - $59,999 | 2 | 2 | 2.0 | 2 |
| AG03-AGRICULTURAL RESEARCH SERVICE | ARXA-UNITED STATES ARMY INSTALLATION MANAGEMENT COMMAND | 37-NORTH CAROLINA | 49-UTAH | 65 or more | 55-59 | 13-BACHELOR'S DEGREE | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | $60,000 - $69,999 | 1 | 2 | 2.0 | 2 |
| AG03-AGRICULTURAL RESEARCH SERVICE | ARXA-UNITED STATES ARMY INSTALLATION MANAGEMENT COMMAND | 37-NORTH CAROLINA | 49-UTAH | 65 or more | 55-59 | 13-BACHELOR'S DEGREE | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | $30,000 - $39,999 | 1 | 2 | 2.0 | 2 |
| AG03-AGRICULTURAL RESEARCH SERVICE | ARXA-UNITED STATES ARMY INSTALLATION MANAGEMENT COMMAND | 37-NORTH CAROLINA | 49-UTAH | 65 or more | 55-59 | 13-BACHELOR'S DEGREE | 04-HIGH SCHOOL GRADUATE OR CERTIFICATE OF EQUIVALENCY | $40,000 - $49,999 | 3 | 2 | 2.0 | 2 |
| ARXQ-U.S. ARMY JOINT MUNITIONS COMMAND | DN00-DEPARTMENT OF ENERGY | 21-KENTUCKY | 24-MARYLAND | 30-34 | 60-64 | 12-FOUR YEARS COLLEGE | 22-POST-DOCTORATE | $170,000 - $179,999 | 4 | 1 | 1.0 | 1 |
| ARXQ-U.S. ARMY JOINT MUNITIONS COMMAND | DN00-DEPARTMENT OF ENERGY | 21-KENTUCKY | 24-MARYLAND | 30-34 | 60-64 | 12-FOUR YEARS COLLEGE | 22-POST-DOCTORATE | $160,000 - $169,999 | 1 | 1 | 1.0 | 1 |
| DN00-DEPARTMENT OF ENERGY | ARXQ-U.S. ARMY JOINT MUNITIONS COMMAND | 24-MARYLAND | 21-KENTUCKY | 60-64 | 30-34 | 22-POST-DOCTORATE | 12-FOUR YEARS COLLEGE | $60,000 - $69,999 | 1 | 5 | 1.6 | 5 |
Last table should be empty.
genmeblog commented
Join on multiple columns creates artificial index column based on hashes and looks like hashes are the same for these cases.