Search pandas series for value and split series at that value
Question: Python 3.3.3 Pandas 0.12.0

I have a single column .csv file with hundreds of float values separated by an
arbitrary string (the string contains letters edit: _and will vary run to
run_). I'm a pandas beginner, hoping to find a way to load that .csv file and
split the float values into two columns at the level of that string.

I'm so stuck at the first part (searching for the string) that I haven't yet
been able to work on the second, which I thought should be much easier.

So far, I've been trying to use `raw = pandas.read_csv('myfile.csv',
squeeze=True)`, then something like `raw.str.findall('[a-z]')`, but I'm not
having much luck. I'd really appreciate if someone could lend a hand. I'm
planning to use this process on a number of similar .csv files, so I'd hope to
find a fairly automated way of performing the task.

Example input.csv:

    
    
    123.4932
    239.348
    912.098098989
    49391.1093
    ....
    This is a fake string that splits the data.
    ....
    1323.4942
    2445.34223
    914432.4
    495391.1093090
    

Desired eventual DataFrame:

    
    
    Column A         Column B
    123.4932         1323.4942
    239.348          2445.34223
    912.098098989    914432.4
    49391.1093       495391.1093090
    ...              ...
    

Thanks again if you can point me in the right direction.

* * *

20131123 EDIT: Thank you for the responses thus far. Updated to reflect that
the splitting string will not remain constant, hence my statement that I'd
been trying to find a solution employing a regex `raw.str.findall('[a-z]')`
instead of using `.contains`.

My solution at this point is to just read the .csv file and split with `re`,
accumulate into lists, and load those into pandas.

    
    
    import pandas as pd
    import re
    
    raw = open('myfile.csv', 'r').read().split('\n')
    df = pd.DataFrame()
    keeper = []
    counter = 0
    
    # Iterate through the rows. Consecutive rows that can be made into float are accumulated.
    for row in raw:
        try:
            keeper.append(float(row))
        except:
            if keeper:
                df = pd.concat([df, pd.DataFrame(keeper, columns = [counter] )], axis = 1)
                counter += 1            
            keeper = []
    
    # Get the last column, assuming the file hasn't ended on a line
    # that will trigger the exception in the above loop.
    if keeper:
        df = pd.concat([df, pd.DataFrame(keeper, columns = [counter] )], axis = 1)
    
    df.describe()
    

Thank you for any further suggestions.


Answer: If you know you only have two columns, then you could do something like

    
    
    >>> ser = pd.read_csv("colsplit.csv", header=None, squeeze=True)
    >>> split_at = ser.str.contains("fake string that splits").idxmax()
    >>> parts = [ser[:split_at], ser[split_at+1:]]
    >>> parts = [part.reset_index(drop=True) for part in parts]
    >>> df = pd.concat(parts, axis=1)
    >>> df.columns = ["Column A", "Column B"]
    >>> df
            Column A            Column B
    0       123.4932                ....
    1        239.348           1323.4942
    2  912.098098989          2445.34223
    3     49391.1093            914432.4
    4           ....      495391.1093090
    5            NaN  extra test element
    

If you have an arbitrary number of places to split at, then you can use a
boolean Series/shift/cumsum/groupby pattern, but if you can get away without
it, so much the better.

(PS: I'm sure there's a better way than `idxmax`, but for the life of me I
can't remember the idiom to find the first True right now.
`split_at[split_at].index[0]` would do it, but I'm not sure that's much
better.)

