My aim is to find the hourly wages of each observation. But the issue is monthly pay is given in bounds and monthly hours worked are given in levels. What can I do to divide the lower and upper bounds of monthly pay by the hours worked?
dput(joint.time)
structure(list(totpinc = structure(c(2, 4, 5, 4, 5, 4, 5, 4,
5, 4, 5, 5, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 3, 3, 3, 3, 3, 3, 5,
5, 5, 5, 3, 3, 3, 3, 4, 4, 4, 4, 4, 2, 2, 4, 3, 4, 3, 4, 3, 2,
4, 4, 4, 4, 5, 5, 4, 2, 2, 4, 4, 2, 2, 3, 3, 3, 2, 5, 2, 5, 2,
5, 2, 5, 6, 5, 3, 5, 5, 3, 3, 3, 3, 3, 3, 3, 3, 3, 1, 1, 1, 1,
1, 5, 5, 5, 3, 3, 3, 9, 4, 3, 3, 3, 3, 3, 4, 4, 3, 4, 4, 4, 3,
2, 3, 2, 3, 2, 4, 5, 4, 5, 3, 2, 2, 2, 2, 6, 6, 6, 1, 1, 1, 5,
4, 1, 5, 4, 1, 5, 4, 1, 5, 4, 6, 6, 6, 2, 2, 5, 5, 5, 5, 4, 3,
3, 3, 7, 4, 7, 4, 7, 4, 7, 7, 6, 5, 5, 6, 5, 6, 5, 6, 5, 6, 5,
6, 5, 6, 5, 6, 5, 1, 1, 2, 2, 2, 2, 2, 2, 6, 1, 2, 1, 2, 6, 6,
6, 2, 6, 6, 6, 2, 2, 2, 2, 3, 3, 1, 4, 5, 2, 2, 2, 2, 3, 2, 3,
5, 5, 5, 3, 3, 2, 3, 4, 4, 4, 4, 4, 4, 4, 4, 1, 1, 1, 1, 4, 4,
3, 2, 2, 3, 3, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 6, 4, 6, 2, 2,
2, 8, 7, 5, 5, 5, 3, 10, 2, 1, 4, 4, 1, 1, 1, 1, 2, 1, 1, 1,
6, 3, 3, 3, 3, 2, 3, 2, 3, 2, 4, 6, 4, 2, 6, 4, 2, 2, 2, 2, 2,
4, 4, 3, 3, 3, 3, 4, 4, 3, 3, 3, 3, 3, 3, 8, 8, 8, 8, 5, 5, 3,
10, 4, 4, 4, 4, 1, 4, 4, 4, 5, 5, 5, 4, 4, 4, 4, 4, 6, 6, 6,
6, 6, 2, 6, 6, 6, 3, 3, 4, 4, 3, 3, 3, 3, 3, 5, 3, 5, 5, 5, 5,
2, 3, 2, 3, 4, 6, 6, 6, 5, 5, 5, 5, 2, 2, 4, 3, 6, 4, 4, 4, 4,
4, 3, 3, 3, 2, 2, 2, 2, 2, 4, 6, 4, 4, 4, 5, 5, 5, 5, 5, 5, 3,
6, 6, 6, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5, 10, 5,
10, 5, 10, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,
3, 1, 11, 11, 1, 3, 6, 2, 2, 2, 5, 5, 5, 5, 5, 3, 5, 3, 4, 3,
8, 8, 3, 1, 3, 1, 1, 4, 4, 4, 4, 1, 6, 4, 4, 4, 4, 4, 4, 4, 5,
3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4), labels = c(`ineligible - not currently employed/ self employed` = -2,
`ineligible - under 16yrs` = -1, ` less than £ 215` = 1,
`£ 215 to less than £ 435` = 2, `£ 435 to less than £ 870` = 3,
`£ 870 to less than £1305` = 4, `£1305 to less than £1740` = 5,
`£1740 to less than £2820` = 6, `£2820 to less than £3420` = 7,
`£3420 to less than £3830` = 8, `£3830 to less than £4580` = 9,
`£4580 to less than £6670` = 10, `£6670 or more` = 11, `eligible (current employee or self-emp) - dk/ refuse income` = 12
), label = "total net monthly personal income (for employees & self-employed together)", class = c("haven_labelled",
"vctrs_vctr", "double")), mthhrs = c(150.5, 193.5, 86, 193.5,
86, 193.5, 86, 193.5, 86, 215, 172, 172, 154.8, 154.8, 154.8,
150.5, 150.5, 150.5, 150.5, 150.5, 150.5, 150.5, 43, 43, 43,
43, 258, 258, 172, 172, 172, 172, 129, 129, 129, 129, 150.5,
150.5, 150.5, 150.5, 150.5, 60.2, 60.2, 172, 167.7, 172, 167.7,
172, 167.7, 51.6, 159.1, 159.1, 159.1, 159.1, 167.7, 167.7, 150.5,
68.8, 68.8, 197.8, 197.8, 86, 68.8, 141.9, 141.9, 141.9, 64.5,
258, 64.5, 258, 64.5, 258, 64.5, 258, 193.5, 258, 64.5, 172,
172, 86, 86, 86, 86, 86, 86, 86, 193.5, 193.5, 391.3, 391.3,
391.3, 391.3, 391.3, 159.1, 159.1, 159.1, 86, 86, 86, 202.1,
163.4, 60.2, 60.2, 60.2, 60.2, 60.2, 103.2, 103.2, 120.4, 159.1,
159.1, 159.1, 150.5, 86, 150.5, 86, 150.5, 86, 180.6, 193.5,
180.6, 193.5, 159.1, 34.4, 34.4, 43, 43, 236.5, 236.5, 236.5,
124.7, 124.7, 90.3, 172, 150.5, 90.3, 172, 150.5, 90.3, 172,
150.5, 90.3, 172, 150.5, 172, 172, 172, 150.5, 150.5, 167.7,
167.7, 167.7, 167.7, 159.1, 215, 215, 215, 193.5, 154.8, 193.5,
154.8, 193.5, 154.8, 159.1, 159.1, 159.1, 129, 129, 150.5, 172,
150.5, 172, 150.5, 172, 150.5, 172, 150.5, 172, 150.5, 172, 150.5,
172, 86, 86, 47.3, 47.3, 47.3, 81.7, 47.3, 47.3, 150.5, 55.9,
107.5, 55.9, 107.5, 172, 172, 172, 64.5, 159.1, 159.1, 159.1,
172, 172, 172, 172, 86, 86, 64.5, 163.4, 150.5, 81.7, 81.7, 81.7,
172, 103.2, 172, 103.2, 172, 172, 172, 86, 86, 68.8, 150.5, 236.5,
159.1, 150.5, 159.1, 150.5, 159.1, 150.5, 159.1, 64.5, 64.5,
64.5, 64.5, 172, 159.1, 103.2, 86, 86, 137.6, 137.6, 64.5, 64.5,
64.5, 94.6, 94.6, 94.6, 94.6, 159.1, 159.1, 159.1, 159.1, 301,
159.1, 301, 60.2, 60.2, 60.2, 258, 215, 150.5, 150.5, 150.5,
120.4, 387, 51.6, 30.1, 159.1, 150.5, 43, 43, 43, 43, 12.9, 154.8,
154.8, 154.8, 159.1, 77.4, 150.5, 150.5, 77.4, 64.5, 77.4, 64.5,
77.4, 64.5, 193.5, 193.5, 172, 266.6, 193.5, 172, 266.6, 107.5,
107.5, 107.5, 107.5, 150.5, 150.5, 129, 129, 81.7, 81.7, 159.1,
159.1, 159.1, 150.5, 159.1, 150.5, 159.1, 150.5, 258, 258, 258,
258, 172, 172, 129, 193.5, 167.7, 172, 172, 159.1, 129, 94.6,
94.6, 94.6, 258, 258, 258, 150.5, 150.5, 150.5, 154.8, 154.8,
236.5, 236.5, 236.5, 236.5, 236.5, 68.8, 159.1, 159.1, 215, 133.3,
133.3, 172, 172, 8.6, 8.6, 8.6, 167.7, 129, 129, 129, 129, 129,
129, 129, 60.2, 107.5, 60.2, 107.5, 55.9, 154.8, 154.8, 154.8,
129, 129, 129, 129, 68.8, 68.8, 107.5, 120.4, 193.5, 184.9, 94.6,
94.6, 159.1, 159.1, 167.7, 167.7, 167.7, 68.8, 68.8, 68.8, 86,
86, 361.2, 258, 150.5, 150.5, 150.5, 206.4, 206.4, 206.4, 206.4,
206.4, 206.4, 159.1, 129, 129, 129, 154.8, 154.8, 150.5, 77.4,
150.5, 77.4, 86, 86, 86, 172, 172, 172, 172, 172, 146.2, 236.5,
146.2, 236.5, 146.2, 236.5, 86, 86, 150.5, 60.2, 150.5, 60.2,
150.5, 60.2, 150.5, 60.2, 150.5, 60.2, 150.5, 60.2, 150.5, 60.2,
150.5, 60.2, 103.2, 202.1, 202.1, 172, 120.4, 154.8, 86, 86,
283.8, 180.6, 180.6, 180.6, 180.6, 172, 107.5, 172, 107.5, 159.1,
258, 150.5, 150.5, 116.1, 47.3, 124.7, 129, 129, 301, 301, 159.1,
159.1, 34.4, 172, 215, 215, 150.5, 150.5, 150.5, 150.5, 150.5,
159.1, 197.8, 197.8, 172, 159.1, 172, 159.1, 172, 159.1, 172,
159.1, 193.5, 193.5, 193.5, 193.5)), row.names = c(NA, 500L), class = "data.frame")
| totpinc | mthhrs | hrwage_bound | hrwage |
| -------- | ----- | -------- |--------|
| £215 - £435 | 150.5 | 1.4 - 2.9 | 2.2 |
| £870 - £1305 | 193.5 | 4.5 - 6.7 | 5.6 |
| £1305 - £1740 | 86 | 15.2 - 20.2 | 17.7 |
| £870 - £1305 | 193.5 | 4.5 - 6.7 | 5.6 |
| £1305 - £1740 | 86 | 15.2 - 20.2 | 17.7 |
| £870 - £1305 | 193.5 | 4.5 - 6.7 | 5.6 |
| £1305 - £1740 | 86 | 15.2 - 20.2 | 17.7 |
| £870 - £1305 | 193.5 | 4.5 - 6.7 | 5.6 |
| £1305 - £1740 | 86 | 15.2 - 20.2 | 17.7 |
| £870 - £1305 | 193.5 | 4.5 - 6.7 | 5.6 |
This is a complete re-write of my previous answer.
yy =
attributes(joint.time$totpinc)$labels[
attributes(joint.time$totpinc)$labels %in% unique(joint.time$totpinc)] |>
names() |>
strsplit("\\D+") |>
lapply(\(x) x[x != ""]) |>
lapply(as.numeric)
yy[[1]] = append(yy[[1]], 0, after = 0) # hard-coded
yy[[11]] = append(yy[[11]], 500000, after = 1) # hard-coded
lookup =
data.frame(seq_len(length(yy)),
t(list2DF(yy))) |>
`colnames<-`(c("label", "minb", "maxb"))
lookup$maxb = lookup$maxb - 1 # "less than"
idx = lookup[match(joint.time$totpinc, lookup$label), ]
rm(yy, lookup)
new =
within(joint.time, {
hrwage = lapply(idx[c("minb", "maxb")],
\(x) x / mthhrs) |> list2DF() |> rowMeans() |> round(1)
totpinc = paste0("£", idx$minb, "-£", idx$maxb)
})
> head(new, n = 10)
totpinc mthhrs hrwage
1 £215-£434 150.5 2.2
2 £870-£1304 193.5 5.6
3 £1305-£1739 86.0 17.7
4 £870-£1304 193.5 5.6
5 £1305-£1739 86.0 17.7
6 £870-£1304 193.5 5.6
7 £1305-£1739 86.0 17.7
8 £870-£1304 193.5 5.6
9 £1305-£1739 86.0 17.7
10 £870-£1304 215.0 5.1
hrwage_bound is not provided and thus missing in the result.
joint.time =
structure(list(totpinc = structure(c(2, 4, 5, 4, 5, 4, 5, 4,
5, 4, 5, 5, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 3, 3, 3, 3, 3, 3, 5,
5, 5, 5, 3, 3, 3, 3, 4, 4, 4, 4, 4, 2, 2, 4, 3, 4, 3, 4, 3, 2,
4, 4, 4, 4, 5, 5, 4, 2, 2, 4, 4, 2, 2, 3, 3, 3, 2, 5, 2, 5, 2,
5, 2, 5, 6, 5, 3, 5, 5, 3, 3, 3, 3, 3, 3, 3, 3, 3, 1, 1, 1, 1,
1, 5, 5, 5, 3, 3, 3, 9, 4, 3, 3, 3, 3, 3, 4, 4, 3, 4, 4, 4, 3,
2, 3, 2, 3, 2, 4, 5, 4, 5, 3, 2, 2, 2, 2, 6, 6, 6, 1, 1, 1, 5,
4, 1, 5, 4, 1, 5, 4, 1, 5, 4, 6, 6, 6, 2, 2, 5, 5, 5, 5, 4, 3,
3, 3, 7, 4, 7, 4, 7, 4, 7, 7, 6, 5, 5, 6, 5, 6, 5, 6, 5, 6, 5,
6, 5, 6, 5, 6, 5, 1, 1, 2, 2, 2, 2, 2, 2, 6, 1, 2, 1, 2, 6, 6,
6, 2, 6, 6, 6, 2, 2, 2, 2, 3, 3, 1, 4, 5, 2, 2, 2, 2, 3, 2, 3,
5, 5, 5, 3, 3, 2, 3, 4, 4, 4, 4, 4, 4, 4, 4, 1, 1, 1, 1, 4, 4,
3, 2, 2, 3, 3, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 6, 4, 6, 2, 2,
2, 8, 7, 5, 5, 5, 3, 10, 2, 1, 4, 4, 1, 1, 1, 1, 2, 1, 1, 1,
6, 3, 3, 3, 3, 2, 3, 2, 3, 2, 4, 6, 4, 2, 6, 4, 2, 2, 2, 2, 2,
4, 4, 3, 3, 3, 3, 4, 4, 3, 3, 3, 3, 3, 3, 8, 8, 8, 8, 5, 5, 3,
10, 4, 4, 4, 4, 1, 4, 4, 4, 5, 5, 5, 4, 4, 4, 4, 4, 6, 6, 6,
6, 6, 2, 6, 6, 6, 3, 3, 4, 4, 3, 3, 3, 3, 3, 5, 3, 5, 5, 5, 5,
2, 3, 2, 3, 4, 6, 6, 6, 5, 5, 5, 5, 2, 2, 4, 3, 6, 4, 4, 4, 4,
4, 3, 3, 3, 2, 2, 2, 2, 2, 4, 6, 4, 4, 4, 5, 5, 5, 5, 5, 5, 3,
6, 6, 6, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5, 10, 5,
10, 5, 10, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,
3, 1, 11, 11, 1, 3, 6, 2, 2, 2, 5, 5, 5, 5, 5, 3, 5, 3, 4, 3,
8, 8, 3, 1, 3, 1, 1, 4, 4, 4, 4, 1, 6, 4, 4, 4, 4, 4, 4, 4, 5,
3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4), labels = c(`ineligible - not currently employed/ self employed` = -2,
`ineligible - under 16yrs` = -1, ` less than £ 215` = 1,
`£ 215 to less than £ 435` = 2, `£ 435 to less than £ 870` = 3,
`£ 870 to less than £1305` = 4, `£1305 to less than £1740` = 5,
`£1740 to less than £2820` = 6, `£2820 to less than £3420` = 7,
`£3420 to less than £3830` = 8, `£3830 to less than £4580` = 9,
`£4580 to less than £6670` = 10, `£6670 or more` = 11, `eligible (current employee or self-emp) - dk/ refuse income` = 12
), label = "total net monthly personal income (for employees & self-employed together)", class = c("haven_labelled",
"vctrs_vctr", "double")), mthhrs = c(150.5, 193.5, 86, 193.5,
86, 193.5, 86, 193.5, 86, 215, 172, 172, 154.8, 154.8, 154.8,
150.5, 150.5, 150.5, 150.5, 150.5, 150.5, 150.5, 43, 43, 43,
43, 258, 258, 172, 172, 172, 172, 129, 129, 129, 129, 150.5,
150.5, 150.5, 150.5, 150.5, 60.2, 60.2, 172, 167.7, 172, 167.7,
172, 167.7, 51.6, 159.1, 159.1, 159.1, 159.1, 167.7, 167.7, 150.5,
68.8, 68.8, 197.8, 197.8, 86, 68.8, 141.9, 141.9, 141.9, 64.5,
258, 64.5, 258, 64.5, 258, 64.5, 258, 193.5, 258, 64.5, 172,
172, 86, 86, 86, 86, 86, 86, 86, 193.5, 193.5, 391.3, 391.3,
391.3, 391.3, 391.3, 159.1, 159.1, 159.1, 86, 86, 86, 202.1,
163.4, 60.2, 60.2, 60.2, 60.2, 60.2, 103.2, 103.2, 120.4, 159.1,
159.1, 159.1, 150.5, 86, 150.5, 86, 150.5, 86, 180.6, 193.5,
180.6, 193.5, 159.1, 34.4, 34.4, 43, 43, 236.5, 236.5, 236.5,
124.7, 124.7, 90.3, 172, 150.5, 90.3, 172, 150.5, 90.3, 172,
150.5, 90.3, 172, 150.5, 172, 172, 172, 150.5, 150.5, 167.7,
167.7, 167.7, 167.7, 159.1, 215, 215, 215, 193.5, 154.8, 193.5,
154.8, 193.5, 154.8, 159.1, 159.1, 159.1, 129, 129, 150.5, 172,
150.5, 172, 150.5, 172, 150.5, 172, 150.5, 172, 150.5, 172, 150.5,
172, 86, 86, 47.3, 47.3, 47.3, 81.7, 47.3, 47.3, 150.5, 55.9,
107.5, 55.9, 107.5, 172, 172, 172, 64.5, 159.1, 159.1, 159.1,
172, 172, 172, 172, 86, 86, 64.5, 163.4, 150.5, 81.7, 81.7, 81.7,
172, 103.2, 172, 103.2, 172, 172, 172, 86, 86, 68.8, 150.5, 236.5,
159.1, 150.5, 159.1, 150.5, 159.1, 150.5, 159.1, 64.5, 64.5,
64.5, 64.5, 172, 159.1, 103.2, 86, 86, 137.6, 137.6, 64.5, 64.5,
64.5, 94.6, 94.6, 94.6, 94.6, 159.1, 159.1, 159.1, 159.1, 301,
159.1, 301, 60.2, 60.2, 60.2, 258, 215, 150.5, 150.5, 150.5,
120.4, 387, 51.6, 30.1, 159.1, 150.5, 43, 43, 43, 43, 12.9, 154.8,
154.8, 154.8, 159.1, 77.4, 150.5, 150.5, 77.4, 64.5, 77.4, 64.5,
77.4, 64.5, 193.5, 193.5, 172, 266.6, 193.5, 172, 266.6, 107.5,
107.5, 107.5, 107.5, 150.5, 150.5, 129, 129, 81.7, 81.7, 159.1,
159.1, 159.1, 150.5, 159.1, 150.5, 159.1, 150.5, 258, 258, 258,
258, 172, 172, 129, 193.5, 167.7, 172, 172, 159.1, 129, 94.6,
94.6, 94.6, 258, 258, 258, 150.5, 150.5, 150.5, 154.8, 154.8,
236.5, 236.5, 236.5, 236.5, 236.5, 68.8, 159.1, 159.1, 215, 133.3,
133.3, 172, 172, 8.6, 8.6, 8.6, 167.7, 129, 129, 129, 129, 129,
129, 129, 60.2, 107.5, 60.2, 107.5, 55.9, 154.8, 154.8, 154.8,
129, 129, 129, 129, 68.8, 68.8, 107.5, 120.4, 193.5, 184.9, 94.6,
94.6, 159.1, 159.1, 167.7, 167.7, 167.7, 68.8, 68.8, 68.8, 86,
86, 361.2, 258, 150.5, 150.5, 150.5, 206.4, 206.4, 206.4, 206.4,
206.4, 206.4, 159.1, 129, 129, 129, 154.8, 154.8, 150.5, 77.4,
150.5, 77.4, 86, 86, 86, 172, 172, 172, 172, 172, 146.2, 236.5,
146.2, 236.5, 146.2, 236.5, 86, 86, 150.5, 60.2, 150.5, 60.2,
150.5, 60.2, 150.5, 60.2, 150.5, 60.2, 150.5, 60.2, 150.5, 60.2,
150.5, 60.2, 103.2, 202.1, 202.1, 172, 120.4, 154.8, 86, 86,
283.8, 180.6, 180.6, 180.6, 180.6, 172, 107.5, 172, 107.5, 159.1,
258, 150.5, 150.5, 116.1, 47.3, 124.7, 129, 129, 301, 301, 159.1,
159.1, 34.4, 172, 215, 215, 150.5, 150.5, 150.5, 150.5, 150.5,
159.1, 197.8, 197.8, 172, 159.1, 172, 159.1, 172, 159.1, 172,
159.1, 193.5, 193.5, 193.5, 193.5)), row.names = c(NA, 500L), class = "data.frame")