用SQL玩轉R數據框

2021-01-21 TalkingData數據學堂
技多不壓身，跨界融合更是對十八般武藝的一種考驗，SQL是一種技能，一樣可以在R的舞臺上展示獨特的魅力，一起看看SQL處理數據的長袖有怎麼樣的舞藝。SQL 是結構化查詢語句，是一種資料庫查詢和程序設計語言。數據分析從業者的主要技能之一，普及程度不低於R語言。當我們用R語言處理數據：檢索，排序，篩選的時候怎覺得力不從心。怎樣用SQL來處理R中的數據呢？用SQL的優勢來武裝R的數據處理更能錦上添花，魚和熊掌兼得。當然我們可以引用R中的sqldf包，讓SQL在R飛起，處理數據就這麼簡單。從簡單的數據來探索有趣的知識，用R組合出關於學生的數據框（student_dframe），比如學號-sid，性別-sex，年齡-age，班級-class，成績等級-level：sid<-c(1,2,3,4,5,6)sex<-c('男', '女','男', '女','男', '女')age<-c(18,24,45,18,19,22) class<-c('class1', 'class1', 'class2','class2', 'class3', 'class3') level<-c('Poor', 'Improved', 'Excellent', 'Poor', 'Improved', 'Excellent')student_dframe <- data.frame(sid, sex, age, class,level)#SQL：表示SQL語法，#R：表示R語法。對比二者同結果操作的不同之處，在比較中提升學習的效率。time is money！！！

library(dplyr)library(sqldf)library(data.table)library(ggplot2)library(compare)library(plotrix)
#SQL：sqldf(" SELECT COUNT(sid) as 'Number of rows' FROM student_dframe; ") #SQL ：sqldf("SELECT * FROM student_dframe LIMIT 3;") #R：head(student_dframe, 3)SQL2=sqldf("SELECT * FROM student_dframe WHERE sex ='男';")R2 = filter(student_dframe, sex=="男")identical(SQL2, R2)3.4，GROUP BY and ORDER BY 分組與排序SQL3 = sqldf("SELECT sex, COUNT(sid) as Total FROM student_dframe WHERE sex IN ('男','女')GROUP BY sex ORDER BY Total DESC ;")R3=student_dframe%>%filter(sex %in%c('F','M','NS','UNK'))%>%group_by(sex) %>%summarise(Total = n())%>%arrange(desc(Total))sid<-c(1,2,3,4,5,6) # 學號一樣 sname<-c('張三', '李四', '王二', '狗蛋', '毛線', '賈伯斯') student_name <- data.frame(sid, sname)SQL4=sqldf("SELECT sd.*, sn.sname as sname FROM student_dframe sdINNER JOIN  student_name snON sd.sid=sn.sid ORDER BY sd.sid, sn.sname")R4 = merge(student_dframe, student_name,by=intersect(names(student_dframe), names(student_name)))compare(R4,SQL4,allowAll = TRUE)student_dframe1<-student_dframe[1:3,] #重複利用數據框 前3行R5 <- rbind(student_dframe, student_dframe1)SQL5 <- sqldf("SELECT  * FROM student_dframe UNION ALL SELECT * FROM student_dframe1;")compare(R5,SQL5, allowAll = TRUE)student_dframe1<-student_dframe[1:3,]R6 <- semi_join(student_dframe, student_dframe1) SQL6 <- sqldf("SELECT  * FROM student_dframe INTERSECT SELECT * FROM student_dframe1;")compare(R6,SQL6, allowAll = TRUE)R7 <- anti_join(student_dframe, student_dframe1) SQL7 <- sqldf("SELECT  * FROM student_dframe EXCEPT SELECT * FROM student_dframe1;") compare(R7,SQL7, allowAll = TRUE)SQL對R數據框檢索，排序，篩選後的數據結果依然是一個數據框，這麼我們也可以直接作圖，讓數據可視化。SQL = sqldf("SELECT sex, COUNT(sid) as Total FROM student_dframe WHERE sex IN ('男','女') GROUP BY sex ORDER BY Total DESC ;")SQL$Total=as.numeric(SQL$Total)pie(SQL$Total, labels =SQL$sex,explode=0.1,col=rainbow(4), main="性別分布餅圖",cex.lab=0.5, cex.axis=0.5, cex.main=1,labelcex=1, family='SimSun')ggplot(sqldf('SELECT age, sex  FROM student_dframe WHERE age between 0 AND 100  ;'),aes(x=age, fill = sex), family='SimSun')+ geom_density(alpha = 0.6)ggplot(sqldf('SELECT age, sex FROM student_dframe union all SELECT age, sex FROM student_dframe1 union all SELECT age, sex FROM student_dframe1union allSELECT age, sex FROM student_dframe1'), aes(x=age, fill = sex), family='SimSun')+geom_bar(alpha=0.6)

