How to select a specific key value of all cells in a Pandas dataframe?-CodePudding

I have a Pandas dataframe that consists of 57 columns, it looks like the following:

Each cell of this dataframe has a key named "Quantity", which is inside a dictionary. I want to select each "Quantity" key, so that I get a dataframe that looks like the following:

I know that it's possible to select only one column by typing something like: total = data1.str.get("Quantity"). But how do I select all my 57 columns in this dataframe? total = data[0:57].str.get("Quantity") does not seem to work. Could a for-loop be an idea?

CodePudding user response：

Suggest using applymap:

def extract_quantity(cell):
    if cell:
        return cell.get('Quantity')
    return cell


df = df.applymap(extract_quantity)

CodePudding user response：

pick out 'Quantity' from the values in the dataframe that are dicts:

import numpy as np
# convert df values into a list
l = df.values.tolist()
# get Quantity for the values which are a dict
vals = [i.get('Quantity') if isinstance(i,dict) else i for sublist in l for i in sublist]
# put back into a df
df = pd.DataFrame(np.array_split(vals,df.shape[1]))

df
    0   1   2
0   3   1   3
1   2   2   0
2   2   0   0