Re: [Tutor] pandas read sql query advice

[Albert-Jan Roskam]

On Apr 28, 2018 06:54, Glenn Schultz  wrote:
>
> All,
>
> I have the following set-up (below) which will be used to call data from 
> multiple sectors.  There is a sql query (transact sql) and connection.  This 
> works fine.  However, I would like to parametrize the query so I can enter 
> different sectors.  I have checked through pyodbc and several SO posts.  
> However, I am stuck and cannot get the parametrized version to work.  Most 
> examples I have found are either sqlite or postgres.
>
> sql ="""select foo from mutable where model sector = 'mysector'"""
> conn = myconn
>
> def modeldata(modelquery, conn)
> modeldata = pandas.read_sql_query(sql, conn)
> return modeldata
>
> Here is what I have tried (various combinations) - what am I doing wrong?
>
> sql ="""select foo from mutable where model sector = ?""", [params]
> conn = myconn
>
> def modeldata(modelquery, conn, params)
> modeldata = pandas.read_sql_query(sql, conn, params)
> return modeldata

pandas.read_sql_query(sql, conn, params=params)

The third positional arg is actually index_col, so you need to use a keyword 
arg. 
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html#pandas.read_sql_query

BTW, I think you can use a context manager to automatically close the 
connection:
with pyodbc.connect(your_conn_string) as conn:
df = pd.read_sql_query(sql, conn, params=params)

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Tutor] pandas read sql query advice

[Glenn Schultz]

All,

I have the following set-up (below) which will be used to call data from 
multiple sectors.  There is a sql query (transact sql) and connection.  This 
works fine.  However, I would like to parametrize the query so I can enter 
different sectors.  I have checked through pyodbc and several SO posts.  
However, I am stuck and cannot get the parametrized version to work.  Most 
examples I have found are either sqlite or postgres.

sql ="""select foo from mutable where model sector = 'mysector'"""
conn = myconn

def modeldata(modelquery, conn)
modeldata = pandas.read_sql_query(sql, conn)
return modeldata

Here is what I have tried (various combinations) - what am I doing wrong?

sql ="""select foo from mutable where model sector = ?""", [params]
conn = myconn

def modeldata(modelquery, conn, params)
modeldata = pandas.read_sql_query(sql, conn, params)
return modeldata

Thanks in advance,
Glenn
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Tutor] [Pandas] - Creating a bigger dataframe by using one minor dataframe as variable of another minor dataframe

[Henrique C. S. Junior]

I’m working with two dataframes obtained from the infrared spectroscopy
analysis  (yes, we’re scientists trying to work better using python).
The dataframes are as follows:

***DATA1:***

IRRAMAN   CM-1
 245.54  730.41  3538.10   s1 100
   3.93  204.17  3237.13   s6 93
  11.13  477.42  3233.43   s3 14   s5 76
   3.44  136.83  3229.53   s3 -78   s5 15
   6.40  363.33  3219.42   s7 94
   8.02  296.03  3217.14   s2 -90
   6.13   80.90  3209.69   s9 93
   3.43  166.41  3204.74   s4 -92
   8.91  146.43  3203.92   s8 94
26.77  201.97  3168.99   s10 82   s12 -18


***DATA2:***

s 1 1.00   STRE   14   21   NH1.015055  f3538 100
s 2 1.00   STRE1   22   CH1.081994  f3217 90
s 3-1.00   STRE1   22   CH1.081994  f3233 14  f3230 78
s 4 1.00   STRE4   23   CH1.082576  f3205 92
s 5 1.00   STRE   19   26   CH1.080387  f3237 93
s 7 1.00   STRE   17   28   CH1.083210  f3219 94
s 8 1.00   STRE   17   28   CH1.083210  f3204 94
s 571.00   BEND   11   12   14   CCN   129.85  f940 20
s 581.00   BEND   19   18   15   CCC   120.65
s 59   -1.00   BEND   15   17   20   CCC   120.28  f1037 32  f842 10
s 601.00   BEND   118   10   CCO   122.22  f402 38
s 61   -1.00   BEND8   11   13   CCCl   114.81  f221 60  f194 11d
s 931.00   OUT30   19   20   16   OCCC 0.17  f864 14  f746
11  f535 17  f517 11
s 941.00   OUT 7613    0.48  f829 10  f172
41
s 951.00   OUT17   14   18   15   CNCC 3.64  f564 16  f535
15  f396 10
s 961.00   OUT106   118   OCCC 0.74  f822 63

