0

The snippet below works with an old data format however, I am trying to read an updated datasource.txt with an additional data field. I tried regex but cant seem to have it working.

data = {}
with open('datasource.txt') as f:
    for line in f:
        parts = line.split()
        team, a, b, c = parts if len(parts) == 5 else parts[:-1] + ['($0)'] + parts[-1]
        data[team] = tuple(map(sum, zip((int(a), float(b.replace(',', '')), float(c[2:-1].replace(',', ''))), data.get(team, (0, 0, 0)))))

data = {t: (a, b, c) for a, b, c, t in reversed(sorted((a, b, c, t) for t, (a, b, c) in data.items()))}

for team, (a, b, c) in data.items():
    print(f'{team:8} {a:4} {b:,} (${c:,})')

datasource.txt

alpha 1 54,00.01 ABC DSW2S
bravo 3 500,000.00 ACDEF
charlie 1 27,722.29 ($250.45) DGAS-CAS
charlie 10 252,336,733.383 ($492.06) DGAS-CAS
delta 2 11 ($10) SWSDSASS-CCSSW
echo 5 143,299.00 ($101) ACS34S1
echo 8 145,300 ($125.01) ACS34S1
falcon 3 0.1234 DSS2SFS3
falcon 5 9.19 DSS2SFS3
lima 6 45.00181 ($38.9) FGF5GGD-DDD
romeo 12 980 ASDS SSSS SDSD

Expected Output:

echo       13 288,599.0 ($226.01)            ACS34S1
romeo      12 980.0 ($0.0)                   ASDS SSSS SDSD    
charlie    11 252,364,455.67299998 ($742.51) DGAS-CAS
falcon      8 9.3134 ($0.0)                  DSS2SFS3   
lima        6 45.00181 ($38.9)               FGF5GGD-DDD
bravo       3 500,000.0 ($0.0)               ACDEF    
delta       2 11.0 ($10.0)                   SWSDSASS-CCSSW
alpha       1 54,000.01 ($0.0)               ABC DSW2S
rbutrnz
  • 383
  • 4
  • 20

2 Answers2

2

I think one problem is when: (say len(parts) = 5)

team, a, b, c = parts

In this, there's only 4 variables on the left, but, since len(parts) is 5, parts has 5 elements. This is a mismatch as you cannot fit 5 values into 4 variables. So, an additional variable is required there.

pu239
  • 707
  • 7
  • 17
  • in the sourcedata.txt file, some lines have 3 item to the left and some have 4. – rbutrnz Aug 15 '21 at 08:15
  • I'm saying you have a problem on the lines which have 5 elements in total. I can't figure out what 'to the left' means, but if you want 5 'columns' in your output 'table', you need 5 variables – pu239 Aug 15 '21 at 08:19
2

You can do that with pandas.

  • First I have done some preprocessing to get the data to a stable format like converting to int/floats, adding $(0), joining the last column values etc.
  • Then used pandas to groupby and sum up the values.
import pandas as pd

dl = []
with open('text.txt') as f:
    for line in f:
        parts = line.split()
        # Cleaning data here.. Conversions to int/float etc,
        if not parts[3][:2].startswith('($'):
            parts.insert(3,'0')
        if len(parts) > 5:
            temp = ' '.join(parts[4:])
            parts = parts[:4] + [temp]
        parts[1] = int(parts[1])
        parts[2] = float(parts[2].replace(',', ''))
        parts[3] = float(parts[3].strip('($)'))
        
        dl.append(parts)
    
headers = ['col1', 'col2', 'col3', 'col4', 'col5']
df = pd.DataFrame(dl,columns=headers)
df = df.groupby(['col1','col5']).sum().reset_index()
df = df.sort_values('col2',ascending=False)
df['col4'] =  '($' + df['col4'].astype(str) + ')'
df = df[headers]
print(df)


      col1  col2          col3       col4            col5
4     echo    13  2.885990e+05  ($226.01)         ACS34S1
7    romeo    12  9.800000e+02     ($0.0)  ASDS SSSS SDSD
2  charlie    11  2.523645e+08  ($742.51)        DGAS-CAS
5   falcon     8  9.313400e+00     ($0.0)        DSS2SFS3
6     lima     6  4.500181e+01    ($38.9)     FGF5GGD-DDD
1    bravo     3  5.000000e+05     ($0.0)           ACDEF
3    delta     2  1.100000e+01    ($10.0)  SWSDSASS-CCSSW
0    alpha     1  5.400010e+03     ($0.0)       ABC DSW2S
Ram
  • 4,724
  • 2
  • 14
  • 22