Save numpy array into pandas cell

user_9557222 0
Viewed 1560

Here's my code that places some numpy arrays into cells:

for name in tqdm(names):
   img = PIL.Image.open(os.path.join(TRAIN_IMG_DIR, name)).convert('RGB')
   img = np.array(img)
   idxs = tile(img)
   mydict[name] = [idxs]

df = pd.DataFrame.from_dict(mydict, orient='index').reset_index()
df.columns = ['ImageId', 'TileIds']
df.to_csv('36x224x224_otsu.csv')

Which gives:

ImageId TileIds
0   0eacb18986da2b25c5d82bd9676536e0_1.jpeg [50, 59, 122, 115, 150, 74, 66, 58, 143, 67, 2...
1   1796a7081ed86d83fddf677904d3843f_1.jpeg [96, 56, 66, 106, 43, 76, 86, 47, 45, 25, 116,...
2   9459f98d4b344c82d5c9b45a7af81b53_1.jpeg [375, 392, 184, 116, 248, 167, 217, 297, 99, 3...

So far so good but when I load the csv file and display it I've got:

    ImageId TileIds
0   0452381085998676ae1e3c877df5bd4d_1.jpeg [ 7 21 1 58 69 14 15 64 74 27 8 40 47 34 79 ...
1   bbb2c6d4f203c71ba63d6ce8048d1ebc_1.jpeg [171 193 68 159 113 79 136 67 124 102 57 2...
2   ee7818bcdaf1b51212aa523778f68ee8_1.jpeg [206 224 191 172 223 197 180 190 42 251 162 2...

The commas have disappeared and some space were inserted so I cannot split by space. My goal is to use that array for indexing a list of images. I've tried various things such as pickle and changing the delimiter but it was a fail so far. Would appreciate some help thanks !

pandas numpy-ndarray csv numpy python
2 Answers

A dataframe is basically a 2d structure. Putting lists or arrays in cells means Python objects in those cells

To illustrate, make a frame with an array in one cell, a list in the other:

In [347]: df = pd.DataFrame([None,None], columns=['a'])                         
In [348]: df                                                                    
Out[348]: 
      a
0  None
1  None
In [349]: df['a'][1]=[1,2,3]                                                    
In [350]: df['a'][0]=np.array([1,2,3])                                          
In [351]: df                                                                    
Out[351]: 
           a
0  [1, 2, 3]
1  [1, 2, 3]

save it:

In [355]: df.to_csv('pd.csv', index=False)                                      
In [356]: cat pd.csv                                                            
a
[1 2 3]
"[1, 2, 3]"

The array has been saved in its print/str format (with [] and without comma). The list too is saved as string. It may be quoted so the commas don't confuse the default comma delimiter.

read_csv loads both of those cells as strings. It does not try to convert them back into some sort Python object:

In [357]: df1 = pd.read_csv('pd.csv')                                           
In [358]: df1                                                                   
Out[358]: 
           a
0    [1 2 3]
1  [1, 2, 3]
In [359]: type(df1['a'][0])                                                     
Out[359]: str
In [360]: type(df1['a'][1])                                                     
Out[360]: str

The list string can be converted back to string easily:

In [361]: eval(df1['a'][1])                                                     
Out[361]: [1, 2, 3]

Converting the array str takes more work. That format is not intended for recreating an array. For large arrays (more than 1000 items) it will even have ellipsis - missing values.

Putting arrays in dataframe cells is inherently awkward. This save/load issue is just one problem.

user_9019250

A simple workaround would be to convert list to strings before saving it. Then, after loading it, split the string back to list. This will ensure the same result.

df["TileIds"] = df["TileIds"].str.join(",")
df.to_csv("df.csv", index=False)

Then, after loading:

df = pd.read_csv("df.csv")
df["TileIds"] = df["TileIds"].str.split(',')
user_92992590
Related