At this point, they are both (after a LOT of work) converted into a pandas
dataframe but, as a final result, I’d like to create one big dataframe
because those two are related by the variables s1, s2, s3, s4 and so on.
So, I’d like to insert the variables s1, s2, s3, sn every time they appear
in the DATA1:

***DATA1 with DATA2:***
*(I tried to use ** to show you every time DATA 2 was inserted into DATA1)*

IRRAMAN   CM-1
 245.54  730.41  3538.10   *s1* 100 ***STRE   14   21   NH1.015055*
**
   3.93  204.17  3237.13   *s6* 93 ***STRE   19   26   CH1.080387*
**
  11.13  477.42  3233.43   *s3* ***14 STRE1   22   CH1.081994***
 *s5* 76 ***STRE   19   26   CH1.080387***
   3.44  136.83  3229.53   *s3* -78 ***STRE1   22   CH1.081994***
*s5* 15  ***STRE   19   26   CH 1.080387***

Observe that some lines can have more than two variables.
I have to admit that I’m completely lost using pandas right now and any
help is much appreciated.

Thanks in advance.

-- 
*Henrique C. S. Junior*
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Tutor] pandas

[banda gunda]

Dear tutor,


python3

pandas


The function below returns the maximum cost row of the df.


def maximum():
return [df.loc[df['cost'].idxmax()]]

 maximum()


But I am only interested in specific column of the df.

How could I extract from the returned row of the df?


Thanks in advance.

Best.

banda

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] pandas data frame

[Bachir Bachir via Tutor]

 Hi Peter Thank you much for your help its very appreciated ,bellow is an 
example of what i need.   The main dataframe ( need to be separated into 
separate dataframes  ). The desired dataframes output correctly as i need it to 
be,Thanks much
Main dataframendx     V_id        Average        Mean       Peak 0         1    
          3                      2          51         2              2         
             1          62         3              4                     1       
   83         1              2                      2          74         2     
         3                     3          65         3              5           
          3          46         1              1                     1          
87         2              2                     5          108         3        
      5                     5          99         1              2              
       5          1010        2             5                     5          
911        3             4                     3          10
Dataframe-1ndx     V_id   Average     Mean    Peak 0         1           3      
        2          53         1           2              2          76         
1           1              1          89         1           2              5   
       10
Dataframe-2ndx     V_id    Averag    Mean     Peak 1          2           2     
         1          64          2           3              3          67        
  2           2              5          1010        2           5              
5          9
Dataframe-3ndx     V_id    Average    Mean    Peak 2         3           4      
        1          85         3           5              3          48         
3           5              5          911        3           4              3   
       10
Bachir

 

On Monday, January 18, 2016 11:25 AM, Peter Otten <__pete...@web.de> wrote:
 

 Bachir Bachir wrote:

[Bachir, please send your mails to the list, not to me. That way you 
increase the likelihood to get a good answer]

