Sponsored Content
Top Forums UNIX for Beginners Questions & Answers Merging rows based on same ID in First column. Post 303037774 by anjaliANJALI on Tuesday 13th of August 2019 08:34:25 AM
Old 08-13-2019
Merging rows based on same ID in First column.

Hellow,


I have a tab-delimited file with 3 columns :


Code:
BINPACKER.13259.1.p2    SSF48239    
BINPACKER.13259.1.p2    PF13243    
BINPACKER.13259.1.p2    G3DSA:1.50.10.20
BINPACKER.13259.2.p2    SSF48239    
BINPACKER.13259.2.p2    PF13243    
BINPACKER.13259.2.p2    G3DSA:1.50.10.20
BINPACKER.31705.4.p1    PF00176    GO:0005524
BINPACKER.31705.4.p1    SM00490    
BINPACKER.31705.4.p1    SSF52540    
BINPACKER.31705.4.p1    G3DSA:3.40.50.300
BINPACKER.31705.4.p1    mobidb-lite
BINPACKER.31705.4.p1    SM00487    
BINPACKER.31705.4.p1    PS51194    
BINPACKER.31705.4.p1    cd00079    
BINPACKER.31705.4.p1    PF00271    
BINPACKER.31705.4.p1    PS51192    
BINPACKER.31705.4.p1    cd00046
BINPACKER.31705.4.p1    G3DSA:3.40.50.10810    
BINPACKER.31705.4.p1    SSF52540    
BINPACKER.9719.7.p1    PF00443    GO:0016579|GO:0036459
BINPACKER.9719.7.p1    SSF57850
BINPACKER.9719.7.p1    PS50235    
BINPACKER.9719.7.p1    mobidb-lite
BINPACKER.9719.7.p1    PF02148    GO:0008270
BINPACKER.9719.7.p1    SSF54001    
BINPACKER.9719.7.p1    mobidb-lite
BINPACKER.9719.7.p1    cd02669    GO:0000245|GO:0006397
BINPACKER.9719.7.p1    PS50271    GO:0008270
BINPACKER.9719.7.p1    SM00290    GO:0008270
BINPACKER.9719.7.p1    mobidb-lite
BINPACKER.9719.7.p1    mobidb-lite
BINPACKER.9719.7.p1    G3DSA:3.30.40.10    
BINPACKER.9719.7.p1    G3DSA:3.90.70.10
BINPACKER.937.4.p1    PS51032    GO:0003700|GO:0006355
BINPACKER.937.4.p1    PIRSF038123    GO:0003700
BINPACKER.937.4.p1    cd00018    GO:0003700|GO:0006355
BINPACKER.937.4.p1    SSF54171    GO:0003677
BINPACKER.937.4.p1    G3DSA:3.30.730.10    GO:0003700|GO:0006355
BINPACKER.937.4.p1    PR00367    GO:0003700|GO:0006355

I want to mege the rows based on first column with same ID. In column 2, I want only ID starting with PF and in 3rd column, want to concatenate all GO term seperated with comma. in each case there should be no duplicate eg:


Code:
BINPACKER.13259.1.p2    PF13243    NA
BINPACKER.13259.2.p2  PF13243                    NA
 BINPACKER.31705.4.p1    PF00176,PF00271    GO:0005524
BINPACKER.9719.7.p1    PF00443,PF02148    GO:0016579,GO:0036459,GO:0008270,GO:0000245,GO:0006397
BINPACKER.937.4.p1    NA    GO:0003700,GO:0006355,GO:0003677

Thankyou

Last edited by anjaliANJALI; 08-13-2019 at 01:43 PM..
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

merging column from two files based on identifier

Hi, I have two files consisting of two columns. So I want to merge column 2 if column 1 is the same. So heres an example of what I mean. FILE1 driver 444 car 333 hat 222 FILE2 driver 333 car 666 hat 999 So I want to merge the column 2's together so... (4 Replies)
Discussion started by: phil_heath
4 Replies

2. Shell Programming and Scripting

column to rows based on another column...

