BUG GH#43414 Infer and coerce datetimes am pm
State: closed

- [ ] closes #43414 
- [ ] tests added / passed
- [ ] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [ ] whatsnew entry


Labels:
name: Bug
name: Datetime
description: Datetime data dtype

Comments:
Something interesting I found is that when we infer_datetime_format we actually only use the first record.  This is important because in some of the tests we generate data with pd.date_range where by default the first record will start at midnight.  This can lead to different behavior than if the data did not start with a record at midnight, which is probably more common in practice.
Thanks @mikephung122 for the PR.

tests are failing https://github.com/pandas-dev/pandas/pull/43416/checks?check_run_id=3517046932

> Something interesting I found is that when we infer_datetime_format we actually only use the first record.

This is clearly stated in the docs. 

```
infer_datetime_format : bool, default False
        If True and no `format` is given, attempt to infer the format of the
        datetime strings based on the first non-NaN element,
        and if it can be inferred, switch to a faster method of parsing them.
        In some cases this can increase the parsing speed by ~5-10x.
```

There is an underlying issue with `infer_datetime_format=True` and `errors='coerce'` specified together, #25143

The fact that specifying `errors='raise'` or not specifying `infer_datetime_format=True` gives the correct result with the code sample in the issue OP #43414, would fixing the underlying issue not fix this issue without the code added here?
@simonjayhawkins When I found this problem I also thought it was related to #25143.  However, the more I looked into it the more I feel like it's unclear and now I believe fixing this as a separate issue might make more sense.

I think this issue and fix by itself is straight-forward.  When converting we always try to guess the datetime format first, regardless of the errors argument and error handling.  Ideally, the guess_datetime_format function can and should be able to guess date and time formats with AM/PM.  However, right now it will return something like this '%m/%d/%Y %H:%M:%S AM' for AM/PM.  So when we try to use that to parse with our best guess it never works and the subsequent issue of inconsistent error handling and the fallback is surfaced.
![Screen Shot 2021-09-04 at 10 08 45 PM](https://user-images.githubusercontent.com/41494027/132135965-d156885f-2a24-4ec1-be36-ce88d61634fc.jpg)

We might be able to completely ignore this issue since the fallback method works.  However, I'm wondering why it was including in the first place if that's true.  The other option would be trying the fallback before accepting the coerced NaT, but then I would say this is still an issue worth fixing.  Overall, I think cleaning this up and ensuring that no existing functionality is broken would be significantly more difficult.  What do you think?
@simonjayhawkins I checked the test, it looks like its the new unit tests that are failing for one posix environment while another posix environment shows INTERNALERROR and 'Error: Process completed with exit code 3.'.  However, the unit tests don't fail locally or on the other posix environments.  I'm wondering if this is an issue with how these are built and ran but don't see anything immediately obvious from the logs during the build or test?  This is actually my first time touching any of the cython, are there any additional steps that need to be done outside of changing the .pyx file?  
![Screen Shot 2021-09-07 at 4 48 23 PM](https://user-images.githubusercontent.com/41494027/132424320-4e6b65bb-7ac6-42aa-a966-4794f03d2b36.jpg)