Convert a Pandas Core Series with Dictionary in Rows into Pandas Dataframe

user_11622176 0
Viewed 582

I have a Pandas Core series called "series_1" which has dictionaries of three variables ("V-Ratio", "Z-score", "P") in each row. It looks like this:

V1    [{'V-Ratio': 0.98, 'Z-score': -0.94, 'P': 0.17}]
V2    [{'V-Ratio': 0.97, 'Z-score': -1.34, 'P': 0.09}]
V3     [{'V-Ratio': 1.03, 'Z-score': 1.21, 'P': 0.89}]
V4     [{'V-Ratio': 1.04, 'Z-score': 1.41, 'P': 0.92}]
V5    [{'V-Ratio': 0.99, 'Z-score': -0.42, 'P': 0.34}]
V6    [{'V-Ratio': 0.96, 'Z-score': -1.72, 'P': 0.04}]
V7      [{'V-Ratio': 1.02, 'Z-score': 0.7, 'P': 0.76}]
V8    [{'V-Ratio': 0.97, 'Z-score': -1.09, 'P': 0.14}]
V9     [{'V-Ratio': 0.98, 'Z-score': -0.7, 'P': 0.24}]
V10     [{'V-Ratio': 0.76, 'Z-score': -9.31, 'P': 0.0}]
V11     [{'V-Ratio': 0.83, 'Z-score': -6.74, 'P': 0.0}]
V12    [{'V-Ratio': 0.92, 'Z-score': -3.14, 'P': 0.0}]
V13     [{'V-Ratio': 0.92, 'Z-score': -3.23, 'P': 0.0}]
V14     [{'V-Ratio': 0.87, 'Z-score': -4.99, 'P': 0.0}]
V15     [{'V-Ratio': 1.02, 'Z-score': 0.75, 'P': 0.77}]
V16       [{'V-Ratio': 0.9, 'Z-score': -3.7, 'P': 0.0}]
V17     [{'V-Ratio': 0.95, 'Z-score': -2.1, 'P': 0.02}]

I would like to convert it into a Pandas Dataframe that looks like this:

    V-Ratio Z-score  P
V1  0.98   -0.94    0.17
V2  0.97    1.34    0.09
V3  1.03    1.21    0.89
V4  1.04    1.41    0.92
V5  0.99   -0.42    0.34
.........................
.........................
.........................

What I have Tried:

  1. "series = pd.DataFrame(series_1)" gives me a dataframe with one column which has the dictionaries containing the variables in the rows.
  2. "series = pd.DataFrame([series_1])" doesn't achieve my results.
  3. "series = pd.DataFrame(series_1, index=[0])" doesn't work.

Does anyone have any clues on how I can fix my problem?

pandas series dataframe python
3 Answers

Please check

column represents the column name where you have list of dictionary.

df = df[column].apply(pd.Series)[0].apply(pd.Series)

Output

    V-Ratio Z-score P
V1  0.98    -0.94   0.17
V2  0.97    -1.34   0.09
V3  1.03    1.21    0.89
V4  1.04    1.41    0.92
V5  0.99    -0.42   0.34
user_135654940

I think the main problem is that, each of V1, V2, ... is a list of dictionaries, however each with only 1 element in the list. Ideally, you would want something like

[{'V-Ratio': 0.98, 'Z-score': -0.94, 'P': 0.17}, {'V-Ratio': 0.97, 'Z-score': -1.34, 'P': 0.09}, ....], each element corresponding to V1, V2 etc. but you have [[{'V-Ratio': 0.98, 'Z-score': -0.94, 'P': 0.17}], [{'V-Ratio': 0.97, 'Z-score': -1.34, 'P': 0.09}], ....] Please check that out. After that, you can try the steps you did, or convert the Series to list using .tolist() and converting to dataframe.

If you are not able to change that, you would have to convert only the first element of the list of each series to convert to a dataframe

user_99264720

Try this You need to get first dict from your list

# here, `s` is your series
df = s.str[0].apply(pd.Series)      
df
    V-Ratio  Z-score     P
v1     0.98    -0.94  0.17
v2     0.97    -1.34  0.09
v3     1.03     1.21  0.89
......

or

df = s.apply(lambda x: pd.Series(x[0]))
user_60756990
Related