0

I am trying to replace a value in the data frame dh based on the data frame larceny. If the date in larceny exists, I want to find the corresponding date in dh and replace the corresponding 5th column entry with 1.

I am currently (somewhat successfully) doing it with the below code but, it is taking forever. Any help on this?

When I try to compare the dates, the code does not work, so I compare the .value of the dates and this seems to work. 

import pandas as pd
from datetime import datetime

for i, row in dh.iterrows():
    for j in range(45314):

        if dh.iat[i,0].value==larceny.iat[j,0].value:
                dh.iat[i,5]=1
                print("Larceny")
                print(i,j)
                print(dh.iat[i,0],larceny.iat[j,0])
                print(dh.iat[i,0].value,larceny.iat[j,0].value,'\n\n')

Basically, dh has a cell for each hour of each day for 4 years. I want to populate the cell for each hour with a 1 in the "Is_larceny" column, if that corresponding year-month-day-hour appears in the larceny data frame.

Please help. I tried some pandas search methods but I was having a problem with comparing dates and searching and replacing properly.

Thanks.

Ryan Fink
  • 39
  • 4
  • Please add an example dataframe and an expected output. We understand data better than lots of text and nested loops. Find more information [here](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) on how to ask a good `pandas` question – Erfan Apr 26 '19 at 21:11
  • It seems to me the problem is you are modifying the object that you're iterating over and that leads to unpredictable results. Make a copy of ```dh``` and iterate over the copy. Then modify the corresponding column on ```dh```. – accdias Apr 26 '19 at 21:14

1 Answers1

1
dh.loc[dh['col1'].isin(larceny['col2']), 'col1'] = 1

This looks for any value in the dh['col1'] that also appears in larceny['col2'], then sets those values in dh['col1'] to 1. You will have to replace col1 and col2 with your respective column names.

Ben Pap
  • 2,549
  • 1
  • 8
  • 17
  • This is really close to what I want, but I want to set a different column in dh, say col3, to 1 – Ryan Fink Apr 26 '19 at 22:00
  • So you want it to have the same values as dh['col1'] but with the 1's? You can just write dh['col3'] = dh['col1'] to copy the column, then do my previous code. Or do you want another value when its not 1? – Ben Pap Apr 26 '19 at 22:05
  • Okay wow, this example just completely cleared up how to use True/False expressions as arguments in .loc. I completely understand now. Thank you!!! – Ryan Fink Apr 26 '19 at 22:13
  • 1
    I basically do not want to change col1 at all. I want to check col1 of larceny with col1 of dh. so, dh.loc[dh['col1'].isin(larceny['col1']), 'col3'] = 1 – Ryan Fink Apr 26 '19 at 22:15