InfluxDB result set to Pandas Dataframe using Python

Question

I'd really appreciate your help here.

I'm having this result set from influx db. It is actually a dictionary:

    {u'current': [[0.03341725795376516, u'2018-10-10T12:41:27Z']],  u'voltage': [[12.95246814679179, u'2018-10-10T12:41:27Z']], u'temperature': [[0.035324635690852216, u'2018-10-10T12:41:27Z']], u'tags': {u'product': u'00000000000000'}}

Another example would be:

        u'data': {
        u'measurement': u'telemetry'},
        u'tags': {u'product_imei': u'000000000000000'},
        u'current': [
            [1.234, u'2016-01-01T00:00:00Z'], [2.234, u'2016-01-01T04:00:00Z'], [3.234, u'2016-01-01T08:00:00Z'], [1.234, u'2016-01-01T12:00:00Z'], [2.345, u'2016-01-01T16:00:00Z'], [2.678, u'2016-01-01T20:00:00Z'], [2.91, u'2016-01-02T00:00:00Z'], [2.345, u'2016-01-02T04:00:00Z'], [2.678, u'2016-01-02T08:00:00Z'], [2.91, u'2016-01-02T12:00:00Z'], [2.345, u'2016-01-02T16:00:00Z'], [2.678, u'2016-01-02T20:00:00Z'], [2.91, u'2016-01-03T00:00:00Z']
        ],
        u'voltage': [
            [14.243, u'2016-01-01T00:00:00Z'], [14.723, u'2016-01-01T04:00:00Z'], [14.826, u'2016-01-01T08:00:00Z'], [13.284, u'2016-01-01T12:00:00Z'], [12.345, u'2016-01-01T16:00:00Z'], [12.678, u'2016-01-01T20:00:00Z'], [12.91, u'2016-01-02T00:00:00Z'], [12.345, u'2016-01-02T04:00:00Z'], [12.678, u'2016-01-02T08:00:00Z'], [12.91, u'2016-01-02T12:00:00Z'], [12.345, u'2016-01-02T16:00:00Z'], [12.678, u'2016-01-02T20:00:00Z'], [12.91, u'2016-01-03T00:00:00Z']
        ],
        u'temperature': [
            [21.345, u'2016-01-01T00:00:00Z'], [None, u'2016-01-01T04:00:00Z'], [21.345, u'2016-01-01T08:00:00Z'], [None, u'2016-01-01T12:00:00Z'], [21.345, u'2016-01-01T16:00:00Z'], [None, u'2016-01-01T20:00:00Z'], [21.91, u'2016-01-02T00:00:00Z'], [None, u'2016-01-02T04:00:00Z'], [21.678, u'2016-01-02T08:00:00Z'], [None, u'2016-01-02T12:00:00Z'], [21.345, u'2016-01-02T16:00:00Z'], [None, u'2016-01-02T20:00:00Z'], [21.91, u'2016-01-03T00:00:00Z']
        ]
        }

I would like to have a pandas DataFrame similar to this using python:

    time                 current  product    voltage  temperature
------------------------------------------------------------------
2016-01-01 00:00:00   1.234  000000000000000   14.243   21.345
2016-01-01 04:00:00   2.234  000000000000000   14.723
2016-01-01 08:00:00   3.234  000000000000000   14.826   21.345
2016-01-01 12:00:00   1.234  000000000000000   13.284
2016-01-01 16:00:00   2.345  000000000000000   12.345   21.345
2016-01-01 20:00:00   2.678  000000000000000   12.678
2016-01-02 00:00:00   2.910  000000000000000   12.910   21.910
2016-01-02 04:00:00   2.345  000000000000000   12.345
2016-01-02 08:00:00   2.678  000000000000000   12.678   21.678
2016-01-02 12:00:00   2.910  000000000000000   12.910
2016-01-02 16:00:00   2.345  000000000000000   12.345   21.345
2016-01-02 20:00:00   2.678  000000000000000   12.678
2016-01-03 00:00:00   2.910  000000000000000   12.910   21.910

I already tried a very very inefficient way to do this that's actually to write row by row. Too much time. I'd spent ages doing this for thousands units.

    for i, line in enumerate(results['voltage']):

        aux_dict = {}
        for key in results.keys():
                try:
                    results[key]
                    aux_dict[key] = results[key][i][0]
                    aux_dict['time'] = pd.to_datetime(line[1], infer_datetime_format=True)
                    output.append(aux_dict)
                except:
                    "Column '" + key + "' does not have data."
                    continue

    df = pd.DataFrame(output)

Thanks in advance for your help.

