Creating a Pandas DataFrame from a dictionary that contains key and nested list of dictionary items

user_14968676 0
Viewed 45

I'm hoping to find an answer to an issue I'm facing. I am currently trying to scrape financial data for a project and am in the final step of creating a DataFrame to store my data for downstream manipulation.

My issue is that I have created a dictionary of key value pairs, where the key is a company symbol, and the value pair is a list that contains a nested dictionary of key value pairs which correspond to the market data of that company. Below is an example of the raw data.

{'MMM': [{'Market Cap': '102.234B'},
  {'Beta (5Y Monthly)': '0.95'},
  {'PE Ratio (TTM)': '19.09'},
  {'EPS (TTM)': '9.25'},
  {'Earnings Date': 'Apr 26, 2021 - Apr 30, 2021'},
  {'Forward Dividend & Yield': '5.92 (3.35%)'},
  {'Ex-Dividend Date': 'Feb 11, 2021'},
  {'1y Target Est': '183.43'}],
 'AXP': [{'Market Cap': '106.104B'},
  {'Beta (5Y Monthly)': '1.28'},
  {'PE Ratio (TTM)': '34.94'},
  {'EPS (TTM)': '3.77'},
  {'Earnings Date': 'Apr 22, 2021 - Apr 26, 2021'},
  {'Forward Dividend & Yield': '1.72 (1.31%)'},
  {'Ex-Dividend Date': 'Jan 07, 2021'},
  {'1y Target Est': '130.32'}],
 'AMGN': [{'Market Cap': '133.816B'},
  {'Beta (5Y Monthly)': '0.73'},
  {'PE Ratio (TTM)': '18.82'},
  {'EPS (TTM)': '12.31'},

I have tried to create a DataFrame using .from_dict and an index orientation but this is the result I get..

    0   1   2   3   4   5   6   7
MMM {'Market Cap': '102.234B'}  {'Beta (5Y Monthly)': '0.95'}   {'PE Ratio (TTM)': '19.09'} {'EPS (TTM)': '9.25'}   {'Earnings Date': 'Apr 26, 2021 - Apr 30, 2021'}    {'Forward Dividend & Yield': '5.92 (3.35%)'}    {'Ex-Dividend Date': 'Feb 11, 2021'}    {'1y Target Est': '183.43'}
AXP {'Market Cap': '106.104B'}  {'Beta (5Y Monthly)': '1.28'}   {'PE Ratio (TTM)': '34.94'} {'EPS (TTM)': '3.77'}   {'Earnings Date': 'Apr 22, 2021 - Apr 26, 2021'}    {'Forward Dividend & Yield': '1.72 (1.31%)'}    {'Ex-Dividend Date': 'Jan 07, 2021'}    {'1y Target Est': '130.32'}
AMGN    {'Market Cap': '133.816B'}  {'Beta (5Y Monthly)': '0.73'}   {'PE Ratio (TTM)': '18.82'} {'EPS (TTM)': '12.31'}  {'Earnings Date': 'Apr 28, 2021 - May 03, 2021'}    {'Forward Dividend & Yield': '7.04 (3.04%)'}    {'Ex-Dividend Date': 'Feb 11, 2021'}    {'1y Target Est': '254.20'}

How would I go about extracting the key/value pairs, such that the keys are the column names and the values are the datapoints? Any help would be appreciated... thanks

pandas dictionary python
1 Answers

You can flatten nested dictionaries first and pass to DataFrame.from_dict:

d = {k: {k1: v1 for y in v for k1, v1 in y.items()} for k, v in d.items()}

df = pd.DataFrame.from_dict(d, orient='index')

Solution with convert nested values to Series and then use concat with tranpose:

df = pd.concat({k: pd.concat([pd.Series(y) for y in v]) for k, v in d.items()}, axis=1).T
print (df)
    Market Cap Beta (5Y Monthly) PE Ratio (TTM) EPS (TTM)  \
MMM   102.234B              0.95          19.09      9.25   
AXP   106.104B              1.28          34.94      3.77   

                   Earnings Date Forward Dividend & Yield Ex-Dividend Date  \
MMM  Apr 26, 2021 - Apr 30, 2021             5.92 (3.35%)     Feb 11, 2021   
AXP  Apr 22, 2021 - Apr 26, 2021             1.72 (1.31%)     Jan 07, 2021   

    1y Target Est  
MMM        183.43  
AXP        130.32
user_29010020
Related