R - 每个文档的文章之间的相关性

Question

我尝试计算文章之间的相关性，以了解不同文章编号一起出现在文档(发票)中的频率。

我有一个来自 SQL 查询的表，其中包含两列:文档编号、文章编号
该表非常大，有 21k 行。

我有 5k 个文档编号和 700 篇不同的文章，如下所示的示例。
它是一个数据框“db_belege”。

    Document Number Article Number
1   14198915        00115027
2   14198915        00100288
3   14198915        00011754
4   14198917        00033908
5   14198917        00096478
6   14198917        00033835
7   14198917        00051912
8   14198917        00152477
9   14198917        00033831
10  14198917        00100279
11  14198917        00011754
... 14198924        00053366
5k  14200000        00053366

我的目的是创建一个表，其中包含行的文档和列的文章。我通过以下 r 代码为此准备了一个空矩阵:

#create distinct article list
db_dist_art <- dbFetch(dbSendQuery(conant, "SELECT DISTINCT [Artikelnummer] FROM documenttable WHERE conditions "))

#Number of aticles
dim(db_dist_art)[1]

#create distint document numbers list
db_dist_art <- dbFetch(dbSendQuery(conant, "SELECT DISTINCT [Belegnummer] FROM documenttable WHERE conditions "))

#Number of documents
dim(db_dist_bel)[1]

mat <- matrix(,dim(db_dist_bel)[1],dim(db_dist_art)[1])

这将生成请求的行和列的列表。

1/我现在如何根据 db_dist_art 和 db_dist_bel 命名列和行？

2/是否可以使用行和列指定来分配值？

我的目标是我将能够使用 cor() 函数。

3/或者还有另一种我在研究中没有找到的更容易的方法吗？

(如果可用，请引用相应的解决方案。由于一项研究，我是这个主题的新手，不确定我是否已经找到了正确的搜索词，但在问我的第一个问题之前，我搜索了很多。)

我的基于回复的解决方案

#First of all thank you very much for your awnser. the 2nd solution works without trouble. But i have issues to get the first one running below my adaption and results.
#straghtforward way from JBGruber

#Load/Install packages
library(DBI)
library(odbc)

#Establish connection
conant <- DBI::dbConnect(odbc::odbc(), 
                      Driver = "ODBC Driver 17 for SQL Server", 
                      Server = "Serveradress", 
                      Database = "databasename", 
                      encoding = "latin1",
                      Trusted_Connection = "yes")

#extract data for correlation

cordata <-  dbSendQuery(conant, "SELECT [Belegnummer] as 'Document_Number' ,[Artikelnummer] as 'Article_Number'  FROM tablename WHERE conditions ")
db_cordata <- dbFetch(cordata,)
dbClearResult(cordata)

#calculate correlation and store results in cor_result
library(tidyverse)
library(widyr)

cor_result <- db_cordata %>%
  pairwise_cor(Article_Number, Document_Number, upper = FALSE)

仍然与相关矩阵和 cor() 函数斗争

#to use cor():
#my adaption below, I had values 1 and "NULL" instead of NA so I adapted also the replace statement to the following below:

library(tidyverse)
mat <- db_cordata %>% 
  mutate(value = 1) %>% 
  pivot_wider(id_cols = Document_Number, names_from = Article_Number) %>%
  {
    mat <- as.matrix(.[, -1])
    rownames(mat) <- unlist(.[, 1])
    mat
  } %>% 
  replace(.,. == "NULL", 0)

# the matrix created seems to be correct. The values are 1 or 0 now. type duoble


#when i execute:
mat %>% 
  cor()

#it result in this issue
Fehler in cor(.) : 'x' must be numeric

#But double is numeric as far as i know. but the cor() return an error.
#a strange thing appear for me in this solution. When i hit mat in the Envirionment register > area "Data" this result in a View(mat) command in the console and display a List [50043 x 743]; List of length 3746949 and the valies are double[1] with 0 or 1
#by running the same command in console manually i receive the following: list[3746950] List of length 3746950 (so one more) with the values double[1] with 1 and NULL with Pairlist of length 0.
#why is it handled differently? I guess this is the reason for the issue with cor().

