I have a problem coordinating ‘looked up’ data in a dictionary with new columns I need to create in a base dataframe that is constructed first. Here is a more detailed layout of the symptoms and my goals.
Base or original dataframe compiled from earlier Python Processes(REGULAR Symbol Data)
DateSym Symbol ... 'FIELD1' 'FIELD2'
0 20230110DDD DDD 0.395 -.5
1 20230110EEE EEE .16 6
2 20230110FFF FFF 1.22 22.1
3 20230111DDD DDD .335 -13.2
4 20230111EEE EEE .3341 -0.3967
5 20230111FFF FFF 3.5 4.2
Dictionary containing values I want to ‘lookup’ based on a concatenated key made up of a symbol e.g. ‘AAA’ and a date in ‘yyyymmdd’ format.(SPECIAL Symbol Data)
{'20230110AAA ': [‘AAA’, ‘22.01’, ‘322.11’], ’ 20230110BBB ': [‘BBB’, ‘230.19’, ‘33.25’], ’ 20230110CCC ': [‘CCC’, ‘18.19’, ‘13.25’]
'20230111AAA ': [‘AAA’, ‘.056’, ‘32.65’], ’ 20230111BBB ': [‘BBB’, ‘23.18’, ‘3.16’], ’ 20230111CCC ': [‘CCC’, ‘15.15’, 9.01] }
My system design requires that I string ‘SPECIAL’ data from the lookup all out on ONE ROW—REGULAR Symbol—data SPECIAL Symbol AAA—data then SPECIAL Symbol BBB—data etc.
- I take the ‘Date part’ from column ‘DateSym’ in REGULAR Symbol Data, create a loop attaching each of a LIST of SPECIAL Symbols[AAA,BBB,CCC] and concatenate a key, which looks like
‘20230110AAA’ and ‘lookup’ the values for that key in the dictionary and want to create NEW COLUMNS in the original dataframe for each of the multiple associated values in the dictionary
For that key.
HERE IS WHAT I WANT:
DateSym Symbol … ‘FIELD1’ ‘FIELD2’ ‘FIELD3’ ‘FIELD4’ ‘FIELD5’ ‘FIELD6’ ‘FIELD7’ ‘FIELD8’ ‘FIELD9’ ‘FIELD10’ ‘FIELD11’
0 20230110DDD DDD 0.395 -.5 AAA 22.01 322.11 BBB 230.19 33.25 CCC 18.19 13.25
1 20230110EEE EEE .16 6 AAA 22.01 322.11 BBB 230.19 33.25 CCC 18.19 13.25
2 20230110FFF FFF 1.22 22.1 AAA 22.01 322.11 BBB 230.19 33.25 CCC 18.19 13.25
3 20230111DDD DDD .335 -13.2 AAA .056 32.65 BBB 23.18 3.16 CCC 15.15 9.01
4 20230111EEE EEE .3341 -0.3967 AAA .056 32.65 BBB 23.18 3.16 CCC 15.15 9.01
5 20230111FFF FFF 3.5 4.2 AAA .056 32.65 BBB 23.18 3.16 CCC 15.15 9.01
THIS IS WHAT I GET(note only the LAST LOOKED UP values on each and every record—LIST of special symbols has 3 elements 0-2, I only get values associated with final iteration or third element(2)):
DateSym Symbol … ‘FIELD1’ ‘FIELD2’ ‘FIELD3’ ‘FIELD4’ ‘FIELD5’ ‘FIELD6’ ‘FIELD7’ ‘FIELD8’ ‘FIELD9’ ‘FIELD10’ ‘FIELD11’
0 20230110DDD DDD 0.395 -.5 AAA .056 32.65 BBB 23.18 3.16 CCC 15.15 9.01
1 20230110EEE EEE .16 6 AAA .056 32.65 BBB 23.18 3.16 CCC 15.15 9.01
2 20230110FFF FFF 1.22 22.1 AAA .056 32.65 BBB 23.18 3.16 CCC 15.15 9.01
3 20230111DDD DDD .335 -13.2 AAA .056 32.65 BBB 23.18 3.16 CCC 15.15 9.01
4 20230111EEE EEE .3341 -0.3967 AAA .056 32.65 BBB 23.18 3.16 CCC 15.15 9.01
5 20230111FFF FFF 3.5 4.2 AAA .056 32.65 BBB 23.18 3.16 CCC 15.15 9.01
This does NOT seem like a complex thing to do in any programming language. I just can’t seem to get it to work. I’ve tried various ideas from online suggestions. Can suggest a
Piece of code that can handle this? Thank you in advance.