Change Excel Dates to Number Ranges

Question

I have some data that excel decided to take some liberties with-- something I'm sure we can all relate to.

I have some number ranges that are important for the data I'm working with, and in some cases it converted the range in the column to a date. I need to convert it back to a number range. I'm struggling to think of an easy way to do this. I have a feeling that I am going to need to keep this code for when I want to work with this data again in the future. I've tried replacing the month abbreviation, but realized that this gives the range the wrong order if the "date" is less than the month.

data = {'Col1': ['a', 'b', 'c'],
        'Col2': ['101-200', 'Aug-1', 'Sep-8']}

goal = {'Col1': ['a', 'b', 'c'],
        'Col2': ['101-200', '1-8', '8-9']}

current_df = pd.DataFrame(data)
goal_df = pd.DataFrame(goal)

CURRENT:

  Col1     Col2
0    a  101-200
1    b    Aug-1
2    c    Sep-8

GOAL:

  Col1     Col2
0    a  101-200
1    b      1-8
2    c      8-9

Here is a dictionary with the first 100 rows of my data, right out of the csv. I am interested in the Shares Column. I've done some other things in my code to clean the data up to where I want it, but this is still stumping me.

data = {'Publication Date': {0: '21-Sep-21', 1: '21-Sep-21', 2: '21-Sep-21', 3: '21-Sep-21', 4: '21-Sep-21', 5: '21-Sep-21', 6: '21-Sep-21', 7: '21-Sep-21', 8: '21-Sep-21', 9: '21-Sep-21', 10: '21-Sep-21', 11: '21-Sep-21', 12: '21-Sep-21', 13: '21-Sep-21', 14: '21-Sep-21', 15: '21-Sep-21', 16: '21-Sep-21', 17: '21-Sep-21', 18: '21-Sep-21', 19: '21-Sep-21', 20: '21-Sep-21', 21: '21-Sep-21', 22: '21-Sep-21', 23: '21-Sep-21', 24: '21-Sep-21', 25: '21-Sep-21', 26: '21-Sep-21', 27: '21-Sep-21', 28: '21-Sep-21', 29: '21-Sep-21', 30: '21-Sep-21', 31: '21-Sep-21', 32: '21-Sep-21', 33: '21-Sep-21', 34: '21-Sep-21', 35: '21-Sep-21', 36: '21-Sep-21', 37: '21-Sep-21', 38: '21-Sep-21', 39: '21-Sep-21', 40: '21-Sep-21', 41: '21-Sep-21', 42: '21-Sep-21', 43: '21-Sep-21', 44: '21-Sep-21', 45: '21-Sep-21', 46: '21-Sep-21', 47: '21-Sep-21', 48: '21-Sep-21', 49: '21-Sep-21', 50: '21-Sep-21', 51: '21-Sep-21', 52: '21-Sep-21', 53: '21-Sep-21', 54: '21-Sep-21', 55: '21-Sep-21', 56: '21-Sep-21', 57: '21-Sep-21', 58: '21-Sep-21', 59: '21-Sep-21', 60: '16-Sep-21', 61: '16-Sep-21', 62: '16-Sep-21', 63: '16-Sep-21', 64: '16-Sep-21', 65: '16-Sep-21', 66: '16-Sep-21', 67: '16-Sep-21', 68: '16-Sep-21', 69: '16-Sep-21', 70: '14-Sep-21', 71: '15-Sep-21', 72: '15-Sep-21', 73: '15-Sep-21', 74: '15-Sep-21', 75: '14-Sep-21', 76: '15-Sep-21', 77: '15-Sep-21', 78: '14-Sep-21', 79: '14-Sep-21', 80: '15-Sep-21', 81: '15-Sep-21', 82: '15-Sep-21', 83: '15-Sep-21', 84: '15-Sep-21', 85: '15-Sep-21', 86: '14-Sep-21', 87: '14-Sep-21', 88: '14-Sep-21', 89: '14-Sep-21', 90: '14-Sep-21', 91: '14-Sep-21', 92: '14-Sep-21', 93: '14-Sep-21', 94: '14-Sep-21', 95: '14-Sep-21', 96: '14-Sep-21', 97: '14-Sep-21', 98: '14-Sep-21', 99: '14-Sep-21'}, 
'Politician': {0: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 1: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 2: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 3: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 4: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 5: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 6: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 7: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 8: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 9: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 10: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 11: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 12: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 13: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 14: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 15: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 16: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 17: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 18: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 19: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 20: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 21: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 22: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 23: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 24: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 25: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 26: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 27: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 28: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 29: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 30: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 31: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 32: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 33: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 34: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 35: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 36: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 37: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 38: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 39: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 40: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 41: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 42: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 43: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 44: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 45: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 46: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 47: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 48: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 49: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 50: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 51: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 52: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 53: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 54: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 55: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 56: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 57: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 58: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 59: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 60: 'Hern, Kevin RayRep (R - OK)', 61: 'Hern, Kevin RayRep (R - OK)', 62: 'Hern, Kevin RayRep (R - OK)', 63: 'Hern, Kevin RayRep (R - OK)', 64: 'Hern, Kevin RayRep (R - OK)', 65: 'Hern, Kevin RayRep (R - OK)', 66: 'Hern, Kevin RayRep (R - OK)', 67: 'Hern, Kevin RayRep (R - OK)', 68: 'Hern, Kevin RayRep (R - OK)', 69: 'Hern, Kevin RayRep (R - OK)', 70: 'McConnell, Addison Mitchell, Jr (Mitch)Sen (R - KY)', 71: 'DelBene, Suzan Kay OliverRep (D - WA)', 72: 'Banks, James Edward (Jim)Rep (R - IN)', 73: 'DelBene, Suzan Kay OliverRep (D - WA)', 74: 'Banks, James Edward (Jim)Rep (R - IN)', 75: 'Capito, Shelley Wellons MooreSen (R - WV)', 76: 'Banks, James Edward (Jim)Rep (R - IN)', 77: 'Banks, James Edward (Jim)Rep (R - IN)', 78: 'Capito, Shelley Wellons MooreSen (R - WV)', 79: 'Capito, Shelley Wellons MooreSen (R - WV)', 80: 'DelBene, Suzan Kay OliverRep (D - WA)', 81: 'DelBene, Suzan Kay OliverRep (D - WA)', 82: 'DelBene, Suzan Kay OliverRep (D - WA)', 83: 'DelBene, Suzan Kay OliverRep (D - WA)', 84: 'Jacobs, Christopher L (Chris)Rep (R - NY)', 85: 'Jacobs, Christopher L (Chris)Rep (R - NY)', 86: 'Lowenthal, Alan StuartRep (D - CA)', 87: 'Lowenthal, Alan StuartRep (D - CA)', 88: 'Lowenthal, Alan StuartRep (D - CA)', 89: 'Lowenthal, Alan StuartRep (D - CA)', 90: 'Lowenthal, Alan StuartRep (D - CA)', 91: 'Lowenthal, Alan StuartRep (D - CA)', 92: 'Lowenthal, Alan StuartRep (D - CA)', 93: 'Lowenthal, Alan StuartRep (D - CA)', 94: 'McKinley, David BennettRep (R - WV)', 95: 'McKinley, David BennettRep (R - WV)', 96: 'McKinley, David BennettRep (R - WV)', 97: 'McKinley, David BennettRep (R - WV)', 98: 'McKinley, David BennettRep (R - WV)', 99: 'McKinley, David BennettRep (R - WV)'}, 
'Owner': {0: 'Joint', 1: 'Joint', 2: 'Joint', 3: 'Joint', 4: 'Joint', 5: 'Joint', 6: 'Joint', 7: 'Joint', 8: 'Joint', 9: 'Joint', 10: 'Joint', 11: 'Joint', 12: 'Joint', 13: 'Joint', 14: 'Joint', 15: 'Joint', 16: 'Joint', 17: 'Joint', 18: 'Joint', 19: 'Joint', 20: 'Joint', 21: 'Joint', 22: 'Joint', 23: 'Joint', 24: 'Joint', 25: 'Joint', 26: 'Joint', 27: 'Joint', 28: 'Joint', 29: 'Joint', 30: 'Joint', 31: 'Joint', 32: 'Joint', 33: 'Joint', 34: 'Joint', 35: 'Joint', 36: 'Joint', 37: 'Joint', 38: 'Joint', 39: 'Joint', 40: 'Joint', 41: 'Joint', 42: 'Joint', 43: 'Joint', 44: 'Joint', 45: 'Joint', 46: 'Joint', 47: 'Joint', 48: 'Joint', 49: 'Joint', 50: 'Joint', 51: 'Joint', 52: 'Joint', 53: 'Joint', 54: 'Joint', 55: 'Joint', 56: 'Joint', 57: 'Joint', 58: 'Joint', 59: 'Joint', 60: 'Joint', 61: 'Joint', 62: 'Joint', 63: 'Joint', 64: 'Joint', 65: 'Joint', 66: 'Joint', 67: 'Joint', 68: 'Joint', 69: 'Joint', 70: 'Spouse', 71: 'Spouse', 72: 'Not Disclosed', 73: 'Spouse', 74: 'Not Disclosed', 75: 'Spouse', 76: 'Not Disclosed', 77: 'Not Disclosed', 78: 'Spouse', 79: 'Spouse', 80: 'Joint', 81: 'Joint', 82: 'Joint', 83: 'Joint', 84: 'Not Disclosed', 85: 'Not Disclosed', 86: 'Spouse', 87: 'Spouse', 88: 'Spouse', 89: 'Joint', 90: 'Spouse', 91: 'Joint', 92: 'Spouse', 93: 'Spouse', 94: 'Not Disclosed', 95: 'Not Disclosed', 96: 'Not Disclosed', 97: 'Not Disclosed', 98: 'Not Disclosed', 99: 'Not Disclosed'}, 
'Issuer/Asset': {0: 'PINTEREST INC', 1: 'ENDAVA PLC -ADR', 2: 'HALOZYME THERAPEUTICS INC', 3: 'SYNEOS HEALTH INC', 4: 'MICROSOFT CORP', 5: 'MICROSOFT CORP', 6: 'MICROSOFT CORP', 7: 'MICROSOFT CORP', 8: 'MICROSOFT CORP', 9: 'MICROSOFT CORP', 10: 'MICROSOFT CORP', 11: 'MICROSOFT CORP', 12: 'MICROSOFT CORP', 13: 'MICROSOFT CORP', 14: 'TENCENT HOLDINGS LTD', 15: 'NORFOLK SOUTHERN CORP', 16: 'ADVANCED MICRO DEVICES', 17: 'BOOKING HOLDINGS INC', 18: 'PINTEREST INC', 19: 'WAYFAIR INC', 20: 'PINTEREST INC', 21: 'ENDAVA PLC -ADR', 22: 'HALOZYME THERAPEUTICS INC', 23: 'SYNEOS HEALTH INC', 24: 'MICROSOFT CORP', 25: 'MICROSOFT CORP', 26: 'MICROSOFT CORP', 27: 'MICROSOFT CORP', 28: 'MICROSOFT CORP', 29: 'MICROSOFT CORP', 30: 'MICROSOFT CORP', 31: 'MICROSOFT CORP', 32: 'MICROSOFT CORP', 33: 'MICROSOFT CORP', 34: 'TENCENT HOLDINGS LTD', 35: 'NORFOLK SOUTHERN CORP', 36: 'ADVANCED MICRO DEVICES', 37: 'BOOKING HOLDINGS INC', 38: 'PINTEREST INC', 39: 'WAYFAIR INC', 40: 'PINTEREST INC', 41: 'ENDAVA PLC -ADR', 42: 'HALOZYME THERAPEUTICS INC', 43: 'SYNEOS HEALTH INC', 44: 'MICROSOFT CORP', 45: 'MICROSOFT CORP', 46: 'MICROSOFT CORP', 47: 'MICROSOFT CORP', 48: 'MICROSOFT CORP', 49: 'MICROSOFT CORP', 50: 'MICROSOFT CORP', 51: 'MICROSOFT CORP', 52: 'MICROSOFT CORP', 53: 'MICROSOFT CORP', 54: 'TENCENT HOLDINGS LTD', 55: 'NORFOLK SOUTHERN CORP', 56: 'ADVANCED MICRO DEVICES', 57: 'BOOKING HOLDINGS INC', 58: 'PINTEREST INC', 59: 'WAYFAIR INC', 60: 'EATON VANCE TX MNGD OPPTY FD', 61: 'EMERSON ELECTRIC CO', 62: 'FIDELITY QUALITY FACTOR ETF', 63: 'HOME DEPOT INC', 64: 'ILLINOIS TOOL WORKS', 65: 'ISHARES CORE DIV GROWTH ETF', 66: 'JPMORGAN CHASE & CO', 67: 'LINDE PLC', 68: 'MEDTRONIC PLC', 69: 'VUZIX CORP', 70: 'WELLS FARGO & CO', 71: 'MICROSOFT CORP', 72: 'FORD MOTOR CO', 73: 'MICROSOFT CORP', 74: 'ROBLOX CORP', 75: 'APPLE INC', 76: 'ROBLOX CORP', 77: 'ROBLOX CORP', 78: 'APPLE INC', 79: 'MICROSOFT CORP', 80: 'SOMERSET COUNTY NJ', 81: 'SOMERSET COUNTY NJ', 82: 'STATE OF TEXAS', 83: 'TOWN OF WOLCOTT CONNECTICUT', 84: 'NEW YORK CITY TRANSITIONAL FINANCE AUTHORITY', 85: 'DORMITORY AUTHORITY OF THE STATE OF NEW YORK', 86: 'VMWARE INC -CL A', 87: 'T-MOBILE US INC', 88: 'BOOKING HOLDINGS INC', 89: 'BOOKING HOLDINGS INC', 90: 'BOOKING HOLDINGS INC', 91: 'GENERAL MOTORS CO', 92: 'GENERAL MOTORS CO', 93: 'GENERAL MOTORS CO', 94: 'ACCENTURE PLC', 95: 'ALPHABET INC', 96: 'APPLE INC', 97: 'ESTEE LAUDER COMPANIES INC', 98: 'INTUIT INC', 99: 'THERMO FISHER SCIENTIFIC INC'}, 
'Ticker': {0: 'PINS', 1: 'DAVA', 2: 'HALO', 3: 'SYNH', 4: 'MSFT', 5: 'MSFT', 6: 'MSFT', 7: 'MSFT', 8: 'MSFT', 9: 'MSFT', 10: 'MSFT', 11: 'MSFT', 12: 'MSFT', 13: 'MSFT', 14: 'TCEHY', 15: 'NSC', 16: 'AMD', 17: 'BKNG', 18: 'PINS', 19: 'W', 20: 'PINS', 21: 'DAVA', 22: 'HALO', 23: 'SYNH', 24: 'MSFT', 25: 'MSFT', 26: 'MSFT', 27: 'MSFT', 28: 'MSFT', 29: 'MSFT', 30: 'MSFT', 31: 'MSFT', 32: 'MSFT', 33: 'MSFT', 34: 'TCEHY', 35: 'NSC', 36: 'AMD', 37: 'BKNG', 38: 'PINS', 39: 'W', 40: 'PINS', 41: 'DAVA', 42: 'HALO', 43: 'SYNH', 44: 'MSFT', 45: 'MSFT', 46: 'MSFT', 47: 'MSFT', 48: 'MSFT', 49: 'MSFT', 50: 'MSFT', 51: 'MSFT', 52: 'MSFT', 53: 'MSFT', 54: 'TCEHY', 55: 'NSC', 56: 'AMD', 57: 'BKNG', 58: 'PINS', 59: 'W', 60: 'ETV', 61: 'EMR', 62: 'FQAL', 63: 'HD', 64: 'ITW', 65: 'DGRO', 66: 'JPM', 67: 'LIN', 68: 'MDT', 69: 'VUZI', 70: 'WFC', 71: 'MSFT', 72: 'F', 73: 'MSFT', 74: 'RBLX', 75: 'AAPL', 76: 'RBLX', 77: 'RBLX', 78: 'AAPL', 79: 'MSFT', 80: '--', 81: '--', 82: '--', 83: '--', 84: '--', 85: '--', 86: 'VMW', 87: 'TMUS', 88: 'BKNG', 89: 'BKNG', 90: 'BKNG', 91: 'GM', 92: 'GM', 93: 'GM', 94: 'ACN', 95: 'GOOGL', 96: 'AAPL', 97: 'EL', 98: 'INTU', 99: 'TMO'}, 
'Transaction Date': {0: '27-Aug-21', 1: '20-Aug-21', 2: '20-Aug-21', 3: '20-Aug-21', 4: '16-Aug-21', 5: '16-Aug-21', 6: '16-Aug-21', 7: '16-Aug-21', 8: '16-Aug-21', 9: '16-Aug-21', 10: '16-Aug-21', 11: '16-Aug-21', 12: '16-Aug-21', 13: '16-Aug-21', 14: '9-Aug-21', 15: '5-Aug-21', 16: '4-Aug-21', 17: '4-Aug-21', 18: '2-Aug-21', 19: '2-Aug-21', 20: '27-Aug-21', 21: '20-Aug-21', 22: '20-Aug-21', 23: '20-Aug-21', 24: '16-Aug-21', 25: '16-Aug-21', 26: '16-Aug-21', 27: '16-Aug-21', 28: '16-Aug-21', 29: '16-Aug-21', 30: '16-Aug-21', 31: '16-Aug-21', 32: '16-Aug-21', 33: '16-Aug-21', 34: '9-Aug-21', 35: '5-Aug-21', 36: '4-Aug-21', 37: '4-Aug-21', 38: '2-Aug-21', 39: '2-Aug-21', 40: '27-Aug-21', 41: '20-Aug-21', 42: '20-Aug-21', 43: '20-Aug-21', 44: '16-Aug-21', 45: '16-Aug-21', 46: '16-Aug-21', 47: '16-Aug-21', 48: '16-Aug-21', 49: '16-Aug-21', 50: '16-Aug-21', 51: '16-Aug-21', 52: '16-Aug-21', 53: '16-Aug-21', 54: '9-Aug-21', 55: '5-Aug-21', 56: '4-Aug-21', 57: '4-Aug-21', 58: '2-Aug-21', 59: '2-Aug-21', 60: '1-Jul-21', 61: '1-Jul-21', 62: '1-Jul-21', 63: '1-Jul-21', 64: '1-Jul-21', 65: '1-Jul-21', 66: '1-Jul-21', 67: '1-Jul-21', 68: '1-Jul-21', 69: '1-Jul-21', 70: '3-Sep-21', 71: '31-Aug-21', 72: '30-Aug-21', 73: '30-Aug-21', 74: '30-Aug-21', 75: '25-Aug-21', 76: '25-Aug-21', 77: '25-Aug-21', 78: '17-Aug-21', 79: '17-Aug-21', 80: '16-Aug-21', 81: '12-Aug-21', 82: '12-Aug-21', 83: '11-Aug-21', 84: '5-Aug-21', 85: '3-Aug-21', 86: '8-Sep-21', 87: '24-Aug-21', 88: '23-Aug-21', 89: '23-Aug-21', 90: '23-Aug-21', 91: '23-Aug-21', 92: '23-Aug-21', 93: '23-Aug-21', 94: '19-Aug-21', 95: '19-Aug-21', 96: '19-Aug-21', 97: '19-Aug-21', 98: '19-Aug-21', 99: '19-Aug-21'}, 
'Transaction': {0: 'SELL (Stock)*', 1: 'SELL (P) (Stock)*', 2: 'BUY (Stock)*', 3: 'SELL (P) (Stock)*', 4: 'BUY (Stock Options)*', 5: 'BUY (Stock Options)*', 6: 'BUY (Stock Options)*', 7: 'SELL (Stock Options)*', 8: 'SELL (Stock Options)*', 9: 'SELL (Stock Options)*', 10: 'BUY (Stock Options)*', 11: 'SELL (Stock Options)*', 12: 'SELL (Stock Options)*', 13: 'BUY (Stock Options)*', 14: 'BUY (Stock)*', 15: 'SELL (P) (Stock)*', 16: 'BUY (Stock)*', 17: 'BUY (Stock)*', 18: 'SELL (P) (Stock)*', 19: 'SELL (P) (Stock)*', 20: 'SELL (Stock)*', 21: 'SELL (P) (Stock)*', 22: 'BUY (Stock)*', 23: 'SELL (P) (Stock)*', 24: 'BUY (Stock Options)*', 25: 'BUY (Stock Options)*', 26: 'BUY (Stock Options)*', 27: 'SELL (Stock Options)*', 28: 'SELL (Stock Options)*', 29: 'SELL (Stock Options)*', 30: 'BUY (Stock Options)*', 31: 'SELL (Stock Options)*', 32: 'SELL (Stock Options)*', 33: 'BUY (Stock Options)*', 34: 'BUY (Stock)*', 35: 'SELL (P) (Stock)*', 36: 'BUY (Stock)*', 37: 'BUY (Stock)*', 38: 'SELL (P) (Stock)*', 39: 'SELL (P) (Stock)*', 40: 'SELL (Stock)*', 41: 'SELL (P) (Stock)*', 42: 'BUY (Stock)*', 43: 'SELL (P) (Stock)*', 44: 'BUY (Stock Options)*', 45: 'BUY (Stock Options)*', 46: 'BUY (Stock Options)*', 47: 'SELL (Stock Options)*', 48: 'SELL (Stock Options)*', 49: 'SELL (Stock Options)*', 50: 'BUY (Stock Options)*', 51: 'SELL (Stock Options)*', 52: 'SELL (Stock Options)*', 53: 'BUY (Stock Options)*', 54: 'BUY (Stock)*', 55: 'SELL (P) (Stock)*', 56: 'BUY (Stock)*', 57: 'BUY (Stock)*', 58: 'SELL (P) (Stock)*', 59: 'SELL (P) (Stock)*', 60: 'BUY (Stock)*', 61: 'BUY (Stock)*', 62: 'BUY (ETF)*', 63: 'BUY (Stock)*', 64: 'BUY (Stock)*', 65: 'BUY (ETF)*', 66: 'BUY (Stock)*', 67: 'BUY (Stock)*', 68: 'BUY (Stock)*', 69: 'BUY (Stock)*', 70: 'BUY (Stock)*', 71: 'BUY (Stock)', 72: 'BUY (Stock)*', 73: 'BUY (Stock)', 74: 'BUY (Stock)*', 75: 'BUY (Stock)', 76: 'SELL (P) (Stock)*', 77: 'SELL (Stock)*', 78: 'SELL (P) (Stock)*', 79: 'SELL (P) (Stock)*', 80: 'SELL (P) (Municipal Security)*', 81: 'SELL (P) (Municipal Security)*', 82: 'SELL (Municipal Security)', 83: 'BUY (Municipal Security)', 84: 'BUY (Municipal Security)*', 85: 'SELL (Municipal Security)*', 86: 'BUY (Corporate Bond)*', 87: 'BUY (Corporate Bond)*', 88: 'BUY (Stock)*', 89: 'BUY (Stock)*', 90: 'BUY (Stock)*', 91: 'SELL (Stock)*', 92: 'SELL (Stock)*', 93: 'SELL (Stock)*', 94: 'SELL (P) (Stock)*', 95: 'SELL (P) (Stock)*', 96: 'SELL (P) (Stock)*', 97: 'SELL (P) (Stock)*', 98: 'SELL (P) (Stock)*', 99: 'SELL (P) (Stock)*'}, 
'Shares': {0: '18 - 262', 1: '8 - 109', 2: '26 - 379', 3: '12 - 170', 4: '--', 5: '--', 6: '--', 7: '--', 8: '--', 9: '--', 10: '--', 11: '--', 12: '--', 13: '--', 14: '17 - 252', 15: 'Apr-58', 16: '9 - 126', 17: '7-Jan', 18: '18 - 262', 19: 'May-61', 20: '18 - 262', 21: '8 - 109', 22: '26 - 379', 23: '12 - 170', 24: '--', 25: '--', 26: '--', 27: '--', 28: '--', 29: '--', 30: '--', 31: '--', 32: '--', 33: '--', 34: '17 - 252', 35: 'Apr-58', 36: '9 - 126', 37: '7-Jan', 38: '18 - 262', 39: 'May-61', 40: '18 - 262', 41: '8 - 109', 42: '26 - 379', 43: '12 - 170', 44: '--', 45: '--', 46: '--', 47: '--', 48: '--', 49: '--', 50: '--', 51: '--', 52: '--', 53: '--', 54: '17 - 252', 55: 'Apr-58', 56: '9 - 126', 57: '7-Jan', 58: '18 - 262', 59: 'May-61', 60: '924 - 3,076', 61: '516 - 1,031', 62: '2,013 - 5,032', 63: '312 - 777', 64: '223 - 445', 65: '4,932 - 9,863', 66: '319 - 636', 67: '52 - 171', 68: '119 - 396', 69: '2,757 - 5,512', 70: '23 - 339', 71: '16,563 - 82,814', 72: '77 - 1,149', 73: '824 - 1,646', 74: '13 - 183', 75: '7 - 101', 76: '12 - 166', 77: '12 - 166', 78: 'Jul-99', 79: 'Apr-51', 80: '--', 81: '--', 82: '--', 83: '--', 84: '--', 85: '--', 86: '--', 87: '--', 88: '23-Aug', 89: '7-Jan', 90: '7-Jan', 91: '21 - 311', 92: '312 - 1,037', 93: '21 - 311', 94: 'Apr-45', 95: '5-Jan', 96: '7 - 102', 97: 'Apr-45', 98: '27-Feb', 99: '27-Feb'}, 
'Share Price': {0: '$57.22 ', 1: '$137.42 ', 2: '$39.55 ', 3: '$88.14 ', 4: '--', 5: '--', 6: '--', 7: '--', 8: '--', 9: '--', 10: '--', 11: '--', 12: '--', 13: '--', 14: '$59.33 ', 15: '$254.47 ', 16: '$118.77 ', 17: '$2,085.64 ', 18: '$57.12 ', 19: '$242.37 ', 20: '$57.22 ', 21: '$137.42 ', 22: '$39.55 ', 23: '$88.14 ', 24: '--', 25: '--', 26: '--', 27: '--', 28: '--', 29: '--', 30: '--', 31: '--', 32: '--', 33: '--', 34: '$59.33 ', 35: '$254.47 ', 36: '$118.77 ', 37: '$2,085.64 ', 38: '$57.12 ', 39: '$242.37 ', 40: '$57.22 ', 41: '$137.42 ', 42: '$39.55 ', 43: '$88.14 ', 44: '--', 45: '--', 46: '--', 47: '--', 48: '--', 49: '--', 50: '--', 51: '--', 52: '--', 53: '--', 54: '$59.33 ', 55: '$254.47 ', 56: '$118.77 ', 57: '$2,085.64 ', 58: '$57.12 ', 59: '$242.37 ', 60: '$16.25 ', 61: '$96.92 ', 62: '$49.68 ', 63: '$321.54 ', 64: '$224.61 ', 65: '$50.69 ', 66: '$157.08 ', 67: '$290.84 ', 68: '$126.13 ', 69: '$18.14 ', 70: '$44.17 ', 71: '$301.88 ', 72: '$13.05 ', 73: '$303.59 ', 74: '$81.87 ', 75: '$148.36 ', 76: '$90.34 ', 77: '$90.34 ', 78: '$150.19 ', 79: '$293.08 ', 80: '--', 81: '--', 82: '--', 83: '--', 84: '--', 85: '--', 86: '--', 87: '--', 88: '$2,137.32 ', 89: '$2,137.32 ', 90: '$2,137.32 ', 91: '$48.18 ', 92: '$48.18 ', 93: '$48.18 ', 94: '$328.25 ', 95: '$2,713.60 ', 96: '$146.70 ', 97: '$327.40 ', 98: '$543.44 ', 99: '$551.88 '}, 'Value': {0: '$1,001 - $15,000', 1: '$1,001 - $15,000', 2: '$1,001 - $15,000', 3: '$1,001 - $15,000', 4: '$1,000,001 - $5,000,000', 5: '$1,000,001 - $5,000,000', 6: '$500,001 - $1,000,000', 7: '$500,001 - $1,000,000', 8: '$500,001 - $1,000,000', 9: '$1,000,001 - $5,000,000', 10: '$500,001 - $1,000,000', 11: '$1,000,001 - $5,000,000', 12: '$500,001 - $1,000,000', 13: '$500,001 - $1,000,000', 14: '$1,001 - $15,000', 15: '$1,001 - $15,000', 16: '$1,001 - $15,000', 17: '$1,001 - $15,000', 18: '$1,001 - $15,000', 19: '$1,001 - $15,000', 20: '$1,001 - $15,000', 21: '$1,001 - $15,000', 22: '$1,001 - $15,000', 23: '$1,001 - $15,000', 24: '$1,000,001 - $5,000,000', 25: '$1,000,001 - $5,000,000', 26: '$500,001 - $1,000,000', 27: '$500,001 - $1,000,000', 28: '$500,001 - $1,000,000', 29: '$1,000,001 - $5,000,000', 30: '$500,001 - $1,000,000', 31: '$1,000,001 - $5,000,000', 32: '$500,001 - $1,000,000', 33: '$500,001 - $1,000,000', 34: '$1,001 - $15,000', 35: '$1,001 - $15,000', 36: '$1,001 - $15,000', 37: '$1,001 - $15,000', 38: '$1,001 - $15,000', 39: '$1,001 - $15,000', 40: '$1,001 - $15,000', 41: '$1,001 - $15,000', 42: '$1,001 - $15,000', 43: '$1,001 - $15,000', 44: '$1,000,001 - $5,000,000', 45: '$1,000,001 - $5,000,000', 46: '$500,001 - $1,000,000', 47: '$500,001 - $1,000,000', 48: '$500,001 - $1,000,000', 49: '$1,000,001 - $5,000,000', 50: '$500,001 - $1,000,000', 51: '$1,000,001 - $5,000,000', 52: '$500,001 - $1,000,000', 53: '$500,001 - $1,000,000', 54: '$1,001 - $15,000', 55: '$1,001 - $15,000', 56: '$1,001 - $15,000', 57: '$1,001 - $15,000', 58: '$1,001 - $15,000', 59: '$1,001 - $15,000', 60: '$15,001 - $50,000', 61: '$50,001 - $100,000', 62: '$100,001 - $250,000', 63: '$100,001 - $250,000', 64: '$50,001 - $100,000', 65: '$250,001 - $500,000', 66: '$50,001 - $100,000', 67: '$15,001 - $50,000', 68: '$15,001 - $50,000', 69: '$50,001 - $100,000', 70: '$1,001 - $15,000', 71: '$5,000,001 - $25,000,000', 72: '$1,001 - $15,000', 73: '$250,001 - $500,000', 74: '$1,001 - $15,000', 75: '$1,001 - $15,000', 76: '$1,001 - $15,000', 77: '$1,001 - $15,000', 78: '$1,001 - $15,000', 79: '$1,001 - $15,000', 80: '$50,001 - $100,000', 81: '$100,001 - $250,000', 82: '$500,001 - $1,000,000', 83: '$500,001 - $1,000,000', 84: '$15,001 - $50,000', 85: '$15,001 - $50,000', 86: '$15,001 - $50,000', 87: '$15,001 - $50,000', 88: '$15,001 - $50,000', 89: '$1,001 - $15,000', 90: '$1,001 - $15,000', 91: '$1,001 - $15,000', 92: '$15,001 - $50,000', 93: '$1,001 - $15,000', 94: '$1,001 - $15,000', 95: '$1,001 - $15,000', 96: '$1,001 - $15,000', 97: '$1,001 - $15,000', 98: '$1,001 - $15,000', 99: '$1,001 - $15,000'},
'URL': {0: 'View', 1: 'View', 2: 'View', 3: 'View', 4: 'View', 5: 'View', 6: 'View', 7: 'View', 8: 'View', 9: 'View', 10: 'View', 11: 'View', 12: 'View', 13: 'View', 14: 'View', 15: 'View', 16: 'View', 17: 'View', 18: 'View', 19: 'View', 20: 'View', 21: 'View', 22: 'View', 23: 'View', 24: 'View', 25: 'View', 26: 'View', 27: 'View', 28: 'View', 29: 'View', 30: 'View', 31: 'View', 32: 'View', 33: 'View', 34: 'View', 35: 'View', 36: 'View', 37: 'View', 38: 'View', 39: 'View', 40: 'View', 41: 'View', 42: 'View', 43: 'View', 44: 'View', 45: 'View', 46: 'View', 47: 'View', 48: 'View', 49: 'View', 50: 'View', 51: 'View', 52: 'View', 53: 'View', 54: 'View', 55: 'View', 56: 'View', 57: 'View', 58: 'View', 59: 'View', 60: 'View', 61: 'View', 62: 'View', 63: 'View', 64: 'View', 65: 'View', 66: 'View', 67: 'View', 68: 'View', 69: 'View', 70: 'View', 71: 'View', 72: 'View', 73: 'View', 74: 'View', 75: 'View', 76: 'View', 77: 'View', 78: 'View', 79: 'View', 80: 'View', 81: 'View', 82: 'View', 83: 'View', 84: 'View', 85: 'View', 86: 'View', 87: 'View', 88: 'View', 89: 'View', 90: 'View', 91: 'View', 92: 'View', 93: 'View', 94: 'View', 95: 'View', 96: 'View', 97: 'View', 98: 'View', 99: 'View'}}

