rdataframe
Updated question about increasing the values in a dataset so that they cover as entirely as possible the continuum of some number
I posted similar question before, and want to re-post it with some additional details.
The date is:
data <- structure(list(country = c("Italy", "Italy", "Italy", "Italy",
"Italy", "Italy", "Austria", "Austria", "Austria", "Austria",
"Austria", "Austria", "Germany", "Germany", "Germany", "Germany",
"Germany", "Germany", "Switzerland", "Switzerland", "Switzerland",
"Switzerland", "Switzerland", "Switzerland", "Denmark", "Denmark",
"Denmark", "Denmark", "Denmark", "Denmark", "Norway", "Norway",
"Norway", "Norway", "Norway", "Norway", "Sweden", "Sweden", "Sweden",
"Sweden", "Sweden", "Sweden", "France", "France", "France", "France",
"France", "France", "Spain", "Spain", "Spain", "Spain", "Spain",
"Spain", "Portugal", "Portugal", "Portugal", "Portugal", "Portugal",
"Portugal", "Poland", "Poland", "Poland", "Poland", "Poland",
"Poland", "Russia", "Russia", "Russia", "Russia", "Russia", "Russia",
"Czech", "Czech", "Czech", "Czech", "Czech", "Czech", "Hungary",
"Hungary", "Hungary", "Hungary", "Hungary", "Hungary"), date = c(1000,
1200, 1500, 1600, 1700, 1800, 1000, 1200, 1500, 1600, 1700, 1800,
1000, 1200, 1500, 1600, 1700, 1800, 1000, 1200, 1500, 1600, 1700,
1800, 1000, 1200, 1500, 1600, 1700, 1800, 1000, 1200, 1500, 1600,
1700, 1800, 1000, 1200, 1500, 1600, 1700, 1800, 1000, 1200, 1500,
1600, 1700, 1800, 1000, 1200, 1500, 1600, 1700, 1800, 1000, 1200,
1500, 1600, 1700, 1800, 1000, 1200, 1500, 1600, 1700, 1800, 1000,
1200, 1500, 1600, 1700, 1800, 1000, 1200, 1500, 1600, 1700, 1800,
1000, 1200, 1500, 1600, 1700, 1800), fit.0.025quant = c(0.769658840175483,
1.2348464356932, 1.31495547349783, 0.719961329486957, 0.533187633507986,
0.41237993202555, 0.356857796236969, 0.451661489951035, 0.397337695624864,
0.409974403121542, 0.393472893048412, 0.287098718925738, 0.354883715712781,
0.448886474450658, 0.39715908146481, 0.410487903703693, 0.395658067652691,
0.29532573597963, 0.356128697768401, 0.424643741286452, 1.61932567884506,
2.30548827584594, 2.76268553213141, 3.56905828257608, 0.376482568951331,
0.48293037879362, 0.456032236653499, 0.576182569333386, 0.833933073149153,
0.505329284288562, 0.353803982641129, 0.447057201802133, 0.406336078139197,
0.412791109089504, 0.395171011307627, 0.288354069920872, 0.382168678659812,
0.482002569482113, 0.755468540519089, 1.42716100547254, 1.96250491461143,
2.10457588767127, 0.803868996482994, 1.37593437757613, 1.3439452894484,
0.813514702370298, 0.91011173357764, 1.63924488873152, 0.580362444026788,
0.934855991877621, 0.60121456278639, 0.534015226467462, 0.622887536264883,
0.904806796706558, 0.353024270759662, 0.438686138447152, 0.685977522753906,
1.17963934886268, 1.31914382834096, 1.28789209079277, 0.701713931393234,
1.26972914823194, 1.90066571401692, 2.07177013841196, 1.93618111824184,
0.992803246021283, 0.343613131108278, 0.430456920488114, 0.417579056383305,
0.418671290357986, 0.402050606863924, 0.308364224621195, 0.583328703310094,
0.918786031443329, 1.06376749897766, 0.655600082733113, 0.51395693234016,
0.426788514799349, 0.555489913959345, 0.876131197255411, 0.590736399231011,
0.48277052821614, 0.433796959718193, 0.329941536902841), fit.mean = c(1.44477219719004,
2.08847795346887, 1.92074020692043, 1.32464829710997, 1.13796066089688,
1.08343928221085, 1.0270630140043, 1.05528951308053, 0.976661348814801,
0.988745362827999, 0.987479550023199, 0.964770050345412, 1.02449122893449,
1.05015220962167, 0.976564469857287, 0.989147401032551, 0.989069619749544,
0.970583787671834, 1.03847015147848, 1.06933995625971, 2.22488490046604,
2.88660593193075, 3.36054537244564, 4.42004358638192, 1.04595162968831,
1.08727301417595, 1.03638887686208, 1.1536166718476, 1.50053537077068,
1.17627912933396, 1.02316037397786, 1.0471028064008, 0.984404683845781,
0.991111655212924, 0.988737905272986, 0.965489841545364, 1.05099802550399,
1.08432480769346, 1.37909032310489, 2.00490281582626, 2.63726205887859,
2.84334982786008, 1.48411988883991, 2.15479009506574, 1.9287002282937,
1.46182505532069, 1.56062168440954, 2.40992161620059, 1.25026845456292,
1.58672045233699, 1.18288150111622, 1.12016814013165, 1.21698183308848,
1.6429380910084, 1.02130662625173, 1.03621997583324, 1.27713301352629,
1.77365203410072, 1.92492063997973, 1.96675791602409, 1.39766534448098,
1.88778510186449, 2.58940980132166, 2.73324890368023, 2.56170704830504,
1.83047658204545, 1.01151006076038, 1.02327754863757, 0.99485311187439,
0.996288739427046, 0.994000331900843, 0.980075930151572, 1.25114729764489,
1.6084916455723, 1.6886774938227, 1.23925646732008, 1.11166030345131,
1.1008350449816, 1.22031434122474, 1.53062736345093, 1.16749661547219,
1.05991033202474, 1.02338313757614, 0.99829580992981), fit.0.975quant = c(2.1467629708395,
2.86800630410102, 2.51470712494928, 1.95333925576533, 1.77489115834241,
1.7877280165075, 1.70378532556924, 1.66231397117297, 1.55641371966469,
1.56640209717694, 1.57861036681008, 1.63693645172889, 1.69986070323533,
1.65430128162006, 1.55628507271894, 1.5667605188449, 1.5799958764014,
1.64125536079131, 1.73831613627608, 1.73284437119186, 2.8505231915393,
3.4683315348823, 3.97299977634175, 5.16994639025026, 1.7298419269412,
1.70332798100424, 1.62358809814901, 1.73503460780531, 2.1309767822864,
1.85862536193042, 1.69808462301802, 1.65012962012338, 1.5629733104483,
1.56855801488705, 1.57970304136785, 1.63743275050033, 1.74180211662632,
1.71027755854349, 2.03181318141753, 2.58279095091443, 3.26695568561824,
3.52780543298105, 2.20237329589412, 2.88519836047102, 2.50741694603542,
2.14275125784703, 2.2512588191423, 3.12462265816526, 1.94809249419394,
2.20744185138983, 1.77512404032398, 1.72085964175628, 1.82622191471393,
2.33007667194589, 1.69788616480218, 1.64224392607015, 1.88459325405899,
2.35615418597892, 2.51705325980683, 2.63321977331943, 2.14835481116668,
2.48812497421984, 3.23679202898418, 3.36053476964815, 3.16592918332051,
2.78811525721561, 1.68210669163609, 1.61753862767797, 1.57246161300083,
1.5735230229861, 1.58459708632124, 1.64902261852296, 1.93733077288017,
2.24895076383125, 2.29176328616676, 1.83579531693941, 1.73333081545444,
1.81135126517726, 1.90284502294021, 2.15299250030576, 1.74961986136672,
1.64322805676024, 1.61840886544359, 1.67093316282806)), class = "data.frame", row.names = c(NA,
-84L))
The question is the following:
For Italy in date ‘1000’, the values for ‘fit.0.025quant’, fit.mean’ and ‘fit.0.975quant’ are 0.7696588, 1.4447722 and 2.146763, respectively. The highest number that currently lies within their boundaries is 2. I want all three values to get lifted, so that they cover as entirely as possible the continuum between 2.01-2.99.
And so should happen for every row in a dataset. For example, for Italy in date ‘1200’, the values for ‘fit.0.025quant’, fit.mean’ and ‘fit.0.975quant’ are 1.2348464, 2.0884780 and 2.868006, respectively. The highest number that currently lies within their boundaries is 2. I want all three values to get lifted, so that they cover as entirely as possible the continuum between 2.01-2.99.
So it is like lifting ‘fit.0.025quant’, fit.mean’ and‘fit.0.975quant’ to the uppermost number that currently lies within their boundaries, so that all three cover as entirely as possible the continuum of that number.
Preferrably, the results should not be like '1.2', '1.4', 1.6', but be stretched over the continuum of 1, 1.01-1.99.
In my previous question, Zephryl suggested a code:
add <- apply(data[, 3:5], 1, \(x) floor(max(x))) - apply(data[, 3:5], 1, min)
add <- ifelse(add < -0.01, 0, add + 0.01)
data[, 3:5] <- data[, 3:5] + add
The excerpt from the results:
new.data <- structure(list(country = c("Italy", "Italy", "Italy"), date = structure(1:3, levels = c("1000",
"1200", "1500", "1600", "1700", "1800"), class = "factor"), democracy = c(1.34436508740733,
3.99, 2.99), fit.0.025quant = c(1.01, 3.01, 2.01), fit.mean = c(1.99,
3.48392980663905, 2.87661379777081), fit.0.975quant = c(2.1467629708395,
2.86800630410102, 2.51470712494928)), row.names = c(NA, 3L), class = "data.frame")
This leads to some values to weirdly get shrunk (like the values of Italy in 1200).
Guys, I hope my explanations are not confusing.
Do you think there can be a suitable code that automatically transforms the values in a whole dataset as expected?
Solution
We can start by finding the ratio between the top value we want to fit (fit.0.975quant) and the bottom value (fit.0.025quant). After scaling, we want these to be 0.98 apart (the distance from 0.01 to 0.99). So if the top number in one row were double the bottom one, we want to find what number would squeeze that 2.000 to 0.9800. The formula gives us 2.041 for that number -- and sure enough, 2.000 / 2.041 = 0.9800.
We can then use that coefficient for each row to rescale the distance from the 0.025quant to each mean and 0.975quant figure so they are at most 0.98 away. That's what we can add to where we've put the 0.025quant points (floored + 0.01).
library(dplyr)
data |>
mutate(scale = fit.0.975quant/fit.0.025quant / 0.98,
fit.0.025quant_scaled = floor(fit.0.975quant) + 0.01,
fit.mean_scaled = fit.0.025quant_scaled + fit.mean/fit.0.025quant/scale,
fit.0.975quant_scaled = fit.0.025quant_scaled + fit.0.975quant/fit.0.025quant/scale)
Result
country date fit.0.025quant fit.mean fit.0.975quant scale fit.0.025quant_scaled fit.mean_scaled fit.0.975quant_scaled
1 Italy 1000 0.7696588 1.4447722 2.146763 2.846163 2.01 2.669540 2.99
2 Italy 1200 1.2348464 2.0884780 2.868006 2.369960 2.01 2.723635 2.99
3 Italy 1500 1.3149555 1.9207402 2.514707 1.951418 2.01 2.758527 2.99
4 Italy 1600 0.7199613 1.3246483 1.953339 2.768487 1.01 1.674583 1.99
5 Italy 1700 0.5331876 1.1379607 1.774891 3.396766 1.01 1.638321 1.99
6 Italy 1800 0.4123799 1.0834393 1.787728 4.423620 1.01 1.603922 1.99
7 Austria 1000 0.3568578 1.0270630 1.703785 4.871847 1.01 1.600756 1.99
8 Austria 1200 0.4516615 1.0552895 1.662314 3.755553 1.01 1.632135 1.99
9 Austria 1500 0.3973377 0.9766613 1.556414 3.997047 1.01 1.624957 1.99
...
The chart below is a visual demonstration of how the input values are transformed into scaled values as specified in the OP.
- Calculate transition probabilities
- How to subset R dataframe based on specific values in several columns?
- Change colon to period in figure caption in Quarto
- Applying different functions to different groups using 2 data frames
- Disable button for 5 seconds with different label and change after it
- How to group columns with an odd index, based on columns with an even index?
- Different plots of marginal effects with interaction term and offset when using different packages
- Animate a MWE GIF in R
- populating a matrix by rows
- How to relevel in ROC curve in R/plotROC?
- R - glmer different results on different machines (non-deterministic)
- Dynamically operate on column (inside :=)
- Random change in obj_addr() output when including the objects into a list and vectorizing over them
- Add to curly-curly argument
- h2o predict error: Test/Validation dataset has no columns in common with the training set
- Is is possible to convert a dataframe object to a tribble constructor?
- encoding issues in R
- How to Scrape NBA stats page using rvest
- Conversion of Decimal Minutes Seconds into Decimal Degrees when cells within the Columns Contains the Factor 'Missed' in R
- Difficulty using case_when() to add column that, conditionally, pastes value from another column
- Flip the matrix
- Creating test for date columns incorrectly throwing error
- Exists() and getSymbols() can't find symbol listed in yahoo
- spearman's rho plot
- Use OpenMP on M2 Mac with R and data.table
- How to find the connected instances from a minimum spanning trees model in R
- Hide a column in shiny datatable but keep it searchable
- How to extract data from an edited datatable in shiny?
- Render Data Table with double header
- filter dataTables in Shiny Dashboad based on selectInput Values