0

I am trying to convert a dictionary within a list to a dataframe with keys as the columns name. Below is the sample data.

df = [{'id': '755', 'player_name': 'Jamie Vardy', 'games': '35', 'time': '3034', 'goals': '23', 'xG': '18.903537318110466', 'assists': '5', 'xA': '6.3682975601404905', 'shots': '89', 'key_passes': '32', 'yellow_cards': '3', 'red_cards': '0', 'position': 'F S', 'team_title': 'Leicester', 'npg': '19', 'npxG': '15.097693115472794', 'xGChain': '21.02660731226206', 'xGBuildup': '1.7243406660854816'}, {'id': '318', 'player_name': 'Pierre-Emerick Aubameyang', 'games': '36', 'time': '3143', 'goals': '22', 'xG': '16.352623080834746', 'assists': '3', 'xA': '4.492486916482449', 'shots': '93', 'key_passes': '26', 'yellow_cards': '3', 'red_cards': '1', 'position': 'F M S', 'team_title': 'Arsenal', 'npg': '20', 'npxG': '14.830358987674117', 'xGChain': '19.964282035827637', 'xGBuildup': '5.339657470583916'}]

I could call each dictionary like df[0] and keys as df[0].keys().

I use the following code to convert to dataframe.

cols = list (df[0].keys())
df_new = pd.DataFrame.from_dict(df[0], orient='index',
                  columns = cols )

It threw me the error : ValueError: Shape of passed values is (18, 1), indices imply (18, 18) Can someone advise me?

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
Zephyr
  • 1,332
  • 2
  • 13
  • 31

1 Answers1

1

IIUC, the DataFrame constructor should do the job:

out = pd.DataFrame(df)

Output:

    id                player_name games  time goals                  xG  \
0  755                Jamie Vardy    35  3034    23  18.903537318110466   
1  318  Pierre-Emerick Aubameyang    36  3143    22  16.352623080834746   

  assists                  xA shots key_passes yellow_cards red_cards  \
0       5  6.3682975601404905    89         32            3         0   
1       3   4.492486916482449    93         26            3         1   

  position team_title npg                npxG             xGChain  \
0      F S  Leicester  19  15.097693115472794   21.02660731226206   
1    F M S    Arsenal  20  14.830358987674117  19.964282035827637   

            xGBuildup  
0  1.7243406660854816  
1   5.339657470583916

This creates a DataFrame of shape (2, 18).

If you wanted to make the dict keys as indices instead, one option is to convert df to a dict and pass to DataFrame.from_dict:

out = pd.DataFrame(dict(enumerate(df)))

This produces a DataFrame of shape (18, 2).