0

I'm having a hard time figuring out the following. I have a dictionary in the following format.

x = dict(zip(df2['instrumentId'],df2['relatedInstruments']))
print(x.items())

dict_items([('1', "[{u'instrumentId': u'123456x', u'startDate': u'2000-03-06', u'relationshipType': u'144A'}]"), ('2', "[{u'instrumentId': 123456xx', u'startDate': u'2000-11-17', u'relationshipType': u'144A'}, {u'instrumentId': u'A9306xx', u'startDate': u'2000-11-17', u'relationshipType': u'REGS'}]"), ('3', "[{u'instrumentId': u'A13635xx', u'startDate': u'1998-02-23', u'relationshipType': u'144A'}]"),....

Essentially what Im trying to do is assign each related instrument to an instrumentId, which can be multiple values. The result should look like the below.

enter image description here

I have tried using DataFrame(), DataFrame.from_records(), and .from_dict() but no luck. What would be the correct approach for this issue I'm having?

Thanks in advance!

EDIT:

This is what the original dataframe lookslike

df2[['instrumentId','relatedInstruments']].head(2)

instrumentId    relatedInstruments
A123456 [{u'instrumentId': u'A1657501', u'startDate': u'2011-06-14', u'relationshipType': u'144A', u'endDate': u'2018-06-16'}]
A321456 [{u'instrumentId': u'A782951', u'startDate': u'2018-11-14', u'relationshipType': u'144A'}, {u'instrumentId': u'A782951', u'startDate': u'2011-04-05', u'relationshipType': u'144A', u'endDate': u'2018-11-14'}]
Cesar
  • 617
  • 3
  • 8
  • 17
  • 1
    Please post a [sample of original data](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) which appears to be a data frame itself. Did you import using json, xml, or some other nested structure? If so, please post a sample of that input. – Parfait Sep 16 '19 at 15:34
  • @Parfait Really sorry about that. I've added what the input data looks like. All I got was the dF format. I did not import it using json/xml. – Cesar Sep 16 '19 at 15:55
  • Curious then, how did you get data in such a format? Surely not through `read_csv`. – Parfait Sep 16 '19 at 16:36

0 Answers0