pass along rows and columns to find the value at their intersection

[Владимир]

Hi! I'm just learning

I have a table. How I can get a value on cell of intersection rows and columns?
I must insert value, code and service into database.

                            code0         code1       code2        code3         ....
service0   text     value0       value0:1    value0:2     value0:3      ...
service1   text    value1:0     value1:1    value1:2     value1:3      ...
service2   text    value2:0     value2:1    value2:2     value2:3      ...
service3   text    value3:0     value3:1    value3:2     value3:3      ...
service4      ....                 ...               ...              ...
....



Thanks.

[Adrian Klaver]

See Tutorial:

http://www.simplistix.co.uk/presentations/python-excel.pdf

Page 10

I would suggest just going through the tutorial, it will bring you up to
speed on how xl* works.

>
>
>
> Thanks.
>
> --
> You received this message because you are subscribed to the Google
> Groups "python-excel" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to python-excel...@googlegroups.com
> <mailto:python-excel...@googlegroups.com>.
> To post to this group, send email to python...@googlegroups.com
> <mailto:python...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/python-excel.
> For more options, visit https://groups.google.com/d/optout.


--
Adrian Klaver
adrian...@aklaver.com

[Владимир]

Thanks, but this code take only a value and dont take a number of rows and columns
I think take a 'service' in dictionary and 'code' on dictionary and sort out their values.
it is possible?


четверг, 18 декабря 2014 г., 13:09:11 UTC+2 пользователь Владимир написал:

[Adrian Klaver]

On 12/22/2014 11:58 PM, Владимир wrote:
> Thanks, but this code take only a value and dont take a number of rows
> and columns
> I think take a 'service' in dictionary and 'code' on dictionary and sort
> out their values.
> it is possible?

Well a spreadsheet is just a grid of rows and columns where the
intersection of a row and a column (a cell) can be anything. So to do
what I think you want you will need to iterate over the cells and look
for the presence of 'service' and 'code'. If you know the spreadsheet is
static then you can limit that iteration to the desired column(s) and
rows(s). What I usually do is walk through the header row and create a
dictionary that maps the column headers to an index and then use that to
match the incoming data to a column. The same can be done for the row
labels in a column. Whatever you do at some point you will be working on
a (x,y) cell identifier. If you continue on with the tutorial from the
page I sent previously you will see more examples that illustrate this.

>
>
> четверг, 18 декабря 2014 г., 13:09:11 UTC+2 пользователь Владимир написал:
>
> Hi! I'm just learning
>
> I have a table. How I can get a value on cell of intersection rows
> and columns?
> I must insert value, code and service into database.
>
> code0 code1 code2
> code3 ....
> service0 text value0 value0:1 value0:2
> value0:3 ...
> service1 text value1:0 value1:1 value1:2
> value1:3 ...
> service2 text value2:0 value2:1 value2:2
> value2:3 ...
> service3 text value3:0 value3:1 value3:2
> value3:3 ...
> service4 .... ...
> ... ...
> ....
>
>
>
> Thanks.
>

[Владимир Дроменко]

Thank you for your answers, Adrian!

Ок, I take 'services' and 'codes' :

    for col_index in xrange(0,1):
        for row_index in xrange(9,sheet.nrows):
            service = "%03d" % (float(sheet.cell_value(row_index,col_index)))

    for row_index in xrange(7,8):
        for col_index in xrange(3,sheet.ncols):
            code = int(sheet.cell(row_index,col_index).value)

And now I must create a dictionary.
What key of dictionary I must use?

an email to python-excel+unsubscribe@googlegroups.com
<mailto:python-excel+unsub...@googlegroups.com>.

To post to this group, send email to python...@googlegroups.com

<mailto:python-excel@googlegroups.com>.

Visit this group at http://groups.google.com/group/python-excel.
For more options, visit https://groups.google.com/d/optout.

--
Adrian Klaver
adrian...@aklaver.com

--
You received this message because you are subscribed to a topic in the Google Groups "python-excel" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/python-excel/cZxhlprdsQQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to python-excel+unsubscribe@googlegroups.com.
To post to this group, send an email to python...@googlegroups.com.

[Adrian Klaver]

On 12/23/2014 09:15 AM, Владимир Дроменко wrote:
> Thank you for your answers, Adrian!
>
> Ок, I take 'services' and 'codes' :
>
> for col_index in xrange(0,1):
> for row_index in xrange(9,sheet.nrows):
> service = "%03d" %
> (float(sheet.cell_value(row_index,col_index)))

First of why xrange(0, 1)?

Why not?:

for row_index in xrange(9,sheet.nrows):
service = "%03d" % (float(sheet.cell_value(row_index, 0))

>
> for row_index in xrange(7,8):
> for col_index in xrange(3,sheet.ncols):
> code = int(sheet.cell(row_index,col_index).value)
>
> And now I must create a dictionary.
> What key of dictionary I must use?

Not sure, it still not clear to me what you are trying to do?

Alright, went back and read your previous post, so I think:

Given

code0 code1 code2 code3 ....
service0 text value0 value0:1 value0:2 value0:3 ...
service1 text value1:0 value1:1 value1:2 value1:3 ...

and

"I must insert value, code and service into database."

then you need to build some sequence that has as each 'record' another sequence
that contains (service_value, code_value, cell_value). Not sure what your code
that inserts into the database needs to see for each record, a list, tuple or
dictionary. In any case the general principle is the same:

Untested code based on assumptions, no warranty given:)

Walk through the code header:

code_dict = {}
for col_index in xrange(3,sheet.ncols):
code = int(sheet.cell(7,col_index).value)
code_dict[col_index] = code
This builds a look up for the code values.

Then assuming dictionary records

records_list = []

