Victoria
Reputation: 93
Is it possible to find a point value from bounds?
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?
Sample data
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")
Goal
- Divide the lower and upper bounds of monthly pay by the hours worked. This will give me hourly wages in bounds.
- Using the hourly wage bounds, find the mid-point value.
Desired output:
| 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 |
Upvotes: 2
Views: 148
Answers (1)
Friede
Reputation: 7994
This is a complete re-write of my previous answer.
Approach
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)
})
Result
> 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.
Data
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")
Upvotes: 1
Related Questions
- How to access the last value in a vector?
- How to find the statistical mode?
- Extracting specific columns from a data frame
- Find file name from full file path
- How do I install an R package from source?
- Run R script from command line
- How to find the length of a string in R
- How to find out which package version is loaded in R?
- Convert data.frame columns from factors to characters
- How to find common elements from multiple vectors?