#when i just call 'mat' it display the matric correctly with 0 instead of "NULL".
mat

我想知道它是否是 R Studio 的错误。

Answer 1

如果你想把数据变成正确的格式来使用 cor ，我们可以使用 tidyr的 pivot_wider然后转换成矩阵:

library(tidyverse)
mat <- df %>% 
  mutate(value = 1) %>% 
  pivot_wider(id_cols = Document_Number, names_from = Article_Number) %>%
  {
    mat <- as.matrix(.[, -1])
    rownames(mat) <- unlist(.[, 1])
    mat
  } %>% 
  replace(is.na(.), 0)

mat %>% 
  cor()
#>            115027     100288      11754      33908      96478      33835
#> 115027  1.0000000  1.0000000  0.5773503 -0.3333333 -0.3333333 -0.3333333
#> 100288  1.0000000  1.0000000  0.5773503 -0.3333333 -0.3333333 -0.3333333
#> 11754   0.5773503  0.5773503  1.0000000  0.5773503  0.5773503  0.5773503
#> 33908  -0.3333333 -0.3333333  0.5773503  1.0000000  1.0000000  1.0000000
#> 96478  -0.3333333 -0.3333333  0.5773503  1.0000000  1.0000000  1.0000000
#> 33835  -0.3333333 -0.3333333  0.5773503  1.0000000  1.0000000  1.0000000
#> 51912  -0.3333333 -0.3333333  0.5773503  1.0000000  1.0000000  1.0000000
#> 152477 -0.3333333 -0.3333333  0.5773503  1.0000000  1.0000000  1.0000000
#> 33831  -0.3333333 -0.3333333  0.5773503  1.0000000  1.0000000  1.0000000
#> 100279 -0.3333333 -0.3333333  0.5773503  1.0000000  1.0000000  1.0000000
#> 53366  -0.5773503 -0.5773503 -1.0000000 -0.5773503 -0.5773503 -0.5773503
#>             51912     152477      33831     100279      53366
#> 115027 -0.3333333 -0.3333333 -0.3333333 -0.3333333 -0.5773503
#> 100288 -0.3333333 -0.3333333 -0.3333333 -0.3333333 -0.5773503
#> 11754   0.5773503  0.5773503  0.5773503  0.5773503 -1.0000000
#> 33908   1.0000000  1.0000000  1.0000000  1.0000000 -0.5773503
#> 96478   1.0000000  1.0000000  1.0000000  1.0000000 -0.5773503
#> 33835   1.0000000  1.0000000  1.0000000  1.0000000 -0.5773503
#> 51912   1.0000000  1.0000000  1.0000000  1.0000000 -0.5773503
#> 152477  1.0000000  1.0000000  1.0000000  1.0000000 -0.5773503
#> 33831   1.0000000  1.0000000  1.0000000  1.0000000 -0.5773503
#> 100279  1.0000000  1.0000000  1.0000000  1.0000000 -0.5773503
#> 53366  -0.5773503 -0.5773503 -0.5773503 -0.5773503  1.0000000

widyr 提供了一种更加直接的方式。包虽然:

library(widyr)
df %>%
  pairwise_cor(Article_Number, Document_Number, upper = FALSE)
#> # A tibble: 55 x 3
#>     item1  item2 correlation
#>     <int>  <int>       <dbl>
#>  1 115027 100288       1    
#>  2 115027  11754       0.577
#>  3 100288  11754       0.577
#>  4 115027  33908      -0.333
#>  5 100288  33908      -0.333
#>  6  11754  33908       0.577
#>  7 115027  96478      -0.333
#>  8 100288  96478      -0.333
#>  9  11754  96478       0.577
#> 10  33908  96478       1    
#> # … with 45 more rows

数据

df <- structure(list(Document_Number = c(14198915L, 14198915L, 14198915L, 
14198917L, 14198917L, 14198917L, 14198917L, 14198917L, 14198917L, 
14198917L, 14198917L, 14198924L, 14200000L), Article_Number = c(115027L, 
100288L, 11754L, 33908L, 96478L, 33835L, 51912L, 152477L, 33831L, 
100279L, 11754L, 53366L, 53366L)), class = "data.frame", row.names = c(NA, 
-13L))