Python/Data - Revision history

Rb: /* Setting the Date as Index */

2014-07-25T12:04:43Z

‎Setting the Date as Index

Rb at 11:02, 25 July 2014

2014-07-25T11:02:39Z

Rb at 08:51, 25 July 2014

2014-07-25T08:51:30Z

Rb at 15:39, 24 July 2014

2014-07-24T15:39:39Z

Rb at 13:54, 24 July 2014

2014-07-24T13:54:41Z

Rb at 21:47, 21 July 2014

2014-07-21T21:47:06Z

Rb at 21:45, 21 July 2014

2014-07-21T21:45:04Z

Rb at 21:34, 21 July 2014

2014-07-21T21:34:41Z

Rb at 21:12, 21 July 2014

2014-07-21T21:12:04Z

Rb: Created page with " = Introduction = Here we will give a brief introduction into how to best handle data when using Python to solve Econometric problems. Here we will use a tool called Pandas...."

2014-07-21T20:35:49Z

Created page with " = Introduction = Here we will give a brief introduction into how to best handle data when using Python to solve Econometric problems. Here we will use a tool called Pandas...."

New page

= Introduction =

Here we will give a brief introduction into how to best handle data when using Python to solve Econometric problems. Here we will use a tool called Pandas. They are based on Numpy arrays. So first you got to make sure that the [[[http://www.numpy.org/ Numpy]|[http://www.numpy.org/ Numpy]]] and [[[http://pandas.pydata.org/ Panda]|[http://pandas.pydata.org/ Panda]]] modules are available.

You can use Pandas to any of the following:

* Merge data-sets
* Filter data-sets
* Calculate summary statistics

We will do this by way of an example. Here are two datafiles:

# S&P500: [[media:SP500.csv|SP500.csv]]
# IBM: [[media:IBM.csv|IBM.csv]]

These are csy files downloaded from [[[http://www.yahoo.com/finance Yahoo|http://www.yahoo.com/finance Yahoo]]] which contain information about the S&P500 share price index and the IBM share prices. But let’s use Python and Pandas to explore the data.

= Data Import =

Use the following code

<source>import numpy as np # import modules for use
import pandas as pd

data_SP = pd.read_csv('SP500.csv')
data_IBM = pd.read_csv('IBM.csv')</source>
= Literature =

Hamilton J.D. (1994) ''Time Series Analysis'', Princeton, Section 5.7 as well as Judge G.G, W.E. Griffiths, R.C. Hill, H. Lütkepohl and T.-C. Lee (1985) ''The Theory and Practice of Econometrics'', John Wiley, Appendix B, give good introductions into the mechanics of nonlinear optimisation algorithms.

Martin V., Hurn S. and Harris D. (2012) ''Econometric Modelling with Time Series: Specification, Estimation and Testing (Themes in Modern Econometrics)'', Chapter 3 gives an excellent introduction into nonlinear optimisation strategies.

@@ Line 231: / Line 231: @@
 -01-02    2.45
 Name: Adj Close, Length: 13227, dtype: float64</source>
 = Literature =
 The Panda Documentation can be found here [http://pandas.pydata.org/pandas-docs/stable/pandas.pdf]. I found the tutorials particularly useful [http://pandas.pydata.org/pandas-docs/dev/tutorials.html].

@@ Line 152: / Line 152: @@
 = Joining Datasets =
-If you check out Panda’s documentation on merging DataFrames you will find a bewildering array of ways how you can join different DataFrames. This site makes no claim to deliver an exhausting documentation so will will unashamedly restrict our demonstration to our example at hand. Earlier we uploaded two datasets into DataFrames, <code>dataSP</code>, the one which we were using so far, and <code>dataIBM</code>.
+If you check out Panda’s documentation on merging DataFrames you will find a bewildering array of ways how you can join different DataFrames. This site makes no claim to deliver an exhausting documentation so will will unashamedly restrict our demonstration to our example at hand. Earlier we uploaded two datasets into DataFrames, <code>data_SP</code>, the one which we were using so far, and <code>data_IBM</code>.
 The following command will join the two DataFrames:
-<source>data = pd.concat([data_SP,data_IBM],axis=1,keys=['SP','IBM'])</source>
+<source>data = pd.concat({'SP': data_SP, 'IBM': data_IBM},axis=1,join='inner')</source>
-Let’s investigate the elements of this command. First, the <code>pd.</code> ensures that Python looks in the Panda module for the <code>concat</code> command. The first argument, <code>[dataSP,dataIBM]</code>, is a list of the DataFrames that are to be joined. The second argument, <code>axis=1</code>, indicates that we want to join by creating new variables/columns (as opposed to new entries which would be <code>axis=0</code>). The last argument, <code>keys=[’SP’,’IBM’]</code>, basically introduces an new layer of variable names. Both DataFrames contain, e.g. a variable called ''Adj Close'' and this is the way to ensure that we are not getting them muddled up. How this works can be seen as follows:
+Let’s investigate the elements of this command. First, the <code>pd.</code> ensures that Python looks in the Panda module for the <code>concat</code> command. The second argument, <code>axis=1</code>, indicates that we want to join by creating new variables/columns (as opposed to new entries which would be <code>axis=0</code>). But, this by itself will cause a problem, as both DataFrames that are to be merged have identical column names. This will cause a conflict. With this in the back of our names, the first argument will make most sense. It is presented in curly brackets which in Python indicates a dictionary [https://docs.python.org/2/tutorial/datastructures.html#dictionaries]. <code></code>, is dictionary which associates <code>data_SP</code> with SP and <code>data_IBM</code> with IBM and the reason why the two DataFrames to be merged in this way are presented to the concatenation function in this way will become obvious very shortly. The last argument in the above command is <code>join=’inner’</code>. This ensures that the new records are only those for which we have available data for all variables. The IBM datafile was somewhat shorter (it only had 13,227 daily entries) and therefore the new DataFrame has 13,227 entries (indexed from 0 to 13226). If you are happy to create partially empty records you can leave this last argument out (or use <code>join=’outer’</code>). How this works can be seen as follows:
 <source>print(data)
@@ Line 179: / Line 179: @@
 (SP, Adj Close)     13227  non-null values
 dtypes: datetime64[ns](2), float64(12)</source>
-The result is obvious.
+The result is obvious. The columns/variables now have been given a two layer name. The first layer indicating whether the data are from Sp or IBM and the second indicating the variable. We can call a particular variable as follows:
-But we now merged entries from <code>dataSP</code> and <code>dataIBM</code> with the same index. No guarantee that they are from the same day. Need to think carefully about index.
+<source>data2['SP']['Adj Close'].plot()</source>
 = Literature =
-The Panda Documentation can be found here [http://pandas.pydata.org/pandas-docs/stable/pandas.pdf].
+The Panda Documentation can be found here [http://pandas.pydata.org/pandas-docs/stable/pandas.pdf]. I found the tutorials particularly useful [http://pandas.pydata.org/pandas-docs/dev/tutorials.html].

@@ Line 50: / Line 50: @@
 Adj Close    16240  non-null values
 dtypes: datetime64[ns](1), float64(6)</source>
-You can see that we get 16240 daily entries. They have index 0 to 16239, as usual in Python we start counting at 0. Then we find out that we have 7 data columns, or variables. They are called Date, Open, High, Low, Close, Volume, Adj Close. These names are automatically taken from the first row of the Excel file. In the last line we see the data-types contained in the DataFrame. One of the variables is a date, the others are floating variables. You can get a glimpse at the actual data as follows
+You can see that we get 16240 daily entries. They have index 0 to 16239, as usual in Python we start counting at 0. The index is actually crucial to understanding DataFrames. Each entry has an index and it is the index by which we can recall that entry. The central role of indices will become obvious when we join datasets together a little later and we will then see how to change it. Then we find out that we have 7 data columns, or variables. They are called Date, Open, High, Low, Close, Volume, Adj Close. These names are automatically taken from the first row of the Excel file. In the last line we see the data-types contained in the DataFrame. One of the variables is a date, the others are floating variables. You can get a glimpse at the actual data as follows
 <source>print(data_SP.values)</source>
@@ Line 65: / Line 65: @@
 You can get to individual variables/columns using the following referencing style: <code>dataSP[’Adj Close’]</code> which will select the ''Adjusted Close'' variable.
 = Extracting/Slicing data =
@@ Line 136: / Line 134: @@
 <source>data_SP[3:6].values</source>
-will return an array that contains entries 3 to 5 from the DataFrame data<sub>S</sub>P. You can also select entries on conditions. Say you want to select those days on which there were more than 10 Billion shares traded in the S&P500. They are selected as follows.
+will return an array that contains entries 3 to 5 from the DataFrame data<sub>S</sub>P. Even if you only want to select one entry, say entry 3, you need to use <code>dataSP[3:4].values</code> rather than <code>dataSP[3].values</code>, which will not work. You can also select entries on conditions. Say you want to select those days on which there were more than 10 Billion shares traded in the S&P500. They are selected as follows.
 <source>data_SP[data_SP.Volume>10000000000]</source>
@@ Line 152: / Line 150: @@
 This returns an array with three rows, each of which contains the date, the adjusted close and the volume of shares traded on these three days with the highest volume. As you can see, the last of these days was in 2010.
-It will select
+= Joining Datasets =
 = Literature =
 The Panda Documentation can be found here [http://pandas.pydata.org/pandas-docs/stable/pandas.pdf].

@@ Line 69: / Line 69: @@
 = Extracting/Slicing data =
 You can select the data according to column/variable information and/or according to index information. Let’s start with columnwise selection. The following command
@@ Line 121: / Line 123: @@
 Out[84]: (16240L, 1L)</source>
 It turns out that <code>test2</code> is a 1-dimensional array (with 16240 elements), whereas <code>test3</code> is a two-dimensional array with 16240 rows and 1 column.
 = Literature =
 The Panda Documentation can be found here [http://pandas.pydata.org/pandas-docs/stable/pandas.pdf].

@@ Line 3: / Line 3: @@
 = Introduction =
-Here we will give a brief introduction into how to best handle data when using Python to solve Econometric problems. Here we will use a tool called Pandas. They are based on Numpy arrays. So first you got to make sure that the Numpy[http://www.numpy.org/] and Panda[http://pandas.pydata.org/] modules are available.
+Here we will give a brief introduction into how to best handle data when using Python to solve Econometric problems. Here we will use a tool called Pandas. They are based on Numpy arrays. So first you got to make sure that the Numpy [http://www.numpy.org/] and Panda [http://pandas.pydata.org/] modules are available.
 You can use Pandas to any of the following:
@@ Line 63: / Line 63: @@
   [datetime.datetime(1950, 1, 3, 0, 0) 16.66 16.66 ..., 16.66 1260000.0 16.66]]</source>
 Here you can see the data, the first being a vector of dates (starting with the last observation from 18 July 2014). This is followed by the ''Open''ing value of the S&P500 index on that date (1961.54), then the ''High''est price (1979.91) on that day. The last three columns we can see (some are hidden from display) are the ''Close'' price, the ''Volume'' and the ''Adj''usted ''Close''.
 = Literature =

@@ Line 29: / Line 29: @@
 xlsxfile = pd.ExcelFile('SP500.xlsx')
-data_IBM = xlsxfile.parse('SP500')</source>
+data_SP = xlsxfile.parse('SP500')</source>
 Importing Excel files works in the two steps of first uploading an Excel file (which is what the <code>pd.ExcelFile(’IBM.xlsx’)</code> command does). The second line, <code>dataIBM = xlsxfile.parse(’IBM’)</code> puts the data into what is called a ''DataFrame''. We’ll see what that is in a minute.
-If you have the data in a csv file, then this process is even simpler! Then you only need one line <code>dataIBM = pd.readcsv(’IBM.csv’)</code>
+If you have the data in a csv file, then this process is even simpler! Then you only need one line <code>dataIBM = pd.readcsv(’IBM.csv’)</code> to achieve the same. The importing process will also be significantly longer from an xlx file, when compared to a csv file. So if you have large datasets it may be much better to import them from a csv file.
 = Literature =

@@ Line 13: / Line 13: @@
 We will do this by way of an example. Here are two datafiles:
-# S&P500: [[media:SP500.csv|SP500.csv]]
+# S&P500: [[media:SP500.xlsx|SP500.xlsx]]
-# IBM: [[media:IBM.csv|IBM.csv]]
+# IBM: [[media:IBM.xlsx|IBM.xlsx]]
 These are csy files downloaded from [[[http://www.yahoo.com/finance Yahoo|http://www.yahoo.com/finance Yahoo]]] which contain information about the S&P500 share price index and the IBM share prices. But let’s use Python and Pandas to explore the data.
@@ Line 20: / Line 20: @@
 = Data Import =
-Use the following code
+Use the following code:
 <source>import numpy as np      # import modules for use
 import pandas as pd
-data_SP = pd.read_csv('SP500.csv')
+xlsxfile = pd.ExcelFile('IBM.xlsx') # Loads an Excel Table
-data_IBM = pd.read_csv('IBM.csv')</source>
+data_IBM = xlsxfile.parse('IBM')    # Read an Excel table into DataFrame
-= Literature =
-Hamilton J.D. (1994) ''Time Series Analysis'', Princeton, Section 5.7 as well as Judge G.G, W.E. Griffiths, R.C. Hill, H. Lütkepohl and T.-C. Lee (1985) ''The Theory and Practice of Econometrics'', John Wiley, Appendix B, give good introductions into the mechanics of nonlinear optimisation algorithms.
+If you have the data in a csv file, then this process is even simpler! Then you only need one line <code>dataIBM = pd.readcsv(’IBM.csv’)</code>
-Martin V., Hurn S. and Harris D. (2012) ''Econometric Modelling with Time Series: Specification, Estimation and Testing (Themes in Modern Econometrics)'', Chapter 3 gives an excellent introduction into nonlinear optimisation strategies.
+= Literature =