Extracting data in a particular Format- only Raw counts in matrix format

Question

[David_emir]

I have gene.csv file which is about 1.3 GB in size and has got 300 columns and more than 1 million rows. it looks like following,

id1      id2    id3    id4         count1      count2
S1001    450    GAF    ARHGAP18    1.56E-05    1483
S1001    450    GAF    ARHGAP12    5E-05       3698
S1001    450    GAF    ARHGAP15    2.75E-06    93
S1001    450    GAF    ARHGAP17    3E-05       1889
S1001    450    GAF    ARHGAP19    4.291E-06   596
S1002    450    GAF    ARHGAP18    5.955E-05   5353
S1002    450    GAF    ARHGAP12    8.578E-08   14
S1002    450    BAF    ARHGAP15    2.91E-05    5381
S1002    450    BAF    ARHGAP17    1.78E-06    105
S1002    450    BAF    ARHGAP19    3.62E-05    5764
S1003    450    BAF    ARHGAP18    5.2697E-06  330
S1003    450    BAF    ARHGAP12    5.955E-05   2263
S1003    450    BAF    ARHGAP15    8.578E-08   3147
S1003    450    CAF    ARHGAP17    2.91E-05    50
S1003    450    CAF    ARHGAP19    5.955E-05   1595
S1004    450    CAF    ARHGAP18    8.578E-08   970
S1004    450    CAF    ARHGAP12    2.91E-05    816
S1004    450    CAF    ARHGAP15    5.955E-05   4981
S1004    450    CAF    ARHGAP17    8.578E-08   816
S1004    450    CAF    ARHGAP19    2.91E-05    4981

I want only id1. id4 and count2 data fetched in the following format (as a matrix)

id4        S1001   S1002 S1003  S1004
ARHGAP18    1483   5353  330    970
ARHGAP12    3698   14    2263   816
ARHGAP15    93     5381  3147   4981
ARHGAP17    1889   105   50     816
ARHGAP19    596    5764  1595   4981

Please note, here id1 I need as column ids(Repeated only once). I would be very happy if you guys help me.

Thanks in advance,

Dave!

[Devon Ryan]

What have you tried? This looks like a job for dplyr in R (plus cut on the command line to make things quicker).

BTW, I replaced "10 Lakhs" with "10 million", since a Lakh is a concept that doesn't exist outside of India.

[David_emir]

Thanks Devon! I will look into dplyr