CODEWITHSUNDEEP
rdataframespread
Julia
Julia

Reputation: 251

Spread a data frame with non unique values in some columns

this is the data I am working with:

> data
         Segment Product               Value           Key
1   non-domestic      S1  517.50760307564053 Actuals Sales
2   non-domestic      S2  1235.3088913918129 Actuals Sales
3   non-domestic      S3  2141.6841816176966 Actuals Sales
4       domestic      S1 -958.38836859580044 Actuals Sales
5       domestic      S2 -1129.5593769492507 Actuals Sales
6       domestic      S3 -137.68477107274975 Actuals Sales
7  non-domestic       S1 -296.07559218703756 Quarter Sales
8  non-domestic       S2  1092.0390648120747 Quarter Sales
9  non-domestic       S3  1156.2866848179935 Quarter Sales
10     domestic       S1 -1975.0222255105061 Quarter Sales
11     domestic       S2 -2549.8125184965966 Quarter Sales
12     domestic       S3 -2608.2434152116011 Quarter Sales

I am trying to spread it in order to get a table with 6 rows and 4 columns (Segment, Product, Actuals Sales, Quarter Sales) without missing values

spread(data=data, key=Key, value=Value)

Unfortunately, what I get is this. I understand that it is because there are non unique values in columns Segment and Product.

         Segment Product       Actuals Sales       Quarter Sales
1       domestic      S1 -958.38836859580044                <NA>
2       domestic      S2 -1129.5593769492507                <NA>
3       domestic      S3 -137.68477107274975                <NA>
4      domestic       S1                <NA> -1975.0222255105061
5      domestic       S2                <NA> -2549.8125184965966
6      domestic       S3                <NA> -2608.2434152116011
7   non-domestic      S1  517.50760307564053                <NA>
8   non-domestic      S2  1235.3088913918129                <NA>
9   non-domestic      S3  2141.6841816176966                <NA>
10 non-domestic       S1                <NA> -296.07559218703756
11 non-domestic       S2                <NA>  1092.0390648120747
12 non-domestic       S3                <NA>  1156.2866848179935

Could you please help me, how I can remove the missing values and create a table, where values in the first two columns are not duplicated?

Here is the reproductible example:

> dput(data)
structure(list(Segment = c("non-domestic", "non-domestic", "non-domestic", 
"domestic", "domestic", "domestic", "non-domestic ", "non-domestic ", 
"non-domestic ", "domestic ", "domestic ", "domestic "), Product = c("S1", 
"S2", "S3", "S1", "S2", "S3", "S1", "S2", "S3", "S1", "S2", "S3"
), Value = c("517.50760307564053", "1235.3088913918129", "2141.6841816176966", 
"-958.38836859580044", "-1129.5593769492507", "-137.68477107274975", 
"-296.07559218703756", "1092.0390648120747", "1156.2866848179935", 
"-1975.0222255105061", "-2549.8125184965966", "-2608.2434152116011"
), Key = c("Actuals Sales", "Actuals Sales", "Actuals Sales", 
"Actuals Sales", "Actuals Sales", "Actuals Sales", "Quarter Sales", 
"Quarter Sales", "Quarter Sales", "Quarter Sales", "Quarter Sales", 
"Quarter Sales")), .Names = c("Segment", "Product", "Value", 
"Key"), row.names = c(NA, -12L), class = "data.frame")

Upvotes: 1

Views: 438

Answers (5)

wiebke
wiebke

Reputation: 111

I would do it with the data.table package and generate 2 tables and merge them afterwards.

I hope this code helps you.

library(data.table)

#"test" is your data frame input
test <- data.table(test)

a <- test[Key=="ActualsSales",.(Segment=Segment, Product=Product, ActualsSales=Value)]
b <- test[Key=="QuarterSales",.(Segment=Segment, Product=Product, QuarterSales=Value)]

output <- merge(a,b, by=c("Segment","Product"))
print(output)

Upvotes: 1

AnilGoyal
AnilGoyal

Reputation: 26218

Your sample data actually contains some white spaces, after removing these the pivot_wider along with its argument id_cols works like a charm

data <- structure(list(Segment = c("non-domestic", "non-domestic", "non-domestic", 
                                   "domestic", "domestic", "domestic", "non-domestic", "non-domestic", 
                                   "non-domestic", "domestic", "domestic", "domestic"), Product = c("S1", 
                                                                                                        "S2", "S3", "S1", "S2", "S3", "S1", "S2", "S3", "S1", "S2", "S3"
                                   ), Value = c("517.50760307564053", "1235.3088913918129", "2141.6841816176966", 
                                                "-958.38836859580044", "-1129.5593769492507", "-137.68477107274975", 
                                                "-296.07559218703756", "1092.0390648120747", "1156.2866848179935", 
                                                "-1975.0222255105061", "-2549.8125184965966", "-2608.2434152116011"
                                   ), Key = c("Actuals Sales", "Actuals Sales", "Actuals Sales", 
                                              "Actuals Sales", "Actuals Sales", "Actuals Sales", "Quarter Sales", 
                                              "Quarter Sales", "Quarter Sales", "Quarter Sales", "Quarter Sales", 
                                              "Quarter Sales")), .Names = c("Segment", "Product", "Value", 
                                                                            "Key"), row.names = c(NA, -12L), class = "data.frame")

