2

I have the following jason:

{'ADA': {'free': '0.00000000', 'locked': '1.32580000'},
 'ADX': {'free': '15.0000000', 'locked': '0.00000000'},
 'AE': {'free': '0.23000000', 'locked': '1.253000000'},
 'ZRX': {'free': '0.00000000', 'locked': '0.00000000'}}

I am trying to convert the above Jason to pandas but only the lines which has amount <> 0 in "free" or in "locked" + with no "0" when not needed so ZRX regarding the above example will not be shown and 1.2530000 will be only 1.253

My desired pandas will be:

    free   locked
ADA 0      1.3258
ADX 15     0
AE  0.23   1.253

Thanks for helping!

Giladbi
  • 1,822
  • 3
  • 19
  • 34

2 Answers2

3

I believe you need DataFrame.from_dict first, then convertto floats and filter by Series.ne with boolean indexing, last for strings without trailing 0 use this solution:

d =  {'ADA': {'free': '0.00000000', 'locked': '1.32580000'},
 'ADX': {'free': '15.0000000', 'locked': '0.00000000'},
 'AE': {'free': '0.23000000', 'locked': '1.253000000'},
 'ZRX': {'free': '0.00000000', 'locked': '0.00000000'}}

df = pd.DataFrame.from_dict(d,orient='index')

df = df.astype(float)

df = df[df['free'].ne(0) | df['locked'].ne(0)].applymap('{0:g}'.format)
#if need remove 0 from all rows
#df = df[df.ne(0).any(axis=1)].applymap('{0:g}'.format)
print (df)
     free  locked
ADA     0  1.3258
ADX    15       0
AE   0.23   1.253
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • why when trying to do free+locked i get only free? even after i rechange the df to float? – Giladbi Apr 08 '19 at 08:10
  • @Giladbi - Not sure if understand, can you explain more? How you get values? If check `print (df.dtypes)` there are not both float columns? – jezrael Apr 08 '19 at 08:12
  • it's an object. I change it to "df = df.astype(float)" and still getting a+b =a or b+a =b – Giladbi Apr 08 '19 at 08:36
  • @Giladbi - Are data confidental? – jezrael Apr 08 '19 at 08:48
  • I added "balance['Total Position'] = (balance['locked']) + (balance['free'])" and the results are: free locked Total Position AAA 2.99831 0 02.99831 – Giladbi Apr 08 '19 at 09:16
  • Data columns (total 3 columns): free 7 non-null object locked 7 non-null object Total Position 7 non-null object dtypes: object(3) – Giladbi Apr 08 '19 at 09:17
  • 1
    @Giladbi - So i think problem is with `applymap('{0:g}'.format)` - it convert floats to strings. So need `df = df.astype(float)` and then `df['Total Position'] = (df['locked']) + (df['free'])` and last `df = df[df['free'].ne(0) | df['locked'].ne(0)].applymap('{0:g}'.format)` – jezrael Apr 08 '19 at 09:19
2

You can use:

json = {'ADA': {'free': '0.00000000', 'locked': '1.32580000'},
 'ADX': {'free': '15.0000000', 'locked': '0.00000000'},
 'AE': {'free': '0.23000000', 'locked': '1.253000000'},
 'ZRX': {'free': '0.00000000', 'locked': '0.00000000'}}

df = pd.DataFrame.from_dict(json).astype(float).transpose()
df = df.applymap(lambda s: str(s).rstrip('0').rstrip('.') if '.' in str(s) else s)
df = df[~((df['free']=='0') & (df['locked']=='0'))]   # This is to drop row containing all zeros
print(df)



     free  locked
ADA     0  1.3258
ADX    15       0
AE   0.23   1.253
heena bawa
  • 818
  • 6
  • 5