CODEWITHSUNDEEP
pythonpandasdataframe
lakshmen
lakshmen

Reputation: 29084

Check if string is in pandas dataframe and amend the row

I would like to check if the string is in the dataframe and amend that particular row.

I know how to find if the string exists in the dataframe, e.g. like this:

if usd_margin_data['acct'].str.contains(row['acct']).any():

But now i want to amend the row that contains the string. How do I do that?

TypeError: first argument must be string or compiled pattern

Upvotes: 1

Views: 111

Answers (1)

jezrael
jezrael

Reputation: 863166

Use:

df11 = (df1.pivot_table(index='acct',
                    columns='exchange',
                    values='spanReq',
                    aggfunc='sum')
      )
print (df11)
exchange        CME         HKEX          OSE          SGX
acct                                                      
10        1728005.0         0.00          NaN          NaN
20          83671.0          NaN  106516141.0  14872000.00
30           6237.0          NaN          NaN          NaN
40         857120.0  36071131.40          NaN   2987340.93
60        2803385.0   3459377.95          NaN   2030260.39
70        9007666.0     81300.00    1800000.0  57172391.00
80              NaN  23698214.00          NaN          NaN
90         119644.0          NaN          NaN          NaN

df2 = df2.replace('-', np.nan).replace(',', '', regex=True).astype(float).set_index('Account')
print (df2)
               SGX       HKEX        OSE        CME      Total
Account                                                       
10.0           NaN        NaN        NaN  1549946.0  1549946.0
20.0      144262.0        NaN  1173680.0    83557.0  1401500.0
30.0           NaN        NaN        NaN     6237.0     6237.0
40.0     3834382.0  4455808.0        NaN   631892.0  8922082.0
60.0     2329690.0   199192.0        NaN  3754770.0  6283652.0
70.0      514978.0    10360.0    16213.0  4777373.0  5318923.0
80.0           NaN  1741612.0        NaN        NaN  1741612.0
90.0           NaN        NaN        NaN   119644.0   119644.0

df3 = df11.combine_first(df2)
print (df3)
            CME         HKEX          OSE          SGX      Total
acct                                                             
10    1728005.0         0.00          NaN          NaN  1549946.0
20      83671.0          NaN  106516141.0  14872000.00  1401500.0
30       6237.0          NaN          NaN          NaN     6237.0
40     857120.0  36071131.40          NaN   2987340.93  8922082.0
60    2803385.0   3459377.95          NaN   2030260.39  6283652.0
70    9007666.0     81300.00    1800000.0  57172391.00  5318923.0
80          NaN  23698214.00          NaN          NaN  1741612.0
90     119644.0          NaN          NaN          NaN   119644.0

Upvotes: 3

Related Questions