# Adding Column Values Using Pattern Match

**URL:** <https://community.unix.com/t/adding-column-values-using-pattern-match/326084>\
**Category:** Shell Programming and Scripting\
**Created:** [March 22, 2013, 1:23am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084 "2013-03-22T01:23:38Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![angshuman](https://community.unix.com/letter_avatar/angshuman/32/5_5575768a8748004e209b776fc1b2916d.png) [@angshuman](https://community.unix.com/u/angshuman)\
**Post date:** [March 22, 2013, 1:23am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/1 "2013-03-22T01:23:38Z")

</div>

Hi All,

I have a file with data as below:

```auto
A,FILE1_MYFILE_20130309_1038,80,25.60
B,FILE1_MYFILE_20130309_1038,24290,18543.38
C,FILE1_dsc_dlk_MYFILE_20130309_1038,3,10.10
A,FILE2_MYFILE_20130310_1039,85,110.10
B,FILE2_MYFILE_20130310_1039,10,12.10
C,FILE2_err_dlk_MYFILE_20130310_1039,3,10.10

```

I am using following command to sum values of 3 column based on the value in second column.

```auto
for i in `cat OUTPUT_FILE|awk -F"," '{print $2}'|sort -u`;do grep $i OUTPUT_FILE|awk -F"," '{c+=$3}END{print $2".edr""|"c}';done

```

However the above command will definitely exclude third row as the value of second column in third row is not matching with that for other two rows. However, I want to include the third rwo as well. Hence I need to perform a pattern matching in the underlined part so that my output looks as below:

```auto
FILE1_MYFILE_20130309_1038,24373
FILE2_MYFILE_20130310_1039,98

```

How do I perform a pattern matching here.

Thanks and Regards  
Angshuman

---

<div class="post-metadata">

**Author:** ![guruprasadpr](https://community.unix.com/user_avatar/community.unix.com/guruprasadpr/32/1542_2.png) [@guruprasadpr](https://community.unix.com/u/guruprasadpr)\
**Post date:** [March 22, 2013, 1:42am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/2 "2013-03-22T01:42:53Z")

</div>

One way:

```nohighlight
awk -F, '{x=$2;sub(/_.*/,"",x);if(!a[x])a[x]=$2;b[x]+=$3;}END{for (i in a)print a","b;}' file

```

Guru.

---

<div class="post-metadata">

**Author:** ![angshuman](https://community.unix.com/letter_avatar/angshuman/32/5_5575768a8748004e209b776fc1b2916d.png) [@angshuman](https://community.unix.com/u/angshuman)\
**Post date:** [March 22, 2013, 3:45am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/3 "2013-03-22T03:45:57Z")

</div>

Hi Guru,

Thank you for your reply.

Why is it not working agaisnt following data set. Can you please explain a bit on how is the sub function working in awk.

```nohighlight
SUSPREL,ICP_MED_DEL_SEM_20130309_1038,80,25.60
REL,ICP_MED_DEL_SEM_20130309_1038,24290,18543.38
ERROR_ALLRATE_DSC_DLK,ICP_dsc_dlk_MED_DEL_SEM_20130309_1038,3,10.10
SUSPREL,ICP_MED_DEL_SEM_20130309_1039,80,25.60
REL,ICP_MED_DEL_SEM_20130309_1039,24290,18543.38
ERROR_ALLRATE_DSC_DLK,ICP_dsc_dlk_MED_DEL_SEM_20130309_1039,3,10.10

```

Another point is that the data between ICP and MED can be of any leth.

---------- Post updated at 01:15 PM ---------- Previous update was at 12:30 PM ----------

Hi Rudic,

Thank you for your reply. However, the output that you have showed is not what I am expecting

Let me explain a bit more. If you check the value in column 2, you will see that the values are like FILE1\_MYFILE\_20130309\_1038, FILE1\_MYFILE\_20130309\_1038, FILE1\_dsc\_dlk\_MYFILE\_20130309\_1038. These are actually from the same group. The only difference is that there are some additional values between FILE1 and MYFILE. Hence I want to add all the corresponsing column 3 values. If this is not possible in awk, I was thinking if I remove the values between FILE1 and MYFILE first using awk and then add the values in column 3. What is your input on that and how do I remove those values?

Thanks and Regards  
Angshuman

---

<div class="post-metadata">

**Author:** ![panyam](https://community.unix.com/letter_avatar/panyam/32/5_5575768a8748004e209b776fc1b2916d.png) [@panyam](https://community.unix.com/u/panyam)\
**Post date:** [March 22, 2013, 4:08am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/5 "2013-03-22T04:08:10Z")

</div>

A crude approach

```nohighlight
awk -F"," '{a["ICP_"substr($2,index($2,"MED"))]+=$3} END{ for(i in a) print i,a}' OFS="," file

```

---

<div class="post-metadata">

**Author:** ![RudiC](https://community.unix.com/letter_avatar/rudic/32/5_5575768a8748004e209b776fc1b2916d.png) [@RudiC](https://community.unix.com/u/RudiC)\
**Post date:** [March 22, 2013, 4:26am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/6 "2013-03-22T04:26:24Z")

</div>

> [@angshuman](#):
>
> . . .  
> Hi Rudic,
> 
> Thank you for your reply. However, the output that you have showed is not what I am expecting
> 
> Let me explain a bit more. If you check the value in column 2, you will see that the values are like FILE1\_MYFILE\_20130309\_1038, FILE1\_MYFILE\_20130309\_1038, FILE1\_dsc\_dlk\_MYFILE\_20130309\_1038. These are actually from the same group. The only difference is that there are some additional values between FILE1 and MYFILE. Hence I want to add all the corresponsing column 3 values. If this is not possible in awk, I was thinking if I remove the values between FILE1 and MYFILE first using awk and then add the values in column 3. What is your input on that and how do I remove those values?
> 
> Thanks and Regards  
> Angshuman

Hi Angshuman,

I figured that out and deleted my post, as it was irrelevant. In order to do what you want, we need to know what parts of the file names are to be retained, either by counting the subfields between separators (here: "\_"), or by identifying static substrings (here: FILEn and MYFILE). If you can't give any hint on anchoring the to-be-deleted substring, things become difficult.

---

<div class="post-metadata">

**Author:** ![angshuman](https://community.unix.com/letter_avatar/angshuman/32/5_5575768a8748004e209b776fc1b2916d.png) [@angshuman](https://community.unix.com/u/angshuman)\
**Post date:** [March 22, 2013, 4:34am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/7 "2013-03-22T04:34:31Z")

</div>

Hi Panyam,

Sorry your code does not provide the output that I am expecting. I get the following output:

```nohighlight
MED_DEL_SEM_20130309_1038 24373
MED_DEL_SEM_20130309_1039 24373

```

and my expected result is

```nohighlight
ICP_MED_DEL_SEM_20130309_1038,24373
ICP_MED_DEL_SEM_20130309_1039,24373

```

---

<div class="post-metadata">

**Author:** ![panyam](https://community.unix.com/letter_avatar/panyam/32/5_5575768a8748004e209b776fc1b2916d.png) [@panyam](https://community.unix.com/u/panyam)\
**Post date:** [March 22, 2013, 4:40am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/8 "2013-03-22T04:40:01Z")

</div>

> [@angshuman](#):
>
> Hi Panyam,
> 
> Sorry your code does not provide the output that I am expecting. I get the following output:
> 
> ```plaintext
> MED_DEL_SEM_20130309_1038 24373
> MED_DEL_SEM_20130309_1039 24373
> 
> ```
> 
> and my expected result is
> 
> ```plaintext
> ICP_MED_DEL_SEM_20130309_1038,24373
> ICP_MED_DEL_SEM_20130309_1039,24373
> 
> ```

Check my earlier post now. I edited it.

---

<div class="post-metadata">

**Author:** ![RudiC](https://community.unix.com/letter_avatar/rudic/32/5_5575768a8748004e209b776fc1b2916d.png) [@RudiC](https://community.unix.com/u/RudiC)\
**Post date:** [March 22, 2013, 9:26am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/9 "2013-03-22T09:26:07Z")

</div>

```nohighlight
$ awk '{sub (/ICP_.*MED/, "ICP_MED", $2); SUM[$2]+=$3}END {for (X in SUM) print X, SUM[X]}' FS="," OFS="," file
ICP_MED_DEL_SEM_20130309_1039,24373
ICP_MED_DEL_SEM_20130309_1038,24373

```

---

<div class="post-metadata">

**Author:** ![avig](https://community.unix.com/letter_avatar/avig/32/5_5575768a8748004e209b776fc1b2916d.png) [@avig](https://community.unix.com/u/avig)\
**Post date:** [March 22, 2013, 11:16am UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/10 "2013-03-22T11:16:46Z")

</div>

apologies for the same

---

<div class="post-metadata">

**Author:** ![panyam](https://community.unix.com/letter_avatar/panyam/32/5_5575768a8748004e209b776fc1b2916d.png) [@panyam](https://community.unix.com/u/panyam)\
**Post date:** [March 22, 2013, 12:35pm UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/11 "2013-03-22T12:35:58Z")

</div>

Hi Avig,

Don't hijack others post. Please open a new request.

Regards,

---

<div class="post-metadata">

**Author:** ![alister](https://community.unix.com/letter_avatar/alister/32/5_5575768a8748004e209b776fc1b2916d.png) [@alister](https://community.unix.com/u/alister)\
**Post date:** [March 22, 2013, 12:50pm UTC](https://community.unix.com/t/adding-column-values-using-pattern-match/326084/12 "2013-03-22T12:50:34Z")

</div>

avig,

In addition to what panyam said, do not post duplicates hoping for a faster response. Do yourself a favor and read the forum rules, because you have yet to post in a way that doesn't violate them: [Simple rules of the UNIX.COM forums](http://www.unix.com/unix-dummies-questions-answers/2971-simple-rules-unix-com-forums.html)

I urge members to not reward thread hijacks with helpful responses.

Regards,  
Alister