-
Notifications
You must be signed in to change notification settings - Fork 4
Interacting with datasets in MySQL
Sean Quigley edited this page Nov 3, 2015
·
18 revisions
Data has been loaded into a MySQL relational database for your consumption. This is particularly useful for especially large datasets that can be hard to load into memory on your local machine or are slow to download from S3.
Log in:
$ mysql -h health-db-internet.c6clocfz5zxy.us-east-1.rds.amazonaws.com -P3306 -u data_hacker -p
Enter password:
SHOW available databases:
MySQL [(none)]> show databases;
+------------------------------------------------+
| Database |
+------------------------------------------------+
| information_schema |
| CMS_open_payments_2013 |
| CMS_open_payments_2014 |
| Chronic_conditions_PUFs_2008 |
| Chronic_conditions_PUFs_2010 |
| Elhadad |
| Medicare_provider_utilization_and_payment_data |
| NPDB_malpractice_claims |
| SPARCS |
| innodb |
| mysql |
| performance_schema |
+------------------------------------------------+
12 rows in set (0.11 sec)
Describe a table:
MySQL [CMS_open_payments_2013]> use CMS_open_payments_2013;
Database changed
MySQL [CMS_open_payments_2013]> show tables;
+----------------------------------+
| Tables_in_CMS_open_payments_2013 |
+----------------------------------+
| general_payment_data |
| ownership_payment_data |
| research_payment_data |
+----------------------------------+
3 rows in set (0.32 sec)
MySQL [CMS_open_payments_2013]> desc ownership_payment_data;
+------------------------------------------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------------------------------------------+--------------+------+-----+---------+-------+
| Physician_Profile_ID | bigint(20) | YES | | NULL | |
| Physician_First_Name | varchar(255) | YES | | NULL | |
| Physician_Middle_Name | varchar(255) | YES | | NULL | |
| Physician_Last_Name | varchar(255) | YES | | NULL | |
| Physician_Name_Suffix | varchar(255) | YES | | NULL | |
| Recipient_Primary_Business_Street_Address_Line1 | varchar(255) | YES | | NULL | |
| Recipient_Primary_Business_Street_Address_Line2 | varchar(255) | YES | | NULL | |
| Recipient_City | varchar(255) | YES | | NULL | |
| Recipient_State | varchar(255) | YES | | NULL | |
| Recipient_Zip_Code | varchar(255) | YES | | NULL | |
| Recipient_Country | varchar(255) | YES | | NULL | |
| Recipient_Province | varchar(255) | YES | | NULL | |
| Recipient_Postal_Code | bigint(20) | YES | | NULL | |
| Physician_Primary_Type | varchar(255) | YES | | NULL | |
| Physician_Specialty | varchar(255) | YES | | NULL | |
| Record_ID | bigint(20) | YES | | NULL | |
| Program_Year | bigint(20) | YES | | NULL | |
| Total_Amount_Invested_USDollars | double | YES | | NULL | |
| Value_of_Interest | double | YES | | NULL | |
| Terms_of_Interest | text | YES | | NULL | |
| Submitting_Applicable_Manufacturer_or_Applicable_GPO_Name | varchar(255) | YES | | NULL | |
| Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_ID | bigint(20) | YES | | NULL | |
| Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_Name | varchar(255) | YES | | NULL | |
| Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_State | varchar(255) | YES | | NULL | |
| Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_Country | varchar(255) | YES | | NULL | |
| Dispute_Status_for_Publication | varchar(255) | YES | | NULL | |
| Interest_Held_by_Physician_or_an_Immediate_Family_Member | varchar(255) | YES | | NULL | |
| Payment_Publication_Date | varchar(255) | YES | | NULL | |
+------------------------------------------------------------------+--------------+------+-----+---------+-------+
28 rows in set (0.03 sec)
Run a query:
MySQL [CMS_open_payments_2013]> select Physician_Profile_ID, Value_of_Interest FROM ownership_payment_data WHERE Value_of_Interest > 1000000 ORDER BY Value_of_Interest DESC LIMIT 5;
+----------------------+-------------------+
| Physician_Profile_ID | Value_of_Interest |
+----------------------+-------------------+
| 240414 | 158911292.87 |
| 1315076 | 127454475.84 |
| 75460 | 41580000 |
| 34470 | 41040000 |
| 1210535 | 24708809.59 |
+----------------------+-------------------+
5 rows in set (0.07 sec)
Ensure you have the MySQL Python library installed:
$ pip install MySQL-python
An example script can be found here.
Running the script:
$ python mysql_python.py
Please enter password for data_hacker:
Running query: SELECT * FROM CMS_open_payments_2013.general_payment_data WHERE Recipient_State="WY";...
Columns: ('Covered Recipient Physician', None, None, 200910L, 'JOHN', 'H', 'BABSON', None, '2600 E 18TH ST', None, 'CHEYENNE', 'WY', '82001', 'United States', None, None, 'Medical Doctor', 'Allopathic & Osteopathic Physicians/ Family Medicine', 'WY', None, None, None, None, 'Forest Laboratories, Inc.', 100000005529L, 'FOREST PHARMACEUTICALS, INC.', 'NJ', 'United States', 11.89, '12/06/2013', 1L, 'In-kind items and services', 'Food and Beverage', None, None, None, 'No', 'No Third Party Payment', None, 'No', None, None, 'No', 100251992L, 'No', 'Covered', 'LINZESS', None, None, None, None, '0456120130', None, None, None, None, None, None, None, None, None, 2013L, '06/30/2015')
Number of records: 2333
An example script can be found here.
Running the script:
$ python pandas_example.py
starting call to sql
finished processing ask