Difference between revisions of "Bklein7 Week 6"

From LMU BioDB 2015
Jump to: navigation, search
(Added notes on downloading and editing the zip file)
(Added information for creating the sed command for application.txt)
Line 6: Line 6:
 
*Next, I unziped the FDA file
 
*Next, I unziped the FDA file
 
  unzip UCM054599.zip
 
  unzip UCM054599.zip
−
*The resulting output included the 'application.txt' and 'Product.txt' files. To view the format of these files, I used more commands. The output I received appeared to have listed columns which were not clearly defined. Therefore, I visualized the data using 'Notepad'. This made the columns easier to read.
+
*The resulting output included the ''application.txt'' and ''Product.txt'' files. To view the format of these files, I used more commands. The output I received appeared to have listed columns which were not clearly defined. Therefore, I visualized the data using ''Notepad''. This made the columns easier to read.
−
**In 'Notepad', I got a better idea of how I needed to edit the txt files to import them into a table in 'pgAdminIII'.
+
**In ''Notepad'', I got a better idea of how I needed to edit the txt files to import them into a table in ''pgAdminIII''.
 
====Editing the txt files with sed====
 
====Editing the txt files with sed====
 
*application.txt
 
*application.txt
−
**To begin, I wanted to determine if there were any special characters in the text file such as ';' or ''' that needed to be worked around. I did two searches in notepad for these characters and found that this was not the case.
+
**To begin, I wanted to determine if there were any special characters in the text file such as semicolons or apostrophes that needed to be worked around. I did two searches in notepad for these characters and found that this was not the case.
 
**Next, I began the pipe with a cat command and then wanted to clean up the file to make it easier to read.
 
**Next, I began the pipe with a cat command and then wanted to clean up the file to make it easier to read.
−
***I started with a sed command to remove excess spaces. Although this cleaned up the file, there were still spaces separating separate values. I deduced that these values were tabs.
+
***Data values were separated by tab delimiters on the FDA website. Therefore, I began by researching how to manipulate tabs on the command line. To figure out how to replace the tabs with commas, I did some google searching. I found a way to tackle this problem on [http://www.electrictoolbox.com/linux-tab-command-line-bash/ electrictoolbox]. Using this piece of information, I wrote a sed command to replace tabs with commas. This cleaned up excess spaces from the file, although not entirely.
−
***To figure out how to replace the tabs (from a tab delimited file) with commas, I did some google searching. I found a way to tackle this problem on [http://www.electrictoolbox.com/linux-tab-command-line-bash/ electrictoolbox]. Using this piece of information, I wrote a sed command to replace tabs with commas. Immediately the output looked more like what I wanted.
+
***In reviewing the output, I found there to be multiple instances of sequences of 2 or 4 spaces. Therefore, I wrote a sed -r command to delete these sequences.
−
**Having separated
+
***Finally, I noticed that some values for the ''SponsorApplicant'' column included unnecessary spaces after the listed name. Therefore, I wrote a sed command to delete these extra spaces. The output given by this version of the command sequence only had spaces in the application sponsor names as was desired.
−
 
+
**Now that excess spaces were deleted, I needed to clean up the syntax so that the 9 data values would be read correctly by SQL.
−
**I started the pipe with a cat command and a sed command to delete excess spaces and make the file easier to read.
+
***The most glaring issue with the cleanup up output was the preponderance of missing data values. Doing a google search, I found [http://www.w3schools.com/sql/sql_null_values.asp here] that missing values are labelled as "NULL" in the command line. Thus, I had to find a way to replace all missing values with NULL.
−
**Next, I added a sed command to include the proper insert command at the beginning of each line.
+
****In every case except for the 9th data value at the end of the line, a null value was indicated by 2 commas in a row. Therefore, I wrote a sed command to insert the word ''NULL'' in between instances of 2 commas. Because occasionally there would be two missing values in a row, this command did not entirely work. Although I image there are more compact ways to address this issue, I simply repeated the command twice to clean up any skipped values.
−
**
+
****To handle the null values at the end of a line, I wrote a sed command to add NULL at the end of each line that ended with a comma.
−
 
+
***
  
  
  
  
 +
cat application.txt | sed "s/,/~/g" | sed "s/  /,/g" | sed -r "s/( ){2}//g"
 +
| sed  "s/,,/,NULL,/g;s/,,/,NULL,/g" | sed "s/ ,/,/g" | sed  "s/,/,'/1;s/,/','/2;s/,/',/3;s/False/'False'/g;s/,/,'/5;
 +
s/,/',/6;s/,/,'/7;s/,/',/8" | sed "s/,\r$/,NULL/g;s/V\r$/'V'/g" | sed  "s/'NULL'/NULL/g"
 +
| sed "s/~/,/g" | sed "s/^/insert into application(ApplNo, ApplType, SponsorApplicant, 
 +
MostRecentLabelAvailableFlag, CurrentPatentFlag, ActionType, Chemical_Type, Ther_Potential, Orphan_Code) values
 +
(/g" | sed  "s/$/);/g" | sed "1D" > ~/public_html/application2.sql.txtcat application.txt
  
 
===Questions to Answer===
 
===Questions to Answer===

Revision as of 06:55, 12 October 2015

The FDA Drug Database

Acquiring Data From the Drugs@FDA ZIP File

Downloading and Viewing

  • To download the zip file directly from the FDA website, I typed the following into the command line:
curl -O http://www.fda.gov/downloads/Drugs/InformationOnDrugs/UCM054599.zip
  • Next, I unziped the FDA file
unzip UCM054599.zip
  • The resulting output included the application.txt and Product.txt files. To view the format of these files, I used more commands. The output I received appeared to have listed columns which were not clearly defined. Therefore, I visualized the data using Notepad. This made the columns easier to read.
    • In Notepad, I got a better idea of how I needed to edit the txt files to import them into a table in pgAdminIII.

Editing the txt files with sed

  • application.txt
    • To begin, I wanted to determine if there were any special characters in the text file such as semicolons or apostrophes that needed to be worked around. I did two searches in notepad for these characters and found that this was not the case.
    • Next, I began the pipe with a cat command and then wanted to clean up the file to make it easier to read.
      • Data values were separated by tab delimiters on the FDA website. Therefore, I began by researching how to manipulate tabs on the command line. To figure out how to replace the tabs with commas, I did some google searching. I found a way to tackle this problem on electrictoolbox. Using this piece of information, I wrote a sed command to replace tabs with commas. This cleaned up excess spaces from the file, although not entirely.
      • In reviewing the output, I found there to be multiple instances of sequences of 2 or 4 spaces. Therefore, I wrote a sed -r command to delete these sequences.
      • Finally, I noticed that some values for the SponsorApplicant column included unnecessary spaces after the listed name. Therefore, I wrote a sed command to delete these extra spaces. The output given by this version of the command sequence only had spaces in the application sponsor names as was desired.
    • Now that excess spaces were deleted, I needed to clean up the syntax so that the 9 data values would be read correctly by SQL.
      • The most glaring issue with the cleanup up output was the preponderance of missing data values. Doing a google search, I found here that missing values are labelled as "NULL" in the command line. Thus, I had to find a way to replace all missing values with NULL.
        • In every case except for the 9th data value at the end of the line, a null value was indicated by 2 commas in a row. Therefore, I wrote a sed command to insert the word NULL in between instances of 2 commas. Because occasionally there would be two missing values in a row, this command did not entirely work. Although I image there are more compact ways to address this issue, I simply repeated the command twice to clean up any skipped values.
        • To handle the null values at the end of a line, I wrote a sed command to add NULL at the end of each line that ended with a comma.



cat application.txt | sed "s/,/~/g" | sed "s/  /,/g" | sed -r "s/( ){2}//g" 
| sed  "s/,,/,NULL,/g;s/,,/,NULL,/g" | sed "s/ ,/,/g" | sed  "s/,/,'/1;s/,/','/2;s/,/',/3;s/False/'False'/g;s/,/,'/5;
s/,/',/6;s/,/,'/7;s/,/',/8" | sed "s/,\r$/,NULL/g;s/V\r$/'V'/g" | sed  "s/'NULL'/NULL/g"
| sed "s/~/,/g" | sed "s/^/insert into application(ApplNo, ApplType, SponsorApplicant,  
MostRecentLabelAvailableFlag, CurrentPatentFlag, ActionType, Chemical_Type, Ther_Potential, Orphan_Code) values
(/g" | sed  "s/$/);/g" | sed "1D" > ~/public_html/application2.sql.txtcat application.txt

Questions to Answer

  1. Provide the DDL (create table) statements that you used for your application and product tables.
  2. Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.
  3. Using the command line, how can you determine the number of records in each file? Provide the command.
  4. Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL select statement.
  5. In your database, are these numbers the same or different? Explain why you think so.
    For the remaining questions, supply (a) the answer to the question and (b) the SQL statement that you used to answer the question.
  6. What are the names of the drug products that are administered in the form INJECTABLE;INTRAVENOUS, SUBCUTANEOUS?
  7. What are the names of the drug products whose active ingredient (activeingred) is ATROPINE?
  8. In what forms and dosages can the drug product named BENADRYL be administered?
  9. Which drug products have a name ending in ESTROL?
  10. Produce a table listing all of the known values for the therapeutic_potential column in the application table and how many application records there are of each. (Side note: The therapeutic_potential codes are explained in the ReviewClass_Lookup.txt file, in case you’re interested.)
  11. Produce a table listing all of the known values for the chemical_type column in the application table and how many application records there are of each. (Side note: The chemical_type codes are explained in the ChemTypeLookup.txt file, in case you’re interested.)
  12. What are the names of the drug products that are sponsored (sponsor applicant column) by MERCK?
  13. Which sponsor applicant companies have the text LABS in their names and have products whose active ingredients (activeingred) include both ASPIRIN and CAFFEINE?

Links

Assignments Pages

Individual Journal Entries

Shared Journal Entries

Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2015/index.php?title=Bklein7_Week_6&oldid=2211"