I am writing a program in Pandas to compare the same two data sets at different time intervals. Once those comparisons are complete, the dataframes will be exported to excel. I’m having an issue trying to format the numbers of employee IDs. An employee number is 8 digits total, with zeros leading. An example would be 00045642. Normally when these numbers are produced excel drops the leading zeros until further formatted.

The map function updates the format with the 8 total digits but it creates two issues: When it exports to excel, excel has the digits as custom rather than an integer and it provides an error on the excel sheet. I have tried putting the .astype function before and after .map function but it still creates the error.

The second issue is not all cells in the columns I want to turn into integers have a value, causing the blanks to become 000000nan – which is not what I want.

I would like to skip any blank cells in the columns designated and the columns that do have numbers to format with 8 total digits and I would like the excel export to obviously not produce an error.

Note – the lambda function note that is on here is something I found that I believe is close to what I want but I can’t get it work properly.

#pd.options.display.float_format = lambda x : '{:,.2f}'.format(x) if (np.isnan(x) | np.isinf(x)) else '{:,.0f}'.format(x) if int(x) == x else '{:,.2f}'.format(x)

merged_WFRL[['Old Empl ID','Old Reports To Empl ID','New Empl ID', 
             'New Reports To Empl ID']] = merged_WFRL[['Old Empl ID','Old Reports To Empl ID',
                                                       'New Empl ID', 'New Reports To Empl ID']].astype(int, errors = 'ignore')

merged_WFRL[['Old Empl ID','Old Reports To Empl ID','New Empl ID', 
            'New Reports To Empl ID']] = merged_WFRL[['Old Empl ID','Old Reports To Empl ID',
                                                       'New Empl ID', 'New Reports To Empl ID']].map('{:08.0f}'.format)