Python - How to convert list back into it's original dataframe after using .apply(pd.eval) function?

user_17949832 0
Viewed 53

I have a panda dataframe (defined in my code as 'df1') where the data are text but I want them converted to integers/numbers. I have years (row/left column) and word descriptions (column) as my dataframe indices. Example below

     |Alpha   |Bravo   |Charlie |Delta  |   
2020 |1.98M   | -7.40M |0.00    |29.55K |
2021 |-5.30K  | 23.2B  |-       |35.01K |

I applied below code to clean the data above to ensure values can be recognised as integers rather than text:

repl_dict = {'−':'-', '—':'','[kK]': '*1e3', '[mM]': '*1e6', '[bB]': '*1e9'}
df2 = df1.replace(repl_dict, regex=True).apply(pd.eval)
print(df2)

The above code manages to convert my values to integers, however the .apply(pd.eval) function also reformats my dataframe by re-transposing, making each row of values back into a list, and my years disappear, and the list goes into one column. Example of output:

       |0                        |
Alpha  |[1980000, -5300]         |
Bravo  |[-74000000, 23200000000] |
Charlie|[0.00, 0.00]             |
Delta  |[29550, 35010]           |

Please can someone help, I have been unable to find a solution to make this work? I do not need to use .apply(pd.eval) function if there's another method to achieve the below output

Desired Output:

     |Alpha     |Bravo         |Charlie |Delta  |   
2020 |1980000   | -7400000     |0.00    |29550  |
2021 |-5300     | 23200000000  |-       |35010  |

Many thanks in advance!

Also tried:

.apply(pd.eval, axis=0, result_type = 'broadcast')

Returns "ValueError: cannot broadcast result"

pandas dataframe eval python
1 Answers

You can use the 'normal' eval, not 'pd.eval', combined with stack/unstack:

df1.stack().replace(repl_dict, regex=True).apply(eval).unstack()

output:


        Alpha        Bravo          Charlie Delta
2020    1980000.0   -7.400000e+06   0.0     29550.0
2021    -5300.0      2.320000e+10   NaN     35010.0

Note I had to tweak you repl_dict for this to work, but this may not be quite the right thing for you depending on how you construct your df1, the white spaces etc so you may have to experiment a bit:

repl_dict = {'−':'-', '-$':'None','[kK]': '*1e3', '[mM]': '*1e6', '[bB]': '*1e9'}
user_145514260
Related