How to write a list of values to a "cell" in a csv file?

user_3540149 0
Viewed 2836

I have a loop that produces a list of values for every iteration. I would like that list of values to be saved as a single string in a csv "cell". Specifically, what I have is the following:

Data

#a csv file that looks something like this:
index,username,user_id
1,name1,1
2,name2,2
...

users = pd.read_csv(csv_file, usecols= ['username', 'user_id'])

for row in users.itertuples(index=True, name='Pandas'):
     get_data(username, user_id)

get_data returns a list of strings. Instead of writing these strings in a comma-separated way to a csv file, I would like to write them in a nested way to the csv. Something like the following:

index,username,user_id,data
1,name1,1,[string1,string2,string3,string4,...]
2,name2,2,[string5,string6,string7,string8,...]
...

Update:

One of the reasons why I would like to store my results in this form is that I am planning on saving more data for each user. This data may be of different length than the output of my get_data call.

pandas csv python
1 Answers

Two key concepts to the problems:

  1. Pandas .to_csv() is quite smart in terms of auto-quoting. One should have no problem reading/writing a csv file without changing the default delimiter even if the data contains literal quotes or commas.

  2. Problem with reading such a format: The data cells, which were originally lists, were stored as strings in the csv file, so they are loaded as strings. ast.literal_eval() can convert a string representation of list back to a list. Caution: This measure may be vulnerable to dirty data. Be sure to perform data cleaning before saving the csv file.

Experiment Code

import pandas as pd
import ast  # for literal string parsing

# data
df = pd.DataFrame(
    data={
        "index": range(1,5),
        "username": [f"name{i}" for i in range(4)],
        "user_id": range(1,5),
        # initialize each data cell with an empty list
        "data": [list() for _ in range(4)]
    }
)

# use `.at[]` to append some values
df.at[0, "data"] += [1, 2, 3]
df.at[2, "data"] += [4]
# mixed types, quotes and commas
df.at[3, "data"] += [1, '"', -3.3, "'", ",,,", ";"]

print(df)

# save
file_path = "/mnt/ramdisk/out.csv"
df.to_csv(file_path)

# load
df_read = pd.read_csv(file_path, index_col=0)
# parse, because contents in data cells were loaded as strings
for i in range(len(df_read)):
    s = df_read.iat[i, 3]
    print(f"row {i} before: {type(s)}")
    df_read.iat[i, 3] = ast.literal_eval(s)
    print(f"       after: {type(s)}, len={len(s)}")

print(df_read)  # identical to df

# check elements within list
ls = df_read.iat[3,3]
for i in range(len(ls)):
    print(f"row {i}: {type(ls[i])}, contents={ls[i]}")

Results

(0) Original dataframe (no quoting was printed, but doesn't affect the data itself)

df
Out[7]: 
index username  user_id                     data
0      1    name0        1                [1, 2, 3]
1      2    name1        2                       []
2      3    name2        3                      [4]
3      4    name3        4  [1, ", -3.3, ', ,,,, ;]

(1) Raw csv file

bill@bill-laptop-deb: /mnt/ramdisk
$ cat out.csv                                                                                                                                                    
,index,username,user_id,data
0,1,name0,1,"[1, 2, 3]"
1,2,name1,2,[]
2,3,name2,3,[4]
3,4,name3,4,"[1, '""', -3.3, ""'"", ',,,', ';']"

(2) Re-load the file in python

df_read  # identical to input
Out[8]: 
index username  user_id                     data
0      1    name0        1                [1, 2, 3]
1      2    name1        2                       []
2      3    name2        3                      [4]
3      4    name3        4  [1, ", -3.3, ', ,,,, ;]

(3) LibreOffice 6 imports the csv file correctly as well.

(4) Type check on the loaded data

ls = df_read.iat[3,3]  # a mixed-up list
for i in range(len(ls)):
    print(f"row {i}: {type(ls[i])}, contents={ls[i]}")

row 0: <class 'int'>, contents=1
row 1: <class 'str'>, contents="
row 2: <class 'float'>, contents=-3.3
row 3: <class 'str'>, contents='
row 4: <class 'str'>, contents=,,,
row 5: <class 'str'>, contents=;

One can see that although quoting/escaping makes the file fairly hard-to-read to human eye, it should not be a problem to pandas and LibreOffice.

Side note: Using a database is strongly recommend for the sake of traceability and data integrity. Such a continuous data augmentation scenario is what database systems were designed for. If your project runs independently, SQLite should be a lightweight choice that is relatively easy in terms of deployment.

user_32186930
Related