Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Data manipulation

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Data manipulation
# 1  
Old 01-11-2014
Data manipulation
Hallo Team,

I need to manipulate existing data file. Have a look at current data and expected data:

Current Data:

Code:
27873517141
27873540000
27873515109
27873517140
27873540001
27873540000
27873501343
27873540000
27873517140
27873511292
27873645989
27873540000
27873540000
27873516481
27873540000
27873515109
27873604866
D8500115778622
D8500115750171
D8500117421926
D8500112340600
D8500110211064
D8500113255400
D8500117013355
D8500110211064
D8500117421926
D8500515063500
D8500514444643
D8500215506300
D8500218886000
D8500214181750
D8500466225490
D8500217619891
D8500215515570

I would like 27xxxxx to be 0xxxxx AND D850xxxxxxxxxx to be xxxxxxxxxx like below:

Code:
0873517141
0873540000
0873515109
0873517140
0873540001
0873540000
0873501343
0873540000
0873517140
0873511292
0873645989
0873540000
0873540000
0873516481
0873540000
0873515109
0873604866
0115778622
0115750171
0117421926
0112340600
0110211064
0113255400
0117013355
0110211064
0117421926
0515063500
0514444643
0215506300
0218886000
0214181750
0466225490
0217619891
0215515570

Regards,

Paxley
# 2  
Old 01-11-2014
Now that we know what you'd like, what's the actual issue?
# 3  
Old 01-11-2014
what command can i use to get my desired output?
# 4  
Old 01-11-2014
Try:
Code:
sed 's/^27/0/;s/^D850//' input

# 5  
Old 01-11-2014
Bartus thank my friend it works very well. I thought i could use your command to manipulate column2 also. My actual file looks like below:

Code:
0785645714,27873539608,E,ISOL,ZAR,0.2065,O,59,20131130,23034400,0.21
0738427364,27873504462,E,ISOL,ZAR,0.259,O,74,20131130,23055200,0.21
0833984635,27873539608,E,ISOL,ZAR,0.0595,O,17,20131130,23060200,0.21
0738353884,27873504384,E,ISOL,ZAR,0.105,O,30,20131130,23043900,0.21
0837213067,27873504037,E,ISOL,ZAR,0.238,O,68,20131130,23010700,0.21
0788119994,27873504037,E,ISOL,ZAR,0.182,O,52,20131130,23202700,0.21
0789476045,27873504462,E,ISOL,ZAR,0.2275,O,65,20131130,23174500,0.21
0730170352,27873540000,E,ISOL,ZAR,0.007,O,2,20131130,23223900,0.21
0717886387,27873507540,E,ISOL,ZAR,0.007,O,2,20131130,23123400,0.21
0719002966,27100032610,E,ISOL,ZAR,0.008667,O,4,20131130,23034700,0.13
0737936004,27100032610,E,ISOL,ZAR,0.002167,O,1,20131130,23150900,0.13
0734865033,27873539608,E,ISOL,ZAR,0.0315,O,9,20131130,23294300,0.21
0719761893,27873540000,E,ISOL,ZAR,0.0245,O,7,20131130,23322900,0.21
0736172962,27873539608,E,ISOL,ZAR,0.091,O,26,20131130,23432500,0.21
0783901658,27873540000,E,ISOL,ZAR,0.063,O,18,20131130,23312300,0.21
0719457275,D8500466229215,E,ISOL,ZAR,0.0875,O,25,20131130,23564000,0.21
0783230531,D8500114855676,E,ISOL,ZAR,0.030333,O,14,20131130,23513000,0.13

Your command worked for column1 tried to manipulate your command :

Code:
sed 's/^,27/0/;s/^,D850//'  mtn_out.csv > pax_out1.txt

but i still cant manipulate column2. Please advice.
# 6  
Old 01-11-2014
Try:
Code:
awk -F"," -vOFS="," '{sub("^27","0",$2);sub("^D850","",$2)}1' input

# 7  
Old 01-12-2014
Thank yu very much for your responce Bertus.
I need more help sorry my friends to hassle you on a Sunday. Now i need to change the date format in field 9 to yyyy-mm-dd:

Code:
0780112843,0873599381,E,ISOL,ZAR,0.0035,O,1,20131116,4373200,0.21
0733001720,0873516499,E,ISOL,ZAR,0.0035,O,1,20131116,4331600,0.21
0731069010,0873550144,E,ISOL,ZAR,0.0105,O,3,20131116,4555400,0.21
0839440085,0873540000,E,ISOL,ZAR,0.0805,O,23,20131116,4510200,0.21
0736747788,0873540000,E,ISOL,ZAR,0.0805,O,23,20131116,3351300,0.21
0735600782,0873516481,E,ISOL,ZAR,0.035,O,10,20131116,3305100,0.21
0834538110,0873504345,E,ISOL,ZAR,0.0035,O,1,20131116,3544700,0.21
0836569560,0873537240,E,ISOL,ZAR,0.021,O,6,20131116,2194800,0.21
0832396855,0873540000,E,ISOL,ZAR,0.0805,O,23,20131116,4104000,0.21
0835829767,0873540000,E,ISOL,ZAR,0.0805,O,23,20131116,4041300,0.21
0835829767,0873540000,E,ISOL,ZAR,0.077,O,22,20131116,4044800,0.21
0835829767,0873540000,E,ISOL,ZAR,0.0805,O,23,20131116,4052400,0.21
0738907463,0873539690,E,ISOL,ZAR,0.0035,O,1,20131116,4082800,0.21
0838780516,0873516487,E,ISOL,ZAR,0.035,O,10,20131116,2263000,0.21
0710394006,0873550144,E,ISOL,ZAR,0.1855,O,53,20131116,4215800,0.21
0832924068,0873514889,E,ISOL,ZAR,0.0945,O,27,20131116,4411400,0.21
0787854993,0873504382,E,ISOL,ZAR,0.021,O,6,20131116,4321500,0.21
0733493939,0873550144,E,ISOL,ZAR,0.035,O,10,20131116,4395000,0.21
0781564696,0873504384,E,ISOL,ZAR,0.007,O,2,20131116,3002500,0.21
0732790849,0873540000,E,ISOL,ZAR,0.0525,O,15,20131116,4584500,0.21
0839440085,0873540000,E,ISOL,ZAR,0.07,O,20,20131116,4484000,0.21
0796403431,0312742529,E,ISOL,ZAR,0.021,O,6,20131116,4595300,0.21
0836826338,0114855676,E,ISOL,ZAR,0.056333,O,26,20131116,4424700,0.13
0733871277,0514444643,E,ISOL,ZAR,0.1155,O,33,20131116,2321800,0.21

