copy some rows from existing pandas dataframe to a new one

Question

And the copy has to be done for 'City' column starting with 'BH'. The copied df.index shouls be same as the original Eg -

              STATE            CITY
315           KA               BLR
423           WB               CCU
554           KA               BHU
557           TN               BHY

# state_df is new dataframe, df is existing
state_df = pd.DataFrame(columns=['STATE', 'CITY'])      
for index, row in df.iterrows():
    city = row['CITY']

    if(city.startswith('BH')):
        append row from df to state_df # pseudocode

Being new to pandas and Python, I need help in the pseudocode for the most efficient way.

Answer 1

Solution with startswith and boolean indexing:

print (df['CITY'].str.startswith('BH'))
315    False
423    False
554     True
557     True

state_df = df[df['CITY'].str.startswith('BH')]
print (state_df)
    STATE CITY
554    KA  BHU
557    TN  BHY

If need copy only some columns add loc:

state_df = df.loc[df['CITY'].str.startswith('BH'), ['STATE']]
print (state_df)
    STATE
554    KA
557    TN

Timings:

#len (df) = 400k
df = pd.concat([df]*100000).reset_index(drop=True)


In [111]: %timeit (df.CITY.str.startswith('BH'))
10 loops, best of 3: 151 ms per loop

In [112]: %timeit (df.CITY.str.contains('^BH'))
1 loop, best of 3: 254 ms per loop

Answer 2

try this:

In [4]: new = df[df['CITY'].str.contains(r'^BH')].copy()

In [5]: new
Out[5]:
    STATE CITY
554    KA  BHU
557    TN  BHY

What if I need to copy only some columns of the row and not the entire row

cols_to_copy = ['STATE']
new = df.loc[df.CITY.str.contains(r'^BH'), cols_to_copy].copy()

In [7]: new
Out[7]:
    STATE
554    KA
557    TN