pandas loc function refers to different row when writing vs. reading -> ValueError

The ("clouds", "type", None) column has integer dtype:

In [28]: df[("clouds", "type", None)].dtype
Out[28]: dtype('int64')

So if you want to assign NumPy arrays to this column, first change the dtype to object:

df[("clouds", "type", None)] = df[("clouds", "type", None)].astype('object')

• Use df.at or df.iat to select or assign values to particular cells of a DataFrame.
• Use df.loc or df.iloc to select or assign values to columns, rows or sub-DataFrames.

Therefore, use df.at here:

df[("clouds", "type", None)] = df[("clouds", "type", None)].astype('object')
for i, val in values.items():
    for j, v in val.items():
        df.at[(i, j), ("clouds", "type", None)] = np.array(v)

which yields a df that looks like

      clouds                         group                        
     ceiling layer          type from_date from_hours from_minutes
         NaN   NaN           NaN       NaN        NaN          NaN
0  0       0     1        [None]         3          4            5
   1       6     7             8         9         10           11
1  0      12    13        [None]        15         16           17
   1      18    19        [None]        21         22           23
   2      24    25        [None]        27         28           29
   3      30    31        [None]        33         34           35
2  0      36    37        [None]        39         40           41
   1      42    43            44        45         46           47
   2      48    49        [None]        51         52           53
3  0      54    55        [None]        57         58           59
   1      60    61        [None]        63         64           65
   2      66    67        [None]        69         70           71
   3      72    73        [None]        75         76           77
   4      78    79        [None]        81         82           83
   5      84    85        [None]        87         88           89
4  0      90    91        [None]        93         94           95
5  0      96    97            98        99        100          101
6  0     102   103  [None, None]       105        106          107
7  0     108   109           110       111        112          113
8  0     114   115           116       117        118          119
9  0     120   121  [None, None]       123        124          125
...

Regarding the comment that you wish to use the cloud/type column for categorical data:

Columns with categorical data must contain hashable values. Generally, it does not make sense to make mutable objects hashable. So, for instance, Python mutable builtins (such as lists), or NumPy arrays are not hashable. But Python immutable builtins (such as tuples) are hashable. Therefore, if you use

df.at[(i, j), ("clouds", "type", None)] = tuple(v)

then you can make the ("clouds", "type", None) column of category dtype:

df[("clouds", "type", None)] = df[("clouds", "type", None)].astype('object')
for i, val in values.items():
    for j, v in val.items():
        df.at[(i, j), ("clouds", "type", None)] = tuple(v)

df[("clouds", "type", None)] = df[("clouds", "type", None)].astype('category')

Notice that it is necessary to first make the column of object dtype so that it may contain Python objects such as tuples, and then convert to category dtype only after all the possible values have been assigned.

Depending on what you want to do with the data, it might also make more sense to "tidy" the data by assigning only strings to the clouds/type column and using multiple rows instead of tuples:

For example,

6  0     102   103  'foo'       105        106          107
6  0     102   103  'bar'       105        106          107

instead of

6  0     102   103  ('foo', 'bar')       105        106          107

One advantage of using multiple rows is that selecting all rows with cloud/type 'foo' is now easy:

df.loc[df[("clouds", "type", None)] == 'foo']

or to select all rows with foo or bar cloud/type:

df.loc[df[("clouds", "type", None)].isin(['foo', 'bar'])]

If you use tuples, you would have to use something like

df.loc[[any(kind in item for kind in ('foo', 'bar')) 
       for item in df[("clouds", "type", None)]]]

Note only is this longer and harder to read, it is also slower.

One disadvantage of using multiple rows is that it create repeated data which may require greater memory usage. There may be ways around this, such as using multiple tables (and only joining them when required), but a discussion of this would be going way beyond the scope of this question.

So in summary, in general, use tidy data, use multiple rows, and keep your DataFrame dtypes simple -- use integers, floats whenever possible, 'strings' if necessary. Try to avoid using tuples, lists or NumPy arrays as DataFrame values.

I think you should either:

1. create one column per possible cloud layer (if order is important), or
2. use a bitmask, e.g. a column dtype of 'u8' which has 64 bits, and you can set as many cloud types as are applicable to that row (if order doesn't matter).