Pandas time series comparison with missing data/records
<p>This question is somewhat related to an earlier question from me (<a href="http://stackoverflow.com/questions/38658811/remapping-numpy-array-with-missing-values">Remapping `numpy.array` with missing values</a>), where I was struggling with time series with missing data, and someone suggested <em>"use Pandas!"</em>. So here I go...</p>

<p>I'm dealing with large data sets, basically consisting of time series from different observation sites, where I would like to statistically compare the sites. These data sets are quite messy; lots of missing data (indicated with e.g. <code>-99</code>), missing time records (one station might have the time record, another not), and I only want to include/compare data where either (1) all sites have data for a certain variable, or (2) the two sites I would like to compare have data for that variable, ignoring whether the other sites (don't) have data. </p>

<p>Take this minimal example:</p>

<pre><code>import pandas as pd
from io import StringIO

data = StringIO("""\
  1,  2001-01-01, 00:00, 1.0, 0.5, 1.0
  1,  2001-01-01, 01:00, 1.1, 0.6, 2.0
  1,  2001-01-01, 02:00, 1.2, 0.7, 3.0
  1,  2001-01-01, 03:00, 1.3, 0.8, 4.0

  2,  2001-01-01, 00:00, 2.0, -99, -99
  2,  2001-01-01, 01:00, -99, 1.6, 2.0
  2,  2001-01-01, 02:00, 2.2, 1.7, 3.0
  2,  2001-01-01, 03:00, 2.3, 1.8, 4.0

  3,  2001-01-01, 00:00, 3.0, 2.5, 1.0
  3,  2001-01-01, 01:00, 3.1, 2.6, -99
  3,  2001-01-01, 02:00, -99, -99, 3.0
  3,  2001-01-01, 03:00, 3.3, 2.8, 4.0
  3,  2001-01-01, 04:00, 3.4, 2.9, 5.0
""")

columns = ['id','date','time','T','Td','cc']
df = pd.read_table(data, header=None, names=columns, delimiter=',', na_values=-99, parse_dates=[['date','time']])
</code></pre>

<p>Where <code>-99</code> indicates a missing value. I would like to compare the data (columns <code>T</code>,<code>Td</code>,<code>cc</code>) from different sites (column <code>id</code>), but as mentioned, only if either two or all <code>id</code>'s have data for the variable I'm interested in (completely ignoring whether the data in other columns is missing).</p>

<p>So for this example, if all sites need to have data, comparing <code>T</code> would only result in comparing data from <code>2001-01-01, 00:00</code> and <code>03:00</code>, since for the other times, either <code>id=2</code> or <code>id=3</code> is missing <code>T</code>, and the last time record for <code>id=3</code> is completely absent in the other <code>id</code>'s. </p>

<p>I've been playing with this for hours now, but honestly I don't really know where to start. Is it possible to extract a <code>numpy.array</code>, using the criteria outlined above, of size <code>n_sites x n_valid_values</code> (<code>3x2</code>, for this example), which I could then use for further analysis?</p>

<p><em>EDIT</em> As a partial, but really (<em>really</em>) ugly solution, something like this seems to work:</p>

<pre><code># Loop over all indexes where T is nan:
for i in np.where(df['T'].isnull())[0]:
    # For each of them, set records with the same date_time to nan
    j = np.where(df['date_time'] == df['date_time'][i])[0]
    df['T'][j] = np.nan
# Drop all records where T is nan
df2 = df.dropna(subset=['T'])

# Group by the different stations:
g = df2.groupby('id')

# Get the arrays (could automate this based on the unique id's):
v1 = g.get_group(1)['T']
v2 = g.get_group(2)['T']
v3 = g.get_group(3)['T']
</code></pre>

<p>But this still doesn't drop the record for <code>id=3</code>, <code>date_time=2001-01-01, 04:00</code>, and I guess/hope that there are more elegant methods with <code>Pandas</code>.</p>

<p>One method (based on this: <a href="http://stackoverflow.com/a/34985243/3581217">http://stackoverflow.com/a/34985243/3581217</a> answer) which seems to work is to create a <code>Dataframe</code> where the observations from the different sites have different columns, then a <code>dropna()</code> with <code>subset</code> set to either all columns, or the two sites I want to compare, which drops all rows where data is missing. </p>

<pre><code>import pandas as pd
import numpy as np
from io import StringIO

data1 = StringIO("""\
  1,  2001-01-01, 00:00, 1.0
  1,  2001-01-01, 01:00, 1.1
  1,  2001-01-01, 02:00, 1.2
  1,  2001-01-01, 03:00, 1.3
""")

data2 = StringIO("""\
  2,  2001-01-01, 00:00, 2.0
  2,  2001-01-01, 01:00, -99
  2,  2001-01-01, 02:00, 2.2
  2,  2001-01-01, 03:00, 2.3
""")

data3 = StringIO("""\
  3,  2001-01-01, 00:00, 3.0
  3,  2001-01-01, 01:00, 3.1
  3,  2001-01-01, 02:00, -99
  3,  2001-01-01, 03:00, 3.3
  3,  2001-01-01, 04:00, 3.4
""")

columns = ['id','date','time','T1']
df1 = pd.read_table(data1, header=None, names=columns, delimiter=',', na_values=-99, parse_dates=[['date','time']])
columns = ['id','date','time','T2']
df2 = pd.read_table(data2, header=None, names=columns, delimiter=',', na_values=-99, parse_dates=[['date','time']])
columns = ['id','date','time','T3']
df3 = pd.read_table(data3, header=None, names=columns, delimiter=',', na_values=-99, parse_dates=[['date','time']])

df = pd.concat([df1,df2,df3]).groupby('date_time').max()
df = df.dropna(subset=['T1','T2','T3'])
</code></pre>

<p>The resulting <code>Dataframe</code> looks like:</p>

<pre><code>In [232]: df
Out[232]: 
                      T1   T2   T3  id
date_time                             
2001-01-01 00:00:00  1.0  2.0  3.0   3
2001-01-01 03:00:00  1.3  2.3  3.3   3
</code></pre>

<p>And if I want to compare only two sites, ignoring in this case <code>T3</code>, a <code>df.dropna(subset=['T1','T2'])</code> results in:</p>

<pre><code>In [234]: df
Out[234]: 
                      T1   T2   T3  id
date_time                             
2001-01-01 00:00:00  1.0  2.0  3.0   3
2001-01-01 02:00:00  1.2  2.2  NaN   3
2001-01-01 03:00:00  1.3  2.3  3.3   3
</code></pre>

<p>Is this the way to go? Still feels a bit non-Panda-ish..?</p>

python pandas time-series