Giovanni Pignatelli
Reputation: 111
Combining rows from 3 dataframes with same rownames in R
I have three different dataframes in R with values for specific assets at determined dates in time. I need to multiply each value from the dataframe by a specific multiplier and then combine (add) the three values into one final dataframe.
This is what I have so far:
First Dataframe (df1)
structure(list(
`CRB IM Equity` = c(0, 0, 0),
`US10 IM Equity` = c(0.25, 0.25, 0.2),
`SYBM GY Equity` = c(0, 0, 0),
`LEM FP Equity` = c(0, 0, 0),
`IUSE LN Equity` = c(0, 0, 0),
`LGCPTREH Index` = c(0.25, 0, 0),
`IJPE LN Equity` = c(0, 0, 0),
`XEIN GY Equity` = c(0, 0, 0),
`IQQR GY Equity` = c(0, 0, 0),
`LTI1TREH Index` = c(0.25, 0, 0),
`XAD1 IM Equity` = c(0, 0, 0),
`IMEU NA Equity` = c(0, 0, 0),
`IHYG LN Equity` = c(0, 0, 0),
`IPRP NA Equity` = c(0, 0, 0),
`JPEIHDEU Index` = c(0, 0, 0.2),
`IBTM IM Equity` = c(0.25, 0.25, 0.2),
`EUNR GY Equity` = c(0, 0, 0),
`MWO FP Equity` = c(0, 0.25, 0.2),
`LTM FP Equity` = c(0, 0.25, 0.2),
`XGLE GY Equity` = c(0, 0, 0),
RiskFree = c(0, 0, 0)),
.Names = c("CRB IM Equity", "US10 IM Equity", "SYBM GY Equity", "LEM FP Equity", "IUSE LN Equity", "LGCPTREH Index", "IJPE LN Equity", "XEIN GY Equity", "IQQR GY Equity", "LTI1TREH Index", "XAD1 IM Equity", "IMEU NA Equity", "IHYG LN Equity", "IPRP NA Equity", "JPEIHDEU Index", "IBTM IM Equity", "EUNR GY Equity", "MWO FP Equity", "LTM FP Equity", "XGLE GY Equity", "RiskFree"
),
row.names = c("2019-03-29", "2019-04-30", "2019-05-31"),
class = "data.frame")
Second Dataframe (df2):
structure(list(
`US10 IM Equity` = c(0, 0, 0),
`SYBM GY Equity` = c(0.00613496932515337, 0, 0.0109983795832177),
`CRB IM Equity` = c(0.0081799591002045, 0.0327510917030568, 0),
`IUSE LN Equity` = c(0, 0, 0.00219967591664355),
`LEM FP Equity` = c(0, 0, 0),
`LGCPTREH Index` = c(0.6032719, 0.58296943231441, 0.406940044579056),
`IQQR GY Equity` = c(0, 0.00655021834061135, 0.0131980554998613),
`XEIN GY Equity` = c(0, 0, 0.103384768082247),
`IJPE LN Equity` = c(0.0061349693, 0.00873362445414847, 0),
`LTI1TREH Index` = c(0.0061349693, 0.02838427947, 0.002199675916), `IMEU NA Equity` = c(0, 0, 0.00879870366657419),
`XAD1 IM Equity` = c(0.00204498977505112, 0.00873362445414847, 0), `IHYG LN Equity` = c(0.3476482617, 0.266375545, 0.008798703666),
`IPRP NA Equity` = c(0.0102249488, 0.02620087336, 0),
`JPEIHDEU Index` = c(0.0102249488, 0.0283842, 0.267507919757698),
`IBTM IM Equity` = c(0, 0, 0.0043993518332871),
`EUNR GY Equity` = c(0, 0, 0.151777638248405),
`MWO FP Equity` = c(0, 0.0087336244, 0.0131980),
`LTM FP Equity` = c(0, 0, 0),
`XGLE GY Equity` = c(0, 0.00218340611353712, 0.00659902774993064), RiskFree = c(0, 0, 1.11022302462516e-16)),
.Names = c("US10 IM Equity", "SYBM GY Equity", "CRB IM Equity",
"IUSE LN Equity", "LEM FP Equity", "LGCPTREH Index", "IQQR GY Equity",
"XEIN GY Equity", "IJPE LN Equity", "LTI1TREH Index", "IMEU NA Equity",
"XAD1 IM Equity", "IHYG LN Equity", "IPRP NA Equity", "JPEIHDEU Index",
"IBTM IM Equity", "EUNR GY Equity", "MWO FP Equity", "LTM FP Equity",
"XGLE GY Equity", "RiskFree"),
row.names = c("2019-03-29", "2019-04-30", "2019-05-31"),
class = "data.frame")
Third Dataframe (df3):
structure(list(
`US10 IM Equity` = c(0.0449534, 0.0485797, 0.042739),
`SYBM GY Equity` = c(0.033885, 0.031267, 0.057019316),
`CRB IM Equity` = c(0.02968644, 0.02332370, 0.0214887),
`IUSE LN Equity` = c(0.02201, 0.0318220, 0.0163260),
`LEM FP Equity` = c(0.013045, 0.016096, 0.0209666),
`LGCPTREH Index` = c(0.08580, 0.088966, 0.1830580),
`IQQR GY Equity` = c(0.026100, 0.024201, 0.015489),
`XEIN GY Equity` = c(0.0821468, 0.094291, 0.075394),
`IJPE LN Equity` = c(0.036475, 0.039088, 0.032539),
`LTI1TREH Index` = c(0.124383, 0.134396, 0.071760),
`IMEU NA Equity` = c(0.03881, 0.03608789, 0.017015),
`XAD1 IM Equity` = c(0.0569415, 0.012407, 0.0396417),
`IHYG LN Equity` = c(0.021150, 0.0800146, 0.0043337),
`IPRP NA Equity` = c(0.033825, 0.0305668, 0.0410015),
`JPEIHDEU Index` = c(0.0619405, 0.0954634, 0.0755022),
`IBTM IM Equity` = c(0.066880, 0.0417783, 0.089807),
`EUNR GY Equity` = c(0.085595, 0.034687, 0.104800),
`MWO FP Equity` = c(0.03117, 0.0225397, 0.0294995),
`LTM FP Equity` = c(0.0077293, 0.01021646, 0.016242),
`XGLE GY Equity` = c(0.0974537, 0.1042031, 0.0453708),
RiskFree = c(0, 0, 1.11022302462516e-16)),
.Names = c("US10 IM Equity", "SYBM GY Equity", "CRB IM Equity",
"IUSE LN Equity", "LEM FP Equity", "LGCPTREH Index", "IQQR GY Equity",
"XEIN GY Equity", "IJPE LN Equity", "LTI1TREH Index", "IMEU NA Equity",
"XAD1 IM Equity", "IHYG LN Equity", "IPRP NA Equity", "JPEIHDEU Index",
"IBTM IM Equity", "EUNR GY Equity", "MWO FP Equity", "LTM FP Equity",
"XGLE GY Equity", "RiskFree"),
row.names = c("2019-03-29", "2019-04-30", "2019-05-31"),
class = "data.frame")
The final dataframe should contain cells with the same rownames and column names and each cell should equate to:
(df1 * 0.5) + (df2 * 0.25) + (df3 * 0.25)
The first problem I have is that the columns aren't in the same order, and secondly, I don't know how to combine values from a dataframe multplied by a factor into a final dataframe
Upvotes: 0
Views: 69
Answers (3)
s__
Reputation: 9485
You can also order the various data.frames by column, then apply your formula:
# to be sure, you can order also rows:
df1 <- df1[order(row.names(df1)),order(names(df1))]
df2 <- df2[order(row.names(df2)),order(names(df2))]
df3 <- df3[order(row.names(df3)),order(names(df3))]
df1*0.5 + df2*0.25 + df3*0.25
CRB IM Equity EUNR GY Equity IBTM IM Equity IHYG LN Equity IJPE LN Equity IMEU NA Equity IPRP NA Equity IQQR GY Equity
2019-03-29 0.009466600 0.02139875 0.1417200 0.092199565 0.01065249 0.009702500 0.01101249 0.006525000
2019-04-30 0.014018698 0.00867175 0.1354446 0.086597536 0.01195541 0.009021972 0.01419192 0.007687805
2019-05-31 0.005372175 0.06414441 0.1235516 0.003283101 0.00813475 0.006453426 0.01025038 0.007171764
IUSE LN Equity JPEIHDEU Index LEM FP Equity LGCPTREH Index LTI1TREH Index LTM FP Equity MWO FP Equity RiskFree SYBM GY Equity
2019-03-29 0.005502500 0.01804136 0.00326125 0.2972680 0.15762949 0.001932325 0.0077925 0.000000e+00 0.01000499
2019-04-30 0.007955500 0.03096190 0.00402400 0.1679839 0.04069507 0.127554115 0.1328183 0.000000e+00 0.00781675
2019-05-31 0.004631419 0.18575253 0.00524165 0.1474995 0.01848992 0.104060500 0.1106744 5.551115e-17 0.01700442
US10 IM Equity XAD1 IM Equity XEIN GY Equity XGLE GY Equity
2019-03-29 0.1362384 0.014746622 0.02053670 0.02436343
2019-04-30 0.1371449 0.005285156 0.02357275 0.02659663
2019-05-31 0.1106847 0.009910425 0.04469469 0.01299246
Clearly this very easy, but is going to work if rownames() and colnames() in the three data.frames are the same (as stated in the question). You can easily check it with setdiff() function.
Upvotes: 1
heck1
Reputation: 726
So one approach with base r and dplyr would be manipulating the dataframes individually and then add date as a grouping variable for dplyr::group_by().
In general I would suggest using date as a column instead of rowname if you are interested in graphing over time etc.
df1 <- df1*0.5
df1$date <- rownames(df1)
df2 <- df2*0.25
df2$date <- rownames(df2)
df3 <- df3*0.25
df3$date <- rownames(df3)
df_fin <- merge(df1,df2,all=T)
df_fin <- merge(df_fin,df3,all = T)
library(dplyr)
df_fin <- df_fin %>% group_by(date) %>% summarise_all(list(sum=sum) )
> df_fin
# A tibble: 3 x 22
date `CRB IM Equity_~ `US10 IM Equity~ `SYBM GY Equity~ `LEM FP Equity_~ `IUSE LN Equity~ `LGCPTREH Index~ `IJPE LN Equity~
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 2019~ 0.00947 0.136 0.0100 0.00326 0.00550 0.297 0.0107
2 2019~ 0.0140 0.137 0.00782 0.00402 0.00796 0.168 0.0120
3 2019~ 0.00537 0.111 0.0170 0.00524 0.00463 0.147 0.00813
# ... with 14 more variables: `XEIN GY Equity_sum` <dbl>, `IQQR GY Equity_sum` <dbl>, `LTI1TREH Index_sum` <dbl>, `XAD1 IM
# Equity_sum` <dbl>, `IMEU NA Equity_sum` <dbl>, `IHYG LN Equity_sum` <dbl>, `IPRP NA Equity_sum` <dbl>, `JPEIHDEU Index_sum` <dbl>,
# `IBTM IM Equity_sum` <dbl>, `EUNR GY Equity_sum` <dbl>, `MWO FP Equity_sum` <dbl>, `LTM FP Equity_sum` <dbl>, `XGLE GY
# Equity_sum` <dbl>, RiskFree_sum <dbl>
>
Upvotes: 0
user2974951
Reputation: 10375
How about this
> rws=intersect(intersect(rownames(df1),rownames(df2)),rownames(df3))
> cls=intersect(intersect(colnames(df1),colnames(df2)),colnames(df3))
> df1[rws,cls]*0.5+df2[rws,cls]*0.25+df3[rws,cls]*0.25
CRB IM Equity US10 IM Equity SYBM GY Equity LEM FP Equity
2019-03-29 0.009466600 0.1362384 0.01000499 0.00326125
2019-04-30 0.014018698 0.1371449 0.00781675 0.00402400
2019-05-31 0.005372175 0.1106847 0.01700442 0.00524165
IUSE LN Equity LGCPTREH Index IJPE LN Equity XEIN GY Equity
2019-03-29 0.005502500 0.2972680 0.01065249 0.02053670
2019-04-30 0.007955500 0.1679839 0.01195541 0.02357275
2019-05-31 0.004631419 0.1474995 0.00813475 0.04469469
IQQR GY Equity LTI1TREH Index XAD1 IM Equity IMEU NA Equity
2019-03-29 0.006525000 0.15762949 0.014746622 0.009702500
2019-04-30 0.007687805 0.04069507 0.005285156 0.009021972
2019-05-31 0.007171764 0.01848992 0.009910425 0.006453426
IHYG LN Equity IPRP NA Equity JPEIHDEU Index IBTM IM Equity
2019-03-29 0.092199565 0.01101249 0.01804136 0.1417200
2019-04-30 0.086597536 0.01419192 0.03096190 0.1354446
2019-05-31 0.003283101 0.01025038 0.18575253 0.1235516
EUNR GY Equity MWO FP Equity LTM FP Equity XGLE GY Equity
2019-03-29 0.02139875 0.0077925 0.001932325 0.02436343
2019-04-30 0.00867175 0.1328183 0.127554115 0.02659663
2019-05-31 0.06414441 0.1106744 0.104060500 0.01299246
RiskFree
2019-03-29 0.000000e+00
2019-04-30 0.000000e+00
2019-05-31 5.551115e-17
Upvotes: 1
Related Questions
- Merge or combine by rownames
- merge 3 data.frames by column names
- Combine dataframes only by mutual rownames
- Combining multiple Dataframes with different row names
- Combining columns one by one from three dataframes
- Merge contents from three dataframes into one column in R (across multiple columns)
- Merge contents from three dataframes into one column
- How to combine several dataframes with different rows using R?
- R- merge 3 or more dataframes
- Combine data.frames in R using only common row.names