How do I create a dataframe out of a long string which was supposed to be a dictionary?
I took the raw data the one you mentioned above and did some changes and considering the string to be
'{"\currentPortfolioId":null/"isNewRTQ":true/"isNewInvestmentTenure":true/"isNearTermVolatility":false/"getPath":true/"riskProfile":"Moderate"/"initialInvestment":200000/"cashflowDate":"01-01-2021"/"currentWealth":200000/"goalPriority":"Wish"/"rebalancing":"yearly"/"goalAmount":2000000/"startDate":"16-06-2021"/"endDate":"01-01-2031"/"isNewGoalPriority":true/"infusions":[0/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/0]/"scenario_type":"regular"/"infusion_type":"monthly"/"xforwardForValue":"49.228.234.102:49907/ 13.86.190.104:3072/ 172.30.217.148:36243"}'
Then the next thing to be done was to replace "/" with "," and "\" to "" using str.replace() in python. The output I got was -
'{"currentPortfolioId":null,"isNewRTQ":true,"isNewInvestmentTenure":true,"isNearTermVolatility":false,"getPath":true,"riskProfile":"Moderate","initialInvestment":200000,"cashflowDate":"01-01-2021","currentWealth":200000,"goalPriority":"Wish","rebalancing":"yearly","goalAmount":2000000,"startDate":"16-06-2021","endDate":"01-01-2031","isNewGoalPriority":true,"infusions":[0,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,0],"scenario_type":"regular","infusion_type":"monthly","xforwardForValue":"49.228.234.102:49907, 13.86.190.104:3072, 172.30.217.148:36243"}'
Then I simply used json.loads() function and the data was converted into the dictionary. The output would be something like this -
{'currentPortfolioId': None, 'isNewRTQ': True, 'isNewInvestmentTenure': True, 'isNearTermVolatility': False, 'getPath': True, 'riskProfile': 'Moderate', 'initialInvestment': 200000, 'cashflowDate': '01-01-2021', 'currentWealth': 200000, 'goalPriority': 'Wish', 'rebalancing': 'yearly', 'goalAmount': 2000000, 'startDate': '16-06-2021', 'endDate': '01-01-2031', 'isNewGoalPriority': True, 'infusions': [0, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 10000, 0], 'scenario_type': 'regular', 'infusion_type': 'monthly', 'xforwardForValue': '49.228.234.102:49907, 13.86.190.104:3072, 172.30.217.148:36243'} ​
I received an excel file containing some JSON data which looks like this:-

{\currentPortfolioId":null/"isNewRTQ":true/"isNewInvestmentTenure":true/"isNearTermVolatility":false/"getPath":true/"riskProfile":"Moderate"/"initialInvestment":200000/"cashflowDate":"01-01-2021"/"currentWealth":200000/"goalPriority":"Wish"/"rebalancing":"yearly"/"goalAmount":2000000/"startDate":"16-06-2021"/"endDate":"01-01-2031"/"isNewGoalPriority":true/"infusions":[0/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/10000/0]/"scenario_type":"regular"/"infusion_type":"monthly"/"xforwardForValue":"49.228.234.102:49907/ 13.86.190.104:3072/ 172.30.217.148:36243"}"

A point to note here is that the above data is just one cell of the excel file, there is similar data located in many other cells of the excel file, the key pairs are similar in all the data as the one mentioned above.
As I extracted the data into python I converted it into a dictionary, as the data is mangled with lots of "/" and "" I cleaned the data by removing these symbols and replacing "/" with "," to make it like a dictionary.
After cleaning I found out that the dictionary is mangled, as "" were missing in some places where it should be:-

1L: '{currentPortfolioId:null,isNewRTQ:true,isNewInvestmentTenure:true,isNearTermVolatility:false,getPath:true,riskProfile:Moderate,initialInvestment:200000,cashflowDate:01-01-2021,currentWealth:200000,goalPriority:Wish,rebalancing:yearly,goalAmount:2000000,startDate:16-06-2021,endDate:01-01-2031,isNewGoalPriority:true,infusions:0,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,0,scenario_type:regular,infusion_type:monthly,xforwardForValue:49.228.234.102:49907, 13.86.190.104:3072, 172.30.217.148:36243}'

As it is visible the data appears like a long long string after cleaning rather than a proper dict. How can I use this dict to make a dataframe which can contain the columns like given in the data:- riskprofile, inititalinvestment, riskprofile, cashflowdate, infusions etc. so that the subsequent values of those key pairs inside that long string falls into subsequent rows, just like a dataframe.