syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me

You are here: Linux101 > Shell 101 > shell101_paste_sqlite3

Linux Shell 101

Question:
In Shell101 how to use paste and sqlite3?

I will expand the above question:

In Shell101 how to write Bash script which vertically concatenates with paste and queries CSV file with sqlite3?

For each date I want today's pct-gain and I want tomorrow's pct-gain.

Does pct-gain tomorrow depend on pct-gain today?
#!/bin/bash

# ~ann/paste_sqlite3_gspc.bash

# This script should work on a CSV file from Yahoo with paste and sqlite3.

# I should cd to the right folder:

cd ~ann

export TKR=GSPC

# Assume that ${TKR}5.csv was just built.
# ${TKR}5.csv has these columns:
# cdate, cp, lag_date, lag_price, delta, pctg
# 1      2   3          4           5      6

awk -F, '{print $1","$2","$3","$6","}' ${TKR}5.csv >  tmp1.csv
echo  un, wanted, row, here, >> tmp1.csv
# head tmp1.csv

echo ,un, wanted, row, here  >  tmp2.csv
awk -F, '{print $1","$2","$3","$6}'    ${TKR}5.csv >> tmp2.csv
# head tmp2.csv

paste tmp1.csv tmp2.csv | grep -v 'un, wanted' | grep -v '[[:blank:]]cdate' > tmp3.csv
# head tmp3.csv

echo 'cdate, cp, lag_pctg, lead_date, lead_pctg' >  ${TKR}6.csv
awk -F, '{print $1","$2","$4","$5","$8}' tmp3.csv >> ${TKR}6.csv

echo I am done, I created ${TKR}6.csv from ${TKR}5.csv
echo Here look:

head ${TKR}6.csv

echo Time for sqlite3!

grep -v cdate ${TKR}6.csv > sqlite3.csv

echo 'MIN(cdate) MAX(cdate) COUNT(cdate) MIN(lag_pctg) MAX(lag_pctg) AVG(lag_pctg)'

sqlite3<<EOF
CREATE TABLE
mytable (cdate DATE, cp FLOAT, lag_pctg FLOAT, lead_date DATE, lead_pctg FLOAT);

.separator ","
.import sqlite3.csv mytable

SELECT
MIN(cdate), MAX(cdate), COUNT(cdate), MIN(lag_pctg), MAX(lag_pctg), AVG(lag_pctg)
FROM mytable;

SELECT cdate FROM mytable WHERE lag_pctg < -20.0;
SELECT cdate FROM mytable WHERE lag_pctg > 11.0 ;
SELECT AVG(lead_pctg) FROM mytable WHERE lag_pctg < -3.0;
SELECT AVG(lead_pctg) FROM mytable WHERE lag_pctg >  3.0;

EOF

exit
When I run the above script I see this:
ann@feb ~ $ 
ann@feb ~ $ 
ann@feb ~ $ ./paste_sqlite3_gspc.bash
I am done, I created GSPC6.csv from GSPC5.csv
Here look:

cdate, cp, lag_pctg, lead_date, lead_pctg
2015-02-19,2097.45 , -.1062,	2015-02-20, .6126
2015-02-18,2099.68 , -.0314,	2015-02-19, -.1062
2015-02-17,2100.34 , .1597,	2015-02-18, -.0314
2015-02-13,2096.99 , .4074,	2015-02-17, .1597
2015-02-12,2088.48 , .9644,	2015-02-13, .4074
2015-02-11,2068.53 , -.0029,	2015-02-12, .9644
2015-02-10,2068.59 , 1.0675,	2015-02-11, -.0029
2015-02-09,2046.74 , -.4247,	2015-02-10, 1.0675
2015-02-06,2055.47 , -.3418,	2015-02-09, -.4247


Time for sqlite3!

MIN(cdate) MAX(cdate) COUNT(cdate) MIN(lag_pctg) MAX(lag_pctg) AVG(lag_pctg)

1950-01-04,2015-02-19,16387,-20.4669,11.58,0.0342218709953015

1987-10-19

2008-10-13

0.405621649484536

0.0423368932038836

ann@feb ~ $ 
ann@feb ~ $ 
ann@feb ~ $ 
The above sqlite3 query tells me interesting facts about my data:
  • The observations start in 1950
  • They end in 2015
  • I have over 16,000 observations
  • The average 1-day-pct-gain is 0.034%
  • On 1987-10-19 the market fell 20.5%
  • On 2008-10-13 the market gained 11.6%
  • Market jumps of more than 3% are not bearish
  • My avg-gain is 12x higher after market falls of more than 3%
The last fact indicates that a machine could learn from price deltas.

So, that is the 7th shell programming demo of shell101.

You are here: Linux101 > Shell 101 > shell101_paste_sqlite3
You can ask questions in Dan's Machine Learning Class Forum:
https://groups.google.com/forum/#!forum/dan101

syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me