>>    On Sunday, January 17, 2016 4:20 PM, Peter Otten <__pete...@web.de>
>> wrote:
>>  Bachir Bachir via Tutor wrote:
>>> Hello EverybodyI need to sort a dataframe according to a specific column
>>> the create new dataframes according to the sorted columns each new
>>> created dataframe should contain the list(set('the sorted element)any
>>> help please, i am new in python and pandas thanks muchBachir
>> 
>> Like this?
>> 
>> >>> df
>> 
>>  foo bar
>> 0  3.3  b
>> 1  2.2  b
>> 2  7.5  a
>> 3  1.1  c
>> 4  4.7  a
>> 
>> [5 rows x 2 columns]
>> 
>> >>> df.groupby("bar").sum()
>> 
>>      foo
>> bar    
>> a    12.2
>> b    5.5
>> c    1.1
>> 
>> [3 rows x 1 columns]

> Thanks Peter ,This way but i  want keep all the table elements only
> separate them  by a single column 

I'm sorry, I don't understand what you mean by "separate them by a single 
column". Can you give an example? What exactly should the sorted version of

>> >>> df
>> 
>>  foo bar
>> 0  3.3  b
>> 1  2.2  b
>> 2  7.5  a
>> 3  1.1  c
>> 4  4.7  a
>> 
>> [5 rows x 2 columns]

look like?

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor


  
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] pandas data frame

[Peter Otten]

Bachir Bachir wrote:

[Bachir, please send your mails to the list, not to me. That way you 
increase the likelihood to get a good answer]

>> On Sunday, January 17, 2016 4:20 PM, Peter Otten <__pete...@web.de>
>> wrote:
>>  Bachir Bachir via Tutor wrote:
>>> Hello EverybodyI need to sort a dataframe according to a specific column
>>> the create new dataframes according to the sorted columns each new
>>> created dataframe should contain the list(set('the sorted element)any
>>> help please, i am new in python and pandas thanks muchBachir
>> 
>> Like this?
>> 
>> >>> df
>> 
>>   foo bar
>> 0  3.3  b
>> 1  2.2  b
>> 2  7.5  a
>> 3  1.1  c
>> 4  4.7  a
>> 
>> [5 rows x 2 columns]
>> 
>> >>> df.groupby("bar").sum()
>> 
>>   foo
>> bar 
>> a12.2
>> b5.5
>> c1.1
>> 
>> [3 rows x 1 columns]

> Thanks Peter ,This way but i  want keep all the table elements only
> separate them  by a single column 

I'm sorry, I don't understand what you mean by "separate them by a single 
column". Can you give an example? What exactly should the sorted version of

>> >>> df
>> 
>>   foo bar
>> 0  3.3  b
>> 1  2.2  b
>> 2  7.5  a
>> 3  1.1  c
>> 4  4.7  a
>> 
>> [5 rows x 2 columns]

look like?

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] pandas data frame

[Peter Otten]

Bachir Bachir via Tutor wrote:

> Hi Peter Thank you much for your help its very appreciated ,bellow is an
> example of what i need.   The main dataframe ( need to be separated into
> separate dataframes  ). The desired dataframes output correctly as i need
> it to be,Thanks much Main dataframendx V_idAverageMean
>   Peak 0 1  3  2  51  
>   2  2  1  62 3   
>   4 1  83 1  2
>  2  74 2  3 3 
> 65 3  5 3  46 
>1  1 1  87 2   
>   2 5  108 3  5   
>  5  99 1  2 5 
> 10102 5 5  911   
> 3 4 3  10 Dataframe-1ndx V_id 
>  Average MeanPeak 0 1   3  2 
> 53 1   2  2  76 1   1 
> 1  89 1   2  5  10
> Dataframe-2ndx V_idAveragMean Peak 1  2  
> 2  1  64  2   3  3
>  67  2   2  5  10102  
> 5  5  9 Dataframe-3ndx V_idAverageMean   
> Peak 2 3   4  1  85 3 
>  5  3  48 3   5  5
>  9113   4  3  10 Bachir

I see that you got an answer over at comp.python.pydata where you managed to 
get the format a bit more readable ;)

I recommend that you go with Goyo's answer, but instead of the hacky use of
locals() 
 
> >>> locals().update((('dataframe_{}'.format(k), grouper.get_group(k))
> ...  for k in grouper.groups))

I recommend that you put the groups into a normal dict

groups = {k: g.get_group(k) for k in g.groups}

and then access the partial data frames with

v_id = 2
groups[v_id]

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Tutor] pandas data frame

[Bachir Bachir via Tutor]

 Hello EverybodyI need to sort a dataframe according to a specific column the 
create new dataframes according to the sorted columns each new created 
dataframe should contain the list(set('the sorted element)any help please , i 
am new in python and pandas thanks muchBachir
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] pandas data frame

[Peter Otten]

Bachir Bachir via Tutor wrote:

> Hello EverybodyI need to sort a dataframe according to a specific column
> the create new dataframes according to the sorted columns each new created
> dataframe should contain the list(set('the sorted element)any help please
> , i am new in python and pandas thanks muchBachir

Like this?

>>> df
   foo bar
0  3.3   b
1  2.2   b
2  7.5   a
3  1.1   c
4  4.7   a

[5 rows x 2 columns]
>>> df.groupby("bar").sum()
  foo
bar  
a12.2
b 5.5
c 1.1

[3 rows x 1 columns]


___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] pandas data frame

[Alan Gauld]

On 17/01/16 11:43, Bachir Bachir via Tutor wrote:
> i am new in python and pandas thanks muchBachir

Hi Bachir,

This list is for questions about core Python and its standard
library. Although pandas is part of some Python distributions
(eg anaconda) it's not really part of the standard library but
part of SciPy.

There is a dedicated support forum for SciPy and you might
get more responses posting there.

http://scipy.org/scipylib/mailing-lists.html

However, a few folks on this list do use pandas so you might
get lucky here. :-)

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos


___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor