1

I have one csv file with four columns, and the first row is the category for each column of expenses: 

Category, A1, A2, A3
Date, Expenses, Expenses, Expenses
05/01/2014, 122.33, 123.35, 155.12
05/02/2014, 123.64, 165.54, 134.45
05/03/2014, 134.35, 123.54, 154.65
05/04/2014, 134.67, 415.56, 134.45
05/05/2014, 145.89, 145.34, 123.46

I want to group the data in this form in R:

Date       Category Expenses
05/01/2014 A1       122.33
05/02/2014 A1       123.64
05/03/2014 A1       134.35
05/04/2014 A1       134.67
05/05/2014 A1       145.89
05/01/2014 A2       123.35
05/02/2014 A2       165.54
05/03/2014 A2       123.54
05/04/2014 A2       415.56
05/05/2014 A2       145.34
05/01/2014 A3       155.12
05/02/2014 A3       134.45
05/03/2014 A3       154.65
05/04/2014 A3       134.45
05/05/2014 A3       123.46

I have tried separating the file into categories and then the expenses, but don't know how to merge rows from one data frame with rows from another. The resulting data frame would be around 9 million rows, so I don't know whether a for loop would be convenient.

Dado
  • 43
  • 5
  • 1
    If this dupe isn't solving your problem you can ping me to reopen, As I'm not entirely sure regarding your strange data structure. It is bad practice to have both numeric and character values in the same column, for example. – David Arenburg Jul 24 '15 at 11:36
  • The expenses belong to the category on top of each column, this is how the data is arranged – Dado Jul 24 '15 at 11:41
  • 2
    Just remove the first row and then melt the data, for example `df1 <- df[-1,] ; library(reshape2) ; res <- melt(df1, "Category")` (if `df` is your original data set), you can rename the columns afterwards by just `names(res) <- c("Date", "Category", "Expenses")` and you good to go. – David Arenburg Jul 24 '15 at 11:48
  • Thank you, I applied your solution but the result is not the expected. If I remove the first row, I don't have category anymore and melt returns the same result. – Dado Jul 24 '15 at 13:23
  • What are your column names? isn't it `Category, A1, A2, A3`? If not, then remove both first rows, and assign you first row to column names. I'm reopening this thread for now, So please provide `dput` of the the first 10 rows of your data. – David Arenburg Jul 24 '15 at 13:24
  • 1
    `Category, A1, A2, A3` are not the column names, they're part of the data. But removing the two first rows and assigning the first row to column names led me to the solution. Thanks a lot @DavidArenburg – Dado Jul 24 '15 at 14:20

0 Answers0