本文經授權轉載自 趣味數據周刊，作者herain

封面圖來源：Photo by Franki Chamaki on Unsplash

相關焦點

  • R+SQL Server的大數據管理
    這是大數據的問題嗎？怎麼那麼不小心就被我碰上了？今天我們就談談「大數據」這個老話題。自2012年以來，大數據(Big Data)已經上過《紐約時報》《華爾街日報》的專欄封面，進入美國白宮官網的新聞，在國內更是被炒得熱火朝天，網際網路、工商業、高校等都紛紛進行探索討論。大數據戰略甚至成為了我們國家「十三五」十四大戰略之一。
  • R語言SQL管理資料庫的方法
    #使用SQL語句操作數據框，需要加載的程序包sqldf,tcltk，使用iris數據集以及演示library(sqldf)library(tcltk)head(iris)#了解數據集由5各變量組成#取出前幾行a1r <- head(iris,10)#一般方法
  • 深入對比數據科學工具箱: SparkR vs Sparklyr
    spark_home = "/data/FinanceR/Spark",                             version = "2.2.0",                             config = sparklyr::spark_config())       以寫Parquet文件為例,同理你可以用
  • 大數據分析工程師入門9-Spark SQL
    早期Spark的切入點是SparkContext，通過它來創建和操作數據集，對於不同的API需要不同的context。比如：使用sql-需要sqlContext，使用hive-需要hiveContext，使用streaming-需要StreamingContext。
  • Java最新SQL注入原因以及預防方案(易理解)
    前沿在現有的框架中sql防注入已經做得很好了，我們需要做的就是儘量不要使用sql拼接調用java sql注入原因以及預防方案(易理解)1.2 演示1.2.1 案例1有一個登錄框，需要 輸入用戶名和密碼 ，然後我們的密碼輸入 'or '123' = '123 這樣的。我們在查詢用戶名和密碼是否正確的時候，本來執行的sql語句是：select * from user where username = '' and password = ''.
  • 在R中子集化數據框的5種方法
    在 R 中對數據框（data frame）進行子集化的最基本方法是使用方括號：example[x, y]example 是我們想要從中提取子集的數據框；』x』是我們想要提取的子集的行；』y』 是我們想要提取的子集的列。 讓我們從網上提取一些數據，看看它是如何在真實的數據集上實現的。
  • R語言筆記1:數據類型(向量、數組、矩陣、 列表和數據框)
    學習R語言，需要先安裝R語言，只需要從 https://www.r-project.org/ 下載適合你系統的最新版本軟體安裝即可。R語言有個優秀的環境叫Rstudio，具體安裝可參考  《R語言學習 - 入門環境Rstudio》一文。R語言中的數據類型（Data Types）R語言的對象（Objects）主要包括向量、矩陣、數組、數據框和列表。
  • SQL Server2008中的9種數據挖掘算法淺析
    【IT168 技術文檔】　　在sql server2008中提供了9種常用的數據挖掘算法，這些算法用在不同數據挖掘的應用場景下，下面我們就各個算法逐個分析討論。　　1.決策樹算法　　決策樹，又稱判定樹，是一種類似二叉樹或多叉樹的樹結構。
  • 對SQL Server跨文件組的表進行分區
    實驗設置　　實驗場景　　Adventure Works Cycles 的數據倉庫中有幾個非常大的表。為改善此數據的存儲，您決定對幾個跨文件組的表進行分區。這樣您就可以控制基礎磁碟存儲。　　對幾個跨文件組的表進行分區時，您希望增添功能並改善您的解決方案的性能。您決定基於訂單日期分配數據，而不是隨意地跨文件組分割數據。
  • WIN10 sql server2008r2資料庫安裝圖解
    1、WIN10安裝sql server2008r2 先關閉防火牆2、打開安裝嚮導點擊左邊欄安裝選擇「全新安裝或向先有安裝添加功能」等待3、安裝程序支持規則確定3、安裝程序支持文件 安裝4、安裝程序支持規則，看一下是否全部通過，點擊下一步
  • 數據可視化之R語言
    3.折線圖折線圖對於分類數據和連續型數據都適用，如果數據為連續數據，則使用plot()命令就可實現，只要設置type=選項即可，如：> plot(Nile,type="l")如果數據的順序是任意的，那麼做出的折線也是任意的，這樣無法觀察趨勢
  • 這個函數讓SQL效率提升99%
    正文共：2335 字 4 圖 預計閱讀時間：5 分鐘我是firewang: 用技術讓數據說話，用數據讓你我進化。簡介窗口函數（window function）， 也可以被稱為 OLAP函數 或 分析函數。
  • 如何向mysql導入數據
    一、導入.sql文件1.mysql命令導入數據基本語法：mysql -h伺服器地址 -u用戶名 -p 資料庫名< 要導入的資料庫文件路徑例：導入G:/mydb.sql2.source命令導入數據基本語法：source 資料庫文件路徑;例：導入G:/mydb2.sql
  • PandaSQL:一個讓你能夠通過SQL語句進行pandas的操作的python包
    Pandas是近年來最好的數據操作庫之一。它允許切片、分組、連接和執行任意數據轉換。如果你熟練的使用SQL,那麼這篇文章將介紹一種更直接、簡單的使用Pandas處理大多數數據操作案例。假設你對SQL非常的熟悉，或者你想有更可讀的代碼。或者您只是想在dataframe上運行一個特殊的SQL查詢。或者，也許你來自R，想要一個sqldf的替代品。
  • 如何用python在工作中「偷懶」?
    Excel自動化處理Excel合併在實際應用中可能會有不同月份的數據或者不同周的報告等等的Excel數據，都是單個獨立的文件，如果想要整體使用的話就需要合併一下，那麼如何利用python把指定目錄下的所有Excel數據合併成一個文件呢？
  • SQL Server 首次登陸 Linux 平臺
    近年來， SQL Server 正在一直演化，除了想一改 DMS（資料庫管理系統）的角色，還想介入到數據分析、機器學習和數據科學領域。2017 年 4 月份， SQL Server 發布了一個重要組件，支持在 SQL Server 中用 Python 運行機器學習負載。
  • 數據科學養成記 之 R語言基礎(3)——excel數據導入
    經過昨天對包的學習，今天我們利用前兩節課學習的知識來對excel數據進行導入。2.利用RODBDC包讀取excelb <- odbcConnectExcel2007('exercise1.xls')#32位作業系統使用odbcConnectExcel函數sqlTable
  • PL/SQL中如何導出表結構和表數據
    在資料庫操作中，很多時候會發現資料庫不兼容，但原來的資料庫中又有很多數據，表結構也很複雜，這時候就會想到將這個表中的數據和表結構都移植到另一個資料庫中，在PL/SQL的工具欄中，可以進行資料庫表移植和數據轉移。
  • 微軟 SQL Server 2016 SP1 發布
    資料庫克隆 - 克隆資料庫是一個新的 DBCC 命令，允許 DBA 並支持團隊通過克隆的模式和元數據來解決現有的生產資料庫的沒有數據統計的故障。克隆資料庫並不意味著在生產環境中使用。以編程方式標識 LPIM 到 SQL 服務帳戶 - DMV sys.dm_os_sys_info 中的新 sql_memory_model、sql_memory_model_desc 列，允許
  • 小白學數據小抄放送 Python,R,大數據,機器學習
    它提供了Python學習的必備包和一些有用的學習技巧等資源。 2. Python基礎小抄表 這張由Datacamp製作的小抄表覆蓋了所有Python數據科學需要的基礎知識。如果你剛開始用Python，可以留著這張做快速參考。背下這些小抄的代碼變量、數據類型函數、字符串操作、類型轉換、列表和常用操作。尤其是它列出了重要的Python包，給出了用於選擇並導入包的小抄代碼。