Import historical data to fermention log

carloswlkr · April 14, 2015, 9:46pm

I have recently installed my BrewPi along with a temperature sensor as I wait for my Spark to arrive. I’ve got a brew that I’ve been logging in another application for the last 3 weeks, and I’d like to migrate the data into the BrewPi database. As I understand, the data is stored in .json files, and I’ve been able to open, edit, and see some results in the BrewPi chart. But the data doesn’t seem to be consistent. Within the brewpi/data folder, there are separate folders for each of the beers, and within the beer I’m logging, there are six .json files. Should I create a new .json file for each day logged? or can one file handle 21 days worth of data? Any suggestions on how to best migrate my data?

Many thanks!

Elco · April 14, 2015, 9:55pm

You can put all data in one file.
The one file per day format was chosen in the past when we still used google charts to display the data. The API could only write the entire data that it had in memory to a file and could not write to a file incrementally. To limit the data in memory, we started a new file each day. When we later switched to DyGraphs, we kept the format.

I would just write a small python script to read your old file and put it in the BrewPi json file.

Look at this class:

github.com

BrewPi/brewpi-script/blob/master/brewpiJson.py

# Copyright 2012 BrewPi
# This file is part of BrewPi.

# BrewPi is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.

# BrewPi is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with BrewPi.  If not, see <http://www.gnu.org/licenses/>.

from datetime import datetime
import time
import os
import re

This file has been truncated. show original

and

github.com

BrewPi/brewpi-script/blob/master/brewpi.py#L282


localJsonFileName = dataPath + jsonFileName + '.json'
brewpiJson.newEmptyFile(localJsonFileName)


# Define a location on the web server to copy the file to after it is written
wwwJsonFileName = wwwDataPath + jsonFileName + '.json'


# Define a CSV file to store the data as CSV (might be useful one day)
localCsvFileName = (dataPath + beerFileName + '.csv')
wwwCsvFileName = (wwwDataPath + beerFileName + '.csv')


# create new empty json file
brewpiJson.newEmptyFile(localJsonFileName)


def startBeer(beerName):
if config['dataLogging'] == 'active':
    setFiles()


changeWwwSetting('beerName', beerName)




def startNewBrew(newName):

and

github.com

BrewPi/brewpi-script/blob/master/brewpi.py#L765


            lineToWrite = (time.strftime("%b %d %Y %H:%M:%S;") +
                        json.dumps(newRow['BeerTemp']) + ';' +
                        json.dumps(newRow['BeerSet']) + ';' +
                        json.dumps(newRow['BeerAnn']) + ';' +
                        json.dumps(newRow['FridgeTemp']) + ';' +
                        json.dumps(newRow['FridgeSet']) + ';' +
                        json.dumps(newRow['FridgeAnn']) + ';' +
                        json.dumps(newRow['State']) + ';' +
                        json.dumps(newRow['Log1Temp']) + ';' +
                        json.dumps(newRow['Log2Temp']) + ';' +
                        json.dumps(newRow['Log3Temp']) + '\n')
            csvFile.write(lineToWrite)
        except KeyError, e:
            logMessage("KeyError in line from controller: %s" % str(e))


        csvFile.close()
        shutil.copyfile(localCsvFileName, wwwCsvFileName)
elif line[0] == 'D':
    # debug message received, should already been filtered out, but print anyway here.
    logMessage("Finding a log message here should not be possible, report to the devs!")
    logMessage("Line received was: {0}".format(line))

carloswlkr · April 14, 2015, 10:15pm

Excellent. I will work on it tonight. Meanwhile, my Spark enclosure and temp probes arrived a few minutes ago from Customs. I’m a happy camper… Can hardly wait to get home!

Dan · April 15, 2015, 1:32am

On the topic of temperature log files @Elco where can I find the code related to exporting the logs as .csv files? I’d like to add in a header and change it to be comma separated so the files can be easily opened in excel.

Elco · April 15, 2015, 4:57am

The files open fine in Excel for me, my Excel prefers semicolon separated. They are missing a header though. Just search brewpi.py for .csv.

Dan · April 15, 2015, 5:52am

thanks @Elco.

When I tried automatically opening the files in excel (double clicking the .csv file) it wouldn’t properly pick up the semicolon separator. You can open excel first and then open the csv file and manually tell it the separator type though. CSV stands for “comma separated values” so commas are probably better.

Elco · April 15, 2015, 6:10am

Yeah, I know. I think it might be related to the system language and regional settings. In Holland, we use commas as a decimal separator and I think that is why Excel defaults to a semicolon for cell separators. I dislike that though, I wish excel would just handle CSVs properly, how they are intended to be used (like you said).

If you send in a pull request for adding a header and switch to commas I’ll gladly merge it.

DanCook · April 15, 2015, 4:49pm

My export/import strategy is to rename the downloaded .csv file with the .dat file extension, then open with Excel. This causes Excel to load the Text Import Wizard as shown below. Choose Delimited, on the next screen choose Semicolon as the delimiter, then hit Finish. You end up with 9 columns of clean data.

I made the attached Excel spreadsheet in order to graph the beer temp vs. beer setting, and on a secondary axis show the % error in beer temp. If you copy and paste the imported .dat file columns into the table of my spreadsheet then the graph will automatically adjust, along with calculations like “total # of days”, “Average beer temp error”, etc.

Feel free to use / improve: BrewPi temperature error log.xlsx (837.0 KB)

carloswlkr · April 16, 2015, 2:56am

Is the month value in the json data file n-1? For some reason, all my data for April appears as
{“c”:[{“v”:“Date(2015,3,15,19,10,25)”},{“v”:69.54},null,null,null,null,null,{“v”:71.26},{“v”:0}]},
The month and time on my Pi are working properly. When I imported my historical data from the other application, my graph went crazy with lines waaay off the charts. But when I open the CSV file that is created automatically, the month value is correct. Any explanation?

Elco · April 16, 2015, 8:26am

Yes, months are counted 0-11 in JavaScripts Datetime…
Python uses 1-12, so we subtract 1 before saving as json.