Adding null values to a pandas dataframe

Question

I have a pandas dataframe that is used to create a JSON which in turn is used to display a highcharts chart.

Pandas dataframe:

Date        colA    colB
12-Sep-14   20      40
13-Sep-14   50      10
14-Sep-14   12      -20
15-Sep-14   74      43

Is there a way to change some of the colA and colB values to null. The reason for this is that I ultimately need a JSON that looks something like this:

[
    [12-Sep-14, 20, 40],
    [13-Sep-14, null, null],
    [14-Sep-14, 12, -20],
    [15-Sep-14, 74, 43]
]

The reason for this is that I require a highcharts chart where certain plot points are blank. To do this, you specify the date followed by null.

So I need to somehow update certain values in the pandas dataframe so that once I convert it to a JSON using .to_json() then the json will contain the specified null values as per the example above.

Thanks for any suggestions.

Answer 1

Try using NaN which is the Pandas missing value:

from numpy import nan 

df = pd.read_clipboard()
df.colA.iloc[1] = NaN

instead of NaN you could also use None. Note that neither of these terms are entered with quotes.

Then you can use to_json() to get your output:

df.to_json()
'{"Date":{"0":"12-Sep-14","1":"13-Sep-14","2":"14-Sep-14","3":"15-Sep-14"},"colA":{"0":20.0,"1":null,"2":12.0,"3":74.0},"colB":{"0":40,"1":10,"2":-20,"3":43}}'

Answer 2

Does this work?

import pandas as pd
# Read in data frame from clipboard
df = pd.read_clipboard()
df = df.replace(df.iloc[1][1:],'null')

        Date  colA  colB
0  12-Sep-14    20    40
1  13-Sep-14  null  null
2  14-Sep-14    12   -20
3  15-Sep-14    74    43

Here, df.iloc[1] gives access to row 1

Finally,

df.to_json(orient='values').replace("\"","")

gives json without the ""

[[12-Sep-14,20,40],[13-Sep-14,null,null],[14-Sep-14,12,-20],[15-Sep-14,74,43]]

Answer 3

Code as below:

import numpy as np

# create null/NaN value with np.nan
df.loc[1, colA:colB] = np.nan

Here's the explanation:

1. locate the entities that need to be replaced: df.loc[1, colA:colB] means selecting row 1 and columns from colA to colB;
2. assign the NaN value np.nan to the specific location.