1

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
lakshmen
  • 28,346
  • 66
  • 178
  • 276
  • 2
    Can you add some sample data? – jezrael Feb 18 '19 at 06:20
  • 2
    And what is expected output? – jezrael Feb 18 '19 at 06:22
  • I should have unique rows in the column called 'acct' – lakshmen Feb 18 '19 at 06:23
  • Thank you for edit your code. But I am more confused now - you compare 2 DataFrames, but here is only sample of one of them. Also [data cannot be used for copy, because picture](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question). Is possible add [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) with both DataFrames, no pictures with expected output, 5-6 rows of data? – jezrael Feb 18 '19 at 07:19
  • edited the full question. please check now. – lakshmen Feb 18 '19 at 07:26
  • It is better now, only is possible change pictures to text? [Why is necessary do it](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question) – jezrael Feb 18 '19 at 07:28
  • 1
    removed the images and added it as text – lakshmen Feb 18 '19 at 07:49
  • So `Expected output:` is `usd_margin_data` and first dataframe `initial_margin_data` ? – jezrael Feb 18 '19 at 07:57
  • No, the initial margin data contains many accounts and is differentiated by the exchange. I want to sum up by accounts as the rows and the exchange as the columns. – lakshmen Feb 18 '19 at 08:01
  • 1
    @jezrael any solution on how to solve the problem? – lakshmen Feb 18 '19 at 08:25
  • Can you check now edited answer? – jezrael Feb 18 '19 at 08:27
  • i need to get from the initial margin data to expected output. – lakshmen Feb 18 '19 at 08:28
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188579/discussion-between-lakesh-and-jezrael). – lakshmen Feb 18 '19 at 08:28

1 Answers1

3

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
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252