I have a data.frame that contains several character columns but also a data.frame. Thus, I have a data.frame inside of my data.frame. My goal is to subset one character column together with a column that is inside the nested data.frame. But, whenever I try to subset the nested column by name, it states it doesnt exist. You can see the data.frame here:
df = structure(
list(
`$id` = c("21", "22", "23"),
Id = c("159347",
"161863", "22646"),
Name = c("159347", "161863", "22646"),
SumPeriod = structure(
list(
AccPeriodBasTwrAtMarketPrice = c(0.0969367972082358, 0.537983489472227,-0.107066381156318),
AccPeriodLocTwrAtMarketPrice = c(0.0969367972082358,
0.537983489472227,-0.107066381156318),
BopDate = c(
"2022-02-28T00:00:00",
"2022-02-28T00:00:00",
"2022-02-28T00:00:00"
),
BopBasHoldingValueAtMarketPrice = c(7592266.52,
5135960.59, 7166815.5),
BopBasInterestAccrual = c(0, 0, 0),
EopDate = c(
"2022-02-28T00:00:00",
"2022-02-28T00:00:00",
"2022-02-28T00:00:00"
),
EopBasHoldingValueAtMarketPrice = c(7599626.22,
5163591.21, 7159142.25),
EopBasInterestAccrual = c(0, 0,
0),
AccPeriodBasTwrAtExposureValue = c(0.0969367972082358,
0.537983489472227,-0.107066381156318),
AccPeriodLocTwrAtExposureValue = c(0.0969367972082358,
0.537983489472227,-0.107066381156318),
AccBasIrr = c(0.0969367972082358,
0.537983489472227,-0.107066381156318),
AccLocIrr = c(0.096936797208258,
0.537983489472227,-0.107066381156318),
AccBasMwr = c(0.0484449181280957,
0.268270120259021,-0.0535618639528656),
PeriodBasIrr = c(0.0969367972082358,
0.537983489472227,-0.107066381156318),
PeriodLocIrr = c(0.096936797208258,
0.537983489472227,-0.107066381156318),
PeriodBasTwrAtMarketPrice = c(0.0969367972082358,
0.537983489472227,-0.107066381156318),
PeriodLocTwrAtMarketPrice = c(0.0969367972082358,
0.537983489472227,-0.107066381156318),
PeriodBasTwrDeposit = c(0,
0, 0),
PeriodBasTwrWithdrawal = c(0, 0, 0),
PeriodBasTwrDepositWithdrawal = c(0,
0, 0),
PeriodBasTwrDividendTax = c(0, 0, 0),
PeriodBasTwr = c(7359.70000000112,
27630.6200000001,-7673.25),
PeriodBasMwr = c(0.0484449181280957,
0.268270120259021,-0.0535618639528656),
BenchmarkCalcType = c(
"BenchmarkNotCalculated",
"BenchmarkNotCalculated",
"BenchmarkNotCalculated"
),
EopBenchmarkName = c("",
"", ""),
AccBasBenchmarkReturnPct = c(0, 0, 0),
PeriodBasBenchmarkReturnPct = c(0,
0, 0)
),
class = "data.frame",
row.names = c(NA, 3L)
),
Series = list(
structure(
list(
AccPeriodBasTwrAtMarketPrice = 0.0969367972082358,
AccPeriodLocTwrAtMarketPrice = 0.0969367972082358,
BopDate = "2022-02-28T00:00:00",
BopBasHoldingValueAtMarketPrice = 7592266.52,
BopBasInterestAccrual = 0,
EopDate = "2022-02-28T00:00:00",
EopBasHoldingValueAtMarketPrice = 7599626.22,
EopBasInterestAccrual = 0,
AccPeriodBasTwrAtExposureValue = 0.0969367972082358,
AccPeriodLocTwrAtExposureValue = 0.0969367972082358,
AccBasIrr = 0,
AccLocIrr = 0,
AccBasMwr = 0.0968429207825055,
PeriodBasIrr = 0,
PeriodLocIrr = 0,
PeriodBasTwrAtMarketPrice = 0.0969367972082358,
PeriodLocTwrAtMarketPrice = 0.0969367972082358,
PeriodBasTwrDeposit = 0,
PeriodBasTwrWithdrawal = 0,
PeriodBasTwrDepositWithdrawal = 0,
PeriodBasTwrDividendTax = 0,
PeriodBasTwr = 7359.70000000112,
PeriodBasMwr = 0.0484449181280957,
BenchmarkCalcType = "BenchmarkNotCalculated",
EopBenchmarkName = "",
AccBasBenchmarkReturnPct = 0,
PeriodBasBenchmarkReturnPct = 0
),
class = "data.frame",
row.names = 1L
),
structure(
list(
AccPeriodBasTwrAtMarketPrice = 0.537983489472227,
AccPeriodLocTwrAtMarketPrice = 0.537983489472227,
BopDate = "2022-02-28T00:00:00",
BopBasHoldingValueAtMarketPrice = 5135960.59,
BopBasInterestAccrual = 0,
EopDate = "2022-02-28T00:00:00",
EopBasHoldingValueAtMarketPrice = 5163591.21,
EopBasInterestAccrual = 0,
AccPeriodBasTwrAtExposureValue = 0.537983489472227,
AccPeriodLocTwrAtExposureValue = 0.537983489472227,
AccBasIrr = 0,
AccLocIrr = 0,
AccBasMwr = 0.535104714457055,
PeriodBasIrr = 0,
PeriodLocIrr = 0,
PeriodBasTwrAtMarketPrice = 0.537983489472227,
PeriodLocTwrAtMarketPrice = 0.537983489472227,
PeriodBasTwrDeposit = 0,
PeriodBasTwrWithdrawal = 0,
PeriodBasTwrDepositWithdrawal = 0,
PeriodBasTwrDividendTax = 0,
PeriodBasTwr = 27630.6200000001,
PeriodBasMwr = 0.26827012025902,
BenchmarkCalcType = "BenchmarkNotCalculated",
EopBenchmarkName = "",
AccBasBenchmarkReturnPct = 0,
PeriodBasBenchmarkReturnPct = 0
),
class = "data.frame",
row.names = 1L
),
structure(
list(
AccPeriodBasTwrAtMarketPrice = -0.107066381156318,
AccPeriodLocTwrAtMarketPrice = -0.107066381156318,
BopDate = "2022-02-28T00:00:00",
BopBasHoldingValueAtMarketPrice = 7166815.5,
BopBasInterestAccrual = 0,
EopDate = "2022-02-28T00:00:00",
EopBasHoldingValueAtMarketPrice = 7159142.25,
EopBasInterestAccrual = 0,
AccPeriodBasTwrAtExposureValue = -0.107066381156318,
AccPeriodLocTwrAtExposureValue = -0.107066381156318,
AccBasIrr = 0,
AccLocIrr = 0,
AccBasMwr = -0.107181136120043,
PeriodBasIrr = 0,
PeriodLocIrr = 0,
PeriodBasTwrAtMarketPrice = -0.107066381156318,
PeriodLocTwrAtMarketPrice = -0.107066381156318,
PeriodBasTwrDeposit = 0,
PeriodBasTwrWithdrawal = 0,
PeriodBasTwrDepositWithdrawal = 0,
PeriodBasTwrDividendTax = 0,
PeriodBasTwr = -7673.25,
PeriodBasMwr = -0.0535618639528656,
BenchmarkCalcType = "BenchmarkNotCalculated",
EopBenchmarkName = "",
AccBasBenchmarkReturnPct = 0,
PeriodBasBenchmarkReturnPct = 0
),
class = "data.frame",
row.names = 1L
)
)
),
class = "data.frame",
row.names = c(NA,
3L)
)
So in the data.frame above, the only columns I am interested in having is "Id" and "EopBasHoldingValueAtMarketPrice" where the latter is a column inside the nested data.frame called "SumPeriod".I try to achieve this by:
df_subset = subset(df, select = c("Id", "SumPeriod$EopBasHoldingValueAtMarketPrice"))
But I get the error:
frame`(x, r, vars, drop = drop) : undefined columns selected
Have you folks any idea how to achieve this?
CodePudding user response：
SumPeriod is a dataframe within your dataframe. So you are suggesting dplyr in your tags, you can solve it with this approach:
library(dplyr)
library(tidyr)
df %>%
as_tibble() %>%
unnest(SumPeriod) %>%
select(Id, EopBasHoldingValueAtMarketPrice)
Output is:
# A tibble: 3 × 2
Id EopBasHoldingValueAtMarketPrice
<chr> <dbl>
1 159347 7599626.
2 161863 5163591.
3 22646 7159142.
CodePudding user response：
You don't have anything named "SumPeriod$EopBasHoldingValueAtMarketPrice", that's an expression to extract a column from SumPeriod. @Stephan gave you a dplyr solution; here's a base R solution:
df1 <- subset(df, select = c("Id", "SumPeriod"))
df1$SumPeriod <- subset(df1$SumPeriod, select = "EopBasHoldingValueAtMarketPrice")
This keeps the structure as a nested dataframe.
CodePudding user response：
data.table approach
library(data.table)
DT <- as.data.table(df)
DT[, .(Id,
EopBasHoldingValueAtMarketPrice = lapply(Series, function(x) x$EopBasHoldingValueAtMarketPrice))]
# Id EopBasHoldingValueAtMarketPrice
# 1: 159347 7599626
# 2: 161863 5163591
# 3: 22646 7159142
CodePudding user response：
If you feel comfortable with dplyr, then use Stephan's solution. If you need a base solution, you can just you assign the needed variable from the nested frame to the parent frame, then use subset():
library(tidyverse)
df["EopBasHoldingValueAtMarketPrice"] <- df$SumPeriod$EopBasHoldingValueAtMarketPrice
df_subset <- subset(df, select = c("Id", "EopBasHoldingValueAtMarketPrice"))
df_subset
#> Id EopBasHoldingValueAtMarketPrice
#> 1 159347 7599626
#> 2 161863 5163591
#> 3 22646 7159142
Created on 2022-03-28 by the reprex package (v2.0.1)
CodePudding user response：
Is there a reason for a data frame inside the data frame? You could have this stuff all in a simple data frame
x <- df$SumPeriod
y <- df$Serie
y <- rbind(y[[1]],y[[2]],y[[3]])
df <- cbind(df[,c("Id","Name","$id")],x,y)
Then, your suggested subset works
df_subset = subset(df, select = c("Id", "EopBasHoldingValueAtMarketPrice"))