Python Pandas Square Brackets LIST from STRING

Question

Warning: I am newbie to Python, Pandas, and PySerial....

I am reading values from an Excel spreadsheet using Pandas. The values in Excel are stored as Text, but contain both alphabetical and numeric characters. see Snip of Excel data

I import these using Pandas command mydata = pd.read_excel (*path etc goes here*) <<< (no problems are encountered with this function)

I can then print them using print(mydata) ....and the output looks the same as it appears in the Excel spreadsheet (i.e., there are no extra characters):

0  MW000000007150000300000;

1  MW000100009850000200000;

2  MW000200009860000200000;      #<<<<<<<< *Notice that there are NO square brackets and no extra Quotes*.

To send these data via the PySerial function serial.write to my RS-232 linked device, I am looping through the values which must (as I understand it...) be in a LIST format. So, I convert the data-field mydata into a LIST, by using the command Allocation_list=mydata.values.tolist()

If I print(Allocation_list), I find many square brackets and single quotes have been added, as you can see here:

Allocation_list =([['MW000000007150000300000;'], ['MW000100009850000200000;'], ['MW000200009860000200000;'], ['MW000300009870000200000;'], ['MW000400009880000200000;'], ['MW000500009890000200000;']])

These square brackets are NOT ignored when I <<serial.write>> the values in the LIST to my RS-232 device.

In fact, the values are written as (binary versions of....)

0 memory written as ['MW000000007150000300000;']

1 memory written as ['MW000100009850000200000;']

2 memory written as ['MW000200009860000200000;']

3 memory written as ['MW000300009870000200000;']

4 memory written as ['MW000400009880000200000;']

5 memory written as ['MW000500009890000200000;']

Unfortunately, for the RS-232 device to accept each of the lines written to it as a acceptable command, they must be in the precise command format for that device, which looks like

MW000000007150000300000; <<<<< the semi-colon is a required part of the syntax

So, the square brackets and the Quotation marks have to be removed, somehow.

Any help with this peculiar problem would be appreciated, as I have tried several of the methods described in other 'threads', and none of them seem to work properly because my datafield is a set of strings (which are converted to bits ONLY as they are about to be written to the RS-232 device). M

Answer 1

Even if you have a frame with just one column avoid this:

l = df.values.tolist()
l
#outputs:
[[40], [10], [20], [10], [15], [30]]

To avoid the issue include a column when outputting to a list:

l = df['amount'].to_list()
l
#outputs:
[40, 10, 20, 10, 15, 30]

If you want a range of rows use loc:

#put rows 3 to 5 (note the index starts at 0!) for only column 'amount' into a list
l = df.loc[2:4,'amount'].to_list()
l
#outputs:
[20, 10, 15]

Showing the code in full on a frame with only one column:

Answer 2

First off, values preserves the dimensionality of the object it's called upon, so you have to target the exact column that holds the serials, something like mydata["column_label"] (just check the relevant column label by printing the dataframe).

As for quotes, pyserial write() accepts bytes-like objects, so you might need to pass an encoded version of your string, using either b'string' or 'string'.encode("utf8") notation.