LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 03-09-2017, 09:07 AM   #1
Bartonsen
Member
 
Registered: Oct 2016
Posts: 34

Rep: Reputation: Disabled
Change format


I have a textfile looking like Format-1. How can it be changed to Format-2, so that it can easily be opened as a "space delimited" file in excel to make charts?

Format-1:
2017-03-01 Joe 7
2017-03-01 Andy 0
2017-03-01 Lily 1
2017-03-01 Bob 6
2017-03-01 Vanessa 2
2017-03-02 Joe 3
2017-03-02 Andy 2
2017-03-02 Lily 9
2017-03-02 Bob 15
2017-03-02 Vanessa 14
2017-03-03 Joe 5
2017-03-03 Andy 25
2017-03-03 Lily 0
2017-03-03 Bob 8
2017-03-03 Vanessa 162

Format-2:
Date Joe Andy Lily Bob Vanessa
2017-03-01 7 0 1 6 2
2017-03-02 3 2 9 15 14
2017-03-03 5 25 0 8 162
 
Old 03-09-2017, 09:11 AM   #2
Turbocapitalist
LQ Guru
 
Registered: Apr 2005
Distribution: Linux Mint, Devuan, OpenBSD
Posts: 5,614
Blog Entries: 3

Rep: Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860
It would be fairly easy with awk or perl. Which are you trying and how far have you gotten?
 
Old 03-09-2017, 09:22 AM   #3
Bartonsen
Member
 
Registered: Oct 2016
Posts: 34

Original Poster
Rep: Reputation: Disabled
I've tried with awk and 'for date in ...', but I've not been successful in getting any useful output....
 
Old 03-09-2017, 09:50 AM   #4
Turbocapitalist
LQ Guru
 
Registered: Apr 2005
Distribution: Linux Mint, Devuan, OpenBSD
Posts: 5,614
Blog Entries: 3

Rep: Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860
"On the Internet nobody can hear you being subtle."
Can you show some or all of your code and explain which approach you are trying? That will give people here something to comment on.
 
Old 03-09-2017, 11:38 AM   #5
Bartonsen
Member
 
Registered: Oct 2016
Posts: 34

Original Poster
Rep: Reputation: Disabled
No. I don't have any useful code to show here, 'cause I don't know how to approach this task.
If someone could tell me in plain text how it could be done, then I might produce some code. I know what I want, but I don't know how to get there...
 
Old 03-09-2017, 11:42 AM   #6
273
LQ Addict
 
Registered: Dec 2011
Location: UK
Distribution: Debian Sid AMD64, Raspbian Wheezy, various VMs
Posts: 7,585

Rep: Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351
I cringe at myself as I type this (I actually am) but wouldn't this be just as quick to do in excel using the transpose paste thingy (whatever it's called)?
It would be cool to see a script to do it though.
 
Old 03-09-2017, 11:59 AM   #7
Turbocapitalist
LQ Guru
 
Registered: Apr 2005
Distribution: Linux Mint, Devuan, OpenBSD
Posts: 5,614
Blog Entries: 3

Rep: Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860Reputation: 2860
Quote:
Originally Posted by Bartonsen View Post
No. I don't have any useful code to show here, 'cause I don't know how to approach this task.
If someone could tell me in plain text how it could be done, then I might produce some code. I know what I want, but I don't know how to get there...
There are several ways to approach this.

One way is with two arrays. One contains Joe Andy Lily Bob Vanessa as keys and values are the numeric positions for their data in the second array. The other is multidimensional and uses the date as the primary key and using the numeric value looked up from the first array to store the data point for that name. Then at the end print out the second array.

If Joe Andy Lily Bob Vanessa are fixed values they can be hard coded. If they are not fixed values then you probably need to make an extra pass at the beginning to harvest the names and generate a lookup table. I'd lean towards perl in the latter situation.
 
Old 03-09-2017, 01:12 PM   #8
GazL
LQ Veteran
 
Registered: May 2008
Posts: 5,931

Rep: Reputation: 3908Reputation: 3908Reputation: 3908Reputation: 3908Reputation: 3908Reputation: 3908Reputation: 3908Reputation: 3908Reputation: 3908Reputation: 3908Reputation: 3908
No need to get clever. Use 'grep' in a for loop to extract each individual's data to a separate file (sorted), then use those as input to a 'join' command keyed on the date field and specifying the desired output fields with the '-o' option.

edit: on reflection, you might be better off with the perl option as the join invocations start to get unwieldy as the number of names increases.

Last edited by GazL; 03-09-2017 at 01:45 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
change .wav to another format Pedroski Linux - Software 10 09-18-2012 04:56 AM
[SOLVED] change one time format to other format himu3118 Linux - Newbie 4 06-21-2010 07:29 AM
change output format for df baddah Programming 3 04-21-2008 08:08 AM
Change Passwd Format shipon_97 Linux - Newbie 1 01-09-2007 08:13 AM
All I want to do is change the date format... Slycordinator Linux - General 5 12-11-2003 01:27 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 11:09 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration