csv filter on linux bash
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
0
Problem:
I have a CSV file with 5 columns (1st column is a string and the other 4 are int). I would like to filter based on the third column, Revenues, the largest on the top and smallest on the bottom in a new CSV file.
It seems that I would need to use something like awk -F '","' 'BEGIN OFS="," if (Revenues($5) > ?? print ' Valuation.csv > Ranking.csv
Data:
Company,Nbr employees, Revenues , Revenues per employee , Valuation
Facebook,"35,587","55,800,000,000","1,567,988","491,000,000,000"
Uber,"16,000","11,300,000,000","706,250","120,000,000,000"
Snapchat,"3,069","1,180,000,000","384,490","7,200,000,000"
Airbnb,"3,100","2,600,000,000","838,710","38,000,000,000"
LinkedIn,"13,000","26,200,000,000","2,015,385","26,200,000,000"
Coursora,280,"140,000,000","500,000","815,000,000"
Google,"98,771","39,120,000,000","396,068","720,000,000,000"
Stripe,"1,500","450,000,000","300,000","22,500,000,000"
Epic Games,700,"3,000,000,000","4,285,714","15,000,000,000"
Grab,"3,000","2,750,000,000","916,667","10,000,000,000"
Pinterest,800,"1,000,000,000","1,250,000","12,000,000,000"
linux awk csv
edited Mar 15 at 14:28
Jeff Schaller♦
45k1164147
asked Mar 15 at 13:05
JamesJames
11
add a comment |
0
Problem:
I have a CSV file with 5 columns (1st column is a string and the other 4 are int). I would like to filter based on the third column, Revenues, the largest on the top and smallest on the bottom in a new CSV file.
It seems that I would need to use something like awk -F '","' 'BEGIN OFS="," if (Revenues($5) > ?? print ' Valuation.csv > Ranking.csv
Data:
Company,Nbr employees, Revenues , Revenues per employee , Valuation
Facebook,"35,587","55,800,000,000","1,567,988","491,000,000,000"
Uber,"16,000","11,300,000,000","706,250","120,000,000,000"
Snapchat,"3,069","1,180,000,000","384,490","7,200,000,000"
Airbnb,"3,100","2,600,000,000","838,710","38,000,000,000"
LinkedIn,"13,000","26,200,000,000","2,015,385","26,200,000,000"
Coursora,280,"140,000,000","500,000","815,000,000"
Google,"98,771","39,120,000,000","396,068","720,000,000,000"
Stripe,"1,500","450,000,000","300,000","22,500,000,000"
Epic Games,700,"3,000,000,000","4,285,714","15,000,000,000"
Grab,"3,000","2,750,000,000","916,667","10,000,000,000"
Pinterest,800,"1,000,000,000","1,250,000","12,000,000,000"
linux awk csv
edited Mar 15 at 14:28
Jeff Schaller♦
45k1164147
asked Mar 15 at 13:05
JamesJames
11
can u share the data in text and share the expected result
– msp9011
Mar 15 at 13:09
1
Your file does not appear to be a CSV file.
– Kusalananda♦
Mar 15 at 13:28
Please show the real contents of your CSV file, not a screenshot of the file loaded into a spredsheet program or similar and not an ASCII table representation. It is important to see the exact format of the raw data to propose a working script.
– Bodo
Mar 15 at 13:33
I just changed it, thanks
– James
Mar 15 at 13:38
add a comment |
0
0
0
Problem:
I have a CSV file with 5 columns (1st column is a string and the other 4 are int). I would like to filter based on the third column, Revenues, the largest on the top and smallest on the bottom in a new CSV file.
It seems that I would need to use something like awk -F '","' 'BEGIN OFS="," if (Revenues($5) > ?? print ' Valuation.csv > Ranking.csv
Data:
Company,Nbr employees, Revenues , Revenues per employee , Valuation
Facebook,"35,587","55,800,000,000","1,567,988","491,000,000,000"
Uber,"16,000","11,300,000,000","706,250","120,000,000,000"
Snapchat,"3,069","1,180,000,000","384,490","7,200,000,000"
Airbnb,"3,100","2,600,000,000","838,710","38,000,000,000"
LinkedIn,"13,000","26,200,000,000","2,015,385","26,200,000,000"
Coursora,280,"140,000,000","500,000","815,000,000"
Google,"98,771","39,120,000,000","396,068","720,000,000,000"
Stripe,"1,500","450,000,000","300,000","22,500,000,000"
Epic Games,700,"3,000,000,000","4,285,714","15,000,000,000"
Grab,"3,000","2,750,000,000","916,667","10,000,000,000"
Pinterest,800,"1,000,000,000","1,250,000","12,000,000,000"
linux awk csv
edited Mar 15 at 14:28
Jeff Schaller♦
45k1164147
asked Mar 15 at 13:05
JamesJames
11
Problem:
I have a CSV file with 5 columns (1st column is a string and the other 4 are int). I would like to filter based on the third column, Revenues, the largest on the top and smallest on the bottom in a new CSV file.
It seems that I would need to use something like awk -F '","' 'BEGIN OFS="," if (Revenues($5) > ?? print ' Valuation.csv > Ranking.csv
Data:
Company,Nbr employees, Revenues , Revenues per employee , Valuation
Facebook,"35,587","55,800,000,000","1,567,988","491,000,000,000"
Uber,"16,000","11,300,000,000","706,250","120,000,000,000"
Snapchat,"3,069","1,180,000,000","384,490","7,200,000,000"
Airbnb,"3,100","2,600,000,000","838,710","38,000,000,000"
LinkedIn,"13,000","26,200,000,000","2,015,385","26,200,000,000"
Coursora,280,"140,000,000","500,000","815,000,000"
Google,"98,771","39,120,000,000","396,068","720,000,000,000"
Stripe,"1,500","450,000,000","300,000","22,500,000,000"
Epic Games,700,"3,000,000,000","4,285,714","15,000,000,000"
Grab,"3,000","2,750,000,000","916,667","10,000,000,000"
Pinterest,800,"1,000,000,000","1,250,000","12,000,000,000"
linux awk csv
linux awk csv
edited Mar 15 at 14:28
Jeff Schaller♦
45k1164147
asked Mar 15 at 13:05
JamesJames
11
edited Mar 15 at 14:28
Jeff Schaller♦
45k1164147
asked Mar 15 at 13:05
JamesJames
11
edited Mar 15 at 14:28
Jeff Schaller♦
45k1164147
edited Mar 15 at 14:28
Jeff Schaller♦
45k1164147
edited Mar 15 at 14:28
Jeff Schaller♦
45k1164147
45k1164147
asked Mar 15 at 13:05
JamesJames
11
asked Mar 15 at 13:05
JamesJames
11
asked Mar 15 at 13:05
JamesJames
11
11
can u share the data in text and share the expected result
– msp9011
Mar 15 at 13:09
1
Your file does not appear to be a CSV file.
– Kusalananda♦
Mar 15 at 13:28
Please show the real contents of your CSV file, not a screenshot of the file loaded into a spredsheet program or similar and not an ASCII table representation. It is important to see the exact format of the raw data to propose a working script.
– Bodo
Mar 15 at 13:33
I just changed it, thanks
– James
Mar 15 at 13:38
add a comment |
can u share the data in text and share the expected result
– msp9011
Mar 15 at 13:09
1
Your file does not appear to be a CSV file.
– Kusalananda♦
Mar 15 at 13:28
Please show the real contents of your CSV file, not a screenshot of the file loaded into a spredsheet program or similar and not an ASCII table representation. It is important to see the exact format of the raw data to propose a working script.
– Bodo
Mar 15 at 13:33
I just changed it, thanks
– James
Mar 15 at 13:38
can u share the data in text and share the expected result
– msp9011
Mar 15 at 13:09
can u share the data in text and share the expected result
– msp9011
Mar 15 at 13:09
1
1
Your file does not appear to be a CSV file.
– Kusalananda♦
Mar 15 at 13:28
Your file does not appear to be a CSV file.
– Kusalananda♦
Mar 15 at 13:28
Please show the real contents of your CSV file, not a screenshot of the file loaded into a spredsheet program or similar and not an ASCII table representation. It is important to see the exact format of the raw data to propose a working script.
– Bodo
Mar 15 at 13:33
Please show the real contents of your CSV file, not a screenshot of the file loaded into a spredsheet program or similar and not an ASCII table representation. It is important to see the exact format of the raw data to propose a working script.
– Bodo
Mar 15 at 13:33
I just changed it, thanks
– James
Mar 15 at 13:38
I just changed it, thanks
– James
Mar 15 at 13:38
add a comment |
1 Answer
1
active
oldest
votes
1
Try this,
head -1 Valuation.csv > Ranking.csv && tail -n+2 Valuation.csv | sort -t '"' -nrk4 >> Ranking.csv
head ....will copy the header to Ranking filetail .... | sort ...will sort the 4th column by excluding the header with"as a delimiter.-n+2will print the file from 2nd line-nrk4numeric reverse sort of the fourth column with"as a delimiter.
Output :
Company,Nbr employees, Revenues , Revenues per employee , Valuation
Facebook,"35,587","55,800,000,000","1,567,988","491,000,000,000"
Google,"98,771","39,120,000,000","396,068","720,000,000,000"
LinkedIn,"13,000","26,200,000,000","2,015,385","26,200,000,000"
Uber,"16,000","11,300,000,000","706,250","120,000,000,000"
Grab,"3,000","2,750,000,000","916,667","10,000,000,000"
Airbnb,"3,100","2,600,000,000","838,710","38,000,000,000"
Snapchat,"3,069","1,180,000,000","384,490","7,200,000,000"
Stripe,"1,500","450,000,000","300,000","22,500,000,000"
Epic Games,700,"3,000,000,000","4,285,714","15,000,000,000"
Pinterest,800,"1,000,000,000","1,250,000","12,000,000,000"
Coursora,280,"140,000,000","500,000","815,000,000"
answered Mar 15 at 13:35
msp9011msp9011
4,61344167
Thank you! That is working but the order is not right. I got: Facebook, Uber, Stripe, Snapchat, Pinterestm LinkedIn, Grab, Google, Epic Games Coursorama Airbnb. The order should be: Facebook, Google, LinkedIn, Uber, Epic Games, Grab, Airbnb, Snapchat, Pinintrest, Stripe, Coursora
– James
Mar 15 at 13:43
@James check my update
– msp9011
Mar 15 at 13:44
Thank you. Now the order is Uber, Stripe, Snapchat, Pinterest, LinkedIn, Grab, Google, Facebook, Epic Games, Coursora, Airbnb
– James
Mar 15 at 13:49
1
Thank you! Just got it! Thank you for your help!
– James
Mar 15 at 13:59
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "106"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f506507%2fcsv-filter-on-linux-bash%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Try this,
head -1 Valuation.csv > Ranking.csv && tail -n+2 Valuation.csv | sort -t '"' -nrk4 >> Ranking.csv
head ....will copy the header to Ranking filetail .... | sort ...will sort the 4th column by excluding the header with"as a delimiter.-n+2will print the file from 2nd line-nrk4numeric reverse sort of the fourth column with"as a delimiter.
Output :
Company,Nbr employees, Revenues , Revenues per employee , Valuation
Facebook,"35,587","55,800,000,000","1,567,988","491,000,000,000"
Google,"98,771","39,120,000,000","396,068","720,000,000,000"
LinkedIn,"13,000","26,200,000,000","2,015,385","26,200,000,000"
Uber,"16,000","11,300,000,000","706,250","120,000,000,000"
Grab,"3,000","2,750,000,000","916,667","10,000,000,000"
Airbnb,"3,100","2,600,000,000","838,710","38,000,000,000"
Snapchat,"3,069","1,180,000,000","384,490","7,200,000,000"
Stripe,"1,500","450,000,000","300,000","22,500,000,000"
Epic Games,700,"3,000,000,000","4,285,714","15,000,000,000"
Pinterest,800,"1,000,000,000","1,250,000","12,000,000,000"
Coursora,280,"140,000,000","500,000","815,000,000"
answered Mar 15 at 13:35
msp9011msp9011
4,61344167
Thank you! That is working but the order is not right. I got: Facebook, Uber, Stripe, Snapchat, Pinterestm LinkedIn, Grab, Google, Epic Games Coursorama Airbnb. The order should be: Facebook, Google, LinkedIn, Uber, Epic Games, Grab, Airbnb, Snapchat, Pinintrest, Stripe, Coursora
– James
Mar 15 at 13:43
@James check my update
– msp9011
Mar 15 at 13:44
Thank you. Now the order is Uber, Stripe, Snapchat, Pinterest, LinkedIn, Grab, Google, Facebook, Epic Games, Coursora, Airbnb
– James
Mar 15 at 13:49
1
Thank you! Just got it! Thank you for your help!
– James
Mar 15 at 13:59
add a comment |
1
Try this,
head -1 Valuation.csv > Ranking.csv && tail -n+2 Valuation.csv | sort -t '"' -nrk4 >> Ranking.csv
head ....will copy the header to Ranking filetail .... | sort ...will sort the 4th column by excluding the header with"as a delimiter.-n+2will print the file from 2nd line-nrk4numeric reverse sort of the fourth column with"as a delimiter.
Output :
Company,Nbr employees, Revenues , Revenues per employee , Valuation
Facebook,"35,587","55,800,000,000","1,567,988","491,000,000,000"
Google,"98,771","39,120,000,000","396,068","720,000,000,000"
LinkedIn,"13,000","26,200,000,000","2,015,385","26,200,000,000"
Uber,"16,000","11,300,000,000","706,250","120,000,000,000"
Grab,"3,000","2,750,000,000","916,667","10,000,000,000"
Airbnb,"3,100","2,600,000,000","838,710","38,000,000,000"
Snapchat,"3,069","1,180,000,000","384,490","7,200,000,000"
Stripe,"1,500","450,000,000","300,000","22,500,000,000"
Epic Games,700,"3,000,000,000","4,285,714","15,000,000,000"
Pinterest,800,"1,000,000,000","1,250,000","12,000,000,000"
Coursora,280,"140,000,000","500,000","815,000,000"
answered Mar 15 at 13:35
msp9011msp9011
4,61344167
Thank you! That is working but the order is not right. I got: Facebook, Uber, Stripe, Snapchat, Pinterestm LinkedIn, Grab, Google, Epic Games Coursorama Airbnb. The order should be: Facebook, Google, LinkedIn, Uber, Epic Games, Grab, Airbnb, Snapchat, Pinintrest, Stripe, Coursora
– James
Mar 15 at 13:43
@James check my update
– msp9011
Mar 15 at 13:44
Thank you. Now the order is Uber, Stripe, Snapchat, Pinterest, LinkedIn, Grab, Google, Facebook, Epic Games, Coursora, Airbnb
– James
Mar 15 at 13:49
1
Thank you! Just got it! Thank you for your help!
– James
Mar 15 at 13:59
add a comment |
1
1
1
Try this,
head -1 Valuation.csv > Ranking.csv && tail -n+2 Valuation.csv | sort -t '"' -nrk4 >> Ranking.csv
head ....will copy the header to Ranking filetail .... | sort ...will sort the 4th column by excluding the header with"as a delimiter.-n+2will print the file from 2nd line-nrk4numeric reverse sort of the fourth column with"as a delimiter.
Output :
Company,Nbr employees, Revenues , Revenues per employee , Valuation
Facebook,"35,587","55,800,000,000","1,567,988","491,000,000,000"
Google,"98,771","39,120,000,000","396,068","720,000,000,000"
LinkedIn,"13,000","26,200,000,000","2,015,385","26,200,000,000"
Uber,"16,000","11,300,000,000","706,250","120,000,000,000"
Grab,"3,000","2,750,000,000","916,667","10,000,000,000"
Airbnb,"3,100","2,600,000,000","838,710","38,000,000,000"
Snapchat,"3,069","1,180,000,000","384,490","7,200,000,000"
Stripe,"1,500","450,000,000","300,000","22,500,000,000"
Epic Games,700,"3,000,000,000","4,285,714","15,000,000,000"
Pinterest,800,"1,000,000,000","1,250,000","12,000,000,000"
Coursora,280,"140,000,000","500,000","815,000,000"
answered Mar 15 at 13:35
msp9011msp9011
4,61344167
Try this,
head -1 Valuation.csv > Ranking.csv && tail -n+2 Valuation.csv | sort -t '"' -nrk4 >> Ranking.csv
head ....will copy the header to Ranking filetail .... | sort ...will sort the 4th column by excluding the header with"as a delimiter.-n+2will print the file from 2nd line-nrk4numeric reverse sort of the fourth column with"as a delimiter.
Output :
Company,Nbr employees, Revenues , Revenues per employee , Valuation
Facebook,"35,587","55,800,000,000","1,567,988","491,000,000,000"
Google,"98,771","39,120,000,000","396,068","720,000,000,000"
LinkedIn,"13,000","26,200,000,000","2,015,385","26,200,000,000"
Uber,"16,000","11,300,000,000","706,250","120,000,000,000"
Grab,"3,000","2,750,000,000","916,667","10,000,000,000"
Airbnb,"3,100","2,600,000,000","838,710","38,000,000,000"
Snapchat,"3,069","1,180,000,000","384,490","7,200,000,000"
Stripe,"1,500","450,000,000","300,000","22,500,000,000"
Epic Games,700,"3,000,000,000","4,285,714","15,000,000,000"
Pinterest,800,"1,000,000,000","1,250,000","12,000,000,000"
Coursora,280,"140,000,000","500,000","815,000,000"
answered Mar 15 at 13:35
msp9011msp9011
4,61344167
edited Mar 15 at 13:43
answered Mar 15 at 13:35
msp9011msp9011
4,61344167
answered Mar 15 at 13:35
msp9011msp9011
4,61344167
answered Mar 15 at 13:35
msp9011msp9011
4,61344167
4,61344167
Thank you! That is working but the order is not right. I got: Facebook, Uber, Stripe, Snapchat, Pinterestm LinkedIn, Grab, Google, Epic Games Coursorama Airbnb. The order should be: Facebook, Google, LinkedIn, Uber, Epic Games, Grab, Airbnb, Snapchat, Pinintrest, Stripe, Coursora
– James
Mar 15 at 13:43
@James check my update
– msp9011
Mar 15 at 13:44
Thank you. Now the order is Uber, Stripe, Snapchat, Pinterest, LinkedIn, Grab, Google, Facebook, Epic Games, Coursora, Airbnb
– James
Mar 15 at 13:49
1
Thank you! Just got it! Thank you for your help!
– James
Mar 15 at 13:59
add a comment |
Thank you! That is working but the order is not right. I got: Facebook, Uber, Stripe, Snapchat, Pinterestm LinkedIn, Grab, Google, Epic Games Coursorama Airbnb. The order should be: Facebook, Google, LinkedIn, Uber, Epic Games, Grab, Airbnb, Snapchat, Pinintrest, Stripe, Coursora
– James
Mar 15 at 13:43
@James check my update
– msp9011
Mar 15 at 13:44
Thank you. Now the order is Uber, Stripe, Snapchat, Pinterest, LinkedIn, Grab, Google, Facebook, Epic Games, Coursora, Airbnb
– James
Mar 15 at 13:49
1
Thank you! Just got it! Thank you for your help!
– James
Mar 15 at 13:59
Thank you! That is working but the order is not right. I got: Facebook, Uber, Stripe, Snapchat, Pinterestm LinkedIn, Grab, Google, Epic Games Coursorama Airbnb. The order should be: Facebook, Google, LinkedIn, Uber, Epic Games, Grab, Airbnb, Snapchat, Pinintrest, Stripe, Coursora
– James
Mar 15 at 13:43
Thank you! That is working but the order is not right. I got: Facebook, Uber, Stripe, Snapchat, Pinterestm LinkedIn, Grab, Google, Epic Games Coursorama Airbnb. The order should be: Facebook, Google, LinkedIn, Uber, Epic Games, Grab, Airbnb, Snapchat, Pinintrest, Stripe, Coursora
– James
Mar 15 at 13:43
@James check my update
– msp9011
Mar 15 at 13:44
@James check my update
– msp9011
Mar 15 at 13:44
Thank you. Now the order is Uber, Stripe, Snapchat, Pinterest, LinkedIn, Grab, Google, Facebook, Epic Games, Coursora, Airbnb
– James
Mar 15 at 13:49
Thank you. Now the order is Uber, Stripe, Snapchat, Pinterest, LinkedIn, Grab, Google, Facebook, Epic Games, Coursora, Airbnb
– James
Mar 15 at 13:49
1
1
Thank you! Just got it! Thank you for your help!
– James
Mar 15 at 13:59
Thank you! Just got it! Thank you for your help!
– James
Mar 15 at 13:59
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Unix & Linux Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f506507%2fcsv-filter-on-linux-bash%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
can u share the data in text and share the expected result
– msp9011
Mar 15 at 13:09
1
Your file does not appear to be a CSV file.
– Kusalananda♦
Mar 15 at 13:28
Please show the real contents of your CSV file, not a screenshot of the file loaded into a spredsheet program or similar and not an ASCII table representation. It is important to see the exact format of the raw data to propose a working script.
– Bodo
Mar 15 at 13:33
I just changed it, thanks
– James
Mar 15 at 13:38