Suppose you have the following dataframe:
df <- data.frame(industry = c("DEU_10T12", "DEU_13T15", "DEU_16", "DEU_17", "ITA_10T12", "ITA_13T15", "ITA_16", "ITA_17"),
DEU_10T12 = c(20, 24, 26, 20, 10, 0, NA, 1.5),DEU_13T15 = c(15, 16, 4.5, NA, 7.5, 5, 3, 0),
DEU_16 = c(1.5, 6, 4, 0, 0.5, 15, 3, 0.5),DEU_17 = c(NA, 20, 10, 2, 0, 0, 0, 7),
ITA_10T12 = c(0.5, 2, 3, 4, 10, 50, 2, 15), ITA_13T15 = c(25, 0, 4.5, NA, 17.5, 5, 13, 0.9),
ITA_16 = c(2, 3, 40, 20, 0.5, 15, 3, 1),ITA_17 = c(1, 9, 0.5, 2, 10, 20, 50, 7))
And the objective is to have the following matrix (it shall be numeric and handle NAs summation):
df2 <- data.frame(industry = c("DEU_10T12", "DEU_13T15", "DEU_16", "DEU_17", "ITA_10T12", "ITA_13T15", "ITA_16", "ITA_17"),
DEU_10T12 = c(0, 0, 0, 0, 10, 0, NA, 1.5),DEU_13T15 = c(0, 0, 0, 0, 7.5, 5, 3, 0),
DEU_16 = c(0, 0, 0, 0, 0.5, 15, 3, 0.5),DEU_17 = c(0, 0, 0, 0, 0, 0, 0, 7),
ITA_10T12 = c(0.5, 2, 3, 4, 0, 0, 0, 0), ITA_13T15 = c(25, 0, 4.5, NA, 0, 0, 0, 0),
ITA_16 = c(2, 3, 40, 20, 0, 0, 0, 0),ITA_17 = c(1, 9, 0.5, 2, 0, 0, 0, 0))
The new matrix (df2, converted to numeric) will mirror the values of the original matrix (df, also numeric), except when a row entry shares the same initial three characters as its corresponding column entry. In such cases, like for example DEU_10T12 in row and a column starting with DEU, the value will be set to zero, disregarding any existing NA values.
I tried as follows. First, I transform df as numeric as follows
# Extract row and column names
row_names <- df$industry
col_names <- colnames(df)[-1] # Exclude 'industry' column
# Create an empty matrix
Z <- matrix(NA, nrow = length(row_names), ncol = length(col_names), dimnames = list(row_names, col_names))
# Fill in the matrix with values from the data frame
for (i in 1:length(row_names)) {
for (j in 1:length(col_names)) {
Z[i, j] <- df[i, col_names[j]]
}
}
# Create an empty matrix for Z_narrow
Z_narrow = matrix(0, nrow = nrow(Z), ncol = ncol(Z))
# Assign row and column names
rownames(Z_narrow) = rownames(Z)
colnames(Z_narrow) = colnames(Z)
# Function to get the indices of columns to be replaced with zeros based on the first three characters of the column name
get_zero_indices <- function(col_name, row_names) {substr(col_name, 1, 3) == substr(row_names, 1, 3)}
# Loop through each row of Z to populate Z_narrow
for (i in 1:nrow(Z)) {
row_name <- rownames(Z)[i]
indices_to_zero <- sapply(colnames(Z), get_zero_indices, row_names = row_name)
Z_narrow[i, indices_to_zero] <- 0
Z_narrow[i, !indices_to_zero] <- Z[i, !indices_to_zero]
}
This code works when using this little dataset, but it causes R to crash when applied to a larger dataset. Any suggestions?
You can melt the original dataframe, and set to 0 if the first three character match; then cast back to wide
library(data.table)
setDT(df)
dcast(
melt(df,id.vars = "industry")[substr(industry,1,3) == substr(variable,1,3), value:=0],
industry~variable
)
Output
industry DEU_10T12 DEU_13T15 DEU_16 DEU_17 ITA_10T12 ITA_13T15 ITA_16 ITA_17
<char> <num> <num> <num> <num> <num> <num> <num> <num>
1: DEU_10T12 0.0 0.0 0.0 0 0.5 25.0 2 1.0
2: DEU_13T15 0.0 0.0 0.0 0 2.0 0.0 3 9.0
3: DEU_16 0.0 0.0 0.0 0 3.0 4.5 40 0.5
4: DEU_17 0.0 0.0 0.0 0 4.0 NA 20 2.0
5: ITA_10T12 10.0 7.5 0.5 0 0.0 0.0 0 0.0
6: ITA_13T15 0.0 5.0 15.0 0 0.0 0.0 0 0.0
7: ITA_16 NA 3.0 3.0 0 0.0 0.0 0 0.0
8: ITA_17 1.5 0.0 0.5 7 0.0 0.0 0 0.0
Another approach, without using any reshaping at all:
mask = apply(df, 1, \(x) c(F,substr(x[1],1,3)==substr(names(x[2:length(x)]),1,3)))
df[t(mask)] <- 0
Output:
industry DEU_10T12 DEU_13T15 DEU_16 DEU_17 ITA_10T12 ITA_13T15 ITA_16 ITA_17
1 DEU_10T12 0.0 0.0 0.0 0 0.5 25.0 2 1.0
2 DEU_13T15 0.0 0.0 0.0 0 2.0 0.0 3 9.0
3 DEU_16 0.0 0.0 0.0 0 3.0 4.5 40 0.5
4 DEU_17 0.0 0.0 0.0 0 4.0 NA 20 2.0
5 ITA_10T12 10.0 7.5 0.5 0 0.0 0.0 0 0.0
6 ITA_13T15 0.0 5.0 15.0 0 0.0 0.0 0 0.0
7 ITA_16 NA 3.0 3.0 0 0.0 0.0 0 0.0
8 ITA_17 1.5 0.0 0.5 7 0.0 0.0 0 0.0