How can I save a tibble that contains a named list column to a csv file?

General
1

I'm having problems to save the output of a process back to csv.

I originally had two csv files containing email addresses. I used the following code to find the nearest match for each email from the first csv within the second csv.

DATA:

---dat1.csv------------------------------
Email,FullName
msmith@example.com.au,Matthew Smith
sprakash@example.com.au,Ishaan Prakash
mkumar@example.com.au,Menab Kumar
kwilliams@example.com.au,Kathryn Williams
cphilip@example.com.au,Christa Philip
ldavies@example.com.au,Lewis Davies
------------------------------------------

---dat2.csv------------------------------
Email,FullName
msmth@example.ogr.au,Matt Smith
sprakash@example.org,Ish Prakash
mkmr@example.com.au,Menab Kumar
kwilliamsATexample.com.au,Kat Williams
cjphilip@example.com.au,Christa J. Philip
ldav@example.com.au,Lewis Gerard Davies
------------------------------------------

library(tidyverse)

sfdc_dat <- read_csv("dat1.csv")
sfmc_dat <- read_csv("dat2.csv")

sfmc_dat$Email_SFDC <- sapply(sfmc_dat$Email,
                        function(x){
                          char_min <- stringdist::stringdist(x, sfdc_dat$EMAIL)
                          sfdc_dat[which.min(char_min), "EMAIL"]
                        })
head(sfmc_dat)

OUTPUT:

Email FullName Email_SFDC
<chr> <chr> <named list>
msmth@example.ogr.au Matt Smith msmith@example.com.au
sprakash@example.org Ish Prakash sprakash@example.com.au
mkmr@example.com.au Menab Kumar mkumar@example.com.au
kwilliamsATexample.com.au Kat Williams kwilliams@example.com.au
cjphilip@example.com.au Christa J. Philip cphilip@example.com.au
ldav@example.com.au Lewis Gerard Davies ldavies@example.com.au

The problem is that I ended up with a tibble, where Email_SFDC is a Named List.

When I tried to write my data back to a csv, it saves the file and creates the column Email_SFDC, but the column is empty, while in the tibble is not empty.

write_csv(sfmc_dat, 'File_Name.csv')
Email FullName Email_SFDC
mbea@example.ogr.au Matt Smith
sprakash@example.org Ish Prakash
mkmr@example.com.au Menab Kumar
kwilliamsATexample.com.au Kat Williams
cphilip@example.com.au Christa J. Philip
ldavies@example.com.au Lewis Gerard Davies

If I try

write.csv(sfmc_dat , 'File_Name.csv')

Error in utils::write.table(sfmc_dat, "File_Name2.csv", col.names = NA, : unimplemented type 'list' in 'EncodeElement'

Traceback:

1. write.csv(sfmc_dat, "File_Name2.csv")
2. eval.parent(Call)
3. eval(expr, p)
4. eval(expr, p)
5. utils::write.table(sfmc_dat, "File_Name2.csv", col.names = NA, 
 .     sep = ",", dec = ".", qmethod = "double")

It took me a whole weekend to process the data and I need to rescue my output, so re-writing and re-running my code is not an option at this point. I need to convert the Named List column to a Chr type.

I will appreciate any suggestions :slight_smile:

2

Welcome to the forum.
At a guess somethig lixe this might help
Convert named list with mixed content to data frame

However in general, we need a reproducible example (reprex) with some data.

A handy way to supply sample data is to use the dput() function. See ?dput.

1 Like
3

Hi jrkrideau,

Thank you for your comments. It's not a list with mixed content. It could all be converted to <chr> type. But it seems that having a <named list> type in the tibble is problematic to export to csv.

I edited my original post, so you can reproduce my example. Please, note that if you create the dataframe with the data directly there's no problem. But, if you read it from csv—which is completely necessary—the problem with the <named list> happens.

4

If proper reprocess is not an option, then simply unlist the variable

sfmc_dat$Email_SFDC = as.character(unlist(sfmc_dat$Email_SFDC))
1 Like
5

It works for me in simple way:

sfdc_dat <- read.csv("/home/sapi/dat1.csv")
sfmc_dat <- read.csv("/home/sapi/dat2.csv")

sfmc_dat$Email_SFDC <- sapply(sfmc_dat$Email,
                              function(x){
                                char_min <- stringdist::stringdist(x, sfdc_dat$Email)
                                sfdc_dat[which.min(char_min), "Email"]
                              })
head(sfmc_dat)
str(sfmc_dat)
write.csv(sfmc_dat, "/home/sapi/dupa.csv")

Please note, it's without tidyverse, please note "Email" instead of "EMAIL".

sapi@t5810:~$ cat dupa.csv 
"","Email","FullName","Email_SFDC"
"1","msmth@example.ogr.au","Matt Smith","msmith@example.com.au"
"2","sprakash@example.org","Ish Prakash","sprakash@example.com.au"
"3","mkmr@example.com.au","Menab Kumar","mkumar@example.com.au"
"4","kwilliamsATexample.com.au","Kat Williams","kwilliams@example.com.au"
"5","cjphilip@example.com.au","Christa J. Philip","cphilip@example.com.au"
"6","ldav@example.com.au","Lewis Gerard Davies","ldavies@example.com.au"
sapi@t5810:~$

If tidyverse and read_csv are required, you can always extract the values by

unlist(sfmc_dat$Email_SFDC[x], use.names = FALSE)

Regards,
Grzegorz

1 Like
6

Fascinating. I tried @lsatch's code last night and got his results. This morning I forgot to load *tidyverse *and got yours. So tidyverse is masking something.

1 Like
7

Thank you, all!

I marked @ andresrcs's answer as the solution.

But, I tested all your suggestions and all of them worked very well (gsapijaszko and jrkrideau).

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.