Split data frame column into multiple column

Question

Working in R. I have a data set see below. I want to have two different columns for "No. of Export partners" and "No. Of Import partners" instead of them being combined under "indicator". What should i do? Have tried the pivot commands but could not figure out how to make it work.

# A tibble: 747 x 4 
reporter indicator              year      value
<chr>    <chr>                  <chr>     <dbl>
1 Aruba    No. Of Export partners year_2018     6
2 Aruba    No. Of Export partners year_2016    15
3 Aruba    No. Of Export partners year_2014    21
4 Aruba    No. Of Import partners year_2018    20
5 Aruba    No. Of Import partners year_2016    23
6 Aruba    No. Of Import partners year_2014    22
7 Angola   No. Of Export partners year_2018   138
8 Angola   No. Of Export partners year_2016   135
9 Angola   No. Of Export partners year_2014    38
10 Angola   No. Of Import partners year_2018   183
# … with 737 more rows

Instead of posting your data like you have done, could you use `dput()` and paste in the output? That will allow us to more easily copy the data into R. Here are some other tips on making a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). — Ben Norris, Sep 09 '20 at 10:53
Yes thank you. First time posting - tried to figure out how to do it. — Oskar Helles Olesen, Sep 09 '20 at 10:55
Welcome to Stack Overflow. Can you please edit your post to include your data. The following code will generate a code snippet with 10 random records that you can paste into your original post: dput(dplyr::sample_n(YourDatasetsNameGoesHere, 10)). To use my code, you may need to install dplyr with: install.packages("dplyr") — itsMeInMiami, Sep 10 '20 at 11:53

score 1 · Accepted Answer · answered Sep 09 '20 at 11:17

You can do this with the tidyverse packages, specifically dplyr, tidyr, and stringr.

library(tidyverse)
your_data %>%
  pivot_wider(names_from = indicator,   # This does what you want
              values_from = value) %>%  
  mutate(year = as.numeric(str_sub(year, 6)))  # converts your year column into numeric

Split data frame column into multiple column

1 Answers1