Convert Matrix to Three Column Data.Frame

Convert matrix to three column data.frame

You can use melt

library(reshape2)
setNames(melt(m1), c('rows', 'vars', 'values'))
#  rows vars values
#1 row1 var1      1
#2 row2 var1      3
#3 row1 var2      2
#4 row2 var2      4

Or

data.frame(rows=rownames(m1)[row(m1)], vars=colnames(m1)[col(m1)],
                      values=c(m1))
#   rows vars values
#1 row1 var1      1
#2 row2 var1      3
#3 row1 var2      2
#4 row2 var2      4

Or

 as.data.frame(as.table(m1))
 #    Var1 Var2 Freq
 #1 row1 var1    1
 #2 row2 var1    3
 #3 row1 var2    2
 #4 row2 var2    4

data

m1 <- structure(c(1L, 3L, 2L, 4L), .Dim = c(2L, 2L), .Dimnames = list(
c("row1", "row2"), c("var1", "var2")))

Convert matrix to Dataframe with three columns

Check with merge

out = df_x.merge(df_y,how='cross').join(df_z)

Convert matrix to three defined columns in R

We can use which with arr.ind=TRUE

cbind(val= 1, which(m==1, arr.ind=TRUE))
#     val row col
#[1,]   1   2   1
#[2,]   1   5   1
#[3,]   1   3   2
#[4,]   1   4   2
#[5,]   1   6   2

For multiple cases, as @RHertel mentioned

for(i in 1:5) print(cbind(i,which(m==i, arr.ind=TRUE)))

Or with lapply

do.call(rbind, lapply(1:2, function(i) {
        m1 <-cbind(val=i,which(m==i, arr.ind=TRUE))
        m1[order(m1[,2]),]}))
#      val row col
#[1,]   1   2   1
#[2,]   1   3   2
#[3,]   1   4   2
#[4,]   1   5   1
#[5,]   1   6   2
#[6,]   2   1   2
#[7,]   2   4   1
#[8,]   2   5   4
#[9,]   2   6   3

---

As the OP mentioned about base R solutions, the above would help. But, in case, if somebody wants a compact solution,

library(reshape2)
melt(m)

and then subset the values of interest.

creating matrix from three column data frame in R

You may define the fun.aggregate=.

library(reshape2)
acast(df1, state~season, value.var = 'val_1', fun.aggregate=sum)
#      spring summer winter
# BOS      26     19     14
# NY       10     24      3
# WASH     66     42     99

converting 3 column dataframe to matrix with columns defined by range

IIUC, you want to create a sparse matrix document vs words, you could do:

import pandas as pd
from scipy.sparse import csr_matrix

rows, cols, data = zip(*df.to_numpy())
mat = csr_matrix((data, (rows, cols)), shape=(max(rows) + 1, max(cols) + 1))
res = pd.DataFrame(data=mat.toarray())
print(res)

Output

    0  1  2  3  4  5  6  7
0  10  0  0  0  5  0  0  2
1   0  0  5  0  0  0  0  0

With this approach the range is determined automatically.

UPDATE

If you want to have 10 columns you could do:

rows, cols, data = zip(*df.to_numpy())
mat = csr_matrix((data, (rows, cols)), shape=(max(rows) + 1, 10))
res = pd.DataFrame(data=mat.toarray())
print(res)

Output

    0  1  2  3  4  5  6  7  8  9
0  10  0  0  0  5  0  0  2  0  0
1   0  0  5  0  0  0  0  0  0  0

Transform a 3-column dataframe into a matrix

You can use pivot_table(), e.g.:

In []:
df.pivot_table(columns='dates', index='names', values='times').reset_index()

Out[]:
dates names  Monday  Sunday  Tuesday
0      John       3       6        2
1      Mary       4       6        7

R: Rearrange matrix into three columns

If I understand you correctly, you need to flatten the matrix.

You can use as.vector and rep to add the id columns e.g. :

m = cbind(c(1,2,3),c(4,5,6),c(7,8,9))
row.names(m) = c('R1','R2','R3')
colnames(m) = c('C1','C2','C3')

d <- data.frame(i=rep(row.names(m),ncol(m)),
                j=rep(colnames(m),each=nrow(m)),
                score=as.vector(m))

Result:

> m
   C1 C2 C3
R1  1  4  7
R2  2  5  8
R3  3  6  9

> d
   i  j score
1 R1 C1     1
2 R2 C1     2
3 R3 C1     3
4 R1 C2     4
5 R2 C2     5
6 R3 C2     6
7 R1 C3     7
8 R2 C3     8
9 R3 C3     9

Please, note that this code converts a matrix into a data.frame, since the row and col names can be string and you can't have a matrix with different column type.

If you are sure that all row and col names are numbers, you can coerced it to a matrix.

Transforming a correlation matrix to a 3 column dataframe in pandas?

Use stack with reset_index:

df1 = df.stack().reset_index()
df1.columns = ['Letter1','Letter2','correlation']
print (df1)
  Letter1 Letter2  correlation
0       a       a          1.0
1       a       b          0.5
2       a       c          0.3
3       b       a          0.5
4       b       b          1.0
5       b       c          0.7
6       c       a          0.3
7       c       b          0.7
8       c       c          1.0

And then insert columns by positions filled by factorizeed values:

df1.insert(0, 'Value1', pd.factorize(df1['Letter1'])[0] + 1)
df1.insert(2, 'Value2', pd.factorize(df1['Letter2'])[0] + 1)

print (df1)
   Value1 Letter1  Value2 Letter2  correlation
0       1       a       1       a          1.0
1       1       a       2       b          0.5
2       1       a       3       c          0.3
3       2       b       1       a          0.5
4       2       b       2       b          1.0
5       2       b       3       c          0.7
6       3       c       1       a          0.3
7       3       c       2       b          0.7
8       3       c       3       c          1.0

transform matrix to 3 column dataframe in R

We can convert the matrix to table and then use as.data.frame (if we don't want the upper.tri or lower.tri, change it to NA and then reshape)

m1[upper.tri(m1)] <- NA
out <- na.omit(as.data.frame.table(m1))
names(out) <- c("C1", "C2", "Value")

-output

> out
   C1 C2    Value
1  G1 G1 0.000000
2  G2 G1 3.575791
3  G3 G1 3.961912
4  G4 G1 4.102760
6  G2 G2 0.000000
7  G3 G2 4.515661
8  G4 G2 4.656509
11 G3 G3 0.000000
12 G4 G3 2.114352
16 G4 G4 0.000000

How to convert dataframe with 3 columns into matrix in R

We can use tapply from base R (no packages needed and it returns the format specified in the OP's post)

out <- tapply(df$count, df[1:2], FUN = mean)
names(dimnames(out)) <- NULL

-output

 out
    A1  A2
B1 1.5 3.5
B2 5.5 7.5

 is.matrix(out)
[1] TRUE

---

or another option is xtabs from base R and divide

xtabs(count ~B + A, df)/2
    A
B     A1  A2
  B1 1.5 3.5
  B2 5.5 7.5

---

Or may also use aggregate with reshape from base R

reshape(aggregate(count ~ B + A, df, mean), idvar = 'B',
      direction = 'wide', timevar = 'A')
   B count.A1 count.A2
1 B1      1.5      3.5
2 B2      5.5      7.5

---

Or we may also use acast from reshape2

library(reshape2)
acast(df, B ~ A, mean)
    A1  A2
B1 1.5 3.5
B2 5.5 7.5

---

Related Topics