【R VS python 数据处理利器】SQL查询

最新推荐文章于 2024-09-06 00:29:15 发布

置顶东华果汁哥

最新推荐文章于 2024-09-06 00:29:15 发布

阅读量696

点赞数

分类专栏：数据科学--R语言

本文链接：https://blog.csdn.net/u013421629/article/details/79411938

版权

数据科学--R语言专栏收录该内容

45 篇文章 27 订阅

订阅专栏

1、R语言中可以使用sqldf实现SQL查询
2、python 中可以使用 pysqldf查询

R语言用sqldf查询数据


#####加载sqldf包
library(sqldf)
###########利用sql语句查询数据，并且行转列######################
df2 <- sqldf("select  

TIJIANBM,
case when XIANGMUBM=23 then TIJIANJG  end as '23',
case when XIANGMUBM=48 then TIJIANJG end as '48',
case when XIANGMUBM=79 then TIJIANJG end as '79',
case when XIANGMUBM=90 then TIJIANJG end as '90',
case when XIANGMUBM=102 then TIJIANJG end as '102',
case when XIANGMUBM=109 then TIJIANJG end as '109',
case when XIANGMUBM=2317 then TIJIANJG end as '2317',
case when XIANGMUBM=2642 then TIJIANJG end as '2642',
case when XIANGMUBM=2707 then TIJIANJG end as '2707',
case when XIANGMUBM=760 then TIJIANJG end as '760',
case when XIANGMUBM=761 then TIJIANJG  end as '761',
case when XIANGMUBM=1937 then TIJIANJG end as '1937',
case when XIANGMUBM=2161 then TIJIANJG end as '2161',
case when XIANGMUBM=762 then TIJIANJG end as '762',
case when XIANGMUBM=2328 then TIJIANJG end as '2328',
case when XIANGMUBM=2329 then TIJIANJG end as '2329'

from data_result 


where XIANGMUBM in(23,48,79,90,102,109,2317,2642,2707,760,761,1937,2161,762,2328,2329)")

python用pysqldf查询数据

pysqldf allows you to query pandas DataFrames using SQL syntax. It works similarly to sqldf in R. pysqldf seeks to provide a more familiar way of manipulating and cleaning data for people new to Python or pandas.
安装：

pip install pysqldf

from pysqldf import SQLDF,load_meat,load_births
births = load_births()
meat = load_meat()
sqldf = SQLDF(globals())
mysqldf = lambda q: sqldf.execute(q)

q1="""
SELECT m.date, m.beef, b.births FROM meat m INNER JOIN births b ON m.date = b.date;
"""
print mysqldf(q1)

运行结果：

"D:\Program Files\Python27\python.exe" D:/PycharmProjects/learn2018/数据处理2.py
                    date    beef  births
0    1975-01-01 00:00:00  2106.0  265775
1    1975-02-01 00:00:00  1845.0  241045
2    1975-03-01 00:00:00  1891.0  268849
3    1975-04-01 00:00:00  1895.0  247455
4    1975-05-01 00:00:00  1849.0  254545
5    1975-06-01 00:00:00  1849.0  254096
6    1975-07-01 00:00:00  1916.0  275163
7    1975-08-01 00:00:00  1961.0  281300
8    1975-09-01 00:00:00  2065.0  270738
9    1975-10-01 00:00:00  2270.0  265494
10   1975-11-01 00:00:00  1970.0  251973
11   1975-12-01 00:00:00  2055.0  260532
12   1976-01-01 00:00:00  2208.0  259173
13   1976-01-01 00:00:00  2208.0  257455
14   1976-02-01 00:00:00  1966.0  238153
15   1976-02-01 00:00:00  1966.0  236551
16   1976-03-01 00:00:00  2318.0  261608
17   1976-03-01 00:00:00  2318.0  257951
18   1976-04-01 00:00:00  2015.0  250992
19   1976-04-01 00:00:00  2015.0  246469
20   1976-05-01 00:00:00  1969.0  261572
21   1976-05-01 00:00:00  1969.0  256986
22   1976-06-01 00:00:00  2161.0  255734
23   1976-06-01 00:00:00  2161.0  250525
24   1976-07-01 00:00:00  2111.0  279744
25   1976-07-01 00:00:00  2111.0  279630
26   1976-08-01 00:00:00  2233.0  279937
27   1976-08-01 00:00:00  2233.0  286496
28   1976-09-01 00:00:00  2274.0  273750
29   1976-09-01 00:00:00  2274.0  283718
..                   ...     ...     ...
377  2008-06-01 00:00:00  2263.4  358251
378  2008-07-01 00:00:00  2371.6  367934
379  2008-08-01 00:00:00  2266.8  387798
380  2008-09-01 00:00:00  2269.9  374711
381  2008-10-01 00:00:00  2340.9  367354
382  2008-11-01 00:00:00  1959.3  351832
383  2008-12-01 00:00:00  2082.0  356111
384  2011-01-01 00:00:00  2122.9  356457
385  2011-02-01 00:00:00  2020.4  338521
386  2011-03-01 00:00:00  2266.2  350630
387  2011-04-01 00:00:00  2052.5  346397
388  2011-05-01 00:00:00  2131.9  354886
389  2011-06-01 00:00:00  2375.0  348587
390  2011-07-01 00:00:00  2134.1  375384
391  2011-08-01 00:00:00  2386.9  373333
392  2011-09-01 00:00:00  2215.2  367965
393  2011-10-01 00:00:00  2215.1  357875
394  2011-11-01 00:00:00  2148.8  323788
395  2011-12-01 00:00:00  2126.3  353871
396  2012-01-01 00:00:00  2113.8  337980
397  2012-02-01 00:00:00  2009.0  316641
398  2012-03-01 00:00:00  2159.8  347803
399  2012-04-01 00:00:00  1990.6  337272
400  2012-05-01 00:00:00  2232.0  345257
401  2012-06-01 00:00:00  2252.1  346971
402  2012-07-01 00:00:00  2200.8  368450
403  2012-08-01 00:00:00  2367.5  359554
404  2012-09-01 00:00:00  2016.0  361922
405  2012-10-01 00:00:00  2343.7  347625
406  2012-11-01 00:00:00  2206.6  320195

[407 rows x 3 columns]

Process finished with exit code 0