Python numpy split a csv file by the values of a string column

Question

I have 5000 rows of data that looks like the following in a csv file, I would like to group by the last column 6 (ie. A, B) using numpy arrays, as I would be plotting data in each group afterwards.

Title
Date, Time, Value1, Value2, Value3, Value4, Value5
,, Unit1, Unit2, Unit3,,
2012-04-02,00:00, 85.5333333333333, 4.87666666666667,    8.96,  323.27,A
2012-04-02,00:30, 196.5, 5.49,    8.42,  323.15,B
2012-04-02,01:00, 68.2, 4.47,    7.83,  325.30,A
2012-04-02,01:30, 320.9, 6.77333333333333,    8.05,  326.63,B

I had to specify dtype=None when I load the data with np.genfromtxt, or else the A term becomes NaN How to use numpy.genfromtxt when first column is string and the remaining columns are numbers?

I am trying to use itertools groupby to return all the values based on the last column, mentioned here: How do I use Python's itertools.groupby()? But first, I would need to sort the numpy array.

I attempted to use advance indexing, by splicing the sixth column and sorting it Python (Numpy) array sorting Ie. v[v[:,0].argsort()]

However, here is a link that mentions numpy will treat my record as a 1D array of my dtype (which that was set to none) and I ran into the same index error trying to sort this: Numpy Array Column Slicing Produces IndexError: invalid index Exception

Questions:

1) How can I split the numpy array up using groupby based on column 6’s string values in order to plot them separately?

2) It would also be nice to be able to skiprows such that I can skip the first (title) and third row (unit) and leave the the second row (column heading) and data. Anyone knows how to do that easily with the options available?

This is the script I have so far, :

import numpy as np
from matplotlib import pyplot as plt
from itertools import groupby
import csv

regression_data_dp1 = np.genfromtxt(“file.csv”, delimiter=',', skiprows=3, dtype=None)

sortindex = regression_data_dp1[:,6]

#Error is hit at this step:
#    sortindex = regression_data_dp1[:,6]
#IndexError: invalid index

regression_data_dp1_sorted = regression_data_dp1[ regression_data_dp1(:,column_WRF_wind_direction).argsort()]

for key, group in groupby(regression_data_dp1, lambda x: x[0]):
    print key

    with open(“file_" + key.strip() + ".csv", 'w') as data_file:
        wr=csv.writer(data_file, quoting=csv.QUOTE_ALL)
        for item in (group):            
            wr.writerow(item)

What is Title? You have more headers than data fields; is this what the data file looks like? — dawg
– dawg, Commented Jul 10, 2013 at 1:17
@drewk Title is the just a description of the file ie. "This is a report of ... etc". The second row is the heading, and there are 7 headings. Next row are the unit. Then the data has 7 columns as well. — frank
– frank, Commented Jul 11, 2013 at 0:46

Jeff Tratner · Accepted Answer · 2013-07-10 01:33:36Z

4

For the sake of an example, let's make your csv file much simpler.

from StringIO import StringIO
import numpy as np
import itertools

data = StringIO("""
Col1,Col2,Col3
1,2,A
2,3,B
8,7,A
""".strip())
arrays = np.genfromtxt(data, dtype=object, delimiter=',', skip_header=1)
sorted_arrays = arrays[np.argsort(arrays[:, 2])] # now it's sorted - yeehaw!

for k, group in itertools.groupby(arrays, lambda x: x[2]):
    # do something

As I have said in other places, make your life easier and use pandas to load in the data and group (make sure you run data.seek(0) first):

import pandas as pd

df = pd.read_csv(data)
for k, group in df.groupby(["Col3"]):
    # do something with group

Plus you can even do plotting with the dataframe itself.

answered Jul 10, 2013 at 1:33

Jeff Tratner

17.3k4 gold badges50 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

frank Over a year ago

Thanks for providing a simpler example, I added in dtype=object into my initial example and the sort now works. It wasn't all that clear from numpy.dtype documentation that this is necessary for sort though. Right now, I am ok with numpy, but thanks for sharing about panda.

Jeff Tratner Over a year ago

@user2412730 you need dtype=object to prevent strings from being converted to NaN. Sorting doesn't depend on having dtype=object, it just depends on the values actually being different (which doesn't happen if they are all NaN)

frank Over a year ago

Without specifying dtype, the string showed up as NaN. I tried specifying dtype=None earlier, and the strings didn't appear as NaN but as actual strings. However, the sorting hit the Index error until I changed dtype=object.

unutbu · Accepted Answer · 2013-07-10 01:37:50Z

2

Instead of sorting the rows of the array, and using itertools.groupby you could use group = arr[arr['f6']==key] to select the rows with the same key:

import numpy as np
import csv

def load_csv(filename):
    with open(filename) as f:
        next(f)
        header = [item.strip() for item in next(f).split(',')]
    arr = np.genfromtxt("file.csv", delimiter=',', skiprows=3, dtype=None)
    arr.dtype.names = header
    return arr

arr = load_csv("file.csv")
keys = np.unique(arr['Value5'])

for key in keys:
    group = arr[arr['Value5']==key]
    filename = 'file_{}.csv' .format(key.strip())
    with open(filename, 'w') as data_file:
        wr = csv.writer(data_file, quoting=csv.QUOTE_ALL)
        wr.writerows(group)

There is no direct facility to tell np.genfromtxt to use the second line as a header. The simplest approach would probably be to open the file, slurp the second line into a list of headers, close the file, then use genfromtxt to load the array and use arr.dtype.names = header to give the structured array the desired column names.

edited Jul 10, 2013 at 1:37

answered Jul 10, 2013 at 1:20

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

1 Comment

frank Over a year ago

Thanks, I like this grouping alternative, as it allows me to immediately obtain a column from the group for plotting, which I couldn't quite do with groupby. ie. from matplotlib import pyplot as plt for key in keys: … value3 = group['Value3'] value4 = group['Value4'] plt.scatter(value3, value 4) plt.show()

Collectives™ on Stack Overflow

Python numpy split a csv file by the values of a string column

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related