converting pandas column data from list of tuples to dict of dicts-CodePudding

I am trying to convert the pandas dataframe column values from -

{'01AB': [("ABC", 5),("XYZ", 4),("LMN", 1)], '02AB_QTY': [("Other", 20),("not_Other", 150)]}

this is what i have tried till now, but not working

import pandas as pd

df = pd.DataFrame.from_records([{'01AB': [("ABC", 5),("XYZ", 4),("LMN", 1)], '02AB_QTY': [("Other", 20),("not_Other", 150)]}])
col_list = ["01AB", "02AB_QTY",]

# for col in col_list:
#     df[col] = df[col].apply(lambda x: {} if x is None else {key: {v[0]:v[1] for v in list_item} for key, list_item in x.items()})

df

expected output is like

{'01AB': {"ABC":5,"XYZ":4,"LMN":1}, '02AB_QTY': {"Other":20,"not_Other":150}}

CodePudding user response：

We can use df.applymap(), with dict comprehension to convert each list to a dict, like this:

df[col_list] = df[col_list].applymap(lambda lst: {k: v for k, v in lst})

CodePudding user response：

import pandas as pd

df = pd.DataFrame.from_records([{'01AB': [("ABC", 5),("XYZ", 4),("LMN", 1)], '02AB_QTY': [("Other", 20),("not_Other", 150)]}])

out_dict = dict()
for col in df.columns:
    out_dict[col] = dict(df[col][0])

Output:

{'01AB': {'ABC': 5, 'XYZ': 4, 'LMN': 1},
 '02AB_QTY': {'Other': 20, 'not_Other': 150}}

CodePudding user response：

You can use a dictionary comprehension:

out = {k: dict(x) for k,v in df.iloc[0].to_dict().items()}

Output:

{'01AB': {'ABC': 5, 'XYZ': 4, 'LMN': 1},
 '02AB_QTY': {'ABC': 5, 'XYZ': 4, 'LMN': 1}}

CodePudding user response：

thanks for all the leads, I was able to resolve the issue like below -

import pandas as pd

df = pd.DataFrame.from_records([{'01AB': [("ABC", 5),("XYZ", 4),("LMN", 1)], '02AB_QTY': [("Other", 20),("not_Other", 150)]}])

col_list = ["01AB", "02AB_QTY",]

print(df)

for col in col_list:
    df[col] = df[col].apply(lambda x: {} if x is None else dict(x))

print(df)

OutPut -

01AB                            02AB_QTY
{'ABC': 5, 'XYZ': 4, 'LMN': 1}  {'Other': 20, 'not_Other': 150}