How to deal with large data sets

So i'm very new to this, forgive my silly questions.

I've got some data I need to analyse, I would usually just use excel it doesnt seem to be able to do the job.

I have .csv files containing data for every seconds over a year period. The data consists simlpy of timestamp and valueX.

I need to analyse these values to see if values X goes below a certain value, and if it does I have various calculations to carry out. Value A is proportional to X and just instantaneous, then value B is a counter proportional to A.

Could you suggest a method/ language / software that would be the best and most accessible to do this? I don't have much experience in data analytics/big data apart from using excel and a bit of matlab and python.

EDIT: Thanks for the replies, my data is like this...

I have a text file for January values.

01/02/2016 00:00:00,49.972332
01/02/2016 00:00:01,49.9690056
01/02/2016 00:00:02,49.9600029
01/02/2016 00:00:03,49.9490013
01/02/2016 00:00:04,49.9430046


Different text files for each month, some files have text or notes at the top which I'd need to clean up.

For some context the second value is grid frequency, If it goes below a certain threshold, it constitutes an grid fault. I need to know how long these faults occur for, how often, how far the f value falls etc.

EDIT 2: Thanks for the tips folks, I've been able to read in my data, clean it up and combine it into one dataframe. I'm now try to add some extra column to the dataframe that are dependent of the col 1 value but having some trouble.

    "Read in month csv files and create dataframes"
df_jan_short = pd.read_csv('2016_01_short.csv', header = None ,parse_dates = [0], index_col = 0, names = ['timestamp','Freq'],squeeze= True)
df_feb_short = pd.read_csv('2016_02_short.csv', header = None ,parse_dates = [0], index_col = 0, names = ['timestamp','Freq'],squeeze= True)

"Combine dataframes into one dataframe for year" months_short = [df_jan_short, df_feb_short] year_short = pd.concat(months_short)

"Repalce NaN values in df with 50.00 Hz"
year_short.fillna(50.00,inplace = True)

"Check what range freq is in"

year_short['Case'] = 'No Case'
year_short['Case'](year_short['Freq'] > Fc & year_short['Freq'] < Ff ) = 'A' year_short['Case'](year_short['Freq'] > Fa & year_short['Freq'] < Fc ) = 'B'
year_short['Case'](year_short['Freq'] < Fa ) = 'C'
year_short['Case'](year_short['Freq'] > Ff & year_short['Freq'] < Fh ) = 'G'
year_short['Case'](year_short['Freq'] > Fh ) = 'H'


But I get the following error...

runfile('C:/Users/ShaneOKeeffe/Documents/Grid Freq/Py/4.py', wdir='C:/Users/ShaneOKeeffe/Documents/Grid Freq/Py')
Traceback (most recent call last):

File "C:\ProgramData\Anaconda3\lib\site-packages\IPython\core\interactiveshell.py", line 2862, in run_code
exec(code_obj, self.user_global_ns, self.user_ns)

File "<ipython-input-74-3a0fbc1f23f6>", line 1, in <module>
runfile('C:/Users/ShaneOKeeffe/Documents/Grid Freq/Py/4.py', wdir='C:/Users/ShaneOKeeffe/Documents/Grid Freq/Py')

File "C:\ProgramData\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 710, in runfile
execfile(filename, namespace)

File "C:\ProgramData\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 101, in execfile

File "C:/Users/ShaneOKeeffe/Documents/Grid Freq/Py/4.py", line 48
year_short['Case'](year_short['Freq'] > Fc & year_short['Freq'] < Ff ) = 'A'
^
SyntaxError: can't assign to function call

• I may have asked this in the wrong place :S – Shane Nov 21 '17 at 17:12
• Give an example with data and we'll show you how. Welcome to the site! – Emre Nov 21 '17 at 19:26
• if you have additional questions you should ask at it in a separate thread. not sure what you are trying to do but I think you have to do something like this: year_short['Case'] = 'No Case' and then year_short.loc[(year_short['Freq'] > Fc) & (year_short['Freq'] < Ff ),'Case'] = 'A'  – oW_ Nov 30 '17 at 21:45

As mentioned python's pandas library is good start. They have a lot of time series functionality, see e.g. the documentation here. You can load your data like so:

import pandas as pd


This gives you a timeseries with the timestamps as index:

s
Out[1]:
timestamp
2016-01-02 00:00:00    49.972332
2016-01-02 00:00:01    49.969006
2016-01-02 00:00:02    49.960003
2016-01-02 00:00:03    49.949001
2016-01-02 00:00:04    49.943005
Name: value, dtype: float64

s<49.95
Out[2]:
timestamp
2016-01-02 00:00:00    False
2016-01-02 00:00:01    False
2016-01-02 00:00:02    False
2016-01-02 00:00:03     True
2016-01-02 00:00:04     True
Name: value, dtype: bool


If your data is too big to process as a whole, you can iterate over chunks using the chunksize option in read_csv.

• Thanks for the reply. That works well, except all the second values are zero. Also what does 'squeeze' do ? – Shane Nov 23 '17 at 11:43
• I didn't see that there are more than one value... if there is only one column squeeze will return a pandas Series instead of a DataFrame. If you have multiple columns you can drop that argument. – oW_ Nov 27 '17 at 16:23

Welcome! I think you asked in the right place.

If you have familiarity with Python, you might look at the numpy and Pandas libraries. Numpy implements fast array and matrix manipulations, and Pandas arranges numpy objects into tables. Along with scipy, they make the basis of the Python numerical computing stack.

Without more detail it's difficult to share a code example that'll do what you want, but if you run into any issues feel free to ask.

Edit: if your dataset is REALLY big and won't fit in memory, there are ways around that too, just specify.

• Thanks for reply, it been a few year since I've used python so i'll be a bit rusty but I'll give it a shot. I've added some data examples above. – Shane Nov 22 '17 at 10:29
• I don't think it doesn't matter what program(ming language) you use. You only need a couple of things: Being able to load .csv files (without the header) and processing it. I think python is ideal for this. You make a script that loads all the files (maybe months separately), converts them into a (numpy) array and just do a check like array > value. Although you have around 60*60*24*365 datapoints, this won't be a problem, you can always split it up. – Laurens Meeus Nov 22 '17 at 10:38