Adding a last column to compute change in percentage

Question

The script below is messy and does not work. I am trying to make this work with my sampledata.txt file. I am currently exploring pandas on this. Any help is very very welcome. Basically, I am trying to add a last column computed percentage and also, if possible a faster approach.

sampledata.txt #-- file content and structure

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

Expected Output: #-- last column for percentage computation

      col1  col2          col3    col4            col5    col6
4     echo    13  2.885990e+05  226.01         ACS34S1   60.0%   #-- (5 + 8) = 13
7    romeo    12  9.800000e+02    0.00  ASDS SSSS SDSD      0%
2  charlie    11  2.523645e+08  742.51        DGAS-CAS  900.0%   #-- (1 + 10) = 11
5   falcon     8  9.313400e+00    0.00        DSS2SFS3  66.67%   #-- (3 + 5) = 8
6     lima     6  4.500181e+01   38.90     FGF5GGD-DDD      0%
1    bravo     3  5.000000e+05    0.00           ACDEF      0%
3    delta     2  1.100000e+01   10.00  SWSDSASS-CCSSW      0%
0    alpha     1  5.400010e+03    0.00       ABC DSW2S      0%

Code:

import pandas as pd
dl = []
with open('sampledata.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['sampledata.txt'] = df['col3'].str.extract('.*$(.*)$.*')          #Extract whats inside the brackets.
df['sampledata.txt'].replace('\$', '', regex=True, inplace=True)
df['col3'] = df['col3'].str.replace(r"(\s*$.*$)|,", "", regex=True)  #Extract whats outside the brackets
df.rename(columns={'col4': 'col5', 'within_brackets': 'col4'}, inplace=True)
df[['col3', 'col4']] = df[['col3', 'col4']].astype(float)

df = df.groupby(['col1', 'col5']).agg(col2 = pd.NamedAgg(column="col2", aggfunc="sum"),
                                      col3 = pd.NamedAgg(column="col3", aggfunc="sum"),
                                      col4 = pd.NamedAgg(column="col4", aggfunc="sum"),
                                      col6 = pd.NamedAgg(column="col2", aggfunc=pd.Series.pct_change)).reset_index()
df['col6'].fillna(0, inplace=True)
#print df here and you will get to know what output looks like till now.
df['col6'].fillna(0, inplace=True)
df['col6'] = df['col6'].apply(lambda x: f"{str(round(x[-1], 4) * 100)}%" if isinstance(x, np.ndarray) else f"{round(x, 4) * 100}%")
df = df[['col1', 'col2', 'col3', 'col4', 'col5', 'col6']]
df.sort_values(by=['col2'], ascending=False, inplace=True)
print(df)

user_16872662 · Answer

As a warning I know there has to be faster ways, but I got this to work for me. I just iterated through the rows and found matching col1, deleting each row to not get both 5,8 and 8,5. I just calculated the percent differences for the matches and wrote to a dictionary.

I also was having issues with the indexing in the grouping step, so I only grouped by the col1 and then took col5 as the first value.

I then wrote a list to become the new column ordered by the grouped df and then added the column.

I wasn't sure what these four lines were doing

df['sampledata.txt'] = df['col3'].str.extract('.*$(.*)$.*')          #Extract whats inside the brackets.
df['sampledata.txt'].replace('\$', '', regex=True, inplace=True)
df['col3'] = df['col3'].str.replace(r"(\s*$.*$)|,", "", regex=True)  #Extract whats outside the brackets
df.rename(columns={'col4': 'col5', 'within_brackets': 'col4'}, inplace=True)

so they were commented out for me. I copied the example, saved it as a txt, and it imported fine without those rows.

import pandas as pd

dl = []
with open('sampledata.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['sampledata.txt'] = df['col3'].str.extract('.*$(.*)$.*')          #Extract whats inside the brackets.
# df['sampledata.txt'].replace('\$', '', regex=True, inplace=True)
# df['col3'] = df['col3'].str.replace(r"(\s*$.*$)|,", "", regex=True)  #Extract whats outside the brackets
# df.rename(columns={'col4': 'col5', 'within_brackets': 'col4'}, inplace=True)
df[['col3', 'col4']] = df[['col3', 'col4']].astype(float)

df1 = df
d= {}
# Pick a row
for index1, row in df.iterrows():
    # Drop that row
    df1= df1.drop(index = index1)
    # Look for match in remaining rows
    for index2, row2 in df1.iterrows():
        if row["col1"] == row2["col1"]:
            if row["col2"]>row2["col2"]:
                d[row["col1"]] =round((row["col2"]-row2["col2"])/row2["col2"]*100,2)
            elif row["col2"]

Adding a last column to compute change in percentage

1 Answers