Pandas hw1b - Basic Queries

Question 1

All columns relating to AAPL

Answer 1

ans = df2010.loc[df2010.symb == ‘AAPL’]

ans.head()

Question 2

All columns from the table and a column with the
difference between the open and close (open-close)
for AAPL on the 7th of January

Answer 2

df2010C = df2010.copy()

df2010C = df2010C.loc[ (df2010.retdate == ‘07-Jan-2010’)
&(df2010.symb == ‘AAPL’) ]

df2010C[ ‘diff’ ] = df2010C[ ‘opn’ ] - df2010C[ ‘cls’ ]

df2010C.head()

Question 3

Write a query which returns the stock symbol, the date,
the open and close price for the top five differences
(open-close) in 2010 for only those stocks on the New
York Stock Exchange

Answer 3

df2010C = df2010.copy()

df2010C[ ‘diff’ ] = df2010C[ ‘opn’ ] - df2010C[ ‘cls ‘ ]

df2010C = ( df2010C.loc[ (df2010[ ‘exch’ ] == ‘NYSE’ ) ]
.sort_values(‘diff’, ascending = False)
) [ [ ‘symb’, ‘retdate’, ‘opn’, ‘cls’ ] ]

df2010C.head()

Question 4

The days when AAPL has a volume more than 20 million and where the high is $3 or more greater than the low.

Answer 4

df2010C = df2010.copy()

df2010C = df2010C.loc[ (df2010C.symb == ‘AAPL’)
& (df2010C.vol > 20000000)
& ((df.2010C.high - df.2010C.low) >= 3)
] [ ‘retdate’ ]

df2010C.head()

Question 5

Write a query which returns 3 columns: the return date,

SYMB and volume, but only for stocks that have a volume larger than 200 million

Answer 5

df2010C = df2010.copy()

df2010C = df2010C.loc[ (df2010C.vol > 200000000)
] [ [ ‘retdate’, ‘symb’, ‘vol’ ] ]

df2010C.head()

Question 6

Write a query which returns all information about
Google (GOOG), Netflix (NFLX), Amazon (AMZN), and
Microsoft (MSFT) in 2010.

Answer 6

df2010C = df2010C.copy()

df2010C = df2010C.loc[ (df2010C.symb == ‘GOOG’)
| (df2010C.symb == ‘NFLX’)
| (df2010C.symb == ‘AMZN’)
| (df2010C.symb == ‘MSFT’)
]

df2010C.head()

Question 7

Write a query which returns the date and symbol of
the largest “one-day gainer”, that is the stock which
has the highest close-open on the NYSE

Answer 7

df2010C = df2010.copy()

df2010C[ ‘oneday’ ] = df2010C.cls - df2010C.opn

df2010C = (df2010C.loc[ (df2010C.exch == ‘NYSE’)]
.nlargest(1, ‘oneday’)
) [ [‘retdate’, ‘symb’] ]

Question 8

Write a query which returns the date and symbol of the
largest “one-day percentage gainer”, that is the stock
which has the highest (close-open)/open on the NYSE

Answer 8

df2010C = df2010.copy()

df2010C[ ‘onedaypct’ ] = (df2010C.cls - df2010C.opn) /
df2010C.opn

df2010C = (df2010C.loc[ (df2010C.exch == ‘NYSE’) ]
.nlargest(1, ‘ondaypct’)
) [ [‘retdate’, ‘symb’] ]

df2010C.head()

Question 9

Consider stocks on the NYSE which had a volume of more than 1 million. Which stocks (symb and date) had their open price the same as their low and their closing price the same as their high?

Answer 9

df2010C.copy()

df2010C = df2010C.loc[ (df2010C.exch == ‘NYSE’)
& (df2010C.vol > 1000000)
& (df2010c.opn == df2010c.low)
& (df2010c.cls == df2010c.high)
] [ [ ‘symb’, ‘retdate’ ] ]

df2010C.head()

Question 10

Consider stocks on the NYSE which had a volume of more than 1 million. Which stocks (symbol and date) had their closing price the same as their low and their opening price the same as their high?

Answer 10

df2010C.copy()

df2010C = df2010C.loc[ (df2010C.exch == ‘NYSE’)
& (df2010C.vol > 1000000)
& (df2010c.opn == df2010c.high)
& (df2010c.cls == df2010c.low)
] [ [ ‘symb’, ‘retdate’ ] ]

df2010C.head()

Question 11

Consider stocks on the NYSE which had a volume of more than 1 milion. Of those days which stock had either (a) open = low and close = high, or (b) open = high and close = low, which symbol and date has the largest volume traded?

Answer 11

df2010C.copy()

df2010C = (df2010C.loc[ (df2010C.exch == ‘NYSE’)
& (df2010C.vol > 1000000)
& (((df2010c.opn == df2010c.high)
& (df2010c.cls == df2010c.low))
| ((df2010c.opn == df2010c.high)
& (df2010c.cls == df2010c.low)))
].nlargest(1, ‘vol’)
[ [ ‘symb’, ‘retdate’ ] ]

df2010C.head()

Question 12

Which company (ticker symbol) had the highest net income over all the years that are in the FND tables?

Answer 12

dffndC = dffnd.copy()

dffndC = dffnd.nlargest(1, ‘netinc’) [ [ ‘tic’ ] ]

Question 13

Which company (ticker symbol) had the highest net income in fiscal year 2011? (using the FND table)

Answer 13

dffndC = dffnd.copy()

dffndC = dffnd.loc [ (dffndC.fyear = 2011) ]
.nlargest(1, ‘netinc’) [ [ ‘tic’ ] ]

dffndC.head()

Question 14

Which company (ticker symbol) had the lowest, non-zero, net income over all the years? (use the FND table)

Answer 14

dffndC = dffnd.copy()

dffndC = dffnd.loc [ (dffndC.fyear = 2011) ]
.nlargest(1, ‘netinc’) [ [ ‘tic’ ] ]

dffndC.head()

Question 15

Which company (ticker symbol) which had a net-income per employee over $1000, had the largest number of employees (over all the years)? Keep units in mind! (use the FND table)

Answer 15

dffndC = dffnd.copy()

dffndC[ ‘netinc_emp’ ] = (dffndC.netinc /
dffndC.emp)*1000

dffndC = dffndC.loc [ (dffnd.netinc_emp > 1000) ]
.nlargest(1, ‘emp’) [ [ ‘tic’ ] ]

dffndC.head()