Difference between revisions of "Blitvak Week 6"

Revision as of 03:25, 15 October 2015

Individual Journal Assignment Week 6

Downloading and Decompressing Data Files, Other Assignment Preparation

• Looking over the Week 6 Assignment Page, I found that curl -O http://www.fda.gov/downloads/Drugs/InformationOnDrugs/UCM054599.zip can be used, while I am in the /nfs/home/blitvak directory, to place a .zip containing the data files directly into my personal folder. I found that unzip UCM054599.zip unzips the files into the personal folder.
• I also downloaded and installed pgAdmin III from http://www.pgadmin.org/
• I spent some time reviewing the PostgreSQL Tutorial
• I booted up PuTTy and unzipped the files into my home folder

Working with application.txt

Opening and Reviewing the File

• I opened up and reviewed the application.txt and Product.txt files using more <filename.txt>; I found that the data column labels for application.txt are:

ApplNo  ApplType  SponsorApplicant  MostRecentLabelAvailableFlag  CurrentPatentFlag  ActionType  Chemical_Type  Ther_Potential  Orphan_Code

• Reviewing the actual data, and with PostgreSQL in mind, I found that the variable type for each column should be:

ApplNo: int(primary key)  ApplType: varchar  SponsorApplicant: varchar  MostRecentLabelAvailableFlag: boolean  CurrentPatentFlag: boolean  ActionType: varchar  Chemical_Type: int 
Ther_Potential: varchar  Orphan_Code: varchar          

• I realized that any empty data spaces in application.txt will have to be turned into null
• I realized that sed "1D" will have to be executed in order to remove the first row (which is column labeling)
• Referencing the Week 6 Assignment Page, I learned that the data within these text files is separated by tabs (\t) instead of commas

Modifying application.txt

• I opened the file and decided to try to turn the tabs into commas using cat application.txt | sed "s/\t/,/g"
  • Adding onto that, I decided to get rid of the other spaces between the data by using sed -e "s/\s\{4,\}//g", which matches 4 whitespaces and removes them. This command was found in a StackExchange post.
• At this point, I noticed that many lines had extra commas either at the ends of the lines or in the middle, indicating missing or nonexistent values
  • I used sed "s/,,,\r$/,null,null,null/;s/,,\r$/,null,null/;s/,\r$/,null/1" | sed "s/,,/,null/;s/,,,/,null,null/g", along with what I already have, in order to turn any extra commas (missing values) into null
• My focus now turned to making sure that all data that was identified as being varchar has apostrophes wrapping around it.
  • Working with the ApplType data and my previous work, I used sed "s/......,/&'/1" | sed "s/'./&'/1" to surround it with apostrophes.
  • Working with the SponsorApplicant data, I added sed "s/',/&'/g" | sed "s/,./'&/3" to wrap apostrophes around the third data type in each line
  • I used sed "s/,/&'/5" | sed "s/'../&'/4", to place apostrophes around the two character data under ActionType
• Using grep "nullP", grep "nullS", and grep "nullV" with the current pipeline of commands:
  • cat application.txt | sed "1D" | sed "s/\t/,/g" | sed -e "s/\s\{4,\}//g" | sed "s/,,,\r$/,null,null,null/;s/,,\r$/,null,null/;s/,\r$/,null/1" | sed "s/,,/,null/;s/,,,/,null,null/g" | sed "s/......,/&'/1" | sed "s/'./&'/1" | sed "s/',/&'/g" | sed "s/,./'&/3" | sed "s/,/&'/5" | sed "s/'../&'/4"
  • I noticed that some of the null values are not separated from adjacent data with commas; I added sed "s/nullS/null,S/g" | sed "s/nullV/null,V/g" | sed "s/nullP/null,P/g" to fix this issue and I checked the result using grep
  • With the Ther_Potential data being now completely separated by commas from the other data, I then proceeded to try to surround it with apostrophes. I first added sed "s/,'..',.,/&'/g" to add the first apostrophe, and I noticed that this command led some null values to gain an apostrophe. I added sed "s/'null/null/g"/<code> to clean them up. I later noticed that some Ther_potential values have asterisks tied to them, I used <code>grep "P\*" and grep "S\*" to confirm the presence of asterisks. Finally, I added sed "s/'S,null/'S',null/g" | sed "s/'P,null/'P',null/g" | sed "s/'S,V/'S',V/g" | sed "s/'P,V/'P',V/g" | sed "s/'P\*/'P\*'/g" | sed "s/'S\*/'S\*'/g" to the pipeline to fully surround the Ther_Potential values.
  • I surrounded the Orphan_Code variable with apostrophes by adding sed "s/,V/,'V'/g" to the pipeline (Orphan_Code is often null but when it is present, it is always a V)
