This is a sample solution for the CSV and data frames exercise. This does not mean that this is the only way to solve this exercise. As with any programming task - and also with most data analysis tasks - there are multiple solutions for the same problem.
Loading data from CSV
Your first step is always accessing the data. Often, the data is stored in a database or within files. One common and generic exchange format for files are Comma Separated Value (CSV) files. The first line of such a file indicates the names of the features, the following lines each contain a single instance.
First, download the bankruptcy data set we prepared for you and and upload it to your Jupyter notebook. Please note, that we slightly modified the data from the original available in the UCI archive UCI for this exercise, e.g., to include missing values.
Use the cell below to load the data from the CSV file. The data should be loaded into a data frame. Data frames are available in python using the pandas library. In comparison to matrices or similar types, they allow different types of columns, are usually easier to manipulate, e.g., by adding or removing rows/columns, and rows and columns can be named.
Once you have done this, print some information about the data:
- number of instances
- number of features
- names of the features
You should have 55 instances with 7 features.
Remove features
If you load all data from a file, you often also load irrelevant features for a task. In case of the data you just loaded, the feature is called Company. This is an ID feature for the instances in the data. Such data must often be removed before further analysis of the data. Your second task is to remove this feature from the data.
import pandas as pd
# load the data directly from the URL. This is supported since pandas 0.19.2
data = pd.read_csv(
'http://user.informatik.uni-goettingen.de/~sherbold/analcatdata_bankruptcy.csv')
# since jupyter notebooks show the return value of the last statement, this shows the data frame
# jupyter also provides a nice HTML rendering for the data
data
# Now we drop the column company
# inplace means that the current data frame is modified
# without inplace, a copy is created that we would have to assign again
data.drop(labels='Company', axis='columns', inplace=True)
data
Remove instances with missing values
Real-life data is often not clean, i.e., the data has many problems which must be addressed first, before it can be used for analysis. One common problem are missing features, i.e., not all features are available for all data. This is also the case for the data you just loaded. All missing values are marked as NA in the CSV file.
Your third task is to remove all instances from the data, that have any missing values and store the remaining instances in a new data frame. If this works correctly, five instances should be removed. You can check this, e.g., by comparing the sizes of the data frames or printing the instances that were removed.
print('Number of instance before dropping instances with missing values:', len(data))
data.dropna(inplace=True)
print('Number of instance after dropping instances with missing values:', len(data))
# columns can be access by their name
# new columns can be added by using their name
data['WC/TA+RE/TA'] = data['WC/TA']+data['RE/TA']
# the above way to access the columns is shorthand for using loc (location)
# : selects all rows
data.loc[:, 'EBIT/TA*S/TA'] = data.loc[:, 'EBIT/TA']*data.loc[:, 'S/TA']
data
Merging data frames
The next task of this exercise is to merge data frames. For this, load the data from the same CSV file as above again. Then merge the data frame with the result from task 2.4, such that:
- the dropped feature from task 2.2 is part of the merged data frame; and
- the removed instances from task 2.3 are still gone; and
- the indirectly computed features from task 2.4 are part of the merged data frame.
data2 = pd.read_csv(
'http://user.informatik.uni-goettingen.de/~sherbold/analcatdata_bankruptcy.csv')
# merge combines two data frames with a join operation
# by default an "inner join" on the index is performed, i.e., all instances with the same index are joined
merged_data = data.merge(data2)
merged_data
Selecting subsets
Based on the data frame from task 2.5, create new data frames according to the following criteria.
- A data frame with only the rows 10 to 20 and all columns.
- A data frame with only the columns 1 to 4 and all rows.
- A data frame with only the columns WC/TA and EBIT/TA and all rows.
- A data frame with all rows that have the value RE/TA less than -20 and all columns.
- A data frame with all rows that have the value RE/TA less than -20 and bankrupt equal to 0 and all columns.
- A data frame with all rows that have the value RE/TA less than -20 and bankrupt equal to 0 and only the columns WC/TA and EBIT/TA.
# integer based indexing fetches rows, in this case from 10 (inclusive) to 21 (exclusive)
merged_data[10:21]
# in general, we can use iloc for integer based locations
merged_data.iloc[:, 1:5]
# with lists of strings we get columns
merged_data[['WC/TA', 'EBIT/TA']]
# we can also use boolean formulas to filter the rows of data frames
merged_data[merged_data['RE/TA'] < -20]
# when we combine multiple conditions, we must use () because we have bitmasks
# we also need to use the bitwise operators & and | instead of the keywords 'and' and 'or'
merged_data[(merged_data['RE/TA'] < -20) & (merged_data['Bankrupt'] == 0)]
# we can use the conditions also with loc and also select the columns we want
merged_data.loc[(merged_data['RE/TA'] < -20) &
(merged_data['Bankrupt'] == 0), ['WC/TA', 'EBIT/TA']]