how to transform R dataframe to rows of indicator values

user_649090 0
Viewed 74

I have a dataframe

A 2
B 4
C 3

and I would like to make a data frame with the following

A 0
A 1
B 0
B 0
B 0
B 1
C 0
C 0
C 1.

So for B, I want to make 4 rows and each one is 0 except for the last one which is 1. Similarly, for A, I'll have 2 rows and the first one has a 0 and the second one has a 1.

In general, if I have a row in the original table with X n, I want to return n rows in the new table with n-1 of them being X 0 and the final one as X 1.

Is there a way to do this in R? Or Python or SQL?

dataframe r python
4 Answers

In R, we may use uncount to replicate the rows from the second column and replace the second column with binary by converting the first to logical column (duplicated)

library(tidyr)
library(dplyr)
df1 %>% 
 uncount(v2) %>%
 mutate(v2 = +(!duplicated(v1, fromLast = TRUE)))

-output

 v1 v2
1  A  0
2  A  1
3  B  0
4  B  0
5  B  0
6  B  1
7  C  0
8  C  0
9  C  1

Or in Python

import pandas as pd
df1 = pd.DataFrame({"v1":["A", "B", "C"], "v2": [2, 4, 3]})
df2 = df1.reindex(df1.index.repeat(df1.v2))
df2['v2'] = (~df2.duplicated(subset = ['v2'], keep = "last")) + 0
df2
   v1   v2
0   A   0
0   A   1
1   B   0
1   B   0
1   B   0
1   B   1
2   C   0
2   C   0
2   C   1

data

df1 <- structure(list(v1 = c("A", "B", "C"), v2 = c(2L, 4L, 3L)), 
class = "data.frame", row.names = c(NA, 
-3L))
user_37322710

It's not that hard with base R...

d <- data.frame(x = LETTERS[1:3], n = c(2L, 4L, 3L))
d
##   x n
## 1 A 2
## 2 B 4
## 3 C 3

data.frame(x = rep.int(d$x, d$n), i = replace(integer(sum(d$n)), cumsum(d$n), 1L))
##   x i
## 1 A 0
## 2 A 1
## 3 B 0
## 4 B 0
## 5 B 0
## 6 B 1
## 7 C 0
## 8 C 0
## 9 C 1
user_126857680 
# load package
library(data.table)

# set as data table
setDT(df)

# work
df1 <- df[rep(seq(.N), b), ][, c := 1:.N, a]

df1[, d := 0][b == c, d := 1][, b := d][, c('c', 'd') := NULL]
user_70138960

There is another way in python to replicate what @akrun did in R:

>>> from datar.all import f, tibble, uncount, mutate, duplicated, as_integer
>>> df1 = tibble(v1=list("ABC"), v2=[2, 4, 3])
>>> df1 >> uncount(f.v2) >> mutate(
...   v2=as_integer(~duplicated(f.v1, from_last=True))
... )
        v1       v2
  <object> <object>
0        A        0
1        A        1
2        B        0
3        B        0
4        B        0
5        B        1
6        C        0
7        C        0
8        C        1

I am the author of datar, which is backed by pandas.

user_50881650
Related