Class03 Answer:

Write Pandas syntax which mimics this SQL:


DELETE FROM prices3 WHERE cdate = '2016-08-10';

DELETE FROM prices3 WHERE cdate < '2016-08-10';

DELETE FROM prices3;

I did this lab by writing the script listed below:


"""
class03pd34.py

This script should mimic the SQL listed below:

-- Delete a row:
DELETE FROM prices3 WHERE cdate = '2016-08-10';

-- Delete some rows:
DELETE FROM prices3 WHERE cdate < '2016-08-10';

-- Delete all rows:
SELECT COUNT(*) FROM prices3; 
DELETE          FROM prices3;
SELECT COUNT(*) FROM prices3; 
"""

# SQL lacks the ability to get data from the web.
# Pandas can get data from the web:

import pandas as pd


prices_df         = pd.read_csv('http://ml4.us/csv/ibm.csv')
prices_df.columns = ['cdate_s', 'openp', 'highp', 'lowp', 'closep', 'adjp', 'volume']

# I should setup prices3_df:
pred1a_sr  = prices_df.cdate_s > '2016-08-08'
pred1b_sr  = prices_df.cdate_s < '2016-09-01'
pred1_sr   = (pred1a_sr & pred1b_sr)
prices3_df = prices_df.copy()[['cdate_s','openp']][pred1_sr]

# I should report:
print(prices3_df)

# Mimic
# DELETE FROM prices3 WHERE cdate = '2016-08-10';

row_sr      = ( prices3_df.cdate_s == '2016-08-10')
prices3_loc =   prices3_df.loc[row_sr]
prices3_df  =   prices3_df.drop(prices3_loc.index)

# I should report:
print(prices3_df)

# Mimic
# DELETE FROM prices3 WHERE cdate < '2016-08-10';

rows_sr     = ( prices3_df.cdate_s < '2016-08-10')
prices3_loc =   prices3_df.loc[rows_sr]
prices3_df  =   prices3_df.drop(prices3_loc.index)

# I should report:
print(prices3_df)

# Mimic
# -- Delete all rows:
# DELETE FROM prices3;

prices3_df = prices3_df.drop(prices3_df.index)

# I should report:
print(prices3_df)

'bye'

I saw this:


dan@h79:~/ml4/public/class03demos $ python class03pd34.py
          cdate_s       openp
13745  2016-08-09  157.957336
13746  2016-08-10  157.928146
13747  2016-08-11  157.986557
13748  2016-08-12  158.901871
13749  2016-08-15  158.132614
13750  2016-08-16  156.876511
13751  2016-08-17  156.740189
13752  2016-08-18  156.662292
13753  2016-08-19  156.613617
13754  2016-08-22  155.795685
13755  2016-08-23  156.117004
13756  2016-08-24  155.688583
13757  2016-08-25  154.821960
13758  2016-08-26  154.705109
13759  2016-08-29  154.656433
13760  2016-08-30  155.561996
13761  2016-08-31  155.464615
          cdate_s       openp
13745  2016-08-09  157.957336
13747  2016-08-11  157.986557
13748  2016-08-12  158.901871
13749  2016-08-15  158.132614
13750  2016-08-16  156.876511
13751  2016-08-17  156.740189
13752  2016-08-18  156.662292
13753  2016-08-19  156.613617
13754  2016-08-22  155.795685
13755  2016-08-23  156.117004
13756  2016-08-24  155.688583
13757  2016-08-25  154.821960
13758  2016-08-26  154.705109
13759  2016-08-29  154.656433
13760  2016-08-30  155.561996
13761  2016-08-31  155.464615
          cdate_s       openp
13747  2016-08-11  157.986557
13748  2016-08-12  158.901871
13749  2016-08-15  158.132614
13750  2016-08-16  156.876511
13751  2016-08-17  156.740189
13752  2016-08-18  156.662292
13753  2016-08-19  156.613617
13754  2016-08-22  155.795685
13755  2016-08-23  156.117004
13756  2016-08-24  155.688583
13757  2016-08-25  154.821960
13758  2016-08-26  154.705109
13759  2016-08-29  154.656433
13760  2016-08-30  155.561996
13761  2016-08-31  155.464615
Empty DataFrame
Columns: [cdate_s, openp]
Index: []
dan@h79:~/ml4/public/class03demos $
dan@h79:~/ml4/public/class03demos $

Class03 Lab


ml4.us About Blog Contact Class01 Class02 Class03 Class04 Class05 Class06 Class07 Class08 Class09 Class10 dan101 Forum Google Hangout Vboxen