library(tidyr)

data %>% pivot_wider(names_from = Key, values_from = Value, id_cols = c(Segment, Product))

#> # A tibble: 6 x 4
#>   Segment      Product `Actuals Sales`     `Quarter Sales`    
#>   <chr>        <chr>   <chr>               <chr>              
#> 1 non-domestic S1      517.50760307564053  -296.07559218703756
#> 2 non-domestic S2      1235.3088913918129  1092.0390648120747 
#> 3 non-domestic S3      2141.6841816176966  1156.2866848179935 
#> 4 domestic     S1      -958.38836859580044 -1975.0222255105061
#> 5 domestic     S2      -1129.5593769492507 -2549.8125184965966
#> 6 domestic     S3      -137.68477107274975 -2608.2434152116011

Still, if your actual data also contains white spaces you can use stringr::str_trim() before pivoting.

data <- structure(list(Segment = c("non-domestic", "non-domestic", "non-domestic", 
                                   "domestic", "domestic", "domestic", "non-domestic ", "non-domestic ", 
                                   "non-domestic ", "domestic ", "domestic ", "domestic "), Product = c("S1", 
                                                                                                        "S2", "S3", "S1", "S2", "S3", "S1", "S2", "S3", "S1", "S2", "S3"
                                   ), Value = c("517.50760307564053", "1235.3088913918129", "2141.6841816176966", 
                                                "-958.38836859580044", "-1129.5593769492507", "-137.68477107274975", 
                                                "-296.07559218703756", "1092.0390648120747", "1156.2866848179935", 
                                                "-1975.0222255105061", "-2549.8125184965966", "-2608.2434152116011"
                                   ), Key = c("Actuals Sales", "Actuals Sales", "Actuals Sales", 
                                              "Actuals Sales", "Actuals Sales", "Actuals Sales", "Quarter Sales", 
                                              "Quarter Sales", "Quarter Sales", "Quarter Sales", "Quarter Sales", 
                                              "Quarter Sales")), .Names = c("Segment", "Product", "Value", 
                                                                            "Key"), row.names = c(NA, -12L), class = "data.frame")
library(tidyverse)
data %>% mutate(Segment = str_trim(Segment)) %>%
  pivot_wider(names_from = Key, values_from = Value, id_cols = c(Segment, Product))
#> # A tibble: 6 x 4
#>   Segment      Product `Actuals Sales`     `Quarter Sales`    
#>   <chr>        <chr>   <chr>               <chr>              
#> 1 non-domestic S1      517.50760307564053  -296.07559218703756
#> 2 non-domestic S2      1235.3088913918129  1092.0390648120747 
#> 3 non-domestic S3      2141.6841816176966  1156.2866848179935 
#> 4 domestic     S1      -958.38836859580044 -1975.0222255105061
#> 5 domestic     S2      -1129.5593769492507 -2549.8125184965966
#> 6 domestic     S3      -137.68477107274975 -2608.2434152116011

Created on 2021-06-11 by the reprex package (v2.0.0)

Upvotes: 2

Pedro Alencar
Pedro Alencar

Reputation: 1089

qs <- df$Value[df$Key == 'Quarter Sales']
as <- df$Value[df$Key == 'Actuals Sales']
df$QS <- c(qs, rep(NA, length(qs)))
df$AS <- c(as, rep(NA, length(as)))
df$Key <- NULL

df <- df[complete.cases(df),]

Upvotes: 0

ThomasIsCoding
ThomasIsCoding

Reputation: 101383

A base R option using reshape

reshape(
  transform(data, Segment = trimws(Segment)),
  direction = "wide",
  idvar = c("Segment", "Product"),
  timevar = "Key"
)

gives

       Segment Product Value.Actuals Sales Value.Quarter Sales
1 non-domestic      S1  517.50760307564053 -296.07559218703756
2 non-domestic      S2  1235.3088913918129  1092.0390648120747
3 non-domestic      S3  2141.6841816176966  1156.2866848179935
4     domestic      S1 -958.38836859580044 -1975.0222255105061
5     domestic      S2 -1129.5593769492507 -2549.8125184965966
6     domestic      S3 -137.68477107274975 -2608.2434152116011

Upvotes: 2

Wimpel
Wimpel

Reputation: 27732

remove the unneeded spaces (trimws()) and the cast to wide

library(data.table)
dcast(setDT(mydata), trimws(Segment) + Product ~ Key, value.var = "Value", fill = NA)
#         Segment Product       Actuals Sales       Quarter Sales
# 1:     domestic      S1 -958.38836859580044 -1975.0222255105061
# 2:     domestic      S2 -1129.5593769492507 -2549.8125184965966
# 3:     domestic      S3 -137.68477107274975 -2608.2434152116011
# 4: non-domestic      S1  517.50760307564053 -296.07559218703756
# 5: non-domestic      S2  1235.3088913918129  1092.0390648120747
# 6: non-domestic      S3  2141.6841816176966  1156.2866848179935

Upvotes: 2

Related Questions