1. The problem statement, all variables and given/known data:
I have two files created from extracting data off of two CSV files, one containing class enrollment on a specific quarter and the other containing grades for that specific quarter. The Enrollment file generated contains course name, instructor and catalog number and a few other columns. The Grade file generated contains GPA and catalog number and a few other columns.
Both files have over 5k rows. I need to join the two files using the catalog number as the key field.
The Enrollment CSV file had some entries with Catalog No. missing, these were, in fact, subtotal entries and they were filtered out after the file was created.
2. Relevant commands, code, scripts, algorithms:
awk was used to generated the files using "|" as the field separator.
Example:
Course file
...
Catalog No. | ART 101 | instructor | Intro to Art
...
Grade file
...
Catalog No. | ART 101 | GPA
...
I need to use
join to create a merged file of the two based on the Catalog No.
Example:
Merge file
...
Catalog No. | ART 101 | Instructor | Intro to Art | GPA
...
The original CSV files have the courses grouped by department (i.e. ART, CRIM, ENGR, CS, etc.)
3. The attempts at a solution (include all code and scripts):
I've attempted two approaches, one where both files are sorted based on Catalog No. and one without sorting.
Every relevant join parameters were used in an attempt to merge the two files but each time, I get either an empty merged file or it just contains one or the other file in it.
join -t"|" -o'1.1 1.2 1.3 2.1 2.3' Courses.txt Grades.txt > Merged.txt
empty
join -t"|" -o'1.1 1.2 1.3 2.1 2.3' Courses.txt Grades.txt > Merged.txt
Gave me:
10000|GEOL|150|EARTH REVEALED|Professor||||
The |||| were where the GPA and other stuff from Grades are supposed to be.
join -t"|" -1 1 -2 1 Courses.txt Grades.txt > Merged.txt
empty
join -t"|" -a1 Courses.txt Grades.txt > Merged.txt
Just Courses entry, none from Grades. Same thing with -a2.
-j was also used, as well as.
Keep in mind I sorted the files so that the catalog numbers would match line to line.
All the examples I've seen regarding how join works has the key fields in both files in order and correspond almost line-to-line so this sort of suggest that join does not search the entire file for matches.
California State University, Los Angeles, California, USA. Albert Cervantes, CS 345
You guys got strange requirements. Gee, where's Los Angeles? In Canada?[COLOR="#738fbf"]