Class03 Answer:

Write Pandas syntax which mimics this SQL:


UPDATE prices3 SET diff = 1 WHERE cdate = '2016-08-01';
UPDATE prices3 SET diff = closep - openp;

I did this lab by writing the script listed below:


"""
class03pd29.py

This script should mimic the SQL listed below:

-- Update some rows of a column:
UPDATE prices3 SET diff = 1 WHERE cdate = '2016-08-10';

-- Update column using other columns:
UPDATE prices3 SET diff = closep - openp;
"""

# 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','closep']][pred1_sr]
prices3_df['diff'] = prices3_df.closep - prices3_df.openp

# Mimic
# UPDATE prices3 SET diff = 1 WHERE cdate = '2016-08-10';

row_sr = (prices3_df.cdate_s == '2016-08-10')
prices3_df.loc[row_sr, 'diff'] = 1

# I should report:
print(prices3_df)

# Mimic
# UPDATE prices3 SET diff = closep - openp;
prices3_df['diff'] = prices3_df.closep - prices3_df.openp

# I should report:
print(prices3_df)

'bye'

I saw this:


dan@h79:~/ml4/public/class03demos $ python class03pd29.py
          cdate_s       openp      closep      diff
13745  2016-08-09  157.957336  161.770004  3.812668
13746  2016-08-10  157.928146  162.080002  1.000000
13747  2016-08-11  157.986557  163.529999  5.543442
13748  2016-08-12  158.901871  161.949997  3.048126
13749  2016-08-15  158.132614  161.880005  3.747391
13750  2016-08-16  156.876511  160.699997  3.823486
13751  2016-08-17  156.740189  160.440002  3.699813
13752  2016-08-18  156.662292  161.360001  4.697709
13753  2016-08-19  156.613617  160.039993  3.426376
13754  2016-08-22  155.795685  160.000000  4.204315
13755  2016-08-23  156.117004  160.259995  4.142991
13756  2016-08-24  155.688583  159.050003  3.361420
13757  2016-08-25  154.821960  158.630005  3.808045
13758  2016-08-26  154.705109  158.320007  3.614898
13759  2016-08-29  154.656433  159.720001  5.063568
13760  2016-08-30  155.561996  159.399994  3.837998
13761  2016-08-31  155.464615  158.880005  3.415390
          cdate_s       openp      closep      diff
13745  2016-08-09  157.957336  161.770004  3.812668
13746  2016-08-10  157.928146  162.080002  4.151856
13747  2016-08-11  157.986557  163.529999  5.543442
13748  2016-08-12  158.901871  161.949997  3.048126
13749  2016-08-15  158.132614  161.880005  3.747391
13750  2016-08-16  156.876511  160.699997  3.823486
13751  2016-08-17  156.740189  160.440002  3.699813
13752  2016-08-18  156.662292  161.360001  4.697709
13753  2016-08-19  156.613617  160.039993  3.426376
13754  2016-08-22  155.795685  160.000000  4.204315
13755  2016-08-23  156.117004  160.259995  4.142991
13756  2016-08-24  155.688583  159.050003  3.361420
13757  2016-08-25  154.821960  158.630005  3.808045
13758  2016-08-26  154.705109  158.320007  3.614898
13759  2016-08-29  154.656433  159.720001  5.063568
13760  2016-08-30  155.561996  159.399994  3.837998
13761  2016-08-31  155.464615  158.880005  3.415390
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