Adding unique labels to tabbed over rows

user_11626289 0
Viewed 63

I have a text file with some rows shifted over one tab, signifying that they are a subcategory of the main row. I need to label each row with a unique label signifying which "group" or category they belong to in order to do statistical analysis on each category.

I'm not too sure how to automate this in Python, as there are thousands of rows in the file. I am unable to convert the file into a csv without first shifting over all the indented rows, which would obviously lose any ability to tell which category each row falls into.

EDIT:
Here is a screenshot of a sample text file that shows what I am talking about visually.

The first 3 rows would have group label "1", the next 2 rows would have label "2", and the last 2 rows would have labels "3" and "4" respectively.

pandas dataframe csv python
3 Answers

IIUC, here is a relatively simple approach using Pandas and itertools.chain:

import pandas as pd
import numpy as np
import io, re, itertools

data = '''
ratio,  weight, flags,  initialTime,    finalTime
15,     0.44,   3,      300500,         400000
        42,     .31,    2,      900432,         100304
        41,     .2,     7,      192834,         200048
17,     0.234,  2,      2019481,        2182350
        8,      .1,     9,      1092834,        1190924
8,      0.241,  2,      2115120,        2159009
10,     0.092,  9,      2011234,        5001234
'''

data = re.sub(r'\t+', '*', data)
df = pd.read_csv(io.StringIO(data))

df.columns = [i.strip('*') for i in df.columns]
df.loc[df['ratio'].str.contains(r'\*'), 'ratio'] = np.nan
df['ratio'] = df['ratio'].ffill()
group_numbers = itertools.chain.from_iterable([[i+1]*v.shape[0] for i, (name,v) in enumerate(df.groupby('ratio', sort=False))])
df['ratio'] = list(group_numbers)
df = df.replace(r'\*', '', regex=True).set_index('ratio')

Yields:

      weight  flags initialTime finalTime
ratio                                    
1       0.44      3      300500    400000
1        .31      2      900432    100304
1         .2      7      192834    200048
2      0.234      2     2019481   2182350
2         .1      9     1092834   1190924
3      0.241      2     2115120   2159009
4      0.092      9     2011234   5001234
user_81465560

this is not the best way to do it, but it should work. First of all you need to receive all data from file. Let just open file and read all data line by line to string called data:

with open("your_text_file.txt", "r") as f:
    data = f.read().split('\n')

To simulate you file I pretended that all data is already stored in data variable:

data = """ratio, weight, flags, initialTime, finalTime
15,    0.44,    3,    300500,    400000
    42,    0.31,   2,   900432,   100304
22,    1.31,   3,   200432,   100304
    52,    0.11,   4,   922432,   111304 """
group = 0
data = data.split('\n')
#insert name group in firts line of your data
data[0] = f"group, {data[0]}"
for count,items in enumerate(data[1:]):
    #if line do not start with tab increment group number by 1
    if not items.startswith("    "):
        group += 1
    #insert first raw as group
    items = f"{group}, {items}"
    #deleta all ugly tabs
    items = ' '.join(items.split())
    #rewrite dataline with new beatiful string
    data[count+1] = items
#now create csv file with beautiful data
with open("new_file.csv", "w") as f:
    for items in data:
        f.write(f"{items}\n")

So, my first data looks like this:

ratio, weight, flags, initialTime, finalTime
15,    0.44,    3,    300500,    400000
    42,    0.31,   2,   900432,   100304
22,    1.31,   3,   200432,   100304
    52,    0.11,   4,   922432,   111304

and after running script like this:

group, ratio, weight, flags, initialTime, finalTime
1, 15, 0.44, 3, 300500, 400000
1, 42, 0.31, 2, 900432, 100304
2, 22, 1.31, 3, 200432, 100304
2, 52, 0.11, 4, 922432, 111304

Hope, this is what you need

user_117158300

You can use numpy genfromtext and a generator to add a new field, if you want to build a dataframe.

def add_group(fd):
    """generator that prepends each line with a group field (returns byte strings)"""
    digit = re.compile(r'^\d')
    line = next(fd)                     # process header line
    yield('group,'+line).encode()
    group=0
    for line in fd:
        if digit.match(line):           # increment group when first char is a digit
            group += 1
        yield "{},{}".format(group, line).encode()

df = pd.DataFrame(np.genfromtxt(add_group(open('file.txt')), delimiter=',', names=True,
                  autostrip=True, dtype=None)

If you only want to build a csv, it is even simpler:

with open('file.txt') as fd, open('file.csv', 'w') as fdout):
    digit = re.compile(r'^\d')
    fdout.write('group,'+next(fd))     # process header line
    group = 0
    for line in fd:
        if digit.match(line):           # increment group when first char is a digit
            group += 1
        fdout.write("{},{}".format(group, line).encode())
user_35452730
Related