Help?--Error in `pivot_longer()`: ! Can't combine `Stock1_name` <character> and `Stock1_dollars` <integer>.

[rhc02]

Hi, I currently have wide data that I'm trying to convert to long data, organized in these columns:
ID | PlanName | Year | State | Stock1_name | Stock1_dollars | Stock2_name | Stock2_dollars

This continues all the way to Stock10_dollars.

I'd like to convert this to long so it's organized like this:
ID, PlanName, Year, State, Stock, Dollars

I run the code below and get the error: Error in pivot_longer(): ! Can't combine Stock1_name and Stock1_dollars . Any help would be greatly appreciated. I'm new to R and just running into a wall. Thank you so much.

# Transform from wide to long format
after_data <- before_data %>%
  pivot_longer(
    cols = starts_with("Stock"),
    names_to = c(".value", "name"),
    names_sep = "_"
  ) %>%
  select(-name)
```r

[jrkrideau]

Could you supply us with some sample data? A handy way to supply some sample data is the dput() function. In the case of a large dataset something like dput(head(mydata, 100)) should supply the data we need. Just do dput(mydata) where mydata is your data. Copy the output and paste it here between
```

```

[FJCC]

Posting data would be very helpful, as @jrkrideau requested. Here is a shot in the dark in the absence of data.

before_data |> pivot_longer(cols = starts_with("Stock"), names_to = c("Asset", ".value"),
                   names_sep = "_")

[rhc02]

I appreciate the reply. I used dput(head(mydata,10)) instead of 100 to try to make it a little more readable. Here is the output:

structure(list(ppd_id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L), State = c("Alabama", "Alabama", "Alabama", "Alabama", "Alabama",
"Alabama", "Alabama", "Alabama", "Alabama", "Alabama"), PlanName = c("Alabama ERS",
"Alabama ERS", "Alabama ERS", "Alabama ERS", "Alabama ERS", "Alabama ERS",
"Alabama ERS", "Alabama ERS", "Alabama ERS", "Alabama ERS"),
fy = 2002:2011, Stock1_name = c("Raycom Media", "Raycom Media",
"Raycom Media", "Raycom Media", "Raycom Media", "Goldman Sachs Equity Linked Notes",
"Raycom Media", "Goldman Sachs Small Cap Equity Linked Notes",
"New Water Street Corporation", "New Water Street Corporation"
), Stock1_dollars = c(365158L, 393472L, 391565L, 131739L,
188705L, 263653L, 276880L, 144876L, 381009L, 417883L), Stock2_name = c("CNHI TR D Preferred",
"CNHI TR D Preferred", "General Electric Corporation ", "Exxon Mobil Corporation",
"Goldman Sachs - S & P Equity Private Placement", "Raycom Media",
"Goldman Sachs Small Cap Equity Linked Notes", "Exxon Mobil Corporation",
"Goldman Sachs West Street 2010-7 Trust", "Goldman Sachs Small Cap Equity Linked Notes"
), Stock2_dollars = c(118126L, 76458L, 84612L, 106182L, 158691L,
241071L, 160667L, 92731L, 282000L, 167212L), Stock3_name = c("General Electric Corporation ",
"Microsoft Corporation", "Exxon Mobil Corporation", "General Electric Corporation ",
"Exxon Mobil Corporation", "Exxon Mobil Corporation", "Community News",
"Credit Suisse Equity S&P 500 Equity Linked Notes", "National Alabama Corporation",
"ISHARES MSCI Emerging Markets"), Stock3_dollars = c(51067L,
70579L, 76370L, 93613L, 104803L, 125681L, 134922L, 72666L,
181913L, 114314L), Stock4_name = c("Microsoft Corporation",
"General Electric Corporation", "Microsoft Corporation",
"CNHI TR D Preferred", "General Electric Corporation", "General Electric Corporation ",
"Exxon Mobil", "Raycom Media (10 noncumulative)", "Goldman Sachs Small Cap Equity Linked Note",
"Raycom Preferred 10% Non-Cum"), Stock4_dollars = c(49728L,
69657L, 73848L, 73785L, 96797L, 111644L, 109373L, 72664L,
166431L, 99520L), Stock5_name = c("Wal Mart Stores", "Pfizer Incorporated",
"CNHI TR D Preferred", "Microsoft Corporation", "Citigroup Incorporated",
"Community News Tranche D", "Goldman Sachs S&P 500 Equity Linked Notes",
"Microsoft Corporation", "ISHARES MSCI Emerging Markets",
"Exxon Mobil Corporation"), Stock5_dollars = c(46295L, 58647L,
66062L, 68441L, 68650L, 79897L, 83728L, 53392L, 94222L, 77559L
), Stock6_name = c("Exxon Mobil Corporation", "Exxon Mobil Corporation",
"Pfizer Incorporated", "Citigroup Incorporated", "Bank Of America ",
"Microsoft", "Credit Suisse Medium Term Equity Linked Notes",
"JP Morgan & Chase", "Raycom Preferred 10% Non Cum", "Apple Inc"
), Stock6_dollars = c(45842L, 58225L, 59720L, 65323L, 67522L,
67979L, 67470L, 48991L, 78240L, 77304L), Stock7_name = c("Pfizer Incorporated",
"Citigroup Incorporated", "Citigroup Incorporated", "Johnson & Johnson",
"Microsoft", "Citigroup Incorporated", "General Electric",
"Johnson & Johnson", "Exxon Mobil Corp", "IBM"), Stock7_dollars = c(37323L,
57695L, 57711L, 53754L, 67312L, 64959L, 67337L, 46912L, 72581L,
49408L), Stock8_name = c("Johnson & Johnson", "Wal Mart Stores",
"Wal Mart Stores", "Pfizer Incorporated", "Community News Tranche D Preferred",
"Bank of America Corporation", "ACON Signal", "General Electric",
"Apple Inc", "Microsoft"), Stock8_dollars = c(36372L, 57442L,
56376L, 51357L, 60814L, 61870L, 66858L, 45462L, 54873L, 47211L
), Stock9_name = c("Citigroup Incorporated", "RSA Holdings LLC",
"American Internation Group", "Bank of America Corporation",
"Pfizer", "Proctor & Gamble", "Microsoft", "Proctor & Gamble",
"Microsoft", "Coca Cola Company"), Stock9_dollars = c(33571L,
50551L, 45193L, 46864L, 58659L, 60690L, 59311L, 45240L, 43665L,
42227L), Stock10_name = c("American International Group",
"Intel Corporation", "Bank of America Corporation", "American International Group",
"Johnson & Johnson", "Johnson & Johnson", "Procter and Gamble",
"IBM", "JP Morgan Chase & Co", "AT&T"), Stock10_dollars = c(31496L,
45835L, 44456L, 45053L, 54305L, 59086L, 67699L, 44839L, 40938L,
39820L)), row.names = c(NA, 10L), class = "data.frame")

[rhc02]

This was almost perfect! It created an additional column Asset which I don't need, but that's not a problem. Is there a way to produce the result without the extra Asset column? The Asset column has the values Stock1, Stock2, Stock3, etc. in the below:

ppd_id State PlanName fy Asset name dollars

1 1 Alabama Alabama ERS 2002 Stock1 "Raycom Media" 365158
2 1 Alabama Alabama ERS 2002 Stock2 "CNHI TR D Preferred" 118126
3 1 Alabama Alabama ERS 2002 Stock3 "General Electric Corporation " 51067
4 1 Alabama Alabama ERS 2002 Stock4 "Microsoft Corporation" 49728
5 1 Alabama Alabama ERS 2002 Stock5 "Wal Mart Stores" 46295
6 1 Alabama Alabama ERS 2002 Stock6 "Exxon Mobil Corporation" 45842

[FJCC]

This results in a data frame with no Asset column.

before_data |>  pivot_longer(cols = starts_with("Stock"), names_to = c(NA, ".value"),
                   names_sep = "_")

[rhc02]

Thank you very much. I will mark Solution. Very much appreciated.