for row_index in xrange(9,sheet.nrows):
service_val = sheet.cell(0, row_index).value
for col_index in xrange(4,sheet.ncols):
cell_val = sheet.cell(row_index, col_index).value
record_dict = dict(service=service_val, code=code_dict[col_index], cell=cell_val)
records_list.append(record_dict)

>
> 2014-12-23 17:45 GMT+02:00 Adrian Klaver <adrian...@aklaver.com

> <mailto:adrian...@aklaver.com>>:

> an email to python-excel+unsubscribe@__googlegroups.com
> <mailto:python-excel%2Bunsu...@googlegroups.com>
> <mailto:python-excel+...@googlegroups.com
> <mailto:python-excel%2Bunsu...@googlegroups.com>>.

> To post to this group, send email to

> python...@googlegroups.com <mailto:python...@googlegroups.com>
> <mailto:python-excel@__googlegroups.com
> <mailto:python...@googlegroups.com>>.
> Visit this group at
> http://groups.google.com/__group/python-excel
> <http://groups.google.com/group/python-excel>.
> For more options, visit https://groups.google.com/d/__optout
> <https://groups.google.com/d/optout>.
>
>
>
> --
> Adrian Klaver
> adrian...@aklaver.com <mailto:adrian...@aklaver.com>

>
> --
> You received this message because you are subscribed to a topic in
> the Google Groups "python-excel" group.
> To unsubscribe from this topic, visit

> https://groups.google.com/d/__topic/python-excel/__cZxhlprdsQQ/unsubscribe
> <https://groups.google.com/d/topic/python-excel/cZxhlprdsQQ/unsubscribe>.

> To unsubscribe from this group and all its topics, send an email to

> python-excel+unsubscribe@__googlegroups.com
> <mailto:python-excel%2Bunsu...@googlegroups.com>.
> To post to this group, send an email to
> python...@googlegroups.com <mailto:python...@googlegroups.com>.
>
> Visit this group at http://groups.google.com/__group/python-excel
> <http://groups.google.com/group/python-excel>.
> For more options, visit https://groups.google.com/d/__optout
> <https://groups.google.com/d/optout>.

>
>
> --
> You received this message because you are subscribed to the Google
> Groups "python-excel" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to python-excel...@googlegroups.com
> <mailto:python-excel...@googlegroups.com>.

> To post to this group, send email to python...@googlegroups.com

> <mailto:python...@googlegroups.com>.

[Adrian Klaver]

On 12/23/2014 09:15 AM, Владимир Дроменко wrote:

> Thank you for your answers, Adrian!
>
> Ок, I take 'services' and 'codes' :
>
> for col_index in xrange(0,1):
> for row_index in xrange(9,sheet.nrows):
> service = "%03d" %
> (float(sheet.cell_value(row_index,col_index)))
>
> for row_index in xrange(7,8):
> for col_index in xrange(3,sheet.ncols):
> code = int(sheet.cell(row_index,col_index).value)
>
> And now I must create a dictionary.
> What key of dictionary I must use?
>

Another thought occurred to me while I was driving; that is to use
pandas to get the data out of Excel and into a data array. I do not know
if you use pandas:

http://pandas.pydata.org/

but it is great for data handling. It can read Excel files using xlrd.
See here for more detail:

http://pandas.pydata.org/pandas-docs/stable/io.html#io-excel

Once the file is read in you have a pandas DataFrame(2D array) to work
with. You can do array operations instead of looping operations. It is
also relatively easy to slice the array to get subsets of data.



--
Adrian Klaver
adrian...@aklaver.com

[Владимир]

"then you need to build some sequence that has as each 'record' another sequence
that contains (service_value, code_value, cell_value). Not sure what your code
that inserts into the database needs to see for each record, a list, tuple or
dictionary. In any case the general principle is the same: "

Yes, it is!

But code has a bug :)

add a picture

четверг, 18 декабря 2014 г., 13:09:11 UTC+2 пользователь Владимир написал:

Hi! I'm just learning

[Adrian Klaver]

On 12/24/2014 07:55 AM, Владимир wrote:
> <https://lh6.googleusercontent.com/-nfweGrIzDVo/VJrhnIM3e-I/AAAAAAAAAUs/x3D4T--74D0/s1600/1.jpg>

>
> "then you need to build some sequence that has as each 'record' another
> sequence
> that contains (service_value, code_value, cell_value). Not sure what
> your code
> that inserts into the database needs to see for each record, a list,
> tuple or
> dictionary. In any case the general principle is the same: "
>
> Yes, it is!
>
> But code has a bug :)

Hey, I said no warranty. The error is in here:

service_val = sheet.cell(0, row_index).value

Aah, I switched the rows and columns, should be:

service_val = sheet.cell(row_index, 0).value

>
> add a picture
>
>
> четверг, 18 декабря 2014 г., 13:09:11 UTC+2 пользователь Владимир написал:
>
> Hi! I'm just learning
>
> I have a table. How I can get a value on cell of intersection rows
> and columns?
> I must insert value, code and service into database.
>
> code0 code1 code2
> code3 ....
> service0 text value0 value0:1 value0:2
> value0:3 ...
> service1 text value1:0 value1:1 value1:2
> value1:3 ...
> service2 text value2:0 value2:1 value2:2
> value2:3 ...
> service3 text value3:0 value3:1 value3:2
> value3:3 ...
> service4 .... ...
> ... ...
> ....
>
>
>
> Thanks.
>

[Владимир]

Оh, thank you very much!
I print 'record_dict' and it is what I need.
Variable 'records_list' does not need me.

Tthank you!

четверг, 18 декабря 2014 г., 13:09:11 UTC+2 пользователь Владимир написал:

Hi! I'm just learning