Guys, i have a file in below format where the barcode's are uniq per site but could be repeated for different site. so i want to convert the site column to rows based on the barcode's as below output. your help is appreciated!!! input: SITE BARCODE QTY SP CP 10001 6281103890017 10 50 48... (5 Replies)
Discussion started by: malcomex999
5 Replies

3. Shell Programming and Scripting

Merging 2 files based on a common column

Hi All, I do have 2 files file 1 has 4 tab delimited columns 234 a c dfgyu 294 b g fih 302 c h jzh 328 z c san 597 f g son File 2 has 2 tab delimted columns 234 23 302 24 597 24 I want to merge file 2 with file 1 based on the data common in both files which is the first column so... (6 Replies)
Discussion started by: Lucky Ali
6 Replies

4. Shell Programming and Scripting

merging two files based on first column

I had two files file1 and file2. I want a o/p file(file3) like below using first column as ref. Pls give suggestion ass join is not working as the number of lines in each file is nealry 5 C? file1 --------------------- 404000324810001 Y 404000324810004 N 404000324810008 Y 404000324810009 N... (1 Reply)
Discussion started by: p_sai_ias
1 Replies

5. Shell Programming and Scripting

Merging rows with same column 1 value

I have the following space-delimited input: 1 11.785710 117.857100 1 15 150 1 20 200 1 25 250 3 2.142855 21.428550 3 25 250 22 1.071435 10.714350 The first field is the ID number, the second field is the percentage of the total points that the person has and the third column is the number... (3 Replies)
Discussion started by: mdlloyd7
3 Replies

6. Shell Programming and Scripting

Merging columns based on one or more column in two files

I have two files. FileA.txt 30910 rs7468327 36587 rs10814410 91857 rs9408752 105797 rs1133715 146659 rs2262038 152695 rs2810979 181843 rs3008128 182129 rs3008131 192118 rs3008170 FileB.txt 30910 1.9415219673 0 36431 1.3351312477 0.0107191428 36587 1.3169171182... (2 Replies)
Discussion started by: genehunter
2 Replies

7. UNIX for Dummies Questions & Answers

merging rows into new file based on rows and first column

I have 2 files, file01= 7 columns, row unknown (but few) file02= 7 columns, row unknown (but many) now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there e.g. file 01 James|0|50|25|10|50|30... (1 Reply)
Discussion started by: A-V
1 Replies

8. UNIX for Dummies Questions & Answers

Merging lines based on one column

Hi, I have a file which I'd like to merge lines based on duplicates in one column while keeping the info for other columns. Let me simplify it by an example: File ESR1 ANASTROZOLE NA FDA_approved ESR1 CISPLATIN NA FDA_approved ESR1 DANAZOL agonist NA ESR1 EXEMESTANE NA FDA_approved... (3 Replies)
Discussion started by: JJ001
3 Replies

9. UNIX for Dummies Questions & Answers

File merging based on column patterns

Hello :) I am in this situation: Input: two tab-delimited files, `File1` and `File2`. `File2` (`$2`) has to be parsed by patterns found in `File1` (`$1`). Expected output: tab-delimited file, `File3`. `File3` has to contain the same rows as `File2`, plus the corresponding value in... (5 Replies)
Discussion started by: dovah
5 Replies

10. UNIX for Beginners Questions & Answers

Merging multiple lines into single line based on one column

I Want to merge multiple lines based on the 1st field and keep into single record. SRC File: AAA_POC_DB.TAB1 AAA_POC_DB.TAB2 AAA_POC_DB.TAB3 AAA_POC_DB.TAB4 BBB_POC_DB.TAB1 BBB_POC_DB.TAB2 CCC_POC_DB.TAB6 OUTPUT ----------------- 'AAA_POC_DB','TAB1','TAB2','TAB3','TAB4'... (10 Replies)
Discussion started by: raju2016
10 Replies
SHOREWALL-LITE(8)						  [FIXME: manual]						 SHOREWALL-LITE(8)

