### load packages
require(pacman)
p_load('tidyverse', 'xlsx', 'readxl','DT')
setwd("data")
drawnConcepts <- xlsx::read.xlsx2(file = "drawnConcepts_translate.xlsx", sheetIndex = 1, encoding = "UTF-8", header = TRUE)
setwd("../output")
translationComment <- xlsx::read.xlsx2(file = "translations_comment_final.xlsx", sheetIndex = 1, encoding = "UTF-8", header = TRUE)
translationText <- xlsx::read.xlsx2(file = "translations_text.xlsx", sheetIndex = 1, encoding = "UTF-8", header = TRUE)Add translations to concept file
Notes
create raw data files
add column for translated text
drawnConcepts$text_translated <- NA
# remove white spaces
drawnConcepts$text <- str_trim(string = drawnConcepts$text, side = "both")
# remove the leading and trailing quotation marks
translationText$English[1:2][1] "\"autonomy concerns\"" "\"unclear responsibility\""translationText$English <- str_remove_all(translationText$English, '^"|"$')
translationText$English[1:2][1] "autonomy concerns" "unclear responsibility"# > match by text
# translationText$German[!translationText$German %in% drawnConcepts$text]
for(i in 1:nrow(translationText)){
drawnConcepts$text_translated[drawnConcepts$text %in% translationText$German[i]] <- translationText$English[i]
if(sum(drawnConcepts$text %in% translationText$German[i]) == 0){
cat("possible error in", i, "\n")
}
}
sum(is.na(drawnConcepts$text_translated))[1] 0drawnConcepts$text[is.na(drawnConcepts$text_translated)]character(0)add column for translated comment
drawnConcepts$comment_translated <- NA
# > match by id
# translationComment$ID[!translationComment$ID %in% drawnConcepts$id]
# remove the leading and trailing quotation marks
translationComment$English[1:2][1] "\"Consideration of whether humans should control the robots or whether the robots should act independently\""
[2] "\"About the actions of rescue robots\"" translationComment$English <- str_remove_all(translationComment$English, '^"|"$')
translationComment$English[1:2][1] "Consideration of whether humans should control the robots or whether the robots should act independently"
[2] "About the actions of rescue robots" for(i in 1:nrow(translationComment)){
drawnConcepts$comment_translated[drawnConcepts$id %in% translationComment$ID[i]] <- translationComment$English[i]
if(sum(drawnConcepts$id %in% translationComment$ID[i]) == 0){
cat("possible error in", i, "\n")
}
}
# sum(is.na(drawnConcepts$comment_translated))
tmp <- drawnConcepts$comment[is.na(drawnConcepts$comment_translated)]
tmp[tmp != ""]character(0)get rows you need to adjust manually
add dummy variables in Excel
# for text:
drawnConcepts$check_text <- 0
drawnConcepts$text_translated[nchar(x = drawnConcepts$text_translated) >= 40] [1] "AI\" in English is \"AI\". This is an acronym standing for \"Artificial Intelligence\" and is used universally."
[2] "AI\" in English is \"AI\". This is an acronym standing for \"Artificial Intelligence\" and is used universally."
[3] "I'm sorry, but \"Allgorythmusentscheidung\" doesn't seem to be a valid German word. Could you confirm the spelling or provide context?"
[4] "I'm sorry, but \"Allgorythmusentscheidung\" doesn't seem to be a valid German word. Could you confirm the spelling or provide context?"
[5] "The provided German phrase \"augen fatig\" is possibly misspelled. If it is intended to be \"augen müde\", it translates to \"eyes tired\" in English. However, for an accurate translation, please provide correct phrase or context."
[6] "The provided German phrase \"augen fatig\" is possibly misspelled. If it is intended to be \"augen müde\", it translates to \"eyes tired\" in English. However, for an accurate translation, please provide correct phrase or context."
[7] "compensation for shortage of skilled workers"
[8] "compensation for shortage of skilled workers"
[9] "comforting\" in German translates to \"beruhigend\" in English."
[10] "The word \"Eedbebenrettung\" appears to have a spelling error. If you mean \"Erdbebenrettung,\" the English translation would be \"earthquake rescue."
[11] "The word \"Eedbebenrettung\" appears to have a spelling error. If you mean \"Erdbebenrettung,\" the English translation would be \"earthquake rescue."
[12] "counteracting shortage of skilled workers"
[13] "counteracting shortage of skilled workers"
[14] "f\" does not correspond to a German word. It is an individual letter in the alphabet and holds the same value as it does in English. Please provide a word for translation."
[15] "if necessary, limited adjustment possibilities"
[16] "if necessary, limited adjustment possibilities"
[17] "The word \"Hifreich\" seems to not exist in the German language. Could you please check the spelling again?"
[18] "inacceptance among nursing home residents"
[19] "inacceptance among nursing home residents"
[20] "It seems like there might be a typo or two in your phrase as \"Nevorsität kurieren\" is not correct in German. It could be that you mean \"Nervosität kurieren\", which would be translated to English as \"cure nervousness\"."
[21] "It seems like there might be a typo or two in your phrase as \"Nevorsität kurieren\" is not correct in German. It could be that you mean \"Nervosität kurieren\", which would be translated to English as \"cure nervousness\"."
[22] "I'm sorry, but \"Rettungsentschedung\" doesn't appear to be a valid German word. Could you please check the spelling?"
[23] "I'm sorry, but \"Rettungsentschedung\" doesn't appear to be a valid German word. Could you please check the spelling?"
[24] "I'm sorry, but the word \"Züganglichkeit\" doesn't seem to exist in the German language. Could you please check the spelling?"
[25] "I'm sorry, but the word \"Züganglichkeit\" doesn't seem to exist in the German language. Could you please check the spelling?" drawnConcepts$check_text[nchar(x = drawnConcepts$text_translated) >= 40] <- 1# for text:
drawnConcepts$check_comment <- 0
str_subset(string = drawnConcepts$comment_translated, pattern = "adjectives")[1] "Be careful with spelling mistakes and do not use capital letters for adjectives."
[2] "For patient/client\n\n\" Be careful with spelling mistakes and do not use capital letters for adjectives. Only return the translation in quotes."
[3] "Negative aspects. Be careful with spelling mistakes and do not use capital letters for adjectives." tmp <- str_detect(string = drawnConcepts$comment_translated, pattern = "adjectives")
tmp[is.na(tmp)] <- FALSE
drawnConcepts$check_comment[tmp] <- 1save file
setwd("output translated file")
xlsx::write.xlsx2(x = drawnConcepts, file = "drawnConcepts_translated.xlsx")