• I finished formatting the file by adding sed "s/^/insert into applications(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | rev | sed -r "s/llun|'V'/;)&/1" | rev to the pipeline. I decided to reverse the file in this set of commands because I could not get sed to add ); to the very end of each line; I decided upon creating a multiple choice sed command that worked on the very first match of a line.

Generating the application.sql.txt file

• I generated the application.sql.txt file using cat application.txt | sed "1D" | sed "s/\t/,/g" | sed -e "s/\s\{4,\}//g" | sed "s/,,,\r$/,null,null,null/;s/,,\r$/,null,null/;s/,\r$/,null/1" | sed "s/,,/,null/;s/,,,/,null,null/g" | sed "s/......,/&'/1" | sed "s/'./&'/1" | sed "s/',/&'/g" | sed "s/,./'&/3" | sed "s/,/&'/5" | sed "s/'../&'/4" | sed "s/nullS/null,S/g" | sed "s/nullV/null,V/g" | sed "s/nullP/null,P/g" | sed "s/,'..',.,/&'/g" | sed "s/'null/null/g" | sed "s/'S,null/'S',null/g" | sed "s/'P,null/'P',null/g" | sed "s/'S,V/'S',V/g" | sed "s/'P,V/'P',V/g" | sed "s/'P\*/'P\*'/g" | sed "s/'S\*/'S\*'/g" | sed "s/,V/,'V'/g" | sed "s/^/insert into applications(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | rev | sed -r "s/llun|'V'/;)&/1" | rev > ~/public_html/application.sql.txt
• I proceeded to input http://my.cs.lmu.edu/~blitvak/application.sql.txt into my browser and I copied all of the data

Testing application.sql.txt in pgAdmin III

• I created a table in postgreSQL using create table applications (ApplNo int primary key, ApplType varchar, SponsorApplicant varchar, MostRecentLabelAvailableFlag boolean, CurrentPatentFlag boolean, ActionType varchar, Chemical_Type int, Ther_Potential varchar, Orphan_Code varchar)
• I pasted the data into the program and noticed these types of errors:

1. Type 1

ERROR:  syntax error at or near "lse"
LINE 17882: ... values(125422,'B','THROMBOGENICS', INC,False,'Fa'lse,AP,nul...

2. Type 2

ERROR:  column "s" does not exist
LINE 161: ...e) values(008319,'N','NOVARTIS',False,False,'AP',14,S,null);
ERROR:  column "s" does not exist
LINE 360: ...10721,'N','CITRON PHARMA LLC',False,False,'AP',null,S,null);
ERROR:  column "p" does not exist
LINE 617: ...de) values(013025,'N','HOSPIRA',False,False,'AP',14,P,null);

• The Type 1 Error, I noticed was linked to the presence of commas in the original raw data with respect to the many company names, should be fixed by initially replacing the commas with a character that does not exist in the data, such as ~; sed "s/,/~/g" was added near the beginning of the pipe, and sed "s/~/,/g" was added at the end of the pipe.
• The Type 2 Error seems to be tied to the presence of a double digit Chemical_Type; it might be remedied by adding sed "s/,S,/,'S',/;s/,P,/,'P',/;s/,S\*,/,'S\*',/;s/,P\*,/,'P\*',/g" near the end of the pipe.
  • The modified pipe to be tested again is:

cat application.txt | sed "s/,/~/g" | sed "1D" | sed "s/\t/,/g" | sed -e "s/\s\{4,\}//g" | sed "s/,,,\r$/,null,null,null/;s/,,\r$/,null,null/;s/,\r$/,null/1" | sed "s/,,/,null/;s/,,,/,null,null/g" | sed "s/......,/&'/1" | sed "s/'./&'/1" | sed "s/',/&'/g" | sed "s/,./'&/3" | sed "s/,/&'/5" | sed "s/'../&'/4" | sed "s/nullS/null,S/g" | sed "s/nullV/null,V/g" | sed "s/nullP/null,P/g" | sed "s/,'..',.,/&'/g" | sed "s/'null/null/g" | sed "s/'S,null/'S',null/g" | sed "s/'P,null/'P',null/g" | sed "s/'S,V/'S',V/g" | sed "s/'P,V/'P',V/g" | sed "s/'P\*/'P\*'/g" | sed "s/'S\*/'S\*'/g" | sed "s/,V/,'V'/g" | sed "s/^/insert into applications(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | rev | sed -r "s/llun|'V'/;)&/1" | rev | sed "s/,S,/,'S',/;s/,P,/,'P',/;s/,S\*,/,'S\*',/;s/,P\*,/,'P\*',/g" | sed "s/~/,/g"

• This new pipeline revealed another error that I did not notice earlier (but resulted in the fixing of the previous errors):

ERROR:  INSERT has more target columns than expressions
LINE 4864: ...atentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Cod...
insert into applications(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(125057,'B','ABBVIE INC',False,False,'AP',null,'V');

• It seems that every line with a null instead of Chemical_Type value, prior to an Orphan_Code, is missing a null value (common pattern is 'AP',null,'V')
  • Inserting sed "s/'AP',null,'V'/'AP',null,null,'V'/g" near the end of the pipe might correct this issue.
• The fix worked and the final pipe resulted in a successful table creation. The final pipeline of commands is:

cat application.txt | sed "s/,/~/g" | sed "1D" | sed "s/\t/,/g" | sed -e "s/\s\{4,\}//g" | sed "s/,,,\r$/,null,null,null/;s/,,\r$/,null,null/;s/,\r$/,null/1" | sed "s/,,/,null/;s/,,,/,null,null/g" | sed "s/......,/&'/1" | sed "s/'./&'/1" | sed "s/',/&'/g" | sed "s/,./'&/3" | sed "s/,/&'/5" | sed "s/'../&'/4" | sed "s/nullS/null,S/g" | sed "s/nullV/null,V/g" | sed "s/nullP/null,P/g" | sed "s/,'..',.,/&'/g" | sed "s/'null/null/g" | sed "s/'S,null/'S',null/g" | sed "s/'P,null/'P',null/g" | sed "s/'S,V/'S',V/g" | sed "s/'P,V/'P',V/g" | sed "s/'P\*/'P\*'/g" | sed "s/'S\*/'S\*'/g" | sed "s/,V/,'V'/g" | sed "s/^/insert into applications(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | rev | sed -r "s/llun|'V'/;)&/1" | rev | sed "s/,S,/,'S',/;s/,P,/,'P',/;s/,S\*,/,'S\*',/;s/,P\*,/,'P\*',/g" | sed "s/~/,/g" | sed "s/'AP',null,'V'/'AP',null,null,'V'/g" > ~/public_html/application.sql.txt

• I replaced the previously made application.sql.txt with the output of the final pipeline: Output

Working with Product.txt

Opening and Reviewing Product.txt

• Product.txt was examined using more Product.txt and the column labels and variable type were found to be:

ApplNo(int)    ProductNo(int)    Form(varchar)    Dosage(varchar)    ProductMktStatus(int)    TECode(varchar)    ReferenceDrug(int)    drugname(varchar)    activeingred(varchar)

Modifying Product.txt

