[Python] Advice on how to handle KeyError occurring when using map Lambda and slicing for...

I'm creating a lookup table / dictionary from a static spreadsheet for upcoming labelling on input data, adding a new field for flavor:

lookup_table_data = pd.read_csv(r'C:\Location\format1.csv', sep=',')
lookup_table_data['label'] = 'apache'


I create the dictionary

my_format = lookup_table_data.set_index('server').T.to_dict('list')
print(my_format)

{'ABC123': ['IBM', 1000, 'East Coast', 'apache'],
'ABC456': ['Dell', 800, 'West Coast', 'apache'],
'XYZ123': ['HP', 900, 'West Coast', 'apache']}


I read in the input data

my_data = pd.read_csv(r'C:\Location\my_data.csv')
print(my_data)

server busy datetime
0 ABC123 24% 6/1/2024 0:02
1 ABC456 45% 6/1/2024 4:01
2 GHI100 95% 6/1/2024 9:10


When I then try to assign some labels via slicing method I end up with 'KeyError' as a server from the input data is not present in the dictionary

my_data['type'] = my_data['server'].map(lambda x: my_format[x][0])
my_data['cost'] = my_data['server'].map(lambda x: my_format[x][1])
my_data['location'] = my_data['server'].map(lambda x: my_format[x][2])

KeyError: 'GHI100'


If I remove GHI100 data the code works fine as intended.

server %busy datetime type cost location
0 ABC123 24% 6/1/2024 0:02 IBM 1000 East Coast
1 ABC456 45% 6/1/2024 4:01 Dell 800 West Coast


Any advice on how I can handle missing keys? Several articles mention using .get with a default value. But doing that I get "Index out of list range" for the slices above 0 (i.e. map(lambda x: my_format.get([x][1], None)). Plugging in the [x][0] as a line by itself

my_data['type'] = my_data['server'].map(lambda x: my_format.get([x][0], None))


Gets the whole dictionary put into the type column though it does prevent a keyerror.

server %busy datetime type
0 ABC123 24% 6/1/2024 0:02 [IBM, 1000, East Coast]
1 ABC456 45% 6/1/2024 4:01 [Dell, 800, West Coast]
2 GHI100 95% 6/1/2024 9:10 None


My only other solution has been to do an inner merge on the format file and the data file before the lookup as that removes data with servers not in the dictionary.

Continue reading...