apply custom function to a pandas dataframe values

Question

I have a dataframe of two columns. Column 1 is a loan number, column 2 are tuples with dates and cashflows. I need to create 3rd column with npv, applying my custom function that I created, unfortunately when I want to apply to all loans , python shows an error, but when I slice a tuple of one loan , the function works. I can't understand how to apply to all set of loans, as there dozen of loans and i can't do it manually for each loan. Here is my dataset and code:

list1=df1['date'].tolist()
list2=df1['Inv_Total_CF'].tolist()
dates=[datetime.strptime(x,'%m/%d/%Y') for x in list1]
s=pd.Series(tuple(zip(dates, list2)), index =df.index)
df2=s.groupby(df1['loan_number']).apply(tuple).reset_index(name='cashflows')
print(df2)
loan_number        cashflows
1            ((2021-06-01 00:00:00, -177638.21405903128), (...
2            ((2021-07-15 00:00:00, 1499.224024), (2021-08-...
...

df2['XNPV']=df2.apply(lambda x: xnpv(0.12,df2['cashflows']))
n<ipython-input-87-df310ee10859> in <lambda>(x)
  6 df2=df2.iloc[1:]
  7 
  ----> 8 df2['XNPV']=df2.apply(lambda x: xnpv(0.12,df2['cashflows']))
  9 #cf=df2['cashflows'].iloc[1]
 10 #cf

 <ipython-input-36-e09ab95d8640> in xnpv(rate, cashflows)
 22     chron_order = sorted(cashflows, key = lambda x: x[0])
 23     t0 = chron_order[0][0] #t0 is the date of the first cash flow
 ---> 24     return sum([cf/(1+rate)**((t-t0).days/365.0) for (t,cf) in chron_order])
 25 
 26 def xirr(cashflows,guess=0.1):

 <ipython-input-36-e09ab95d8640> in <listcomp>(.0)
 22     chron_order = sorted(cashflows, key = lambda x: x[0])
 23     t0 = chron_order[0][0] #t0 is the date of the first cash flow
 ---> 24     return sum([cf/(1+rate)**((t-t0).days/365.0) for (t,cf) in chron_order])
 25 
 26 def xirr(cashflows,guess=0.1):

 ValueError: too many values to unpack (expected 2)

However, If I slice it and apply xnpv function to one loan it wroks:

cf=df2['cashflows'].iloc[1]
print(xnpv(0.12,cf))
42983.47762946672

And here is my XNPV custom function:

def xnpv(rate,cashflows):
chron_order = sorted(cashflows, key = lambda x: x[0])
t0 = chron_order[0][0] #t0 is the date of the first cash flow
return sum([cf/(1+rate)**((t-t0).days/365.0) for (t,cf) in chron_order])

I would appreciate any help! The dataframe Look like this: {"1":[[1622505600000,-177638.2140590313],[1626307200000,3085.658271],[1628985600000,2556.15292],[1631664000000,7213.691688],[1634256000000,8529.293764],[1636934400000,9130.169221],[1639526400000,8155.381948],[1642204800000,11054.53224],[1644883200000,9578.962366],[1647302400000,9114.508608],[1649980800000,7863.10379],[1652572800000,6757.673243],[1655251200000,8700.934134],[1657843200000,12300.05361],[1660521600000,12912.04123],[1663200000000,12753.9491],[1665792000000,11423.29833],[1668470400000,9463.180073],[1671062400000,7748.722828],[1673740800000,6286.920039],[1676419200000,5001.63329],[1678838400000,3992.725525],[1681516800000,3029.329451],[1684108800000,2263.115023],[1686787200000,1627.195315],[1689379200000,868.400985],[1692057600000,643.826055],[1694736000000,474.8510724],[1697328000000,348.6340221],[1700006400000,254.9402172],[1702598400000,185.7620742],[1705276800000,134.9239515],[1707955200000,97.71764749],[1710460800000,70.58748469],[1713139200000,50.86929652],[1715731200000,36.58021428],[1718409600000,26.25289783],[1721001600000,18.80690661],[1723680000000,13.45015537],[1726358400000,9.604195486],[1728950400000,6.848044843],[1731628800000,4.876264821],[1734220800000,3.467862765],[1736899200000,2.46334669],[1739577600000,1.747873444],[1741996800000,1.238922499],[1744675200000,0.877312576],[1747267200000,0.620675664],[1749945600000,0.438729977],[1752537600000,0.309864925],[1755216000000,0.218679833],[1757894400000,0.154213894],[1760486400000,0.10867576],[1763164800000,0.07653341],[1765756800000,0.053863227],[1768435200000,0.037885183],[1771113600000,0.026631401],[1773532800000,0.018710153],[1776211200000,0.013138016],[1778803200000,0.00922064],[1781481600000,0.006468153],[1784073600000,0.004535195],[1786752000000,0.003178456],[1789430400000,0.002226633],[1792022400000,0.001559194],[1794700800000,0.001091384],[1797292800000,0.000763638],[1799971200000,0.000534117],[1802649600000,0.000373447],[1805068800000,0.000261018],[1807747200000,0.000182375],[1810339200000,0.000127386],[1813017600000,0.0000889491],[1815609600000,0.0000620912],[1818288000000,0.0000433302],[1820966400000,0.0000302293],[1823558400000,0.0000210836],[1826236800000,0.000014701],[1828828800000,0.0000102479],[1831507200000,0.0000071419],[1834185600000,0.0000049761],[1836691200000,0.0000034662],[1839369600000,0.000002414],[1841961600000,0.0000016808],[1844640000000,0.00000117],[1847232000000,0.0000008143],[1849910400000,0.0000005666],[1852588800000,0.0000003942],[1855180800000,0.0000002742],[1857859200000,0.0000001906],[1860451200000,0.0000001326],[1863129600000,0.0000000921],[1865808000000,0.0],[1868227200000,0.0],[1870905600000,0.0],[1873497600000,0.0],[1876176000000,0.0],[1878768000000,0.0],[1881446400000,0.0],[1884124800000,0.0],[1886716800000,0.0],[1889395200000,0.0],[1891987200000,0.0],[1894665600000,0.0],[1897344000000,0.0],[1899763200000,0.0],[1902441600000,0.0],[1905033600000,0.0],[1907712000000,0.0],[1910304000000,0.0],[1912982400000,0.0],[1915660800000,0.0],[1918252800000,0.0],[1920931200000,0.0],[1923523200000,0.0],[1926201600000,0.0],[1928880000000,0.0],[1931299200000,0.0],[1933977600000,0.0],[1936569600000,0.0],[1939248000000,0.0]],"2":[[1626307200000,1499.224024],[1628985600000,1012.362554],[1631664000000,2248.431091],[1634256000000,2508.331472],[1636934400000,2598.170958],[1639526400000,2242.086348],[1642204800000,6037.62068],[1644883200000,4452.626783],[1647302400000,3814.301903],[1649980800000,2874.283745],[1652572800000,2391.340011],[1655251200000,1820.379286],[1657843200000,1615.539185],[1660521600000,1247.933088],[1663200000000,1231.91721],[1665792000000,1051.469965],[1668470400000,760.6507882],[1671062400000,604.5499297],[1673740800000,496.9414505],[1676419200000,414.8423286],[1678838400000,344.5661213],[1681516800000,258.6103623],[1684108800000,202.7907663],[1686787200000,144.4798943],[1689379200000,67.73338014],[1692057600000,74.52646198],[1694736000000,80.76949288],[1697328000000,86.48308418],[1700006400000,91.68941693],[1702598400000,96.4114862],[1705276800000,100.6725919],[1707955200000,104.4959884],[1710460800000,107.904641],[1713139200000,110.9210545],[1715731200000,113.5671537],[1718409600000,115.864201],[1721001600000,117.8327412],[1723680000000,119.4925663],[1726358400000,120.8626956],[1728950400000,121.9613675],[1731628800000,122.8060387],[1734220800000,123.4133917],[1736899200000,123.7993458],[1739577600000,123.9790727],[1741996800000,123.9670149],[1744675200000,123.7769049],[1747267200000,123.421788],[1749945600000,122.9140439],[1752537600000,122.2654106],[1755216000000,121.4870076],[1757894400000,120.5893598],[1760486400000,119.5824207],[1763164800000,118.475596],[1765756800000,117.2777668],[1768435200000,115.9973114],[1771113600000,114.6421283],[1773532800000,113.2196568],[1776211200000,111.7368985],[1778803200000,110.2004372],[1781481600000,108.6164586],[1784073600000,106.9907693],[1786752000000,105.3288149],[1789430400000,103.635698],[1792022400000,101.9161944],[1794700800000,100.1747704],[1797292800000,98.41559745],[1799971200000,96.64256796],[1802649600000,94.85930915],[1805068800000,93.06919723],[1807747200000,91.27537017],[1810339200000,89.4807409],[1813017600000,87.68800896],[1815609600000,85.89967198],[1818288000000,84.11803721],[1820966400000,82.34523143],[1823558400000,80.58321133],[1826236800000,78.83377315],[1828828800000,77.09856152],[1831507200000,75.37907826],[1834185600000,73.67669073],[1836691200000,71.99263976],[1839369600000,70.32804685],[1841961600000,68.68392155],[1844640000000,67.06116816],[1847232000000,65.46059212],[1849910400000,63.88290621],[1852588800000,62.32873625],[1855180800000,60.79862666],[1857859200000,59.2930457],[1860451200000,57.81239036],[1863129600000,56.35699113],[1865808000000,54.92711637],[1868227200000,53.52297661],[1870905600000,52.14472846],[1873497600000,50.79247841],[1876176000000,49.46628643],[1878768000000,48.1661693],[1881446400000,0.0],[1884124800000,0.0],[1886716800000,0.0],[1889395200000,0.0],[1891987200000,0.0],[1894665600000,0.0],[1897344000000,0.0],[1899763200000,0.0],[1902441600000,0.0],[1905033600000,0.0],[1907712000000,0.0],[1910304000000,0.0],[1912982400000,0.0],[1915660800000,0.0],[1918252800000,0.0],[1920931200000,0.0],[1923523200000,0.0],[1926201600000,0.0],[1928880000000,0.0],[1931299200000,0.0],[1933977600000,0.0],[1936569600000,0.0],[1939248000000,0.0]],"3":[[1626307200000,2324.304451],[1628985600000,1566.987422],[1631664000000,3475.997451],[1634256000000,3873.187493],[1636934400000,4007.041298],[1639526400000,3453.613387],[1642204800000,9296.63324],[1644883200000,6846.666481],[1647302400000,5857.269396],[1649980800000,4407.932405],[1652572800000,3663.093754],[1655251200000,2785.114114],[1657843200000,2468.736219],[1660521600000,1904.781688],[1663200000000,1878.02413],[1665792000000,1601.051508],[1668470400000,1156.969113],[1671062400000,974.1668535],[1673740800000,913.4646029],[1676419200000,870.0247773],[1678838400000,823.1838831],[1681516800000,734.0328083],[1684108800000,674.78965],[1686787200000,598.8992325],[1689379200000,484.8785306],[1692057600000,488.5056825],[1694736000000,484.75681],[1697328000000,475.2443831],[1700006400000,461.3263337],[1702598400000,444.1364602],[1705276800000,424.6136372],[1707955200000,403.5287763],[1710460800000,381.5091289],[1713139200000,359.0598517],[1715731200000,336.5829378],[1718409600000,314.3936937],[1721001600000,292.7349907],[1723680000000,271.7895206],[1726358400000,251.6902849],[1728950400000,232.5295326],[1731628800000,214.3663453],[1734220800000,197.2330457],[1736899200000,181.1405921],[1739577600000,166.0830985],[1741996800000,152.0416054],[1744675200000,138.9872098],[1747267200000,126.8836508],[1749945600000,115.6894307],[1752537600000,105.3595452],[1755216000000,95.84688205],[1757894400000,87.10334228],[1760486400000,79.08072861],[1763164800000,71.73143947],[1765756800000,65.00900194],[1768435200000,58.86847141],[1771113600000,53.26672167],[1773532800000,48.16264553],[1776211200000,43.51728292],[1778803200000,39.29389056],[1781481600000,35.45796524],[1784073600000,31.97723059],[1786752000000,28.82159555],[1789430400000,25.96309141],[1792022400000,23.37579313],[1794700800000,21.03572932],[1797292800000,18.92078491],[1799971200000,17.01059925],[1802649600000,15.28646225],[1805068800000,13.73121046],[1807747200000,12.32912437],[1810339200000,11.06582834],[1813017600000,9.928193834],[1815609600000,8.904246672],[1818288000000,7.983078603],[1820966400000,7.154763602],[1823558400000,6.410278944],[1826236800000,5.741431127],[1828828800000,5.140786607],[1831507200000,4.601607248],[1834185600000,4.117790349],[1836691200000,3.683813072],[1839369600000,3.29468108],[1841961600000,2.945881154],[1844640000000,2.633337607],[1847232000000,2.353372214],[1849910400000,2.102667488],[1852588800000,1.878233044],[1855180800000,1.677374842],[1857859200000,1.49766712],[1860451200000,1.336926784],[1863129600000,1.193190094],[1865808000000,1.064691449],[1868227200000,0.94984411],[1870905600000,0.847222691],[1873497600000,0.755547287],[1876176000000,0.673669083],[1878768000000,0.60055732],[1881446400000,0.0],[1884124800000,0.0],[1886716800000,0.0],[1889395200000,0.0],[1891987200000,0.0],[1894665600000,0.0],[1897344000000,0.0],[1899763200000,0.0],[1902441600000,0.0],[1905033600000,0.0],[1907712000000,0.0],[1910304000000,0.0],[1912982400000,0.0],[1915660800000,0.0],[1918252800000,0.0],[1920931200000,0.0],[1923523200000,0.0],[1926201600000,0.0],[1928880000000,0.0],[1931299200000,0.0],[1933977600000,0.0],[1936569600000,0.0],[1939248000000,0.0]],"4":[[1626307200000,1216.27095],[1628985600000,957.6667518],[1631664000000,2350.867277],[1634256000000,2692.248066],[1636934400000,2844.863962],[1639526400000,2499.514738],[1642204800000,4020.940494],[1644883200000,3353.833167],[1647302400000,3162.786279],[1649980800000,2652.378239],[1652572800000,2272.005303],[1655251200000,1838.919383],[1657843200000,1672.218075],[1660521600000,1357.86331],[1663200000000,1383.405526],[1665792000000,1269.68702],[1668470400000,1017.096105],[1671062400000,878.0730806],[1673740800000,789.4555119],[1676419200000,712.7393866],[1678838400000,663.9907227],[1681516800000,575.9297277],[1684108800000,513.4751153],[1686787200000,449.9193382],[1689379200000,322.830868],[1692057600000,333.3469581],[1694736000000,341.380355],[1697328000000,347.1784994],[1700006400000,350.9720978],[1702598400000,352.9752359],[1705276800000,353.3858056],[1707955200000,352.3861277],[1710460800000,350.1436932],[1713139200000,346.8119673],[1715731200000,342.531225],[1718409600000,337.4293897],[1721001600000,331.6228608],[1723680000000,325.2173193],[1726358400000,318.3085022],[1728950400000,310.9829417],[1731628800000,303.3186688],[1734220800000,295.3858761],[1736899200000,287.247542],[1739577600000,278.9600165],[1741996800000,270.5735686],[1744675200000,262.1328966],[1747267200000,253.6776044],[1749945600000,245.2426425],[1752537600000,236.8587181],[1755216000000,228.5526744],[1757894400000,220.3478424],[1760486400000,212.2643635],[1763164800000,204.3194903],[1765756800000,196.5278608],[1768435200000,188.9017523],[1771113600000,181.4513151],[1773532800000,174.1847852],[1776211200000,167.1086817],[1778803200000,160.2279862],[1781481600000,153.5463071],[1784073600000,147.0660305],[1786752000000,140.7884573],[1789430400000,134.7139282],[1792022400000,128.8419386],[1794700800000,123.1712416],[1797292800000,117.6999428],[1799971200000,112.4255856],[1802649600000,107.3452292],[1805068800000,102.4555182],[1807747200000,97.75274658],[1810339200000,93.23291415],[1813017600000,88.89177859],[1815609600000,84.72490135],[1818288000000,80.72768905],[1820966400000,76.89543064],[1823558400000,73.22333028],[1826236800000,69.70653679],[1828828800000,66.34016966],[1831507200000,63.11934203],[1834185600000,60.03918101],[1836691200000,57.09484531],[1839369600000,54.28154083],[1841961600000,51.59453394],[1844640000000,49.0291631],[1847232000000,46.58084862],[1849910400000,44.24510111],[1852588800000,42.0175283],[1855180800000,39.89384088],[1857859200000,37.86985701],[1860451200000,35.94150602],[1863129600000,34.1048311],[1865808000000,32.3559913],[1868227200000,30.69126275],[1870905600000,29.10703928],[1873497600000,0.0],[1876176000000,0.0],[1878768000000,0.0],[1881446400000,0.0],[1884124800000,0.0],[1886716800000,0.0],[1889395200000,0.0],[1891987200000,0.0],[1894665600000,0.0],[1897344000000,0.0],[1899763200000,0.0],[1902441600000,0.0],[1905033600000,0.0],[1907712000000,0.0],[1910304000000,0.0],[1912982400000,0.0],[1915660800000,0.0],[1918252800000,0.0],[1920931200000,0.0],[1923523200000,0.0],[1926201600000,0.0],[1928880000000,0.0],[1931299200000,0.0],[1933977600000,0.0],[1936569600000,0.0],[1939248000000,0.0]],"5":[[1626307200000,1975.029434],[1628985600000,1624.997181],[1631664000000,5037.772977],[1634256000000,6041.18977],[1636934400000,6475.54598],[1639526400000,5783.187677],[1642204800000,8517.355611],[1644883200000,7308.835981],[1647302400000,6921.650519],[1649980800000,5898.886849],[1652572800000,5118.049332],[1655251200000,4204.493285],[1657843200000,3841.847069],[1660521600000,3151.57788],[1663200000000,3158.696558],[1665792000000,3085.218184],[1668470400000,2925.744282],[1671062400000,2929.345137],[1673740800000,2953.373271],[1676419200000,2931.392117],[1678838400000,2902.778999],[1681516800000,2728.860954],[1684108800000,2581.340651],[1686787200000,2394.354827],[1689379200000,2020.809113],[1692057600000,1970.639299],[1694736000000,1902.773617],[1697328000000,1822.149753],[1700006400000,1732.807966],[1702598400000,1638.023021],[1705276800000,1540.42154],[1707955200000,1442.084941],[1710460800000,1344.638809],[1713139200000,1249.329917],[1715731200000,1157.092153],[1718409600000,1068.602667],[1721001600000,984.3294432],[1723680000000,904.5714327],[1726358400000,829.4922483],[1728950400000,759.1483344],[1731628800000,693.5124121],[1734220800000,632.4928891],[1736899200000,575.9498511],[1739577600000,523.7081576],[1741996800000,475.5680949],[1744675200000,431.3139817],[1747267200000,390.7210535],[1749945600000,353.5609194],[1752537600000,319.6058279],[1755216000000,288.6319498],[1757894400000,260.4218529],[1760486400000,234.7663133],[1763164800000,211.4655903],[1765756800000,190.3302652],[1768435200000,171.1817324],[1771113600000,153.8524152],[1773532800000,138.1857645],[1776211200000,124.0360922],[1778803200000,111.2682784],[1781481600000,99.75738497],[1784073600000,89.38820532],[1786752000000,80.05476935],[1789430400000,71.65982283],[1792022400000,64.11429419],[1794700800000,57.33676028],[1797292800000,51.25291898],[1799971200000,45.79507523],[1802649600000,40.90164556],[1805068800000,36.51668347],[1807747200000,32.5894291],[1810339200000,29.07388375],[1813017600000,25.92841019],[1815609600000,23.11535897],[1818288000000,20.60072025],[1820966400000,18.35380068],[1823558400000,16.34692443],[1826236800000,14.55515733],[1828828800000,12.95605296],[1831507200000,11.52941958],[1834185600000,10.25710655],[1836691200000,9.12280895],[1839369600000,8.111889369],[1841961600000,7.21121535],[1844640000000,6.409011558],[1847232000000,5.69472541],[1849910400000,5.058905142],[1852588800000,4.493089295],[1855180800000,3.989706646],[1857859200000,3.541985725],[1860451200000,3.143873058],[1863129600000,2.789959366],[1865808000000,2.475413021],[1868227200000,2.195920077],[1870905600000,1.947630288],[1873497600000,1.727108532],[1876176000000,0.0],[1878768000000,0.0],[1881446400000,0.0],[1884124800000,0.0],[1886716800000,0.0],[1889395200000,0.0],[1891987200000,0.0],[1894665600000,0.0],[1897344000000,0.0],[1899763200000,0.0],[1902441600000,0.0],[1905033600000,0.0],[1907712000000,0.0],[1910304000000,0.0],[1912982400000,0.0],[1915660800000,0.0],[1918252800000,0.0],[1920931200000,0.0],[1923523200000,0.0],[1926201600000,0.0],[1928880000000,0.0],[1931299200000,0.0],[1933977600000,0.0],[1936569600000,0.0],[1939248000000,0.0]]}

Answer 1

You'll need to verify things are correct, but since these are lists of lists and not tuples, and the dates weren't converted, I took some liberties. In the end, if you create your code to call the function the same way I do here, there's no reason your code shouldn't work.

setting your dictionary example to data and reading it in...

df2 = pd.json_normalize(data).T
df2.rename(columns={0:'cashflows'}, inplace=True)

#note datetime function to convert the date
def xnpv(rate,cashflows):
    chron_order = sorted(cashflows, key = lambda x: x[0])
    t0 = chron_order[0][0] #t0 is the date of the first cash flow
    return sum([cf/(1+rate)**((datetime.fromtimestamp(t/1000)-datetime.fromtimestamp(t0/1000)).days/365.0) for (t,cf) in chron_order])

# this is the line you need fix in your code
df2['XNPV']=df2.apply(lambda x: xnpv(0.12,x['cashflows']), axis=1)

Output

                                           cashflows           XNPV
1  [[1622505600000, -177638.2140590313], [1626307...  -14092.197419
2  [[1626307200000, 1499.224024], [1628985600000,...   42989.007430
3  [[1626307200000, 2324.304451], [1628985600000,...   67357.553372
4  [[1626307200000, 1216.27095], [1628985600000, ...   46127.473827
5  [[1626307200000, 1975.029434], [1628985600000,...  112983.922505