Comparing two Excel files in R and returning different titles

0 votes

I have to compare two Excel spreadsheets as part of a project at work to determine whether a title has been added to or removed from the second Excel sheet (Book 2). Although the code I wrote is effective, it is also not very aesthetically pleasing and is challenging to understand for someone without any prior knowledge of R.

In order for my coworkers to comprehend and make changes to the differences, I want to compare two Excel files and output the differences in a simple manner. The result should ideally be a table that organizes the data so that it is simple to find and edit. I value all the assistance.

structure(list(Title = c("D", "Mortal Kombat", "Godzilla",
"Wonder", "Suicide Squad", "Mulan"), Studio = c("X", "X",
"X", "X", "X", "Y"), Type = c("Special", "Special", "Special",
"Special", "Special", "Special")), class = c("tbl_df", "tbl",
"data.frame"), row.names = c(NA, -6L))


structure(list(Title = c("D", "Mortal Kombat", "Godzilla",
"Wonder", "Trolls"), Studio = c("X", "X", "X", "X", "X"
), Type = c("Special", "Special", "Special", "Special", "Special"
)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA,
-5L))

library("compareDF")
library("readxl")

dat <- read_xlsx("Book1.xlsx")
dat2 <- read_xlsx("Book2.xlsx")


compare_df(dat, dat2)
Oct 14, 2022 in Others by Kithuzzz
• 27,940 points
249 views

1 answer to this question.

0 votes

Solution:

library(tidyverse)
dat <- read_xlsx("Book1.xlsx")
dat2 <- read_xlsx("Book2.xlsx")

book1_output <- anti_join(dat,dat2, by = "Title") %>% mutate(source = "Book1")
book2_output <- anti_join(dat2,dat, by = "Title") %>% mutate(source = "Book2")

final_output <- rbind(book1_output,book2_output)
answered Oct 14, 2022 by narikkadan
• 51,600 points

Related Questions In Others

0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 51,600 points
185 views
0 votes
1 answer

Word Mail Merge with Excel data has to be saved in different files with custom names

Try this: Public Sub Mail_Merge() On Error GoTo ErrH Dim ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 51,600 points
181 views
0 votes
1 answer
0 votes
1 answer

How to change two different date format into single date format in excel

With data in A2, in B2 enter: =IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))) and apply your format ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 51,600 points
47 views
0 votes
1 answer

Excel, How to split cells by comma delimiter into new cells

The Excel manual method: choose Text to Column ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 51,600 points
111 views
0 votes
1 answer

Conversion of PDF file to Excel in R

I looked at the pdf, and it ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 51,600 points
257 views
0 votes
1 answer

Use R to create chart in Excel sheet

Here is a solution utilizing the XLConnect ...READ MORE

answered Nov 10, 2022 in Others by narikkadan
• 51,600 points
72 views
0 votes
1 answer

Write from R into template in excel while preserving formatting

you have the XLConnect package. Read the documentation or the vignette of that ...READ MORE

answered Nov 10, 2022 in Others by narikkadan
• 51,600 points
128 views
0 votes
1 answer

Download multiple excel files linked through urls in R

Try something along the lines of: for (i ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,600 points
208 views
0 votes
1 answer

Apache POI - watermark in Excel - different appearance in Excel and LibreOffice

There is nothing that apache poi could ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 51,600 points
274 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP