Insert list in pandas dataframe cell

Question

I have a dictionary where each key has a list of values. Length of the list associated with each key is different. I want to convert the dictionary into a pandas dataframe with two columns 'Key' and 'Values'. Each row having one dictionary key in the 'Key' column and the list of values associated with it in 'Values' column. The dataframe will look as follows:

mapping_dict = {'A':['a', 'b', 'c', 'd'], 'B':['aa', 'bb', 'cc']}

df = 
    Key   Value
0   A     ['a', 'b', 'c', 'd']
1   B     ['aa', 'bb', 'cc']

I tried using the answer provided here by modifying it as per my use case. But it didn't output the required answer.

Solution 1

Use pd.Series inside constructor, since dict values sizes are not equal, then set_axis to add column names i.e

mapping_dict = {'A':['a', 'b', 'c', 'd'], 'B':['aa', 'bb', 'cc']}

df = pd.DataFrame(pd.Series(mapping_dict).reset_index()).set_axis(['Key','Value'],1,inplace=False)

  Key         Value
0   A  [a, b, c, d]
1   B  [aa, bb, cc]

Option 2 , convert the dict items to list then pass it to constructor:

df = pd.DataFrame(list(mapping_dict.items()),columns=['Key','Value'])

Solution 2

If you pass a list, pandas considers it as several rows. However, you can trick it by placing your list as the single element of an outer list as bellow:

import pandas as pd
mapping_dict = {'A':[['a', 'b', 'c', 'd']], 'B':[['aa', 'bb', 'cc']]}
df = pd.DataFrame(mapping_dict)
df

        A                 B
0   [a, b, c, d]    [aa, bb, cc]

Solution 3

I think you might have to update your dictionary beforehand then you can use from_dict. Update to make your dictionary to make it a list of list.

import pandas as pd
mapping_dict = {'A':['a', 'b', 'c', 'd'], 'B':['aa', 'bb', 'cc']}
updated_dict = {k: [v] for k, v in mapping_dict.items()}
df = pd.DataFrame.from_dict(updated_dict,orient='index')

If you want your exact formatting

df_formatted = df.reset_index()
df_formatted.columns = ['Key', 'Value']
print(df_formatted)

  Key         Value
0   B  [aa, bb, cc]
1   A  [a, b, c, d]

UPDATE

Bharath's answer is shorter but if you still want to use from_dict then you can take part of his method to do

df2 = pd.DataFrame.from_dict(list(mapping_dict.items()))
df2.columns = ['Key', 'Value']

Solution 4

You can do it simply by using the "at" setter from pandas:

df.at[0,'A'] = ['a', 'b', 'c', 'd']

It works if column dtype is "object".

View more solutions