• I removed the first row (column labels) and replaced all of the tabs with commas; I also turned the extra commas into null values: cat Product.txt | sed "1D" | sed "s/\t/,/g" | sed "s/,,/,null,/g"
• I began placing the apostrophes around the varchar data with the Form, Dosage, and TECode data. I placed apostrophes around this data by adding several sed replacements to the pipeline made earlier: sed "s/,/&'/2" | sed "s/,/'&/3" | sed "s/,/&'/3" | sed "s/,/'&/4" | sed "s/,/&'/5" | sed "s/,/'&/6"
• I noticed that there are a few commas and apostrophes in the raw data, I decided to initially replace the commas with characters that are not present elsewhere in the raw data (such as~); sed "s/,/~/g" was added to the beginning of the command chain to initially replace them, and sed "s/~/,/g" was added to the end of chain in order to restore them. The presence of apostrophes in the data was fixed by through adding sed "s/'//g"<code> to the beginning of the pipe (this addition allows pgSQL to understand that these apostrophes are a part of the actual data.
• I placed apostrophes around all remaining varchar data by adding <code>sed "s/,/&'/7" | sed "s/,/'&/8" | sed "s/,/&'/8" | sed "s/\r$/'/g" to the pipeline
• To the end of the current pipeline, I added sed "s/^/insert into products(ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TECode,ReferenceDrug,drugname,activeingred) values(/g" | sed "s/$/);/g" to wrap up the formatting of the raw data (should now be usable as sequences of SQL insert statements)
• I used the final pipeline of commands: cat Product.txt | sed "1D" | sed "s/,/~/g" | sed "s/'//g" | sed "s/\t/,/g" | sed "s/,,/,null,/g" | sed "s/,/&'/2" | sed "s/,/'&/3" | sed "s/,/&'/3" | sed "s/,/'&/4" | sed "s/,/&'/5" | sed "s/,/'&/6" | sed "s/,/&'/7" | sed "s/,/'&/8" | sed "s/,/&'/8" | sed "s/\r$/'/g" | sed "s/^/insert into products(ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TECode,ReferenceDrug,drugname,activeingred) values(/g" | sed "s/~/,/g" | sed "s/$/);/g" > ~/public_html/Product.sql.txt to generate Product.sql.txt

Testing Products.sql.txt in pgAdmin III

• The table for the data enclosed in Products.sql.txt was produced using create table products (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus int, TECode varchar, ReferenceDrug int, drugname varchar, activeingred varchar)). The contents of this file was checked in pgAdmin III/pgSQL with no issues.

Review/Questions to Answer

1. Provide the DDL (create table) statements that you used for your application and product tables.
   • For the applications table: create table applications (ApplNo int primary key, ApplType varchar, SponsorApplicant varchar, MostRecentLabelAvailableFlag boolean, CurrentPatentFlag boolean, ActionType varchar, Chemical_Type int, Ther_Potential varchar, Orphan_Code varchar)
   • For the products table: create table products (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus int, TECode varchar, ReferenceDrug int, drugname varchar, activeingred varchar)
2. Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.
   • For the application.txt data: cat application.txt | sed "s/,/~/g" | sed "1D" | sed "s/\t/,/g" | sed -e "s/\s\{4,\}//g" | sed "s/,,,\r$/,null,null,null/;s/,,\r$/,null,null/;s/,\r$/,null/1" | sed "s/,,/,null/;s/,,,/,null,null/g" | sed "s/......,/&'/1" | sed "s/'./&'/1" | sed "s/',/&'/g" | sed "s/,./'&/3" | sed "s/,/&'/5" | sed "s/'../&'/4" | sed "s/nullS/null,S/g" | sed "s/nullV/null,V/g" | sed "s/nullP/null,P/g" | sed "s/,'..',.,/&'/g" | sed "s/'null/null/g" | sed "s/'S,null/'S',null/g" | sed "s/'P,null/'P',null/g" | sed "s/'S,V/'S',V/g" | sed "s/'P,V/'P',V/g" | sed "s/'P\*/'P\*'/g" | sed "s/'S\*/'S\*'/g" | sed "s/,V/,'V'/g" | sed "s/^/insert into applications(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | rev | sed -r "s/llun|'V'/;)&/1" | rev | sed "s/,S,/,'S',/;s/,P,/,'P',/;s/,S\*,/,'S\*',/;s/,P\*,/,'P\*',/g" | sed "s/~/,/g" | sed "s/'AP',null,'V'/'AP',null,null,'V'/g" > ~/public_html/application.sql.txt
   • For the Product.txt data: cat Product.txt | sed "1D" | sed "s/,/~/g" | sed "s/'//g" | sed "s/\t/,/g" | sed "s/,,/,null,/g" | sed "s/,/&'/2" | sed "s/,/'&/3" | sed "s/,/&'/3" | sed "s/,/'&/4" | sed "s/,/&'/5" | sed "s/,/'&/6" | sed "s/,/&'/7" | sed "s/,/'&/8" | sed "s/,/&'/8" | sed "s/\r$/'/g" | sed "s/^/insert into products(ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TECode,ReferenceDrug,drugname,activeingred) values(/g" | sed "s/~/,/g" | sed "s/$/);/g" > ~/public_html/Product.sql.txt

Defining the appropriate tables for the Application and Product entities

Process the data files for these entities then load them into those tables

Questions Regarding Database Creation