NAME
shorewall-lite - Administration tool for Shoreline Firewall Lite (Shorewall Lite) SYNOPSIS
shorewall-lite [trace|debug [nolock]] [-options] add interface[:host-list]... zone shorewall-lite [trace|debug [nolock]] [-options] allow address shorewall-lite [trace|debug [nolock]] [-options] clear [-f] shorewall-lite [trace|debug [nolock]] [-options] delete interface[:host-list]... zone shorewall-lite [trace|debug [nolock]] [-options] disable { interface | provider } shorewall-lite [trace|debug [nolock]] [-options] drop address shorewall-lite [trace|debug] [-options] dump [-x] [-l] [-m] shorewall-lite [trace|debug [nolock]] [-options] enable { interface | provider } shorewall-lite [trace|debug [nolock]] [-options] forget [filename] shorewall-lite [trace|debug] [-options] help shorewall-lite [trace|debug] [-options] hits [-t] shorewall-lite [trace|debug] [-options] ipcalc {address mask | address/vlsm} shorewall-lite [trace|debug] [-options] iprange address1-address2 shorewall-lite [trace|debug] [-options] iptrace iptables match expression shorewall-lite [trace|debug [nolock]] [-options] logdrop address shorewall-lite [trace|debug] [-options] logwatch [-m] [refresh-interval] shorewall-lite [trace|debug [nolock]] [-options] logreject address shorewall-lite [trace|debug] [-options] noiptrace iptables match expression shorewall-lite [trace|debug [nolock]] [-options] reject address shorewall-lite [trace|debug [nolock]] [-options] reset shorewall-lite [trace|debug [nolock]] [-options] restart [-n] [-p] [directory] shorewall-lite [trace|debug [nolock]] [-options] restore [filename] shorewall-lite [trace|debug [nolock]] [-options] save [filename] shorewall-lite [trace|debug] [-options] show [-x] [-l] [-t {filter|mangle|nat|raw|rawpost}] [[chain] chain...] shorewall-lite [trace|debug] [-options] show [-f] capabilities shorewall-lite [trace|debug] [-options] show {classifiers|connections|config|filters|ip|ipa|zones|policies|marks} shorewall-lite [trace|debug] [-options] show [-x] {mangle|nat|routing|raw|rawpost} shorewall-lite [trace|debug] [-options] show tc shorewall-lite [trace|debug] [-options] show [-m] log shorewall-lite [trace|debug [nolock]] [-options] start [-n] [-p] shorewall-lite [trace|debug [nolock]] [-options] stop shorewall-lite [trace|debug] [-options] status shorewall-lite [trace|debug] [-options] version [-a] DESCRIPTION
The shorewall-lite utility is used to control the Shoreline Firewall Lite (Shorewall Lite). OPTIONS
The trace and debug options are used for debugging. See http://www.shorewall.net/starting_and_stopping_shorewall.htm#Trace. The nolock option prevents the command from attempting to acquire the Shorewall-lite lockfile. It is useful if you need to include shorewall commands in /etc/shorewall/started. The options control the amount of output that the command produces. They consist of a sequence of the letters v and q. If the options are omitted, the amount of output is determined by the setting of the VERBOSITY parameter in shorewall.conf[1](5). Each v adds one to the effective verbosity and each q subtracts one from the effective VERBOSITY. Anternately, v may be followed immediately with one of -1,0,1,2 to specify a specify VERBOSITY. There may be no white space between v and the VERBOSITY. The options may also include the letter t which causes all progress messages to be timestamped. COMMANDS
The available commands are listed below. add Adds a list of hosts or subnets to a dynamic zone usually used with VPN's. The interface argument names an interface defined in the shorewall-interfaces[2](5) file. A host-list is comma-separated list whose elements are host or network addresses. Caution The add command is not very robust. If there are errors in the host-list, you may see a large number of error messages yet a subsequent shorewall-lite show zones command will indicate that all hosts were added. If this happens, replace add by delete and run the same command again. Then enter the correct command. allow Re-enables receipt of packets from hosts previously blacklisted by a drop, logdrop, reject, or logreject command. clear Clear will remove all rules and chains installed by Shorewall-lite. The firewall is then wide open and unprotected. Existing connections are untouched. Clear is often used to see if the firewall is causing connection problems. If -f is given, the command will be processed by the compiled script that executed the last successful start, restart or refresh command if that script exists. delete The delete command reverses the effect of an earlier add command. The interface argument names an interface defined in the shorewall-interfaces[2](5) file. A host-list is comma-separated list whose elements are a host or network address. disable Added in Shorewall 4.4.26. Disables the optional provider associated with the specified interface or provider. Where more than one provider share a single network interface, a provider name must be given. drop Causes traffic from the listed addresses to be silently dropped. dump Produces a verbose report about the firewall configuration for the purpose of problem analysis. The -x option causes actual packet and byte counts to be displayed. Without that option, these counts are abbreviated. The -m option causes any MAC addresses included in Shorewall-lite log messages to be displayed. The -l option causes the rule number for each Netfilter rule to be displayed. enable Added in Shorewall 4.4.26. Enables the optional provider associated with the specified interface or provider. Where more than one provider share a single network interface, a provider name must be given. forget Deletes /var/lib/shorewall-lite/filename and /var/lib/shorewall-lite/save. If no filename is given then the file specified by RESTOREFILE in shorewall.conf[1](5) is assumed. help Displays a syntax summary. hits Generates several reports from Shorewall-lite log messages in the current log file. If the -t option is included, the reports are restricted to log messages generated today. ipcalc Ipcalc displays the network address, broadcast address, network in CIDR notation and netmask corresponding to the input[s]. iprange Iprange decomposes the specified range of IP addresses into the equivalent list of network/host addresses. iptrace This is a low-level debugging command that causes iptables TRACE log records to be created. See iptables(8) for details. The iptables match expression must be one or more matches that may appear in both the raw table OUTPUT and raw table PREROUTING chains. The trace records are written to the kernel's log buffer with faciility = kernel and priority = warning, and they are routed from there by your logging daemon (syslogd, rsyslog, syslog-ng, ...) -- Shorewall-lite has no control over where the messages go; consult your logging daemon's documentation. logdrop Causes traffic from the listed addresses to be logged then discarded. Logging occurs at the log level specified by the BLACKLIST_LOGLEVEL setting in shorewall.conf[1] (5). logwatch Monitors the log file specified by the LOGFILE option in shorewall.conf[1](5) and produces an audible alarm when new Shorewall-lite messages are logged. The -m option causes the MAC address of each packet source to be displayed if that information is available. The refresh-interval specifies the time in seconds between screen refreshes. You can enter a negative number by preceding the number with "--" (e.g., shorewall-lite logwatch -- -30). In this case, when a packet count changes, you will be prompted to hit any key to resume screen refreshes. logreject Causes traffic from the listed addresses to be logged then rejected. Logging occurs at the log level specified by the BLACKLIST_LOGLEVEL setting in shorewall.conf[1] (5). noiptrace This is a low-level debugging command that cancels a trace started by a preceding iptrace command. The iptables match expression must be one given in the iptrace command being cancelled. reset All the packet and byte counters in the firewall are reset. restart Restart is similar to shorewall-lite start except that it assumes that the firewall is already started. Existing connections are maintained. The -n option causes Shorewall-lite to avoid updating the routing table(s). The -p option causes the connection tracking table to be flushed; the conntrack utility must be installed to use this option. restore Restore Shorewall-lite to a state saved using the shorewall-lite save command. Existing connections are maintained. The filename names a restore file in /var/lib/shorewall-lite created using shorewall-lite save; if no filename is given then Shorewall-lite will be restored from the file specified by the RESTOREFILE option in shorewall.conf[1](5). save The dynamic blacklist is stored in /var/lib/shorewall-lite/save. The state of the firewall is stored in /var/lib/shorewall-lite/filename for use by the shorewall-lite restore. If filename is not given then the state is saved in the file specified by the RESTOREFILE option in shorewall.conf[1](5). show The show command can have a number of different arguments: capabilities Displays your kernel/iptables capabilities. The -f option causes the display to be formatted as a capabilities file for use with compile -e. [ [ chain ] chain... ] The rules in each chain are displayed using the iptables -L chain -n -v command. If no chain is given, all of the chains in the filter table are displayed. The -x option is passed directly through to iptables and causes actual packet and byte counts to be displayed. Without this option, those counts are abbreviated. The -t option specifies the Netfilter table to display. The default is filter. The -l option causes the rule number for each Netfilter rule to be displayed. If the t option and the chain keyword are both omitted and any of the listed chains do not exist, a usage message is displayed. classifiers|filters Displays information about the packet classifiers defined on the system as a result of traffic shaping configuration. config Dispays distribution-specific defaults. connections Displays the IP connections currently being tracked by the firewall. ip Displays the system's IPv4 configuration. ipa Added in Shorewall 4.4.17. Displays the per-IP accounting counters (shorewall-accounting[3] (5)). log Displays the last 20 Shorewall-lite messages from the log file specified by the LOGFILE option in shorewall.conf[1](5). The -m option causes the MAC address of each packet source to be displayed if that information is available. marks Added in Shorewall 4.4.26. Displays the various fields in packet marks giving the min and max value (in both decimal and hex) and the applicable mask (in hex). nat Displays the Netfilter nat table using the command iptables -t nat -L -n -v.The -x option is passed directly through to iptables and causes actual packet and byte counts to be displayed. Without this option, those counts are abbreviated. policies Added in Shorewall 4.4.4. Displays the applicable policy between each pair of zones. Note that implicit intrazone ACCEPT policies are not displayed for zones associated with a single network where that network doesn't specify routeback. routing Displays the system's IPv4 routing configuration. raw Displays the Netfilter raw table using the command iptables -t raw -L -n -v.The -x option is passed directly through to iptables and causes actual packet and byte counts to be displayed. Without this option, those counts are abbreviated. tc Displays information about queuing disciplines, classes and filters. zones Displays the current composition of the Shorewall zones on the system. start Start Shorewall Lite. Existing connections through shorewall-lite managed interfaces are untouched. New connections will be allowed only if they are allowed by the firewall rules or policies. The -p option causes the connection tracking table to be flushed; the conntrack utility must be installed to use this option. stop Stops the firewall. All existing connections, except those listed in shorewall-routestopped[4](5) or permitted by the ADMINISABSENTMINDED option in shorewall.conf[1](5), are taken down. The only new traffic permitted through the firewall is from systems listed in shorewall-routestopped[4](5) or by ADMINISABSENTMINDED. If -f is given, the command will be processed by the compiled script that executed the last successful start, restart or refresh command if that script exists. status Produces a short report about the state of the Shorewall-configured firewall. version Displays Shorewall's version. The -a option is included for compatibility with earlier Shorewall releases and is ignored. FILES
/etc/shorewall-lite/ SEE ALSO
http://www.shorewall.net/starting_and_stopping_shorewall.htm shorewall-accounting(5), shorewall-actions(5), shorewall-blacklist(5), shorewall-hosts(5), shorewall_interfaces(5), shorewall-ipsets(5), shorewall-maclist(5), shorewall-masq(5), shorewall-nat(5), shorewall-netmap(5), shorewall-params(5), shorewall-policy(5), shorewall-providers(5), shorewall-proxyarp(5), shorewall-rtrules(5), shorewall-routestopped(5), shorewall-rules(5), shorewall.conf(5), shorewall-secmarks(5), shorewall-tcclasses(5), shorewall-tcdevices(5), shorewall-tcrules(5), shorewall-tos(5), shorewall-tunnels(5), shorewall-zones(5) NOTES
1. shorewall.conf http://www.shorewall.net/manpages/shorewall.conf.html 2. shorewall-interfaces http://www.shorewall.net/manpages/shorewall-interfaces.html 3. shorewall-accounting http://www.shorewall.net/manpages/manpages/shorewall-accounting.html 4. shorewall-routestopped http://www.shorewall.net/manpages/shorewall-routestopped.html [FIXME: source] 06/28/2012 SHOREWALL-LITE(8)
All times are GMT -4. The time now is 11:57 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy