1
$\begingroup$

I have recently taken up a data wrangling task and inherited a data from my predecessor which contains keyphrases extracted by hand like the following :

data1 = data.frame (ID = c(1,2,3,4,5),
               Date = c(20081011,20081011,20081011,20081011,20081011),
               name = c("John Doe1", "John Doe2", "John Doe3","John Doe4","John Doe5"),
               numeric= c(1,1,1,0,2),
               keyphrase1 = c("mutual interests","support democratic development","fundamental political reforms","minimum conditions not in place","friends"),
               keyphrase2 = c("trade",NA,"economic reforms","elections","speak same language"),
               keyphrase3 = c("justice",NA,NA,"referendum","same values"))

And I have a dictionary within which these key phrases are assigned to different labels, yet again by hand, like the following:

dict = data.frame (Conditionality = c("same values","minimum conditions not in place","speak same language",
                                  "economic reforms","fundamental political reforms","economic reforms"),
                   Interest = c("trade","mutual interests",NA,NA,NA,NA),
                   Politics = c("justice","referendum","elections",NA,NA,NA))

So my task is to create binary variables in the data1 for each label in the dict data and create a data like the following:

data2 = data.frame (ID = c(1,2,3,4,5),
               Date = c(20081011,20081011,20081011,20081011,20081011),
               name = c("John Doe1", "John Doe2", "John Doe3","John Doe4","John Doe5"),
               numeric= c(1,1,1,0,2),
               keyphrase1 = c("mutual interests","support democratic development","fundamental political reforms",
                              "minimum conditions not in place","friends"),
               keyphrase2 = c("trade",NA,"economic reforms","elections","speak same language"),
               keyphrase3 = c("justice",NA,NA,"referendum","same values"),
               Conditionality = c(1,0,1,1,1),
               Interest = c(1,0,0,0,0),
               Politics = c(1,0,0,1,0)
               )

As you can see the new variables indicate whether one or more keyphrases in the data1 fall under a particular label in the dict data or not.

The problem is the actual data1 is relatively large and riddled with NA's. So far, I have tried transforming both the data1 and the dict to long format, get rid of the NAs in the process and merge by phrases using dplyr join functions, merge function from the base. They did not work very well. So any suggestions on how can I go about it ?

The second problem is when I am done with the task, I am supposed to deliver a wide format data and as you can imagine once I transpose the data to long, I can not transpose it back to wide because not each rows, naturally, uniquely identified. Any suggestion would be highly appreciated !

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.