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"
  • 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
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 file
  • tail .... | sort ... will sort the 4th column by excluding the header with " as a delimiter.
  • -n+2 will print the file from 2nd line
  • -nrk4 numeric 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"
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.