0
$\begingroup$

I would like to hear some views on a problem I have with my dataset (which I presume to be a common one).

Let's say I have the following dataset

SKUID   PRODUCT QTY        MFGDT      ......     EXPDT     SUPPLIERID SUPPLIERPH CUSTOMFIELD
FD001     MILK    3   12/01/18 14:12:02 ...    18/01/18    SV01    04053XXXX
FD002    CREAM    3   12/01/18 14:12:02 ...    18/01/18    SV01    04053XXXX
FD003   CHEESE    5   12/01/18 14:12:02 ...             18/01/18    SV01    04053XXXX
FD004   BUTTER    2   12/01/18 14:12:02 ...    18/01/18    SV01    04053XXXX
FD005 ICECREAM    1   12/01/18 14:12:02 ...      SV01   04053XXXX

The dataframe is of shape (123078, 199) and there are few records where the field values are jumbled.

When reading this csv using pandas, I used the error_bad_lines=False attribute to skip the lines where there is a mis-match in the field.

However, I was wondering if there is a way to FIX the data by some means (say, pattern matching with the previous items in the column; based on the dtype etc.)

How do we generally handle a scenario where every record is crucial (or say inter-dependent) and there is mismatch in the field?

Improve this question
$\endgroup$
1
  • 2
    $\begingroup$ Can you post the file content (like some rows as above) by opening it in text editor and copy-pasting from there? $\endgroup$
    – Ankit Seth
    Commented Jul 17, 2018 at 9:45

2 Answers 2

Reset to default
1
$\begingroup$

If you read the entire file without skipping jumbled lines, does it stil work? What values appear in those jumbled cells?

You could go down the road of imputation, i.e. filling the missing gaps, based on something you can deduce from the data that is there.

Example are:

  • fill-forward: fill will preceding non-missing value
  • fill-backwards: fill with following non-missing value
  • average-filling: if numerical, fill gaps with the mean/median etc. of that column. You could also use something like a moving average.
  • model-based: fill missing values using e.g. a regression model on available values (this needs a target variable, or at least timestamps)

One last snity-check: I assume it isn't possible for you to try creating the CSV file again and addressing the problem at its source?

Improve this answer
$\endgroup$
0
$\begingroup$

I doubt this is possible in general without knowing anything.

You can, of course, find outliers in the data and thus try to infer where something got mixed up. But this will likely be a lot more work than just fixing the CSV export.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.