8. Modifying tables and writing to files¶

In this lecture we demonstrate:

1. how to add a new row or column to the indexed table; and
2. how to write a table to a local file.

8.1. Adding new rows and columns¶

Computing the same statistics again and again can drastically slow down data analyses. It is, therefore, quite common in the analysis of large data sets to compute some things in advance and store the values in the table. The reason is obvious: it is faster to get a precomputed value from the table than to run the computation it from scratch.

Let's demonstrate this on a simple example. Recall the marks of students from the previous lecture:

We shall now add a new column to the table, compute the average marks of students and store them in the new column. To add a new column to the table just assign some value to a new column name:

This creates a new column called "Avg(Student)" and assigns zero to all the entries in the column. Let's see what we have accomplished:

As the next step we are going to store the average mark of each student in the corresponding entry of the table. Note that we are no longer allowed to write marks_ix.loc[student].mean() because we have an extra column whose value must not be included into the average.

This is the new table:

To compute the average mark per subject we first add a new row and fill it with dummy values:

and then compute the averages and store them:

Let us go through another example. The file PopulationSrb2017.csv in the folder data contains the estimated number of citizens of Serbia in 2017 according to the sex and age. The first row is the header of the table. Let us load the table and take a look:

Let us index the table by age:

We shall use this data to make a small demographic analysis. We shall compute the average of males and females per age group and display this by a line chart:

The line chart is now easy to get:

The curve starts roughly around 1 (which means that there are roughly the same number of males and females in those age groups), bu then falls well below 1 (which means that at some point there are more females than males). Let us find out which age groups are critical by drawing a horizontal line at the height 1:

We see from the chart that in the age groups of 46 and later the number of females is significantly larger that the number of males.

8.2. Writing tables to files¶

It is important to be able to write modified tables to files so that we do not have to repeat the intermediate computations whose results have already been stored in the table.

The function to_csv writes a table into a CSV file whose name is provided as the argument. For example, we have modified the table popSrb_ix by computing the male-to-female ratio for each age group. It makes sense to write this new table to a new file for later use:

As another example, let us load the table available at

https://raw.githubusercontent.com/cs109/2014_data/master/countries.csv

and let us write the table to a local file data/countries.csv:

If you take a look at the file you'll se that it looks something like this:

,Country,Region
0,Algeria,AFRICA
1,Angola,AFRICA
2,Benin,AFRICA
3,Botswana,AFRICA
4,Burkina,AFRICA
5,Burundi,AFRICA
6,Cameroon,AFRICA
7,Cape Verde,AFRICA
8,Central African Republic,AFRICA
9,Chad,AFRICA
(etc)

So, the system has written the (default) index column together with the relevant data. This was convenient while writing the table popSrb_ix to file because the table was indexed by the age groups, but here this is not the case. We shall, therefore, write the table again, but this time we shall instruct the system not to write the index (which is, in this case, a dummy sequence of numbers):

The file now looks like this:

Country,Region
Algeria,AFRICA
Angola,AFRICA
Benin,AFRICA
Botswana,AFRICA
Burkina,AFRICA
Burundi,AFRICA
Cameroon,AFRICA
Cape Verde,AFRICA
Central African Republic,AFRICA
Chad,AFRICA
(etc)

8.3. Exercises¶

Exercise 1. Look at the code carefully and then answer the questions below:

1. In what format is the table "US" going to be written?
2. Is it going to be written locally, to the hard-drive of your computer, or to some remote resource?

Exercise 2. The biologists have up to now classified more than 2,000,000 species of living beings. They are all divided into five kingdoms and the approximate number of species per kingdom is given in this table:

Turn this table into an indexed DataFrame, then add a new row called "Total" and compute the total number of species that this table refers to.

Exercise 3. The following cell contains data about weight and length/height of a boy in the first seven years of his life.

Transpose the table, add a new column called "BMI" to the transposed table and then for each row comupte the BMI (body mass index) using the formula:

Write the new table to the file data/BMI.csv

Exercise 4. The following table summarizes the highest and the lowest recorded temperatures (in $^\circ$C) on each of the continents:

Add a new row to this table and compute the maximal temperature range for each continent (by subtracting the lowest recorded temperature from the highest recorded temperature).

Zadatak 6. This is an overview of spendings of a family over a year (in the local currency):

This table represented as a list looks like this:

(a) Turn this list into an indexed DataFrame.

(b) Add a new row called "Total" and store in it the total living costs per month (Jan, Feb, Mar, etc).

(c) Add a new column called "Average" and store in it the average costs per each type of cost (Rent, Electricity etc).

(d) Write the new table to data/LivingCosts.csv