I have the following DataFrame: df :
A B C D
2 6 5 8.0
6 11 2 3.6
1 5 7 5.2
to convert to JSON , I write following snippet:
df['total'] = df.apply(lambda i: i.to_json(), axis=1)
I get following output:
total
{"A":2.0, "B":6.0, "C":5.0, "D":8.0}
{"A":6.0, "B":11.0, "C":2.0, "D":3.6}
{"A":1.0, "B":5.0, "C":7.0, "D":5.2}
Why is that extra .0 is added to the result ? How do I remove that extra .0 ?
The problem here is, when you call apply on axis=1, pandas creates a Series out of it and upcasts the values because it is a single Series. For example consider following Series:
>>> s = pd.Series([1, 2, 3.2])
s
0 1.0
1 2.0
2 3.2
dtype: float64
As you can see, the entire series is converted to float because integer type can not hold all the values for the above series, similar is the case when you call apply on axis=1, it is same to :
df.iloc[0]
A 2.0
B 6.0
C 5.0
D 8.0
Name: 0, dtype: float64
There's already an issue DataFrame.apply unintuitively changes int to float because of another column on github for this upcasting behavior of pandas apply.
So, one possible option for you is as I have mentioned in the comment, to call to_json on the entire dataframe as:
>>>df.to_json(orient='index')
'{"0":{"A":2,"B":6,"C":5,"D":8.0},"1":{"A":6,"B":11,"C":2,"D":3.6},"2":{"A":1,"B":5,"C":7,"D":5.2}}'
A working solution for you may be using python's json module alongwith DataFrame.to_json(), but remember, it does the same thing twice so it may be a bit slow for a large dataframes, however, you will get the data in the rquired format:
df['total'] = list(map(json.dumps, [*json.loads(df.to_json(orient='index')).values()]))
OUTPUT:
A B C D total
0 2 6 5 8.0 {"A": 2, "B": 6, "C": 5, "D": 8.0}
1 6 11 2 3.6 {"A": 6, "B": 11, "C": 2, "D": 3.6}
2 1 5 7 5.2 {"A": 1, "B": 5, "C": 7, "D": 5.2}