0

So basically, there is some work I have to do with a huge data-set.

I extracted tables with two different variables within the dataframe, to get Frequency.

For example:

table1<-table(df1$vehicletype,df1$company)

# let's say table 1 looks something like this

  1 2 3 4 5 6 7
1 1 2 3 1 2 6 4
2 3 4 3 4 1 2 3
3 4 6 3 5 6 7 8

table2<-table(df1$vehicletype2,df1$company2)

  1 3 4 5 6 7
1 3 1 4 1 5 6
2 6 3 6 2 0 0
3 4 3 6 3 2 1

now I want to merge these two tables, although there are only 6 columns in table 2 (2nd column is missing) and I want that the corresponding values are added together, the table should look something like this:


  1 2 3 4 5 6 7 
1 4 2 4 5 3 11 10
2 9 4 6 10 ......
3 ...............

So basically, I want to be able to detect that over the two days, there were in total 9 vehicles used of vehicletype 2 and vehiclecompany 1. I want the function to understand, that there are simply no cars from company 2 used on the second day and just use the value 0 for the column.

The real case scenario, which is not simplified goes over a bigger time range and has more tables that need to be added with missing columns and rows.

Does anybody have an idea?

Thank you

I tried different stuff I found on StackOverflow, couldn't solve the issue tho.

Ismaili Mohamedi
  • 906
  • 7
  • 15
Filip1234
  • 1
  • 1

2 Answers2

0

In Base R you could do

merge(table1, table2, by = c('Var1', 'Var2'), all = TRUE)|>
   transform(Freq =  rowSums(cbind(Freq.x, Freq.y), TRUE)) |>
   xtabs(Freq~Var1+Var2, data = _)
    Var2
Var1  1  2  3  4  5  6  7
   1  4  2  4  5  3 11 10
   2  9  4  6 10  3  2  3
   3  8  6  6 11  9  9  9
Onyambu
  • 67,392
  • 3
  • 24
  • 53
  • What if the Variables are named differently, for example in table 1 its Var1.1 and Var1.2 and in table 2 its Var2.1 and Var2.2 ? Thanks ! – Filip1234 Mar 31 '23 at 14:20
  • @Filip1234 then instead of `by` you will use `by.x = c('Var1.1', 'Var1.2'), by.y = c(variable names in table 2)` – Onyambu Mar 31 '23 at 14:22
0

Another option is to arrange the data before tabling:

ie

comp_lev <- unique(c(df1$company, df1$company2))
vtype_lev <- unique(c(df1$vehicletype,df1$vehicletype2))

df1$company <- factor(df1$company, comp_lev)
df1$company2 <- factor(df1$company2, comp_lev)
df1$vehicletype <- factor(df1$vehicletype, vtype_lev)
df1$vehicletype2 <- factor(df1$vehicletype2, vtype_lev)

Then you can create your tables with equal dimensions:

table1 <- table(df1$vehicletype, df1$company)

table2 <- table(df1$vehicletype2, df1$company2)

table1 + table2


   1  2  3  4  5  6  7
1  4  2  4  5  3 11 10
2  9  4  6 10  3  2  3
3  8  6  6 11  9  9  9
Onyambu
  • 67,392
  • 3
  • 24
  • 53