Update: move on to the most current statistcs, here

After two weeks of operation, its time to post a first statistical review of the detected visits in the Bird Box. Right after the bird box was installed a Great Tit discovered the house.

Beginning on April 3rd both birds visited the bird box 720 times until April 18 which is an average of 45 visits per day.

Visits per day

Let us at first check, how many visits where detected each day. You may click on the image to get a more detailed view on the data. The maximum of 136 visits where counted on Wednesday, April 15.

Visits per hour

The following diagram shows the activity in the bird box depending on the time of day. Each set represents the average activity per day detected in one hour. As you can see the birds are more active until noon. By the way, the bird box has it’s front looking to the west.

How to retrieve the statistic data

Summarize data with SQLite

At first the statistical data is created in SQLite. We summarize the data with the SQLite group by attribute and the strftime function and write the results directly to a CSV file which we can process with any office package or Google Drive later on.

Visits per day

.mode csv
.headers on
.separator ;
.out sensordata_days.csv
select strftime('%d.%m.%Y', time), count(*) from tuersensors GROUP BY strftime('%Y%m%d', time);
.quit
  

Visits grouped by time

.mode csv
.headers on
.separator ;
.out sensordata_hour.csv
select strftime('%H', time), count(*) from tuersensors GROUP BY strftime('%H', time);
.quit

You may as well create a batch file for automated processing of the sql database. As far as the given example below is concerned some post processing is needed since SQLite does not support variables. You may execute the script using cat:

cat generate_csv.sql | sqlite development.sqlite3

.mode csv
.headers on
.separator ;
.out sensordata_summary.csv
select count(DISTINCT strftime('%Y%m%d', time)) from tuersensors;
select count(*) from tuersensors;
.out sensordata_days.csv
select strftime('%d.%m.%Y', time), count(*)/2 from tuersensors GROUP BY strftime('%Y%m%d', time);
.out sensordata_hour.csv
select strftime('%H', time), count(*)/2 from tuersensors GROUP BY strftime('%H', time);
.quit

Now all values in sensordata_hour.csv should be devided by the value DISTINCT strftime(‘%Y%m%d’, time) you will find in sensordata_summary.csv