Introduction

This code through explores how to clean a small firm-level dataset in R. The example uses simualted data with variable names similar to Compustat annual data. All firms and values are fictional. These are not actual Compustat records. In management research, a researcher will use firm-level financial data to compare firm size and business activity. Before making those comparison, the researcher needs to check whether the dataset contains duplicate records or missing values.


Content Overview

Specifically, we’ll create a small dataset and check its structure. We will then remove a confirmed duplicate and examine missing values. Finally, we will create a separate sample for comparison that requires both total assets and sales revenue.


Why You Should Care

This topic is valuable because an accidentally duplicated record can cause the same observation to be counted twice or more. Missing values can also affect which observations enter an analysis. Chekcing these issues can help researchers understand their data and document how they selected their sample.


Learning Objectives

Specifically, you’ll learn how to clean and check a dataset, by using ‘distinct()’ to remove confirmed exact duplicates, and count missing value with ‘is.na()’. You will also use ‘filter()’ to select observations with values available for specific variables.



Body Title

Here, we’ll prepare a small dataset for a comparison of total assets and sales revenue.


Further Exposition

The intended unit of observation is a firm-year: one company in one fiscal year. The same company can appear in several years without those rows being duplicates.

The example uses the following variables: | Variable | Meaning | |———-|———| | gvkey | Firm identifier; fictional codes are used in this example | | fyear | Fiscal year | | at | Total assets | | sale | Sales revenue |

For this simulated example, total assets and sales are expressed in millions of dollars. When using real data, check the dataset documentation for units, currency, and reporting format.

WRDS provides a Compustat annual-data example using these variable names. Real extracts also require attention to reporting format and consolidation settings; this tutorial focuses on a small, already defined example.


Basic Example

A basic example shows how we first create a small dataset. It intentionally contains one exact duplicate and several missing values.

# Some code
# Create simulated firm-year data
firm_data <- data.frame(
  gvkey = c("F001", "F001", "F002", "F002", "F002", "F003"),
  fyear = c(2022, 2023, 2022, 2023, 2023, 2023),
  at = c(500, 550, 300, NA, NA, 800),
  sale = c(200, 230, 150, 170, 170, NA)
)

# Display the dataset
pander(firm_data)
gvkey fyear at sale
F001 2022 500 200
F001 2023 550 230
F002 2022 300 150
F002 2023 NA 170
F002 2023 NA 170
F003 2023 800 NA

data.frame() creates a table, and each c() supplies the values for one column. NA represents a missing value. pander() formats the table for display in the knitted document.

Rows 4 and 5 are identical. Firm F001 appears in two different years, so its two observations should both be retained.

Next, we inspect the structure and a summary of the data.

# Inspect variable types and the number of observations
str(firm_data)
## 'data.frame':    6 obs. of  4 variables:
##  $ gvkey: chr  "F001" "F001" "F002" "F002" ...
##  $ fyear: num  2022 2023 2022 2023 2023 ...
##  $ at   : num  500 550 300 NA NA 800
##  $ sale : num  200 230 150 170 170 NA
# Summarize each variable
summary(firm_data)
##     gvkey               fyear            at             sale    
##  Length:6           Min.   :2022   Min.   :300.0   Min.   :150  
##  Class :character   1st Qu.:2022   1st Qu.:450.0   1st Qu.:170  
##  Mode  :character   Median :2023   Median :525.0   Median :170  
##                     Mean   :2023   Mean   :537.5   Mean   :184  
##                     3rd Qu.:2023   3rd Qu.:612.5   3rd Qu.:200  
##                     Max.   :2023   Max.   :800.0   Max.   :230  
##                                    NA's   :2       NA's   :1

str() shows the variable types and dimensions of the dataset. summary() summarizes the numeric variables and reports their missing values. Neither function changes the original data.


Advanced Examples

More specifically, this can be used for removing duplicate rows

# Some code
# Remove duplicate rows
firm_clean <- distinct(firm_data)

# Show the result
pander(firm_clean)
gvkey fyear at sale
F001 2022 500 200
F001 2023 550 230
F002 2022 300 150
F002 2023 NA 170
F003 2023 800 NA

The dataset now contains 5 rows. Observations for the same firm in different years are still retained.


<br>

What's more, it can also be used for cheking missing values


``` r
# Some code
# Count missing values in total assets
sum(is.na(firm_clean$at))
## [1] 1
# Count missing values in sales revenue
sum(is.na(firm_clean$sale))
## [1] 1

$ selects a column. is.na() identifies missing values, and sum() counts them. Both results are 1.

Missing values are kept for now because missing does not mean zero. How we handle them depends on the planned analysis.

# Select observations from 2023
firm_2023 <- filter(firm_clean, fyear == 2023)

# Show the selected observations
pander(firm_2023)
gvkey fyear at sale
F001 2023 550 230
F002 2023 NA 170
F003 2023 800 NA

filter() keeps rows that meet a condition. Here, fyear == 2023 selects records from fiscal year 2023. The result contains 3 rows.


<br>

Most notably, it's valuable for summarizing firm-year data after checking duplicate records and missing values.


``` r
# Some code
# Calculate average total assets
mean(firm_clean$at, na.rm = TRUE)
## [1] 537.5

mean() calculates the average. na.rm = TRUE ignores missing values in this calculation without changing the dataset.

```



Further Resources

Learn more about [package, technique, dataset] with the following:




Works Cited

This code through references and cites the following sources:


  • Radečić, D. (2022, May 3). Data Cleaning in R: 2 R Packages to Clean and Validate Datasets. R-bloggers. Article link