0

I am trying to receive data using mosquitto and save it as csv file using python pandas. The data is continuos until I stop the script.

mqtt_pub.py

import paho.mqtt.client as mqtt
import random
import schedule
import time

mqttc = mqtt.Client("python_pub")
mqttc.connect("localhost", 1883)

def job():
    mqttc.publish("hello/world", random.randint(1, 10))

schedule.every(1).seconds.do(job)

while True:
    schedule.run_pending()
    time.sleep(1)

mqttc.loop(2)

mqtt_sub.py

import paho.mqtt.client as mqtt
import pandas as pd

def on_connect(client, userdata, rc):
    print("Connected with result code "+str(rc))
    client.subscribe("hello/world")

def on_message(client, userdata, msg):
    datas = map(int, msg.payload)
    for num in datas:
        df = pd.DataFrame(data=datas, columns=['the_number'])
        df.to_csv("testing.csv")

client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message

client.connect("localhost", 1883, 60)

client.loop_forever()

from above mqtt_sub.py script, I get testing.csv that looks like this

    | the _number
0   | 2

2 is the last digit that I receive before I stop the mqtt_sub.py script

Connected with result code 0
[3]
[9]
[5]
[3]
[7]
[2]
...
...
KeyboardInterrupt

I was hoping to get testing.csv like this

    | the_number
0   | 3
1   | 9
2   | 5
...
...
5   | 2

To achieve that I try to change the following df = pd.DataFrame(data=datas, columns=['the_number']) to df = pd.DataFrame(data=num, columns=['the_number']) and the following error occured

pandas.core.common.PandasError: DataFrame constructor not properly called!

Do anyone have any idea how to solve the error? I also feel that I did not use the for loop properly in here.

Thank you for your suggestion and help.

[UPDATE]

I add/change the following line in on_message method

def on_message(client, userdata, msg):
    datas = map(int, msg.payload)
    df = pd.DataFrame(data=datas, columns=['the_number'])

    f = open("test.csv", 'a')
    df.to_csv(f)
    f.close()

With help from Nulljack, I am able to get the result like this in my CSV file

   | the_number
0  | 3
   | the_number
0  | 9 
   | the_number
0  | 5
   | the_number
0  | 3
   | the_number
0  | 7

My goal is to achieve something like this in the CSV file

   | the_number
0  | 3
1  | 9
2  | 5 
3  | 3
4  | 7
Fang
  • 824
  • 4
  • 17
  • 32

2 Answers2

0

Having never used mosquitto before I apologize if i my understanding is wrong.

It seems to me like the on_message method in your mqtt_sub.py is run every time your mqtt_pub.py publishes a message (ie every one second) this would cause your testing.csv file to be overwritten every time you publish a message

to fix this I would initialize a dataframe in your on_connect method and then in the on_message add the new value to the dataframe via df.append

as for writing to csv after you terminate, I am unsure.

Hope this helps

Nulljack
  • 21
  • 5
  • I believe, `on_connect` is method use when connecting to the mosquitto broker. `on_message` is the method use to show the message received via the mosquitto broker. – Fang Mar 07 '17 at 08:25
  • yeah so the `on_message` method should be called every time you receive a message and thus you are calling `df.to_csv("testing.csv")` multiple times. This overwrites the previous data – Nulljack Mar 07 '17 at 21:43
  • http://stackoverflow.com/questions/17134942/pandas-dataframe-output-end-of-csv details how to append stuff to csv files using panda instead of overwriting the files @Fang – Nulljack Mar 07 '17 at 21:45
  • Thank you for your suggestion. It is partially worked. Can you elaborate more on `initialize a dataframe in on_connect`? Do you mean initializing an empty dataframe like this -> `df = pd.DataFrame([])` ? and in `on_message` method use command to append like this? -> `df2 = pd.DataFrame(data=datas, columns=['numbers’]) \ df.append(df2)` ? – Fang Mar 08 '17 at 03:31
  • I would just use the suggestion from http://stackoverflow.com/questions/17134942/pandas-dataframe-output-end-of-csv. In the code above you're overwriting the csv file every time you receive a message. – Nulljack Mar 08 '17 at 04:09
  • changing `df.to_csv("testing.csv")` to `df.to_csv("testing.csv", mode='a')` should fix it. You said you were only partially able to fix the problem? – Nulljack Mar 08 '17 at 04:10
0

other thread was crowded so I moved my response here

try using the bellow code

import paho.mqtt.client as mqtt
import pandas as pd

# Move df here 
df = pd.DataFrame(columns=['the_number'])

def on_connect(client, userdata, rc):
    print("Connected with result code "+str(rc))
    client.subscribe("hello/world")

def on_message(client, userdata, msg):
    datas = map(int, msg.payload)

    # this adds the data to the dataframe at the correct index
    df.iloc[df.size] = datas

    # I reverted this line back to what you originally had 
    # This will overwrite the testing.csv file every time your subscriber
    # receives a message, but since the dataframe is formatted like you want 
    # it shouldn't matter 
    df.to_csv("testing.csv")


client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message

client.connect("localhost", 1883, 60)

client.loop_forever()
Nulljack
  • 21
  • 5