CODEWITHSUNDEEP
rexcel
Christina
Christina

Reputation: 935

Modify an excel file automatically with R

I have an excel data as follows:

enter image description here

As you can see, the excel file contains 4 data entries (month, X, Y , Z) all of them are stacked in one column (A). I am interested to create a program in R which can automatically separate the data from each other and finally generate a matrix that contains 4 columns (month, X, Y , Z) and three lines of their corresponding values. For example, the matrix

 M = [March, 12, 15, 44;

     April, 33, 34, 21;

     June, 44, 66, 33].

Else, is there any other solution (for example generate automatically a txt.file or a new excel file that can solve this problem and put all the data in one column each?

I have started writing the R code, but as I am new in R, so I am blocked. I am also not an Excel expert.

Any help will be very appreciated

Upvotes: 0

Views: 187

Answers (1)

Duck
Duck

Reputation: 39613

Try this code. It is a bit long but if will work for any elements you have. Here the code using tidyverse functions. The code loads the file. As the names are in the header you isolate in a vector for late process. With the data we use separate_rows() by ; to split the row and then pivot_wider() to arrange again. Finally, we use str_split() to process the names. We assign them to data and export using write.table():

library(readxl)
library(dplyr)
library(tidyr)
#Load
df <- read_excel('Libro1.xlsx',1,)
#Extract names
names(df)<-'V1'
#Reshape
input <- df %>% mutate(id=row_number()) %>%
  separate_rows(V1,sep=';') %>%
  filter(V1!='') %>%
  group_by(id) %>% mutate(id2=paste0('V',row_number())) %>%
  pivot_wider(names_from = id2,values_from=V1) %>%
  ungroup() %>% select(-id)
#Separate
input <- input %>% separate(V1,c('Month','V1'),sep='-')
#Export
write.table(input,file='Myfile.txt',sep='\t',row.names = F)

In the .txt file you will have this:

  Month V1 V2 V3
1 March 12 15 44
2 April 33 34 21
3  June 44 66 33

Upvotes: 2

Related Questions