Modify an excel file automatically with R

Question

I have an excel data as follows:

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

show the code that you have started?

cory
– cory

2020-11-19 14:21:58 +00:00
Commented Nov 19, 2020 at 14:21 — cory
– cory, Commented Nov 19, 2020 at 14:21
@cory It is a mess trust me:p

Christina
– Christina

2020-11-19 14:23:37 +00:00
Commented Nov 19, 2020 at 14:23 — Christina
– Christina, Commented Nov 19, 2020 at 14:23

Duck · Accepted Answer · 2020-11-19 14:37:08Z

2

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

edited Nov 19, 2020 at 14:37

answered Nov 19, 2020 at 14:04

Duck

39.6k14 gold badges47 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Christina Over a year ago

Thank you a lot. I tried your code, but it seems that when I generated the txt file, the values for X are stacked with the months. How can I solve this problem?

Christina Over a year ago

Don't worry, but this code gives me an error: object 'X' not found

Christina Over a year ago

Always I get the same error: object 'X' not found :(

Duck Over a year ago

@Christina I have added a global change. Please re run all the new code and let me know if that works!

Christina Over a year ago

Thank you a lot! Live love Argentinaaa

Collectives™ on Stack Overflow

Modify an excel file automatically with R

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related