How to convert column values to rows for each unique value in a dataframe in R?

LearneR

I've a large dataframe which contains 12 columns each for two types of values, Rested and Active. I want to convert the columns of each month into rows, thus bring all the month columns (Jan, Feb, Mar... ) under 'Month'

My data is as follows:

ID      L1  L2  Year    JR  FR  MR  AR  MYR JR  JLR AGR SR  OR  NR  DR  JA  FA  MA  AA  MYA JA  JLA AGA SA  OA  NA  DA
1234    89  65  2003    11  34  6   7   8   90  65  54  3   22  55  66  76  86  30  76  43  67  13  98  67  0   127 74
1234    45  76  2004    67  87  98  5   4   3   77  8   99  76  56  4   3   2   65  78  44  53  67  98  79  53  23  65

I'm trying to make it appear as below (column R represents Rested and column A represents Active. and monthly JR, FR, MR respectively means Jan Rested, Feb Rested, Mar Rested and JA, FA, MA respectively means Jan Active, Feb Active, Mar Active and etc):

So, here I'm trying to convert each of the monthly columns to rows and keeping them beside each other for R and A values by creating a new Month column.

 ID     L1  L2  Year    Month   R   A
1234    89  65  2003    Jan     11  76
1234    89  65  2003    Feb     34  86
1234    89  65  2003    Mar     6   30
1234    89  65  2003    Apr     7   76
1234    89  65  2003    May     8   43
1234    89  65  2003    Jun     90  67
1234    89  65  2003    Jul     65  13
1234    89  65  2003    Aug     54  98
1234    89  65  2003    Sep     3   67
1234    89  65  2003    Oct     22  0
1234    89  65  2003    Nov     55  127
1234    89  65  2003    Dec     66  74
1234    45  76  2004    Jan     67  3
1234    45  76  2004    Feb     87  2
1234    45  76  2004    Mar     98  65
1234    45  76  2004    Apr     5   78
1234    45  76  2004    May     4   44
1234    45  76  2004    Jun     3   53
1234    45  76  2004    Jul     77  67
1234    45  76  2004    Aug     8   98
1234    45  76  2004    Sep     99  79
1234    45  76  2004    Oct     76  53
1234    45  76  2004    Nov     56  23
1234    45  76  2004    Dec     4   65

I've tried various things like stack,melt,unlist

data_reshape <- reshape(df,direction="long", varying=list(c("JR", "FR", "MR", "AR", "MYR", "JR", "JLR", "AGR", "SR", "OR", "NR", "DR", "JA", "FA","MA", "AA", "MYA", "JA", "JLA","AGA", "SA", "OA","NA", "DA")), v.names="Precipitation", timevar="Month")

data_stacked <- stack(data, select = c("JR", "FR", "MR", "AR", "MYR", "JR", "JLR", "AGR", "SR", "OR", "NR", "DR", "JA", "FA","MA", "AA", "MYA", "JA", "JLA","AGA", "SA", "OA","NA", "DA"))

but their result is not quite expected - they are giving Jan values of all years, and then Feb values of all years, and then March values of all years, and etc. But I want to structure them in an proper monthly manner for each Year for each ID existing in the entire dataset.

How to achieve this in R?

27 ϕ 9

Here's a base reshape approach:

res <- reshape(mydf, direction="long", varying=list(5:16, 17:28), v.names=c("R", "A"), times = month.name, timevar = "Month")
res[with(res, order(ID, -L1, L2, Year)), -8]

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How to find previous of each unique value in a column based upon datetime column values in a dataframe?

From Dev

Extract unique values and number of occurrences of each value from dataframe column

From Dev

Oracle - produce unique rows for each unique column value and convert rows to columns

From Dev

In standard SQL, how do I select rows such that for each unique value in one column, all of the values in another column are a specified value?

From Dev

In standard SQL, how do I select rows such that for each unique value in one column, all of the values in another column are a specified value?

From Dev

Extract the unique rows with maximum value in another column in R dataframe

From Dev

How do you group values in one column for each unique value in another R?

From Dev

How do I substract a value from a column in a dataframe, with unique rows

From Dev

how to check if each cells (list) of a column of a dataframe are unique in R?

From Dev

Julia: Create summary values for column x for each unique value in column y of DataFrame

From Dev

[SQL]: Unique subsets of rows where a column value has each of a set of values

From Dev

Convert data frame of values into binary data frame where each unique value is a column

From Dev

Convert data frame of values into binary data frame where each unique value is a column

From Dev

Subset dataframe by unique values within a column in R

From Dev

How can I apply a unique value to column A for rows with common values in column A

From Dev

Compare two Rows in a worksheet and test for Unique values for each and Output in an Column

From Dev

For each unique value of some groupid column, how do I get rows with last 3 dates?

From Dev

For each unique value of some groupid column, how do I get rows with last 3 dates?

From Dev

How to find column values which contains unique value in another column from same dataframe?

From Dev

Select the X newest rows for each unique value of a column

From Dev

Pandas: create rows for each unique value of a column, even with missing data

From Java

How to convert DataFrame column to Rows in Python?

From Java

How to convert a dict to a dataframe, with the values as headers and rows?

From Dev

How to combine rows based on unique values in R?

From Dev

How to combine rows based on unique values in R?

From Dev

SQL server how to convert column values into rows

From Dev

Convert a vector to data.frame, one column for each unique value

From Dev

Sum a rows within a column for each unique combination r

From Dev

R count and list unique rows for each column satisfying a condition

Related Related

  1. 1

    How to find previous of each unique value in a column based upon datetime column values in a dataframe?

  2. 2

    Extract unique values and number of occurrences of each value from dataframe column

  3. 3

    Oracle - produce unique rows for each unique column value and convert rows to columns

  4. 4

    In standard SQL, how do I select rows such that for each unique value in one column, all of the values in another column are a specified value?

  5. 5

    In standard SQL, how do I select rows such that for each unique value in one column, all of the values in another column are a specified value?

  6. 6

    Extract the unique rows with maximum value in another column in R dataframe

  7. 7

    How do you group values in one column for each unique value in another R?

  8. 8

    How do I substract a value from a column in a dataframe, with unique rows

  9. 9

    how to check if each cells (list) of a column of a dataframe are unique in R?

  10. 10

    Julia: Create summary values for column x for each unique value in column y of DataFrame

  11. 11

    [SQL]: Unique subsets of rows where a column value has each of a set of values

  12. 12

    Convert data frame of values into binary data frame where each unique value is a column

  13. 13

    Convert data frame of values into binary data frame where each unique value is a column

  14. 14

    Subset dataframe by unique values within a column in R

  15. 15

    How can I apply a unique value to column A for rows with common values in column A

  16. 16

    Compare two Rows in a worksheet and test for Unique values for each and Output in an Column

  17. 17

    For each unique value of some groupid column, how do I get rows with last 3 dates?

  18. 18

    For each unique value of some groupid column, how do I get rows with last 3 dates?

  19. 19

    How to find column values which contains unique value in another column from same dataframe?

  20. 20

    Select the X newest rows for each unique value of a column

  21. 21

    Pandas: create rows for each unique value of a column, even with missing data

  22. 22

    How to convert DataFrame column to Rows in Python?

  23. 23

    How to convert a dict to a dataframe, with the values as headers and rows?

  24. 24

    How to combine rows based on unique values in R?

  25. 25

    How to combine rows based on unique values in R?

  26. 26

    SQL server how to convert column values into rows

  27. 27

    Convert a vector to data.frame, one column for each unique value

  28. 28

    Sum a rows within a column for each unique combination r

  29. 29

    R count and list unique rows for each column satisfying a condition

HotTag

Archive