Data Cleaner - Preprocess and organize column-oriented data - MATLAB (original) (raw)

Preprocess and organize column-oriented data

Since R2022a

Description

The Data Cleaner app is an interactive tool for identifying messy column-oriented data, cleaning multiple variables of data at a time, and iterating on and refining the cleaning process.

Using this app, you can:

• Access column-oriented data in the MATLAB® workspace or import column-oriented data from a file.
• Explore data by using the visualization, data, and summary views.
• Sort by a variable, rename a variable, or remove a variable.
• Retime data in a timetable, stack or unstack table variables, clean missing data, clean outlier data, smooth data, or normalize data.
• Edit previously performed cleaning steps.
• Export cleaned data to the MATLAB workspace, or export code for data cleaning as a script or function.

For more information about cleaning data, watch How to Clean Your Data in MATLAB (5 min, 28 sec).

• The Data Cleaner app currently supports cleaning only table and timetable data.
• The Data Cleaner app currently supports cleaning only one table or timetable at a time.

Open the Data Cleaner App

• MATLAB Toolstrip: On the Apps tab, underMATLAB, click the app icon.
• MATLAB command prompt: Enter dataCleaner.

Examples

expand all

Use the Data Cleaner app to preprocess and organize messy timetable data by removing a variable and retiming, smoothing, and normalizing the data. Then, export the cleaned data to the MATLAB workspace. You can follow these steps to preprocess and organize messy timetable data, but note that your data may require a different set of cleaning steps.

This example shows how to preprocess and organize time-stamped bicycle traffic data. The data set comes from sensors on Broadway Street in Cambridge, MA. The City of Cambridge provides public access to the full data set at the Cambridge Open Data site.

1. Open Timetable in Data Cleaner App
   Use the MATLAB Toolstrip or the MATLAB command window to open the Data Cleaner app.
   Load the time-stamped bicycle traffic data by using bikeData = readtimetable("BicycleCounts.csv") in the command window. Then, select in the Data Cleaner app, and specify the timetable bikeData. Alternatively, import the data by selecting in the Data Cleaner app.
   Once the timetable is loaded into the app, view the raw data in theData tab and a data summary in theSummary tab.
   
   Explore the timetable data in the Visualization tab. Select the Total, Westbound, andEastbound timetable variables in the Variables panel.
   The plots suggest that there is a correlation between time of the year and bike traffic.
2. Remove Variable from Timetable
   The Day variable contains redundant data because the day of data collection is reflected in the timestamp. Interactively remove Day from the timetable by using the Variables panel. To remove the variable, right-click Day and select . Variable removal now appears as a step in theCleaning Steps panel.
3. Retime Timetable
   The data summary shows missing and duplicate timestamp values in the timetable. To sort the timetable and establish unique row times, click Retime Timetable in the Cleaning Methods section of theHome tab of the app. Specify Unique row times of input as the selection method and use the Sum method to aggregate. Accept the cleaning parameters to add the cleaning step and update the timetable.
   After accepting the retiming parameters, the updated data summary shows that there are no missing or duplicate timestamp values and that the timestamps are sorted from earliest to latest.
   If retiming is not necessary for your timetable, you can interactively sort byTimestamp or another timetable variable. Access the sorting options by clicking the arrow in the variable header in the Data tab.
4. Smooth Data
   Because the bicycle traffic spikes for certain days of each week, smoothing can lessen the noise within each week and give better insight into the bicycle traffic trend throughout the year. To smooth the data, use the Smooth Data cleaning method. Select the Moving mean smoothing method and specify a centered 7-day window for smoothing. Accept the cleaning parameters to add the cleaning step and update the timetable.
5. Normalize Data
   Because the three numeric variables Total,Westbound, and Eastbound have different scales, use normalization to scale by standard deviation. To normalize the data, use theNormalize Data cleaning method. SelectScale as the normalization method and Standard deviation as the scale type.
   To more clearly preview this cleaning step, clear the original data in the legend of the visualizations. Accept the cleaning parameters to add the cleaning step and update the timetable.
   
6. Export Timetable
   Export the cleaned timetable to the MATLAB workspace by selecting Export > Export to Workspace.
   Alternatively, export timetable cleaning code by selecting or .

Parameters

expand all

Clean Missing Data

Select one of these values to specify the missing-value indicators.

Select one of these method values and, if necessary, additional method parameters to specify how to handle missing data.

Select one of these method values and, if necessary, additional method parameters to specify how to fill missing data.

Clean Outlier Data

Select one of these method values to specify how to handle outlier data.

Select one of these method values to specify the fill method for replacing outlier data.

Select one of these method values and additional method parameters to specify the detection method for identifying outlier data.

Normalize Data

Select one of these method values and, if necessary, additional method parameters to specify the method for normalizing data.

Smooth Data

Select one of these method values to specify the smoothing method for noisy data.

Select one of these parameter values and additional parameter options to specify the options for data smoothing.

Retime Timetable

Select one of these method values and additional method parameters to specify the selection method for retimed row times.

Select one of these method values to specify the retiming method.

Stack Table Variables

Select one or more table variables to combine.

Unstack Table Variables

Select a table variable containing the names of the new table variables.

Select a table variable to unstack into multiple table variables.

Select one or more table variables to define groups of rows.

Select one of these values to specify the function to aggregate data values into a single value.

Tips

• To interactively sort by a data variable, access the sorting options by clicking the arrow in the variable header in the Data tab. The sorting appears as a step in the Cleaning Steps panel.
• To interactively rename a variable from the data, double-click the variable name in the Variables panel. The renaming appears as a step in theCleaning Steps panel.
• To interactively remove a variable from the data, right-click the variable name in theVariables panel and select Delete. The removal appears as a step in the Cleaning Steps panel.
• To alter previously performed cleaning steps, perform one of these actions:
  • View or edit cleaning parameters by clicking a step in the Cleaning Steps panel.
  • Change the order in which cleaning steps are performed by dragging a step to a new location in the Cleaning Steps panel.
  • Disable cleaning steps by clearing a cleaning step or right-clicking a step and selecting in the Cleaning Steps panel.
• To view only the input data or cleaned data, select or clear elements in the plot legends in the Visualizations tab.

Version History

Introduced in R2022a

expand all

For the Clean Outlier Data cleaning method, define outliers as elements outside of a range specified by an upper and lower threshold by selecting theRange detection method.

Fill missing entries with the mean of nearby points by using the K-nearest neighbors fill method for the Clean Missing Data cleaning method. Specify the number of neighbors, and define the distance between rows as the Euclidean distance or the scaled Euclidean distance.

Save Data Cleaner app session as a binary MAT-file containing the data and cleaning steps. To save the session file, in the File section of theHome tab, select Save.

Import and clean data in a table from the MATLAB workspace or from a file. Previously, you could clean only timetable data.

The Data view displays sparklines and summary statistics to quickly visualize and interpret the data in each table or timetable variable. Show more information related to specific points by pointing to a sparkline.

See Also

Tools

• Clean Missing Data | Clean Outlier Data | Smooth Data | Normalize Data | Retime Timetable | Stack Table Variables | Unstack Table Variables