What can R do about a messy data format?

by

Using data.table::fread:

x = '
+------------+------+------+----------+--------------------------+
|    Date    | Emp1 | Case | Priority | PriorityCountinLast7days |
+------------+------+------+----------+--------------------------+
| 2018-06-01 | A    | A1   |        0 |                        0 |
| 2018-06-03 | A    | A2   |        0 |                        1 |
| 2018-06-03 | A    | A3   |        0 |                        2 |
| 2018-06-03 | A    | A4   |        1 |                        1 |
| 2018-06-03 | A    | A5   |        2 |                        1 |
| 2018-06-04 | A    | A6   |        0 |                        3 |
| 2018-06-01 | B    | B1   |        0 |                        1 |
| 2018-06-02 | B    | B2   |        0 |                        2 |
| 2018-06-03 | B    | B3   |        0 |                        3 |
+------------+------+------+----------+--------------------------+
'

fread(gsub('[\\+-]+\\n', '', x), drop = c(1,7))

#          Date Emp1 Case Priority PriorityCountinLast7days
# 1: 2018-06-01    A   A1        0                        0
# 2: 2018-06-03    A   A2        0                        1
# 3: 2018-06-03    A   A3        0                        2
# 4: 2018-06-03    A   A4        1                        1
# 5: 2018-06-03    A   A5        2                        1
# 6: 2018-06-04    A   A6        0                        3
# 7: 2018-06-01    B   B1        0                        1
# 8: 2018-06-02    B   B2        0                        2
# 9: 2018-06-03    B   B3        0                        3

The gsub part removes the horizontal rules. drop removes the extra columns caused by delimiters at the line ends.

More Related Contents:

  1. Repeat each row of data.frame the number of times specified in a column
  2. Filter data.frame rows by a logical condition
  3. Split column at delimiter in data frame [duplicate]
  4. Pass a data.frame column name to a function
  5. Select groups based on number of unique / distinct values
  6. Assign multiple columns using := in data.table, by group
  7. Filtering a data frame on a vector [duplicate]
  8. How to replace NA values in a table for selected columns
  9. Remove an entire column from a data.frame in R
  10. Selecting multiple odd or even columns/rows for dataframe
  11. Sample n random rows per group in a dataframe
  12. Deleting reversed duplicates with R
  13. Remove Rows From Data Frame where a Row matches a String
  14. duplicates in multiple columns
  15. R – add column that counts sequentially within groups but repeats for duplicates
  16. Extend contigency table with proportions (percentages)
  17. Splitting a data.frame by a variable [duplicate]
  18. Check whether values in one data frame column exist in a second data frame
  19. How do you remove columns from a data.frame?
  20. Unlist a data frame by rows, not columns
  21. Deleting columns from a data.frame where NA is more than 15% of the column length [duplicate]
  22. Using lapply to apply a function over list of data frames and saving output to files with different names
  23. DT[!(x == .)] and DT[x != .] treat NA in x inconsistently
  24. Return df with a columns values that occur more than once [duplicate]
  25. Row-wise sum of values grouped by columns with same name
  26. Replicate each row of data.frame and specify the number of replications for each row?
  27. Numbers as column names of data frames
  28. Divide each value by the sum of values by group
  29. Convert all data frame character columns to factors
  30. What you can do with a data.frame that you can’t with a data.table?

Leave a Comment