Python pandas insert list into a cell
Solution 1
df3.set_value(1, 'B', abc)
works for any dataframe. Take care of the data type of column 'B'. For example, a list can not be inserted into a float column, at that case df['B'] = df['B'].astype(object)
can help.
Solution 2
Since set_value
has been deprecated since version 0.21.0, you should now use at
. It can insert a list into a cell without raising a ValueError
as loc
does. I think this is because at
always refers to a single value, while loc
can refer to values as well as rows and columns.
df = pd.DataFrame(data={'A': [1, 2, 3], 'B': ['x', 'y', 'z']})
df.at[1, 'B'] = ['m', 'n']
df =
A B
0 1 x
1 2 [m, n]
2 3 z
You also need to make sure the column you are inserting into has dtype=object
. For example
>>> df = pd.DataFrame(data={'A': [1, 2, 3], 'B': [1,2,3]})
>>> df.dtypes
A int64
B int64
dtype: object
>>> df.at[1, 'B'] = [1, 2, 3]
ValueError: setting an array element with a sequence
>>> df['B'] = df['B'].astype('object')
>>> df.at[1, 'B'] = [1, 2, 3]
>>> df
A B
0 1 1
1 2 [1, 2, 3]
2 3 3
Solution 3
Pandas >= 0.21
set_value
has been deprecated. You can now use DataFrame.at
to set by label, and DataFrame.iat
to set by integer position.
Setting Cell Values with at
/iat
# Setup
>>> df = pd.DataFrame({'A': [12, 23], 'B': [['a', 'b'], ['c', 'd']]})
>>> df
A B
0 12 [a, b]
1 23 [c, d]
>>> df.dtypes
A int64
B object
dtype: object
If you want to set a value in second row of the "B" column to some new list, use DataFrame.at
:
>>> df.at[1, 'B'] = ['m', 'n']
>>> df
A B
0 12 [a, b]
1 23 [m, n]
You can also set by integer position using DataFrame.iat
>>> df.iat[1, df.columns.get_loc('B')] = ['m', 'n']
>>> df
A B
0 12 [a, b]
1 23 [m, n]
What if I get ValueError: setting an array element with a sequence
?
I'll try to reproduce this with:
>>> df
A B
0 12 NaN
1 23 NaN
>>> df.dtypes
A int64
B float64
dtype: object
>>> df.at[1, 'B'] = ['m', 'n']
# ValueError: setting an array element with a sequence.
This is because of a your object is of float64
dtype, whereas lists are object
s, so there's a mismatch there. What you would have to do in this situation is to convert the column to object first.
>>> df['B'] = df['B'].astype(object)
>>> df.dtypes
A int64
B object
dtype: object
Then, it works:
>>> df.at[1, 'B'] = ['m', 'n']
>>> df
A B
0 12 NaN
1 23 [m, n]
Possible, But Hacky
Even more wacky, I've found that you can hack through DataFrame.loc
to achieve something similar if you pass nested lists.
>>> df.loc[1, 'B'] = [['m'], ['n'], ['o'], ['p']]
>>> df
A B
0 12 [a, b]
1 23 [m, n, o, p]
You can read more about why this works here.
Solution 4
Quick work around
Simply enclose the list within a new list, as done for col2 in the data frame below. The reason it works is that python takes the outer list (of lists) and converts it into a column as if it were containing normal scalar items, which is lists in our case and not normal scalars.
mydict={'col1':[1,2,3],'col2':[[1, 4], [2, 5], [3, 6]]}
data=pd.DataFrame(mydict)
data
col1 col2
0 1 [1, 4]
1 2 [2, 5]
2 3 [3, 6]
Solution 5
Also getting
ValueError: Must have equal len keys and value when setting with an iterable
,
using .at rather than .loc did not make any difference in my case, but enforcing the datatype of the dataframe column did the trick:
df['B'] = df['B'].astype(object)
Then I could set lists, numpy array and all sorts of things as single cell values in my dataframes.
Comments
-
ragesz over 2 years
I have a list 'abc' and a dataframe 'df':
abc = ['foo', 'bar'] df = A B 0 12 NaN 1 23 NaN
I want to insert the list into cell 1B, so I want this result:
A B 0 12 NaN 1 23 ['foo', 'bar']
Ho can I do that?
1) If I use this:
df.ix[1,'B'] = abc
I get the following error message:
ValueError: Must have equal len keys and value when setting with an iterable
because it tries to insert the list (that has two elements) into a row / column but not into a cell.
2) If I use this:
df.ix[1,'B'] = [abc]
then it inserts a list that has only one element that is the 'abc' list (
[['foo', 'bar']]
).3) If I use this:
df.ix[1,'B'] = ', '.join(abc)
then it inserts a string: (
foo, bar
) but not a list.4) If I use this:
df.ix[1,'B'] = [', '.join(abc)]
then it inserts a list but it has only one element (
['foo, bar']
) but not two as I want (['foo', 'bar']
).Thanks for help!
EDIT
My new dataframe and the old list:
abc = ['foo', 'bar'] df2 = A B C 0 12 NaN 'bla' 1 23 NaN 'bla bla'
Another dataframe:
df3 = A B C D 0 12 NaN 'bla' ['item1', 'item2'] 1 23 NaN 'bla bla' [11, 12, 13]
I want insert the 'abc' list into
df2.loc[1,'B']
and/ordf3.loc[1,'B']
.If the dataframe has columns only with integer values and/or NaN values and/or list values then inserting a list into a cell works perfectly. If the dataframe has columns only with string values and/or NaN values and/or list values then inserting a list into a cell works perfectly. But if the dataframe has columns with integer and string values and other columns then the error message appears if I use this:
df2.loc[1,'B'] = abc
ordf3.loc[1,'B'] = abc
.Another dataframe:
df4 = A B 0 'bla' NaN 1 'bla bla' NaN
These inserts work perfectly:
df.loc[1,'B'] = abc
ordf4.loc[1,'B'] = abc
.-
EdChum over 9 yearsWhat version pandas are you using? the following worked using pandas
0.15.0
:df.loc[1,'b'] = ['foo','bar']
-
ragesz over 9 yearsThank you! I use Python 2.7 and I tried pandas 0.14.0 and 0.15.0 and it worked with the test data above. But what if I have a 'C' column as well with some integer values? 'A' has strings. Having an integer column and a srting column I get the same error: ValueError: Must have equal len keys and value when setting with an iterable
-
EdChum over 9 yearsYou're going to have to post data and code to explain and show what you mean
-
-
Thomas about 6 yearsNote that this command has been deprecated. There is an update right below.
-
Naveen Kumar over 2 yearsI am getting empty lists in col2, even though I populated them