0

I'm new to R and needed some help with reshaping a data frame that is similar to the one below:

company_name year profit_usd profit_eur profit_gbp
A 2017 1237 1006 871
B 2017 1337 1096 949
A 2018 1143 937 811
B 2018 1288 1056 914

I want to reshape this table such that I have only one column which shows profit and another column which shows the currency name. Something like this:

company_name year currency profit
A 2017 usd 1237
A 2017 eur 1006
A 2017 gbp 871
B 2017 usd 1337
B 2017 eur 1096
B 2017 gbp 949
A 2018 usd 1143
A 2018 eur 937
A 2018 gbp 811
B 2018 usd 1288
B 2018 eur 1056
B 2018 gbp 914

Any help with this would be greatly appreciated, thank you!

crumblycloth
  • 15
  • 5
  • It would be helpful if you'd provided your data not ina table, e.g. by using `dput(YOURDATA)` or, if the data is too large `dput(head(YOURDATA))`. Also: https://stackoverflow.com/help/minimal-reproducible-example – deschen May 20 '21 at 09:35
  • @deschen Thank you for the tip - I'll keep that in mind for future questions – crumblycloth May 20 '21 at 09:51
  • @zx8754: I vote for reopening this question. Not every question on a reshape topic can be answered by this one post you linked. I.e. in this particular case, we can make good use of the `names_sep` parameter in `pivot_longer`, which isn't a viable option in the post you linked. – deschen May 20 '21 at 09:55
  • 1
    I agree that this isn't covered by the post it's flagged for, but it's definitely a question that's been posted before. [This](https://stackoverflow.com/q/25925556/5325862) and [this](https://stackoverflow.com/q/23945350/5325862) seem like better targets. @zx8754 would you want to add either of those? – camille May 20 '21 at 15:44
  • @camille thank you for the links, updated. – zx8754 May 20 '21 at 15:56
  • @deschen feel free to vote to re-open if disagree. – zx8754 May 20 '21 at 15:57

2 Answers2

1

Assuming your data is stored in an object df:

library(tidyverse)

df %>%
  pivot_longer(cols      = starts_with("profit"),
               names_sep = "_",
               names_to  = c("delete", "currency"),
               values_to = "profit") %>%
  select(-delete)
deschen
  • 2,415
  • 2
  • 13
  • 25
-1

In addition to the answer of deschen.

library(tidyr)

#long format
df <- pivot_longer(df, cols = c("profit_usd", "profit_eur", "profit_gbp"), names_to = "currency", values_to = "profit")

#removes profit_ from your currency column
df$currency <- gsub("profit_", "", df$currency)

I have a personal preference not to use pipe functions. Especially when starting out, the use of pipe functions can create long lines of code which are harder to debug in comparison with multiple lines. Advantage of pipe functions is shorter code ofcourse.

maarvd
  • 406
  • 1
  • 9