CODEWITHSUNDEEP
rreshape
user2543622
user2543622

Reputation: 6796

R manipulating data frame pivot

I have some data in a csv file as below. 

Food            Calories    Cholesterol Total_Fat   Sodium 
Frozen_Broccoli 73.8            0           0.8         68.2 
Carrots,Raw 74.8            23.7            0           0.1

code to create that data is (updated the code after a comment from user Drew Steen below):

Food=c('Frozen Broccoli',   'Carrots,Raw')
Cholesterol=c('0',  '23.7')
Total_Fat=c('0.8',  '0')
Sodium=c('68.2',    '0.1')
Calories=c('73.8',  '74.8')
Food=c('Frozen Broccoli',   'Carrots,Raw')
original=data.frame(Food, Cholesterol,Total_Fat,Sodium,Calories)
View(original)

I would like to transpose part of data so that it looks like below. 

Frozen_Broccoli    Calories X73.8
Frozen_Broccoli Cholesterol   0.0
Frozen_Broccoli   Total_Fat   0.8
Frozen_Broccoli      Sodium  68.2
Carrots,Raw    Calories  74.8
Carrots,Raw Cholesterol  23.7
Carrots,Raw   Total_Fat   0.0
Carrots,Raw      Sodium   0.1

I can create the data using Excel and vba. But is their a way to do the same in R?

Upvotes: 0

Views: 124

Answers (2)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193687

This answer is to serve two purposes:

  1. Address a question in the comments in how to share reproducible data in a better way.
  2. Represent base R with stack.

Part 1: Data creation.

Note that the numbers are not quoted and that we can put them in a data.frame directly. This means that there are fewer objects littering the workspaces of the people trying to answer your question.

original <- data.frame(
  Food = c('Frozen Broccoli', 'Carrots,Raw'),
  Cholesterol = c(0, 23.7), 
  Total_Fat = c(0.8, 0),
  Sodium = c(68.2, 0.1),
  Calories = c(73.8, 74.8))
original
#              Food Cholesterol Total_Fat Sodium Calories
# 1 Frozen Broccoli         0.0       0.8   68.2     73.8
# 2     Carrots,Raw        23.7       0.0    0.1     74.8

An alternative solution

The "reshaping" process can be done with stack from base R. Drop the first column when you are stacking the data. Use cbind to put it back in.

Generally, stack is faster than melt. There is also unlist but that is very cumbersome for this particular problem.

cbind(original[1], stack(original[-1]))
#              Food values         ind
# 1 Frozen Broccoli    0.0 Cholesterol
# 2     Carrots,Raw   23.7 Cholesterol
# 3 Frozen Broccoli    0.8   Total_Fat
# 4     Carrots,Raw    0.0   Total_Fat
# 5 Frozen Broccoli   68.2      Sodium
# 6     Carrots,Raw    0.1      Sodium
# 7 Frozen Broccoli   73.8    Calories
# 8     Carrots,Raw   74.8    Calories

Upvotes: 1

Drew Steen
Drew Steen

Reputation: 16627

The code you supply does not, I think, precisely reproduce your .csv file. (Try names(original) to see how). Try this

original <- read.csv("your_data.csv")
require(reshape2)
new_df <- melt(original, id.vars="Food")

Upvotes: 1

Related Questions