1

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

MDR
  • 2,610
  • 1
  • 8
  • 18
Birdman
  • 11
  • 5
  • Looks like you are dumping the whole dataframe tolist. Is it just one column that needs to go to a list? If so use `Allocation_list = mydata['OneOfTheColumnNames'].to_list()` where `OneOfTheColumnNames` is a valid column name in your dataframe. – MDR Aug 07 '21 at 00:26
  • Thanks for the quick response. In fact, I am using Pandas to read a specified range of rows, from just one specified column of the the spreadsheet. So the dataframe has just two columns (the number assigned to the data, on the left; and the data of concern, on the right) – Birdman Aug 07 '21 at 00:32
  • Still, you should reference the one column. Spot the difference on output from the two ways: https://ibb.co/n6zfZmd – MDR Aug 07 '21 at 00:39
  • So, I did not assign column names when I imported data from the Excel sheet. I have not seen a way to assign column names when using the pd.read.excel function – Birdman Aug 07 '21 at 00:54
  • Makes it trickier when starting out but you can use `mydata.columns[0]` so to a list for certain rows would be `Allocation_list = df.loc[2:4,mydata.columns[0]].to_list()`. Just change the `2` and the `4` for the row range required but note that the index starts at `0`. Hence `2:4` would get rows 3 to 5. – MDR Aug 07 '21 at 00:59
  • I attempted to display ('print') the column names using several methods shown in on-line tutorials....but apparently there are no names. Not even default names assigned by Pandas. So, how do I force the data names onto the data frame during the Excel importation? – Birdman Aug 07 '21 at 01:04
  • I've import the data from Excel spreadsheet using mydata = pd.read_excel ('C:/the/path/to/the/sheet.xls', sheet_name='Book1', skiprows = 3, nrows = 6, usecols ="J") -----is there a syntax to force column names in this command? – Birdman Aug 07 '21 at 01:07
  • `mydata.columns = ['Col1']`. If you have more columns, say three, then: `mydata.columns = ['Col1', 'Col2', 'Col3']` (or something more helpful). Comments are getting too long. Best try the suggestions in the answers, upvote, mark one as the answer (if helpful) and continue on. If you are stuck on something else post a new question. :o) – MDR Aug 07 '21 at 01:09

2 Answers2

0

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:

enter image description here

MDR
  • 2,610
  • 1
  • 8
  • 18
  • Summary:it appears that the command 'pd.read_excel' doesn't allow the user to assign column names to the data – Birdman Aug 07 '21 at 01:21
  • Summary: 1) it appears that the command 'pd.read_excel' doesn't allow the user to assign column names to the data; 2) One can assign column names to the dataframe after pd.read_excel is executed; 3) the assignment looks like mydataframe.columns = ['Bob', 'Alice', 'Jim', etc etc] 4) once the names are assigned to the columns, convert only the named column of interest to the LIST, using my_list_name=mydataframe['Bob'].to_list() . I have tried this method, and it has worked---no extra [ ] and no extra ' ' "" – Birdman Aug 07 '21 at 01:27
0

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.

Max Shouman
  • 1,333
  • 1
  • 4
  • 11