user_8686179 · Answer

I haven't written python in a while, but can give you a solution to use directly in Excel if that helps.

The data below is just in columns A and B and the formula in column B is:

=IF(LEFT(CELL("format",A2),1)="D",DAY(A2)&"-"&MONTH(A2),A2)

What it's doing is checking the 1st character of the cell format in column A. If it's "D", then it knows it's a date and will concatenate the day and month with a hyphen between them, otherwise it just uses the value in column A

user_16555279 · Answer

One possible solution is applying a custom function to convert the date string into your desire format.

You can try this:

import pandas as pd
import datetime as dt


data = {'Publication Date': {0: '21-Sep-21', 1: '21-Sep-21', 2: '21-Sep-21', 3: '21-Sep-21', 4: '21-Sep-21', 5: '21-Sep-21', 6: '21-Sep-21', 7: '21-Sep-21', 8: '21-Sep-21', 9: '21-Sep-21', 10: '21-Sep-21', 11: '21-Sep-21', 12: '21-Sep-21', 13: '21-Sep-21', 14: '21-Sep-21', 15: '21-Sep-21', 16: '21-Sep-21', 17: '21-Sep-21', 18: '21-Sep-21', 19: '21-Sep-21', 20: '21-Sep-21', 21: '21-Sep-21', 22: '21-Sep-21', 23: '21-Sep-21', 24: '21-Sep-21', 25: '21-Sep-21', 26: '21-Sep-21', 27: '21-Sep-21', 28: '21-Sep-21', 29: '21-Sep-21', 30: '21-Sep-21', 31: '21-Sep-21', 32: '21-Sep-21', 33: '21-Sep-21', 34: '21-Sep-21', 35: '21-Sep-21', 36: '21-Sep-21', 37: '21-Sep-21', 38: '21-Sep-21', 39: '21-Sep-21', 40: '21-Sep-21', 41: '21-Sep-21', 42: '21-Sep-21', 43: '21-Sep-21', 44: '21-Sep-21', 45: '21-Sep-21', 46: '21-Sep-21', 47: '21-Sep-21', 48: '21-Sep-21', 49: '21-Sep-21', 50: '21-Sep-21', 51: '21-Sep-21', 52: '21-Sep-21', 53: '21-Sep-21', 54: '21-Sep-21', 55: '21-Sep-21', 56: '21-Sep-21', 57: '21-Sep-21', 58: '21-Sep-21', 59: '21-Sep-21', 60: '16-Sep-21', 61: '16-Sep-21', 62: '16-Sep-21', 63: '16-Sep-21', 64: '16-Sep-21', 65: '16-Sep-21', 66: '16-Sep-21', 67: '16-Sep-21', 68: '16-Sep-21', 69: '16-Sep-21', 70: '14-Sep-21', 71: '15-Sep-21', 72: '15-Sep-21', 73: '15-Sep-21', 74: '15-Sep-21', 75: '14-Sep-21', 76: '15-Sep-21', 77: '15-Sep-21', 78: '14-Sep-21', 79: '14-Sep-21', 80: '15-Sep-21', 81: '15-Sep-21', 82: '15-Sep-21', 83: '15-Sep-21', 84: '15-Sep-21', 85: '15-Sep-21', 86: '14-Sep-21', 87: '14-Sep-21', 88: '14-Sep-21', 89: '14-Sep-21', 90: '14-Sep-21', 91: '14-Sep-21', 92: 
'14-Sep-21', 93: '14-Sep-21', 94: '14-Sep-21', 95: '14-Sep-21', 96: '14-Sep-21', 97: '14-Sep-21', 98: '14-Sep-21', 99: '14-Sep-21'}, 'Politician': {0: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 1: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 2: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 3: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 4: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 5: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 6: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 7: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 8: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 9: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 10: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 11: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 12: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 13: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 14: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 15: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 16: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 17: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 18: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 19: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 20: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 21: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 22: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 23: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 24: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 25: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 26: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 27: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 28: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 29: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 30: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 31: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 32: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 33: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 34: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 35: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 36: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 37: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 38: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 39: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 40: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 41: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 42: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 43: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 44: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 45: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 46: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 47: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 48: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 49: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 50: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 51: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 52: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 53: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 54: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 55: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 56: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 57: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 58: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 59: 'Gottheimer, Joshua S (Josh)Rep (D - NJ)', 60: 'Hern, Kevin RayRep (R - OK)', 61: 'Hern, Kevin RayRep (R - OK)', 62: 'Hern, Kevin RayRep (R - OK)', 63: 'Hern, Kevin RayRep (R - OK)', 64: 'Hern, Kevin RayRep (R - OK)', 65: 'Hern, Kevin RayRep (R - OK)', 66: 'Hern, Kevin RayRep (R - OK)', 67: 'Hern, Kevin RayRep (R - OK)', 68: 'Hern, Kevin RayRep (R - OK)', 69: 'Hern, Kevin RayRep (R - OK)', 70: 'McConnell, Addison Mitchell, Jr (Mitch)Sen (R - KY)', 71: 'DelBene, Suzan Kay OliverRep (D - WA)', 72: 'Banks, James Edward (Jim)Rep (R - IN)', 73: 'DelBene, Suzan Kay OliverRep (D - WA)', 74: 'Banks, James Edward (Jim)Rep (R - IN)', 75: 'Capito, Shelley Wellons MooreSen (R - WV)', 76: 'Banks, James Edward (Jim)Rep (R - IN)', 77: 'Banks, James Edward (Jim)Rep (R - IN)', 78: 'Capito, Shelley Wellons MooreSen (R - WV)', 79: 'Capito, Shelley Wellons MooreSen (R - WV)', 80: 'DelBene, Suzan Kay OliverRep (D - WA)', 81: 'DelBene, Suzan Kay OliverRep (D - WA)', 82: 'DelBene, Suzan Kay OliverRep (D - WA)', 83: 'DelBene, Suzan Kay OliverRep (D - WA)', 84: 'Jacobs, Christopher L (Chris)Rep (R - NY)', 85: 'Jacobs, Christopher L (Chris)Rep (R - NY)', 86: 'Lowenthal, Alan StuartRep (D - CA)', 87: 
'Lowenthal, Alan StuartRep (D - CA)', 88: 'Lowenthal, Alan StuartRep (D - CA)', 89: 'Lowenthal, Alan StuartRep (D - CA)', 90: 'Lowenthal, Alan StuartRep (D - CA)', 91: 'Lowenthal, Alan StuartRep (D - CA)', 92: 'Lowenthal, Alan StuartRep (D - CA)', 93: 'Lowenthal, Alan StuartRep (D - CA)', 94: 'McKinley, David BennettRep (R - WV)', 95: 'McKinley, David BennettRep (R - WV)', 96: 'McKinley, David BennettRep (R - WV)', 97: 'McKinley, David BennettRep (R - WV)', 98: 'McKinley, David BennettRep (R - WV)', 99: 'McKinley, David BennettRep (R - WV)'}, 'Owner': {0: 'Joint', 1: 'Joint', 2: 'Joint', 3: 'Joint', 4: 'Joint', 5: 'Joint', 6: 'Joint', 7: 'Joint', 8: 'Joint', 9: 'Joint', 10: 'Joint', 11: 'Joint', 12: 'Joint', 13: 'Joint', 14: 'Joint', 15: 'Joint', 16: 'Joint', 17: 'Joint', 18: 'Joint', 19: 'Joint', 20: 'Joint', 21: 'Joint', 22: 'Joint', 23: 'Joint', 24: 'Joint', 25: 'Joint', 26: 'Joint', 27: 'Joint', 28: 'Joint', 29: 'Joint', 30: 'Joint', 31: 'Joint', 32: 'Joint', 33: 'Joint', 34: 'Joint', 35: 'Joint', 36: 'Joint', 37: 'Joint', 38: 'Joint', 39: 'Joint', 40: 'Joint', 41: 'Joint', 42: 'Joint', 43: 'Joint', 44: 'Joint', 45: 'Joint', 46: 'Joint', 47: 'Joint', 48: 'Joint', 49: 'Joint', 50: 'Joint', 51: 'Joint', 52: 'Joint', 53: 'Joint', 54: 'Joint', 
55: 'Joint', 56: 'Joint', 57: 'Joint', 58: 'Joint', 59: 'Joint', 60: 'Joint', 61: 'Joint', 62: 'Joint', 63: 'Joint', 64: 'Joint', 65: 'Joint', 66: 'Joint', 67: 'Joint', 68: 'Joint', 69: 'Joint', 70: 'Spouse', 71: 'Spouse', 72: 'Not Disclosed', 73: 'Spouse', 74: 'Not Disclosed', 75: 'Spouse', 76: 'Not Disclosed', 77: 'Not Disclosed', 78: 'Spouse', 79: 'Spouse', 80: 'Joint', 81: 'Joint', 82: 'Joint', 83: 'Joint', 84: 'Not Disclosed', 85: 'Not Disclosed', 86: 'Spouse', 87: 'Spouse', 88: 'Spouse', 89: 'Joint', 90: 'Spouse', 91: 'Joint', 92: 'Spouse', 93: 'Spouse', 94: 'Not Disclosed', 95: 'Not Disclosed', 96: 'Not Disclosed', 97: 'Not Disclosed', 98: 'Not Disclosed', 99: 'Not Disclosed'}, 'Issuer/Asset': {0: 'PINTEREST INC', 1: 'ENDAVA PLC -ADR', 2: 'HALOZYME THERAPEUTICS INC', 3: 'SYNEOS HEALTH INC', 4: 'MICROSOFT CORP', 5: 'MICROSOFT CORP', 6: 'MICROSOFT CORP', 7: 'MICROSOFT CORP', 8: 'MICROSOFT CORP', 9: 'MICROSOFT CORP', 10: 'MICROSOFT CORP', 11: 'MICROSOFT CORP', 12: 'MICROSOFT CORP', 13: 'MICROSOFT CORP', 14: 'TENCENT HOLDINGS LTD', 15: 'NORFOLK SOUTHERN CORP', 16: 'ADVANCED MICRO DEVICES', 17: 'BOOKING HOLDINGS INC', 18: 'PINTEREST INC', 19: 'WAYFAIR INC', 20: 'PINTEREST INC', 21: 'ENDAVA PLC -ADR', 22: 'HALOZYME THERAPEUTICS INC', 23: 'SYNEOS HEALTH INC', 24: 'MICROSOFT CORP', 25: 'MICROSOFT CORP', 26: 'MICROSOFT CORP', 27: 'MICROSOFT CORP', 28: 'MICROSOFT CORP', 29: 'MICROSOFT CORP', 30: 'MICROSOFT CORP', 31: 'MICROSOFT CORP', 32: 'MICROSOFT CORP', 33: 'MICROSOFT CORP', 34: 'TENCENT HOLDINGS LTD', 35: 'NORFOLK SOUTHERN CORP', 36: 'ADVANCED MICRO DEVICES', 37: 'BOOKING HOLDINGS INC', 38: 'PINTEREST INC', 39: 'WAYFAIR INC', 40: 'PINTEREST INC', 41: 'ENDAVA PLC -ADR', 42: 'HALOZYME THERAPEUTICS INC', 43: 'SYNEOS HEALTH INC', 44: 'MICROSOFT CORP', 45: 'MICROSOFT CORP', 46: 'MICROSOFT CORP', 47: 'MICROSOFT CORP', 48: 'MICROSOFT CORP', 49: 'MICROSOFT CORP', 50: 'MICROSOFT CORP', 51: 'MICROSOFT CORP', 52: 'MICROSOFT CORP', 53: 'MICROSOFT CORP', 54: 'TENCENT HOLDINGS LTD', 55: 'NORFOLK SOUTHERN CORP', 56: 'ADVANCED MICRO DEVICES', 57: 'BOOKING HOLDINGS INC', 58: 'PINTEREST INC', 59: 'WAYFAIR INC', 60: 'EATON VANCE TX MNGD OPPTY FD', 61: 'EMERSON ELECTRIC CO', 62: 'FIDELITY QUALITY FACTOR ETF', 63: 'HOME DEPOT INC', 64: 'ILLINOIS TOOL WORKS', 65: 'ISHARES CORE DIV GROWTH ETF', 66: 'JPMORGAN CHASE & CO', 67: 'LINDE PLC', 68: 'MEDTRONIC PLC', 69: 'VUZIX CORP', 70: 'WELLS FARGO & CO', 71: 'MICROSOFT CORP', 72: 'FORD MOTOR CO', 73: 'MICROSOFT CORP', 74: 'ROBLOX CORP', 75: 'APPLE INC', 76: 'ROBLOX CORP', 77: 'ROBLOX CORP', 78: 'APPLE INC', 79: 'MICROSOFT CORP', 80: 'SOMERSET COUNTY NJ', 81: 'SOMERSET COUNTY NJ', 82: 'STATE OF TEXAS', 83: 'TOWN OF WOLCOTT CONNECTICUT', 84: 'NEW YORK CITY TRANSITIONAL FINANCE AUTHORITY', 85: 'DORMITORY AUTHORITY OF THE STATE OF NEW YORK', 86: 'VMWARE INC -CL A', 87: 'T-MOBILE US INC', 88: 'BOOKING HOLDINGS INC', 89: 'BOOKING HOLDINGS INC', 90: 'BOOKING HOLDINGS INC', 91: 'GENERAL MOTORS CO', 92: 'GENERAL MOTORS CO', 93: 'GENERAL MOTORS CO', 94: 'ACCENTURE PLC', 95: 'ALPHABET INC', 96: 'APPLE INC', 97: 'ESTEE LAUDER COMPANIES INC', 98: 'INTUIT INC', 99: 'THERMO FISHER SCIENTIFIC INC'}, 'Ticker': {0: 'PINS', 1: 'DAVA', 2: 'HALO', 3: 'SYNH', 4: 'MSFT', 5: 'MSFT', 6: 'MSFT', 7: 'MSFT', 8: 'MSFT', 9: 'MSFT', 10: 'MSFT', 11: 'MSFT', 12: 'MSFT', 13: 'MSFT', 14: 'TCEHY', 15: 'NSC', 16: 'AMD', 17: 'BKNG', 18: 'PINS', 19: 'W', 20: 'PINS', 21: 'DAVA', 22: 'HALO', 23: 'SYNH', 24: 'MSFT', 25: 'MSFT', 26: 'MSFT', 27: 'MSFT', 28: 'MSFT', 29: 'MSFT', 30: 'MSFT', 31: 'MSFT', 32: 'MSFT', 33: 'MSFT', 34: 'TCEHY', 35: 'NSC', 36: 'AMD', 37: 'BKNG', 38: 'PINS', 39: 'W', 40: 'PINS', 41: 'DAVA', 42: 'HALO', 43: 'SYNH', 44: 'MSFT', 45: 'MSFT', 46: 'MSFT', 47: 'MSFT', 48: 'MSFT', 49: 'MSFT', 50: 'MSFT', 51: 'MSFT', 52: 'MSFT', 53: 'MSFT', 54: 'TCEHY', 55: 'NSC', 56: 'AMD', 57: 'BKNG', 58: 'PINS', 59: 'W', 60: 'ETV', 61: 'EMR', 62: 'FQAL', 63: 'HD', 64: 'ITW', 65: 'DGRO', 66: 'JPM', 67: 'LIN', 68: 'MDT', 69: 'VUZI', 70: 'WFC', 71: 'MSFT', 72: 'F', 73: 'MSFT', 74: 'RBLX', 75: 'AAPL', 76: 'RBLX', 77: 'RBLX', 78: 'AAPL', 79: 'MSFT', 80: '--', 81: '--', 82: '--', 83: '--', 84: '--', 85: '--', 86: 'VMW', 87: 'TMUS', 88: 'BKNG', 89: 'BKNG', 90: 'BKNG', 91: 'GM', 92: 'GM', 93: 'GM', 94: 'ACN', 95: 'GOOGL', 96: 'AAPL', 97: 'EL', 98: 'INTU', 99: 'TMO'}, 'Transaction Date': {0: '27-Aug-21', 1: '20-Aug-21', 2: '20-Aug-21', 3: '20-Aug-21', 4: '16-Aug-21', 5: '16-Aug-21', 6: '16-Aug-21', 7: '16-Aug-21', 8: '16-Aug-21', 9: '16-Aug-21', 10: '16-Aug-21', 11: '16-Aug-21', 12: '16-Aug-21', 13: '16-Aug-21', 14: '9-Aug-21', 15: '5-Aug-21', 16: '4-Aug-21', 17: '4-Aug-21', 18: '2-Aug-21', 19: '2-Aug-21', 20: '27-Aug-21', 21: '20-Aug-21', 22: '20-Aug-21', 23: '20-Aug-21', 24: '16-Aug-21', 25: '16-Aug-21', 26: '16-Aug-21', 27: '16-Aug-21', 28: '16-Aug-21', 29: '16-Aug-21', 30: '16-Aug-21', 31: '16-Aug-21', 32: '16-Aug-21', 33: '16-Aug-21', 34: '9-Aug-21', 35: '5-Aug-21', 36: '4-Aug-21', 37: '4-Aug-21', 38: '2-Aug-21', 39: '2-Aug-21', 40: '27-Aug-21', 41: '20-Aug-21', 42: '20-Aug-21', 43: '20-Aug-21', 44: '16-Aug-21', 45: '16-Aug-21', 46: '16-Aug-21', 47: '16-Aug-21', 48: '16-Aug-21', 49: '16-Aug-21', 50: '16-Aug-21', 51: '16-Aug-21', 52: '16-Aug-21', 53: '16-Aug-21', 54: '9-Aug-21', 55: '5-Aug-21', 56: '4-Aug-21', 57: '4-Aug-21', 58: '2-Aug-21', 59: '2-Aug-21', 60: '1-Jul-21', 61: '1-Jul-21', 62: '1-Jul-21', 63: '1-Jul-21', 64: '1-Jul-21', 65: '1-Jul-21', 66: '1-Jul-21', 67: '1-Jul-21', 68: '1-Jul-21', 69: '1-Jul-21', 70: '3-Sep-21', 71: '31-Aug-21', 72: '30-Aug-21', 73: '30-Aug-21', 74: '30-Aug-21', 75: '25-Aug-21', 76: '25-Aug-21', 77: '25-Aug-21', 78: '17-Aug-21', 79: '17-Aug-21', 80: '16-Aug-21', 81: '12-Aug-21', 82: '12-Aug-21', 83: '11-Aug-21', 84: '5-Aug-21', 85: '3-Aug-21', 86: '8-Sep-21', 87: '24-Aug-21', 88: '23-Aug-21', 89: '23-Aug-21', 90: '23-Aug-21', 91: '23-Aug-21', 92: '23-Aug-21', 93: '23-Aug-21', 94: '19-Aug-21', 95: '19-Aug-21', 96: '19-Aug-21', 97: '19-Aug-21', 98: '19-Aug-21', 99: '19-Aug-21'}, 'Transaction': {0: 'SELL (Stock)*', 1: 'SELL (P) (Stock)*', 2: 'BUY (Stock)*', 3: 'SELL (P) (Stock)*', 4: 'BUY (Stock Options)*', 5: 'BUY (Stock Options)*', 6: 'BUY (Stock Options)*', 7: 'SELL (Stock Options)*', 8: 'SELL (Stock Options)*', 9: 'SELL (Stock Options)*', 10: 'BUY (Stock Options)*', 11: 'SELL (Stock Options)*', 12: 'SELL (Stock Options)*', 13: 'BUY (Stock Options)*', 14: 'BUY (Stock)*', 15: 'SELL (P) (Stock)*', 16: 'BUY (Stock)*', 17: 'BUY (Stock)*', 18: 'SELL (P) (Stock)*', 19: 'SELL (P) (Stock)*', 20: 'SELL (Stock)*', 21: 'SELL (P) (Stock)*', 22: 'BUY (Stock)*', 23: 'SELL (P) (Stock)*', 24: 'BUY (Stock Options)*', 25: 'BUY (Stock Options)*', 26: 'BUY (Stock Options)*', 27: 'SELL (Stock Options)*', 28: 'SELL (Stock Options)*', 29: 'SELL (Stock Options)*', 30: 'BUY (Stock Options)*', 31: 'SELL (Stock Options)*', 32: 'SELL (Stock Options)*', 33: 'BUY (Stock Options)*', 34: 'BUY (Stock)*', 35: 'SELL (P) (Stock)*', 36: 'BUY (Stock)*', 37: 'BUY (Stock)*', 38: 'SELL (P) (Stock)*', 39: 'SELL (P) (Stock)*', 40: 'SELL (Stock)*', 41: 'SELL (P) (Stock)*', 42: 'BUY (Stock)*', 43: 'SELL (P) (Stock)*', 44: 'BUY (Stock Options)*', 45: 'BUY (Stock Options)*', 46: 'BUY (Stock Options)*', 47: 'SELL (Stock Options)*', 48: 'SELL (Stock Options)*', 49: 'SELL (Stock Options)*', 50: 'BUY (Stock Options)*', 51: 'SELL (Stock Options)*', 52: 'SELL (Stock Options)*', 53: 'BUY (Stock Options)*', 54: 'BUY (Stock)*', 55: 'SELL (P) (Stock)*', 56: 'BUY (Stock)*', 57: 'BUY (Stock)*', 58: 'SELL (P) (Stock)*', 59: 'SELL (P) (Stock)*', 60: 'BUY (Stock)*', 61: 'BUY (Stock)*', 62: 
'BUY (ETF)*', 63: 'BUY (Stock)*', 64: 'BUY (Stock)*', 65: 'BUY (ETF)*', 66: 'BUY (Stock)*', 67: 'BUY (Stock)*', 68: 'BUY (Stock)*', 69: 'BUY (Stock)*', 70: 'BUY (Stock)*', 71: 'BUY (Stock)', 72: 'BUY (Stock)*', 73: 'BUY (Stock)', 74: 'BUY (Stock)*', 75: 'BUY (Stock)', 76: 'SELL (P) (Stock)*', 77: 'SELL (Stock)*', 78: 'SELL (P) (Stock)*', 79: 'SELL (P) (Stock)*', 80: 'SELL (P) (Municipal Security)*', 81: 'SELL (P) (Municipal Security)*', 82: 'SELL (Municipal Security)', 83: 'BUY (Municipal Security)', 84: 'BUY (Municipal Security)*', 85: 'SELL (Municipal Security)*', 86: 'BUY (Corporate Bond)*', 87: 'BUY (Corporate Bond)*', 88: 'BUY (Stock)*', 89: 'BUY (Stock)*', 90: 'BUY (Stock)*', 91: 'SELL (Stock)*', 92: 'SELL (Stock)*', 93: 'SELL (Stock)*', 94: 'SELL (P) (Stock)*', 95: 'SELL (P) (Stock)*', 96: 'SELL (P) (Stock)*', 97: 'SELL (P) (Stock)*', 98: 'SELL (P) (Stock)*', 99: 'SELL (P) (Stock)*'}, 'Shares': {0: '18 - 262', 1: '8 - 109', 2: 
'26 - 379', 3: '12 - 170', 4: '--', 5: '--', 6: '--', 7: '--', 8: '--', 9: '--', 10: '--', 11: '--', 12: '--', 13: '--', 14: '17 - 252', 15: 'Apr-58', 16: '9 - 126', 17: '7-Jan', 18: '18 - 262', 19: 'May-61', 20: '18 - 262', 21: '8 - 109', 22: '26 - 379', 23: '12 - 170', 24: '--', 25: '--', 26: '--', 27: '--', 28: 
'--', 29: '--', 30: '--', 31: '--', 32: '--', 33: '--', 34: '17 - 252', 35: 'Apr-58', 36: '9 - 126', 37: '7-Jan', 38: '18 - 262', 39: 'May-61', 40: '18 - 262', 41: '8 - 109', 42: '26 - 379', 43: '12 - 170', 44: '--', 45: '--', 46: '--', 47: '--', 48: '--', 49: '--', 50: '--', 51: '--', 52: '--', 53: '--', 54: '17 - 252', 55: 'Apr-58', 56: '9 - 126', 57: '7-Jan', 58: '18 - 262', 59: 'May-61', 60: '924 - 3,076', 61: '516 - 1,031', 62: '2,013 - 5,032', 63: '312 - 777', 64: '223 - 445', 65: '4,932 - 9,863', 66: '319 - 636', 67: '52 - 171', 68: '119 - 396', 69: '2,757 - 5,512', 70: '23 - 339', 71: '16,563 - 82,814', 72: '77 - 1,149', 73: '824 - 1,646', 74: '13 - 183', 75: '7 - 101', 76: '12 - 166', 77: '12 - 166', 78: 'Jul-99', 79: 'Apr-51', 80: '--', 81: '--', 82: '--', 83: '--', 84: '--', 85: '--', 86: '--', 87: '--', 88: '23-Aug', 89: '7-Jan', 90: '7-Jan', 91: '21 - 311', 92: '312 - 1,037', 93: '21 - 311', 94: 'Apr-45', 95: '5-Jan', 96: '7 - 102', 97: 'Apr-45', 98: '27-Feb', 99: '27-Feb'}, 'Share Price': {0: '$57.22 ', 1: '$137.42 ', 2: '$39.55 ', 3: '$88.14 ', 4: '--', 5: '--', 6: '--', 7: '--', 8: '--', 9: '--', 10: '--', 11: '--', 12: '--', 13: '--', 14: '$59.33 ', 15: '$254.47 ', 16: '$118.77 ', 17: '$2,085.64 ', 18: '$57.12 ', 19: '$242.37 ', 20: '$57.22 ', 21: '$137.42 ', 22: '$39.55 ', 23: '$88.14 ', 24: '--', 25: '--', 26: '--', 27: '--', 28: '--', 29: '--', 30: '--', 31: '--', 32: '--', 33: '--', 34: '$59.33 ', 35: '$254.47 ', 36: '$118.77 ', 37: '$2,085.64 ', 38: '$57.12 ', 39: '$242.37 ', 40: '$57.22 ', 41: '$137.42 ', 42: '$39.55 ', 43: '$88.14 ', 44: '--', 45: '--', 46: '--', 47: '--', 48: '--', 49: '--', 50: '--', 51: '--', 52: '--', 53: '--', 54: '$59.33 ', 55: '$254.47 ', 56: '$118.77 ', 57: '$2,085.64 ', 58: '$57.12 ', 59: '$242.37 ', 60: '$16.25 ', 61: '$96.92 ', 62: '$49.68 ', 63: '$321.54 ', 64: '$224.61 ', 65: '$50.69 ', 66: '$157.08 ', 67: '$290.84 ', 68: '$126.13 ', 69: '$18.14 ', 70: '$44.17 ', 71: '$301.88 ', 72: '$13.05 ', 73: '$303.59 ', 74: '$81.87 ', 75: '$148.36 ', 76: '$90.34 ', 77: '$90.34 ', 78: '$150.19 ', 79: '$293.08 ', 80: '--', 81: '--', 82: '--', 83: '--', 84: '--', 85: '--', 86: '--', 87: '--', 88: '$2,137.32 ', 89: '$2,137.32 ', 90: '$2,137.32 ', 91: '$48.18 ', 92: '$48.18 ', 93: '$48.18 ', 94: '$328.25 ', 95: '$2,713.60 ', 96: '$146.70 ', 97: '$327.40 ', 98: '$543.44 ', 99: '$551.88 '}, 'Value': {0: '$1,001 - $15,000', 1: '$1,001 - $15,000', 2: '$1,001 - $15,000', 3: '$1,001 - $15,000', 4: '$1,000,001 - $5,000,000', 5: '$1,000,001 - $5,000,000', 6: '$500,001 - $1,000,000', 7: '$500,001 - $1,000,000', 8: '$500,001 - $1,000,000', 9: '$1,000,001 - $5,000,000', 10: '$500,001 - $1,000,000', 11: '$1,000,001 - $5,000,000', 12: '$500,001 - $1,000,000', 13: '$500,001 - $1,000,000', 14: '$1,001 - $15,000', 15: '$1,001 - $15,000', 16: '$1,001 - $15,000', 17: '$1,001 - $15,000', 18: '$1,001 - $15,000', 19: '$1,001 - $15,000', 20: '$1,001 - $15,000', 21: '$1,001 - $15,000', 22: '$1,001 - $15,000', 23: '$1,001 - $15,000', 24: '$1,000,001 - $5,000,000', 25: '$1,000,001 - $5,000,000', 26: '$500,001 - $1,000,000', 27: '$500,001 - $1,000,000', 28: '$500,001 - $1,000,000', 29: '$1,000,001 - $5,000,000', 30: '$500,001 - $1,000,000', 31: '$1,000,001 - $5,000,000', 32: '$500,001 - $1,000,000', 33: '$500,001 - $1,000,000', 34: '$1,001 - $15,000', 35: '$1,001 - $15,000', 36: '$1,001 - $15,000', 37: '$1,001 - $15,000', 38: '$1,001 - $15,000', 39: '$1,001 - $15,000', 40: '$1,001 - $15,000', 41: '$1,001 - $15,000', 42: '$1,001 - $15,000', 43: '$1,001 - $15,000', 44: '$1,000,001 - $5,000,000', 45: '$1,000,001 - $5,000,000', 46: '$500,001 - $1,000,000', 47: '$500,001 - $1,000,000', 48: '$500,001 - $1,000,000', 49: '$1,000,001 - $5,000,000', 50: '$500,001 - $1,000,000', 51: '$1,000,001 - $5,000,000', 52: '$500,001 - $1,000,000', 53: '$500,001 - $1,000,000', 54: '$1,001 - $15,000', 55: '$1,001 - $15,000', 56: '$1,001 - $15,000', 57: '$1,001 - $15,000', 58: '$1,001 - $15,000', 59: '$1,001 - $15,000', 60: '$15,001 - $50,000', 61: '$50,001 - $100,000', 62: '$100,001 - $250,000', 63: '$100,001 - $250,000', 64: '$50,001 - $100,000', 65: '$250,001 - $500,000', 66: '$50,001 - $100,000', 67: '$15,001 - $50,000', 68: '$15,001 - $50,000', 69: '$50,001 - $100,000', 70: '$1,001 - $15,000', 71: '$5,000,001 - $25,000,000', 72: '$1,001 - $15,000', 73: '$250,001 - $500,000', 74: '$1,001 - $15,000', 75: '$1,001 - $15,000', 76: '$1,001 - $15,000', 77: '$1,001 - $15,000', 78: '$1,001 - $15,000', 79: '$1,001 - $15,000', 80: '$50,001 - $100,000', 81: '$100,001 - $250,000', 82: '$500,001 - $1,000,000', 83: '$500,001 - $1,000,000', 84: '$15,001 - $50,000', 85: '$15,001 - $50,000', 86: '$15,001 - $50,000', 87: '$15,001 - $50,000', 88: '$15,001 - $50,000', 89: '$1,001 - $15,000', 90: '$1,001 - $15,000', 91: '$1,001 - $15,000', 92: '$15,001 - $50,000', 93: '$1,001 - $15,000', 94: '$1,001 - $15,000', 95: '$1,001 - $15,000', 96: '$1,001 - $15,000', 97: '$1,001 - $15,000', 98: '$1,001 - $15,000', 99: '$1,001 - $15,000'}, 'URL': {0: 'View', 1: 'View', 2: 'View', 3: 'View', 4: 'View', 5: 'View', 6: 'View', 7: 'View', 8: 'View', 9: 'View', 10: 'View', 11: 'View', 12: 'View', 13: 'View', 14: 'View', 15: 'View', 16: 'View', 17: 'View', 18: 'View', 19: 'View', 20: 'View', 21: 'View', 22: 'View', 23: 'View', 24: 'View', 25: 'View', 26: 'View', 27: 'View', 28: 'View', 29: 'View', 30: 'View', 31: 'View', 32: 'View', 33: 'View', 34: 'View', 35: 'View', 36: 'View', 37: 'View', 38: 'View', 39: 'View', 40: 'View', 41: 'View', 42: 'View', 43: 'View', 44: 'View', 45: 'View', 46: 'View', 47: 'View', 48: 'View', 49: 'View', 50: 'View', 51: 'View', 52: 'View', 53: 'View', 54: 'View', 55: 'View', 56: 'View', 57: 'View', 58: 'View', 59: 'View', 60: 'View', 61: 'View', 62: 'View', 63: 'View', 64: 'View', 65: 'View', 66: 'View', 67: 'View', 68: 'View', 69: 'View', 70: 'View', 71: 'View', 72: 'View', 73: 'View', 74: 'View', 75: 'View', 76: 'View', 77: 'View', 78: 'View', 79: 'View', 80: 'View', 81: 'View', 82: 'View', 83: 'View', 84: 'View', 85: 'View', 86: 'View', 87: 'View', 88: 'View', 89: 'View', 90: 'View', 91: 'View', 92: 'View', 93: 'View', 94: 'View', 95: 'View', 96: 'View', 97: 'View', 98: 'View', 99: 'View'}}


