Join Two Data Frames and Fill the NA with Data from the other Data frame

Mr Pool 0
Viewed 36

I have been trying to do the vlookup function in R, along with iferror. Like Iferror(vlookup(X,...)) which basically does is to check if there is any value in the other data frame with the same name and if so, it would show the output corresponding to that and in cases where there isnt, it would show any output which we can select.

Here I have two data frames, The first one is given below, named (columnheaderdf):

structure(list(V1 = c("XYS", "PYZ", "ABC", "Sales", "2022/38", 
"2022/39", "2022/40", "2022/41", "2022/42", "2022/43", "2022/44", 
"2022/45", "2022/46", "2022/47", "2022/48", "2022/49", "2022/50", 
"2022/51", "2022/52", "2022/53", "2022/54")), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -21L))

The Second one is given below named (timedf):

structure(list(`Week Format` = c("2021/01", "2021/02", "2021/03", 
"2021/04", "2021/05", "2021/06", "2021/07", "2021/08", "2021/09", 
"2021/10", "2021/11", "2021/12", "2021/13", "2021/14", "2021/15", 
"2021/16", "2021/17", "2021/18", "2021/19", "2021/20", "2021/21", 
"2021/22", "2021/23", "2021/24", "2021/25", "2021/26", "2021/27", 
"2021/28", "2021/29", "2021/30", "2021/31", "2021/32", "2021/33", 
"2021/34", "2021/35", "2021/36", "2021/37", "2021/38", "2021/39", 
"2021/40", "2021/41", "2021/42", "2021/43", "2021/44", "2021/45", 
"2021/46", "2021/47", "2021/48", "2021/49", "2021/50", "2021/51", 
"2021/52", "2022/01", "2022/02", "2022/03", "2022/04", "2022/05", 
"2022/06", "2022/07", "2022/08", "2022/09", "2022/10", "2022/11", 
"2022/12", "2022/13", "2022/14", "2022/15", "2022/16", "2022/17", 
"2022/18", "2022/19", "2022/20", "2022/21", "2022/22", "2022/23", 
"2022/24", "2022/25", "2022/26", "2022/27", "2022/28", "2022/29", 
"2022/30", "2022/31", "2022/32", "2022/33", "2022/34", "2022/35", 
"2022/36", "2022/37", "2022/38", "2022/39", "2022/40", "2022/41", 
"2022/42", "2022/43", "2022/44", "2022/45", "2022/46", "2022/47", 
"2022/48", "2022/49", "2022/50", "2022/51", "2022/52", "2023/01", 
"2023/02", "2023/03", "2023/04", "2023/05", "2023/06", "2023/07", 
"2023/08", "2023/09", "2023/10", "2023/11", "2023/12", "2023/13", 
"2023/14", "2023/15", "2023/16", "2023/17", "2023/18", "2023/19", 
"2023/20", "2023/21", "2023/22", "2023/23", "2023/24", "2023/25", 
"2023/26", "2023/27", "2023/28", "2023/29", "2023/30", "2023/31", 
"2023/32", "2023/33", "2023/34", "2023/35", "2023/36", "2023/37", 
"2023/38", "2023/39", "2023/40", "2023/41", "2023/42", "2023/43", 
"2023/44", "2023/45", "2023/46", "2023/47", "2023/48", "2023/49", 
"2023/50", "2023/51", "2023/52"), `Week number` = c("Week 01", 
"Week 02", "Week 03", "Week 04", "Week 05", "Week 06", "Week 07", 
"Week 08", "Week 09", "Week 10", "Week 11", "Week 12", "Week 13", 
"Week 14", "Week 15", "Week 16", "Week 17", "Week 18", "Week 19", 
"Week 20", "Week 21", "Week 22", "Week 23", "Week 24", "Week 25", 
"Week 26", "Week 27", "Week 28", "Week 29", "Week 30", "Week 31", 
"Week 32", "Week 33", "Week 34", "Week 35", "Week 36", "Week 37", 
"Week 38", "Week 39", "Week 40", "Week 41", "Week 42", "Week 43", 
"Week 44", "Week 45", "Week 46", "Week 47", "Week 48", "Week 49", 
"Week 50", "Week 51", "Week 52", "Week 01", "Week 02", "Week 03", 
"Week 04", "Week 05", "Week 06", "Week 07", "Week 08", "Week 09", 
"Week 10", "Week 11", "Week 12", "Week 13", "Week 14", "Week 15", 
"Week 16", "Week 17", "Week 18", "Week 19", "Week 20", "Week 21", 
"Week 22", "Week 23", "Week 24", "Week 25", "Week 26", "Week 27", 
"Week 28", "Week 29", "Week 30", "Week 31", "Week 32", "Week 33", 
"Week 34", "Week 35", "Week 36", "Week 37", "Week 38", "Week 39", 
"Week 40", "Week 41", "Week 42", "Week 43", "Week 44", "Week 45", 
"Week 46", "Week 47", "Week 48", "Week 49", "Week 50", "Week 51", 
"Week 52", "Week 01", "Week 02", "Week 03", "Week 04", "Week 05", 
"Week 06", "Week 07", "Week 08", "Week 09", "Week 10", "Week 11", 
"Week 12", "Week 13", "Week 14", "Week 15", "Week 16", "Week 17", 
"Week 18", "Week 19", "Week 20", "Week 21", "Week 22", "Week 23", 
"Week 24", "Week 25", "Week 26", "Week 27", "Week 28", "Week 29", 
"Week 30", "Week 31", "Week 32", "Week 33", "Week 34", "Week 35", 
"Week 36", "Week 37", "Week 38", "Week 39", "Week 40", "Week 41", 
"Week 42", "Week 43", "Week 44", "Week 45", "Week 46", "Week 47", 
"Week 48", "Week 49", "Week 50", "Week 51", "Week 52"), `From Date` = structure(c(1609718400, 
1610323200, 1610928000, 1611532800, 1612137600, 1612742400, 1613347200, 
1613952000, 1614556800, 1615161600, 1615766400, 1616371200, 1616976000, 
1617580800, 1618185600, 1618790400, 1619395200, 1.62e+09, 1620604800, 
1621209600, 1621814400, 1622419200, 1623024000, 1623628800, 1624233600, 
1624838400, 1625443200, 1626048000, 1626652800, 1627257600, 1627862400, 
1628467200, 1629072000, 1629676800, 1630281600, 1630886400, 1631491200, 
1632096000, 1632700800, 1633305600, 1633910400, 1634515200, 1635120000, 
1635724800, 1636329600, 1636934400, 1637539200, 1638144000, 1638748800, 
1639353600, 1639958400, 1640563200, 1641168000, 1641772800, 1642377600, 
1642982400, 1643587200, 1644192000, 1644796800, 1645401600, 1646006400, 
1646611200, 1647216000, 1647820800, 1648425600, 1649030400, 1649635200, 
1650240000, 1650844800, 1651449600, 1652054400, 1652659200, 1653264000, 
1653868800, 1654473600, 1655078400, 1655683200, 1656288000, 1656892800, 
1657497600, 1658102400, 1658707200, 1659312000, 1659916800, 1660521600, 
1661126400, 1661731200, 1662336000, 1662940800, 1663545600, 1664150400, 
1664755200, 1665360000, 1665964800, 1666569600, 1667174400, 1667779200, 
1668384000, 1668988800, 1669593600, 1670198400, 1670803200, 1671408000, 
1672012800, 1672617600, 1673222400, 1673827200, 1674432000, 1675036800, 
1675641600, 1676246400, 1676851200, 1677456000, 1678060800, 1678665600, 
1679270400, 1679875200, 1680480000, 1681084800, 1681689600, 1682294400, 
1682899200, 1683504000, 1684108800, 1684713600, 1685318400, 1685923200, 
1686528000, 1687132800, 1687737600, 1688342400, 1688947200, 1689552000, 
1690156800, 1690761600, 1691366400, 1691971200, 1692576000, 1693180800, 
1693785600, 1694390400, 1694995200, 1695600000, 1696204800, 1696809600, 
1697414400, 1698019200, 1698624000, 1699228800, 1699833600, 1700438400, 
1701043200, 1701648000, 1702252800, 1702857600, 1703462400), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), `To Date` = structure(c(1610236800, 
1610841600, 1611446400, 1612051200, 1612656000, 1613260800, 1613865600, 
1614470400, 1615075200, 1615680000, 1616284800, 1616889600, 1617494400, 
1618099200, 1618704000, 1619308800, 1619913600, 1620518400, 1621123200, 
1621728000, 1622332800, 1622937600, 1623542400, 1624147200, 1624752000, 
1625356800, 1625961600, 1626566400, 1627171200, 1627776000, 1628380800, 
1628985600, 1629590400, 1630195200, 1630800000, 1631404800, 1632009600, 
1632614400, 1633219200, 1633824000, 1634428800, 1635033600, 1635638400, 
1636243200, 1636848000, 1637452800, 1638057600, 1638662400, 1639267200, 
1639872000, 1640476800, 1641168000, 1641686400, 1642291200, 1642896000, 
1643500800, 1644105600, 1644710400, 1645315200, 1645920000, 1646524800, 
1647129600, 1647734400, 1648339200, 1648944000, 1649548800, 1650153600, 
1650758400, 1651363200, 1651968000, 1652572800, 1653177600, 1653782400, 
1654387200, 1654992000, 1655596800, 1656201600, 1656806400, 1657411200, 
1658016000, 1658620800, 1659225600, 1659830400, 1660435200, 1661040000, 
1661644800, 1662249600, 1662854400, 1663459200, 1664064000, 1664668800, 
1665273600, 1665878400, 1666483200, 1667088000, 1667692800, 1668297600, 
1668902400, 1669507200, 1670112000, 1670716800, 1671321600, 1671926400, 
1672531200, 1673136000, 1673740800, 1674345600, 1674950400, 1675555200, 
1676160000, 1676764800, 1677369600, 1677974400, 1678579200, 1679184000, 
1679788800, 1680393600, 1680998400, 1681603200, 1682208000, 1682812800, 
1683417600, 1684022400, 1684627200, 1685232000, 1685836800, 1686441600, 
1687046400, 1687651200, 1688256000, 1688860800, 1689465600, 1690070400, 
1690675200, 1691280000, 1691884800, 1692489600, 1693094400, 1693699200, 
1694304000, 1694908800, 1695513600, 1696118400, 1696723200, 1697328000, 
1697932800, 1698537600, 1699142400, 1699747200, 1700352000, 1700956800, 
1701561600, 1702166400, 1702771200, 1703376000, 1703980800), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), `Month Date` = c("2021-01-01", "2021-01-01", 
"2021-01-01", "2021-01-01", "2021-02-01", "2021-02-01", "2021-02-01", 
"2021-02-01", "2021-03-01", "2021-03-01", "2021-03-01", "2021-03-01", 
"2021-03-01", "2021-04-01", "2021-04-01", "2021-04-01", "2021-04-01", 
"2021-05-01", "2021-05-01", "2021-05-01", "2021-05-01", "2021-05-01", 
"2021-06-01", "2021-06-01", "2021-06-01", "2021-06-01", "2021-07-01", 
"2021-07-01", "2021-07-01", "2021-07-01", "2021-08-01", "2021-08-01", 
"2021-08-01", "2021-08-01", "2021-08-01", "2021-09-01", "2021-09-01", 
"2021-09-01", "2021-09-01", "44470", "44470", "44470", "44470", 
"44501", "44501", "44501", "44501", "44501", "44531", "44531", 
"44531", "44531", "2022-01-01", "2022-01-01", "2022-01-01", "2022-01-01", 
"2022-01-01", "2022-02-01", "2022-02-01", "2022-02-01", "2022-02-01", 
"2022-03-01", "2022-03-01", "2022-03-01", "2022-03-01", "2022-04-01", 
"2022-04-01", "2022-04-01", "2022-04-01", "2022-05-01", "2022-05-01", 
"2022-05-01", "2022-05-01", "2022-05-01", "2022-06-01", "2022-06-01", 
"2022-06-01", "2022-06-01", "2022-07-01", "2022-07-01", "2022-07-01", 
"2022-07-01", "2022-08-01", "2022-08-01", "2022-08-01", "2022-08-01", 
"2022-08-01", "2022-09-01", "2022-09-01", "2022-09-01", "2022-09-01", 
"2022-10-01", "2022-10-01", "2022-10-01", "2022-10-01", "2022-10-01", 
"2022-11-01", "2022-11-01", "2022-11-01", "2022-11-01", "2022-12-01", 
"2022-12-01", "2022-12-01", "2022-12-01", "2023-01-01", "2023-01-01", 
"2023-01-01", "2023-01-01", "2023-01-01", "2023-02-01", "2023-02-01", 
"2023-02-01", "2023-02-01", "2023-03-01", "2023-03-01", "2023-03-01", 
"2023-03-01", "2023-04-01", "2023-04-01", "2023-04-01", "2023-04-01", 
"2023-05-01", "2023-05-01", "2023-05-01", "2023-05-01", "2023-05-01", 
"2023-06-01", "2023-06-01", "2023-06-01", "2023-06-01", "2023-07-01", 
"2023-07-01", "2023-07-01", "2023-07-01", "2023-07-01", "2023-08-01", 
"2023-08-01", "2023-08-01", "2023-08-01", "2023-09-01", "2023-09-01", 
"2023-09-01", "2023-09-01", "2023-10-01", "2023-10-01", "2023-10-01", 
"2023-10-01", "2023-10-01", "2023-11-01", "2023-11-01", "2023-11-01", 
"2023-11-01", "2023-12-01", "2023-12-01", "2023-12-01", "2023-12-01"
)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-156L))

The expected final output is the highlighted column "Final Output":

enter image description here

I tired to do this by using a left join but I am not getting the left join itself,

df2 = left_join(columnheaderdf,timedf,by=c('V1','Week Format'))

The error message is show below:

enter image description here

Can someone let me know what is the issue here or any other way to get the desired output.

left-join join dataframe r
2 Answers

Combining what our friend suggested here and based on your expectation,

library(dplyr)
newdf <- left_join(columnheaderdf, timedf, by = c('V1' = 'Week Format'))
#Need to convert this into character as dates are tricky to deal with
newdf$`From Date` <- as.character(newdf$`From Date`)
#Assigning the NA columns with the data from the column V1
newdf$`From Date`[is.na(newdf$`From Date`)] <- newdf$V1

Hope this helps, at least it will give you the idea of correct execution

user200687610

You might be using the wrong syntax here. Using your

df2 = left_join(columnheaderdf,timedf,by=c('V1','Week Format'))

reproduces your error. When you use this

df2 = left_join(columnheaderdf, timedf, by = c('V1' = 'Week Format'))

instead, the join works.

Watch out, that when you want to join on two columns that have different names you must give an indication that both are meant to be the same column. Thus the c('V1' = 'Week Format').

userMgIl10
Related