dhs_pmco = pmco.groupby(['New Hire Number','New Hire Series','Vacancy Number','Actual EOD Date','Step',
                         'Metric (Days)'],

                        as_index=False).agg({'Cases Metric Met':{'Sum Cases Metric Met': 'sum', 'Count Cases Metric Met': 'count'},
                        'Actual Days to Complete':{'Mean Actual Days to Complete': 'mean'},
                        'Unaccounted Days':{'Median Unaccounted Days': 'median'},
                        'Certificate Request Hiring Validation Date':{'Hiring Validation Date':'max'},
                        'Certificate Request Personnel Action Date' : {'Request Personell Action Date': 'max'},
                        'Certificate Vacancy Creation Date' :{'Vacancy Creation Date': 'max'},
                        'Announcement Review Completion Date' :{'Announcement Review Completion Date': 'max'},
                        'Announcement Open Date': {'Announcement Open Date': 'max'},
                        'Announcement Close Date' : {'Announcement Close Date': 'max'},
                        'Certificate Issue Date': {'Certificate Issue Date': 'max'},
                        'Certificate Review Sent Date' : {'Certificate Review Sent Date': 'max'},
                        'Certificate Review Returned Date' : {'Certificate Review Returned Date': 'max'},
                        'Send Tentative Offer Active Date': {'Send Tentative Offer Active Date': 'max'},
                        'Send Tentative Offer Complete Date': {'Send Tentative Offer Complete Date': 'max'},
                        'New Hire Job Offer Response Date/Time': {'Tentative Offer Response Date': 'max'},
                         'Initiate Background Investigation Complete Date': {'Initiate Background Investigation Complete Date': 'max'},
                        'Receive Background Investigation/Security Clearance Complete Date': {'Receive Background Investigation/Security Clearance Complete Date': 'max'},
                        'Send Official Offer Active Date': {'Send Official Offer Active Date': 'max'},
                        'Send Official Offer Complete Date': {'Send Official Offer Complete Date': 'max'},
                        'New Hire Job Offer Response Date/Time - FJO': {'Formal Offer Response Date': 'max'} })

dhs_pmco.columns = ["".join(x) for x in dhs_pmco.columns.ravel()]

dhs_pmco.rename(columns={'Cases Metric MetSum Cases Metric Met': 'Cases Metric Met',
                            'Cases Metric MetCount Cases Metric Met': 'Total Cases',
                            'Actual Days to CompleteMean Actual Days to Complete': 'Actual Days to Complete',
                            'Unaccounted DaysMedian Unaccounted Days': 'Unaccounted Days',
                         'Certificate Request Hiring Validation DateHiring Validation Date': 'Hiring Validation Date',
                         'Certificate Request Personnel Action DateRequest Personell Action Date':'Personnel Action Date',
                         'Certificate Vacancy Creation DateVacancy Creation Date':'Vacancy Creation Date',
                         'Announcement Review Completion DateAnnouncement Review Completion Date':'Announcement Review Completion Date',
                         'Announcement Open DateAnnouncement Open Date': 'Announcement Open Date',
                         'Announcement Close DateAnnouncement Close Date': 'Announcement Close Date',
                         'Certificate Issue DateCertificate Issue Date': 'Certificate Issue Date',
                         'Certificate Review Sent DateCertificate Review Sent Date':'Certificate Review Sent Date',
                         'Certificate Review Returned DateCertificate Review Returned Date':'Certificate Review Returned Date',
                         'Send Tentative Offer Active DateSend Tentative Offer Active Date': 'Send Tentative Offer Active Date',
                         'Send Tentative Offer Complete DateSend Tentative Offer Complete Date': 'Send Tentative Offer Complete Date',
                         'New Hire Job Offer Response Date/TimeTentative Offer Response Date': 'Receive Tentative Offer Response Date',
                         'Initiate Background Investigation Complete DateInitiate Background Investigation Complete Date': 'Initiate Background Investigation Complete Date',
                         'Receive Background Investigation/Security Clearance Complete DateReceive Background Investigation/Security Clearance Complete Date': 'Receive Background Investigation/Security Clearance Results Date',
                         'Send Official Offer Active DateSend Official Offer Active Date': 'Send Official Offer Active Date',
                         'Send Official Offer Complete DateSend Official Offer Complete Date': 'Send Official Offer Complete Date',
                         'New Hire Job Offer Response Date/Time - FJOFormal Offer Response Date': 'Receive Formal Offer Response Date'},
                            inplace=True)