Regards,

Pax
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Data manipulation, Please help..

Hello, I have a huge set of data that needs to be reformatted. Here is a simple example to explain the process. I have number n=5 and a input with many numbers separated with comma: ... (11 Replies)
Discussion started by: liuzhencc
11 Replies

2. Shell Programming and Scripting

[Solved] Data manipulation

Hallo Team, I need your help. I have a file that has two colums. See sample below: 105550 0.28 105550 0.24 125550 0.28 125550 0.24 215650 0.28 215650 0.24 315550 0.28 315550 0.24 335550 0.28 335550 0.24 40555 0.21 40555 0.17 415550 0.21 415550 0.17 43555 0.21 43555 0.17 (5 Replies)
Discussion started by: kekanap
5 Replies

3. UNIX for Dummies Questions & Answers

Data Manipulation

Dear Sir, I have file input RGR001|108.28|-2.86489|100-120|RANGGAR RGR002|108.071|-2.69028|80-100|RANNGAR RGR003|108.168|-2.97053|50-80|RANNGAR RGR007|108.192722222|-2.766138889|0-50|RANGGARI want to create files by joining each rows with each rows below Output as below ... (4 Replies)
Discussion started by: radius
4 Replies

4. Shell Programming and Scripting

Data manipulation using shell

Dear all I have a dataset (in text format,delimited by tab) which have 100 variables (say, var0-var99) and more than 100,000 observations. I want to do the following: 1. for variable var0-var49, I want to add "00" in front of each data (for example, "1" would become "001") 2. for variable... (8 Replies)
Discussion started by: littlewenwen
8 Replies

5. Shell Programming and Scripting

Help with data manipulation script

Y,T,,H05,6,6,0,0 -> TH05_6 D,5,BT,B -> BT_KIOSK P,KQC222 -> KQC222 G,B,2 -> BRANI_GATE_2 fileA TPM658 Y,T,,H05,6,6,0,0 TPM110 D,5,BT,B TPM136 P,KQC222 TPM180 P,BQC913 TPM575 Y,B,,T05,14,14,0,0 IPM760 G,B,2 TPM011 I need to use second column $1,$2,$3,$4..... if first char... (6 Replies)
Discussion started by: ment0smintz
6 Replies

6. UNIX for Dummies Questions & Answers

Script for data manipulation

Hi all! my first post here, so mods -- if this should ideally be in the scripts section, please move there. Thanks! I have data in the following format: key1:value1 key2:value2 key3:value3 A B C D key1:value4 key2:value5 key3:value6 A1 B1 key1: ... and so on I want an output... (2 Replies)
Discussion started by: gnat01
2 Replies

7. Shell Programming and Scripting

Data manipulation from a file

i have a file in follwing format 0110 1020 1011 1032 1020 2005 2003 1050 i want the output in such a way that all non zero numbers will be converted into 1 like this 0110 1010 1011 1011 1010 1001 1001 1010 (3 Replies)
Discussion started by: vaibhavkorde
3 Replies

8. Shell Programming and Scripting

Tricky data manipulation...

Hi everyone.. I am new here, hello.. I hope this doesn't come across to you folks as a stupid question, I'm somewhat new to scripting :) I'm seeking some help in finding a way to manipulate data output for every two characters - example: numbers.lst contains the following output:... (3 Replies)
Discussion started by: explicit
3 Replies

9. UNIX for Dummies Questions & Answers

Data Manipulation

Hello I am currently having problems in mapulating a certain file which contains vaious data. Belos is a sample content Event=<3190> Client IP=<151.111.11.143> DNS=<abc.sbc.com> TransCount=<139> Client IP=<150.222.133.163> DNS=<xyz.yuu.com> TransCount=<3734> Event=<3120> Client... (11 Replies)
Discussion started by: khestoi
11 Replies

10. UNIX for Dummies Questions & Answers

data manipulation script

I have a folder called {homedata} Within this folder there are 12 subfolders 200601.......200612 Within each subfolder there are 8 sets of files Each filename commences with A B C D E F G or H, so {filename}* can be used. I am trying to write a script which will from the top level go... (1 Reply)
Discussion started by: grinder182533
1 Replies
Login or Register to Ask a Question