How to reformat row values to column fields in R

Question

I have row values that end with a -# (# ranging from 4 - 8). I have a corresponding column that has values associated to each -# row.

I would like to use dplyr to transpose my df so the -3 rows turn into column fields and the number of corresponding values from other variables (columns) be added to each column given it's 'title' (-4, -5, etc.)

I used dplyr package to move my rows to column headers but cannot seem to get the right output.

head(MFP)
 PART_RMV_DT RMV_MFR_PART_NO RMV_LRU_TSO_TM RMV_LRU_TSR_TM LRU
1   2017-06-25        828300-5                               MFP
9   2016-01-11        828300-5          17500          17500 MFP
17  2015-12-27        828300-5          16698          12193 MFP
19  2018-11-30        828300-5          40738          17494 MFP
21  2016-09-19        828300-5          25107          13528 MFP
23  2016-11-17        828300-5          35281          35281 MFP

t <- as.data.frame.matrix(xtabs(RMV_LRU_TSO_TM~RMV_MFR_PART_NO, MFP_df))

Error in Summary.factor(c(4268L, 472L, 3342L, 17L, 1L, 1L, 2834L, 5421L,  : 
  ‘sum’ not meaningful for factors

If I have 4 different groups of values in my column based on -#, I would like to move those values into columns and pull the associated values from other columns below each one of those -# columns.

It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Did expect to have all numeric columns? Are you sure you read your data in correctly? — MrFlick, Sep 11 '19 at 17:23
I included what my 'MFP_df' looks like - hopefully that helps. Essentially I want the values in col 'RMV_MFR_PART_NO' to become columns and col 'RMV_LRU_TSO_TM' values to below each corresponding -# col — Dinho, Sep 11 '19 at 17:33

score 0 · Answer 1 · answered Sep 12 '19 at 01:47

While it's hard to tell what you're actually after with the limited information provided (an example of what you're after would be immensely helpful as well as a ready-to-run tibble/tribble), the code below gets you what it sounds like you're after.

However, it's essentially a dead-end in R because tibbles are set up such that column names must be unique (why I had to nest the RMV_LRU_TSO_TM values) and it's far harder to iterate over columns than it is rows, so you'll need to gather, unnest before, say, group_by'ing and summarizing. If you tell us what your goal is after getting the data into the format you mentioned, that may get you the better answer.

    tribble(
  ~PART_RMV_DT, ~RMV_MFR_PART_NO, ~RMV_LRU_TSO_TM, ~RMV_LRU_TSR_TM, ~LRU,
  # 2017-06-25,        828300-5,                               MFP
  "2016-01-11",        "828300-5",          17500,          17500, "MFP",
  "2015-12-27",        "828300-5",          16698,          12193, "MFP",
  "2018-11-30",        "828300-5",          40738,          17494, "MFP",
  "2016-09-19",        "828300-5",          25107,          13528, "MFP",
  "2016-11-17",        "828300-5",          35281,          35281, "MFP",
  "2019-09-11",        "828300-4",          77777,          88888, "MFP",
) %>% 
  select(RMV_MFR_PART_NO, RMV_LRU_TSO_TM) %>% 
  nest(-RMV_MFR_PART_NO) %>% 
  spread(key = RMV_MFR_PART_NO, value = data) %>%
  rename_at(vars(contains("-")), ~ str_remove(.,"828300"))

Output:

    # A tibble: 1 x 2
  `-4`              `-5`             
  <list>           <list>          
1 <tibble [1 × 1]> <tibble [5 × 1]>

How to reformat row values to column fields in R

1 Answers1