You can convert your dictionary into dataframe , but it should be literal dictionary, can you show your dictionary ? — Karn Kumar, Nov 22 '18 at 11:54
Hi, tks for your answer. The dictionary is in the first code block. The problem is that for example for current it goes like this as a key in the dictionary 'current':[[current1, datetime1], [current2, datetime2], ...]. Then for voltage the same: 'voltage':[[voltage1, datetime1], [voltage2, datetime2], ...], etc. Thanks for your answer. — J. Reyes, Nov 22 '18 at 12:23
The question would be how do I get the unified dataframe sharing the datetimes. Please notice that temperature is logged every 8h. — J. Reyes, Nov 22 '18 at 12:27
Possible duplicate of [InfluxDB and pandas errors in Python](https://stackoverflow.com/questions/51366465/influxdb-and-pandas-errors-in-python) — bummi, Dec 17 '18 at 21:43

J. Reyes · Answer 1 · 2019-03-02T11:24:50.607

I wanted to answer this before. In the end I just made a function that deals with different data inputs and also creates a dataframe with column names. I will only post here the answer to the question.

Background: * A request to an endpoint is made and the result is in r.json()['data'] --> dictionary of labels e.g. 'voltage', 'current' which have a list (multiple measurements) of lists (value measured, time). Example:

import pandas as pd

d = {
'current': [[-1.8795300221255817, '2018-09-14T13:36:00Z']],
'voltage': [[12.0, '2018-09-14T13:36:00Z']]
}

fields = ['current', 'voltage']

df = pd.DataFrame()
for field in fields:
    df_aux = pd.DataFrame(d[field], columns = [field, 'time'])  # check above definition of d
    df_aux.set_index('time', inplace = True)
    df[field] = df_aux[field]

df.index = pd.to_datetime(df.index, errors='coerce')   #convert it to datetime

print df.head()

# When converting to datetime remember to check that the format was read correctly.

Thanks!

score 0 · Answer 2 · answered Mar 12 '19 at 09:02

I suggest using Pinform library, an ORM for InfluxDB to easily create measurement classes and read/write to db.

For your usage:

from pinform import Measurement, MeasurementUtils
from pinform.fields import FloatField
from pinform.tags import Tag

class CurrentAndVoltage(Measurement):
  class Meta:
    measurement_name = 'current_voltage'

  current = FloatField(null=False)
  voltage = FloatField(null=False)


items = CurrentAndVoltage(time_point=datetime.datetime.now(), current=-1.87, voltage=12.0)

df = MeasurementUtils.to_dataframe([item])

Stefano Messina · Answer 3 · 2021-02-10T15:57:59.080

Using the influxdb python module, here's a slim solution that relies on parsing the ResultSet returned object by the InfluxDBClient.query method without GROUP BY clause in your query.

Assuming in Influx you have:

> SELECT P FROM device WHERE  time > now()-24h                                                                                                                
name: device                                                                                                                                                  
time                P                                                                                                                                         
----                -                                                          
1612958108000000000 238                                                                                                                                       
1612958108000000000 0                                                          
1612958108000000000 357                                                        
1612958108000000000 0                                                                                                                                         
1612958108000000000 0

from os import environ

import pandas as pd
from influxdb import InfluxDBClient


def client(database=None):
    return InfluxDBClient(
        username=environ['INFLUXDB_USER'],
        password=environ['INFLUXDB_PASS'],
        host=environ['INFLUXDB_HOST'],
        port=environ['INFLUXDB_PORT'],
        database=database
    )

r = client(database='test').query('SELECT P FROM device WHERE  time > now()-24h')

df = pd.DataFrame(columns=['measurement', 'time', 'P'])

for k, v in r.items():
    data = {'measurement': k[0]}
    for p in v:
        data.update({'time': p['time'], 'P': p['P']})
        df = df.append(data, ignore_index=True)

df.head()

    measurement     time    P
0   device  2021-02-10T11:55:08Z    238.0
1   device  2021-02-10T11:55:08Z    0.0
2   device  2021-02-10T11:55:08Z    357.0
3   device  2021-02-10T11:55:08Z    0.0
4   device  2021-02-10T11:55:08Z    0.0

If you query with GROUP BY clause, assuming in Influx you have:

> SELECT P FROM device WHERE  time > now()-24h GROUP BY "device_id", "asset_id"                                                                               
name: device                                                                                                                                                  
tags: asset_id=57, device_id=44                                                           
time                P                                                                                                                                         
----                -                                                                                                                                         
1612958108000000000 0                                                                                                                                         
1612958108000000000 327                                                        
1612958108000000000 0                                                                                                                                         
1612958108000000000 238                                                        
1612958108000000000 357

make sure you parse the tag from the key of the ResultSet:

r = client(database='test').query('SELECT P FROM device WHERE  time > now()-24h GROUP BY "device_id", "asset_id"')

df = pd.DataFrame(columns=['measurement', 'time', 'P', 'device_id', 'asset_id'])

for k, v in r.items():
    data = {'measurement': k[0], 'device_id': k[1]['device_id'], 'asset_id': k[1]['asset_id']}
    for p in v:
        data.update({'time': p['time'], 'P': p['P']})
        df = df.append(data, ignore_index=True)

df.head()

    measurement     time    P   device_id   asset_id
0   device  2021-02-10T11:55:08Z    0.0     44  57
1   device  2021-02-10T11:55:08Z    327.0   44  57
2   device  2021-02-10T11:55:08Z    0.0     44  57
3   device  2021-02-10T11:55:08Z    238.0   44  57
4   device  2021-02-10T11:55:08Z    357.0   44  57

score 0 · Answer 4 · answered Aug 17 '23 at 13:22

I found a slighly shorter and more generic approach:

res = client(database='test').query('SELECT P FROM device WHERE  time > 
now()-24h GROUP BY "device_id", "asset_id"')

dat = res.raw['series'][0]['values']
col = res.raw['series'][0]['columns']
df = pd.DataFrame(dat, columns=col)

InfluxDB result set to Pandas Dataframe using Python

4 Answers4