def convert_date_str(date_str):
    try:
        date_val = dt.datetime.strptime(date_str, '%d-%b-%y')
        return date_val.strftime('%-d-%-m-%-y')
    except:
        return date_str

current_df = pd.DataFrame(data)

current_df['Publication Date'] = current_df['Publication Date'].apply(convert_date_str)
current_df['Transaction Date'] = current_df['Transaction Date'].apply(convert_date_str)

print(current_df)

Output:

   Publication Date                               Politician          Owner                  Issuer/Asset Ticker Transaction Date           Transaction    Shares Share Price                    Value   URL
0           21-9-21  Gottheimer, Joshua S (Josh)Rep (D - NJ)          Joint                 PINTEREST INC   PINS          27-8-21         SELL (Stock)*  18 - 262     $57.22          $1,001 - $15,000  View
1           21-9-21  Gottheimer, Joshua S (Josh)Rep (D - NJ)          Joint               ENDAVA PLC -ADR   DAVA          20-8-21     SELL (P) (Stock)*   8 - 109    $137.42          $1,001 - $15,000  View
2           21-9-21  Gottheimer, Joshua S (Josh)Rep (D - NJ)          Joint     HALOZYME THERAPEUTICS INC   HALO          20-8-21          BUY (Stock)*  26 - 379     $39.55          $1,001 - $15,000  View
3           21-9-21  Gottheimer, Joshua S (Josh)Rep (D - NJ)          Joint             SYNEOS HEALTH INC   SYNH          20-8-21     SELL (P) (Stock)*  12 - 170     $88.14          $1,001 - $15,000  View
4           21-9-21  Gottheimer, Joshua S (Josh)Rep (D - NJ)          Joint                MICROSOFT CORP   MSFT          16-8-21  BUY (Stock Options)*        --          --  $1,000,001 - $5,000,000  View
..              ...                                      ...            ...                           ...    ...              ...                   ...       ...         ...                      ...   ...
95          14-9-21      McKinley, David BennettRep (R - WV)  Not Disclosed                  ALPHABET INC  GOOGL          19-8-21     SELL (P) (Stock)*     5-Jan  $2,713.60          $1,001 - $15,000  View
96          14-9-21      McKinley, David BennettRep (R - WV)  Not Disclosed                     APPLE INC   AAPL          19-8-21     SELL (P) (Stock)*   7 - 102    $146.70          $1,001 - $15,000  View
97          14-9-21      McKinley, David BennettRep (R - WV)  Not Disclosed    ESTEE LAUDER COMPANIES INC     EL          19-8-21     SELL (P) (Stock)*    Apr-45    $327.40          $1,001 - $15,000  View
98          14-9-21      McKinley, David BennettRep (R - WV)  Not Disclosed                    INTUIT INC   INTU          19-8-21     SELL (P) (Stock)*    27-Feb    $543.44          $1,001 - $15,000  View
99          14-9-21      McKinley, David BennettRep (R - WV)  Not Disclosed  THERMO FISHER SCIENTIFIC INC    TMO          19-8-21     SELL (P) (Stock)*    27-Feb    $551.88          $1,001 - $15,000  View

user_11351671 · Answer

Sabil has the right idea, but it turned out that the data can be wrong two ways: it can convert 1-2 to 2-Jan and it can turn 1-50 into Jan-50 where the 50 is the year. To fix this, you just need to add another try condition to the formula. Also note that datetime works differently on windows as it does Linux. If you are running on Linux, you will need to replace the # in the code with a - since it uses a different code to signify no leading zero.

def convert_date_str(date_str): 
    try:
        date_val = datetime.datetime.strptime(date_str, '%b-%y')
        return date_val.strftime("%#m - %y")
    except:
        pass
    try:
        date_val = datetime.datetime.strptime(date_str, '%d-%b')
        return date_val.strftime('%#d - %#m')
    except:
        return date_str

The first try: checks for month-year while the second try: checks for day-month. You can just apply this function to the column like Sabil's answer. I tried it on your data that you added (after fixing it), and it seems to work well.

Change Excel Dates to Number Ranges

3 Answers