×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

create matrix from csv data file

create matrix from csv data file

create matrix from csv data file

(OP)
Hi,

i have data as below:

-DATA-
tweetid, workerid
10115,    user1
10115,    user2
10190,    user1
10190,    user2
10193,    user3
10320,    user2
10320,    user1

I have no idea, how i can write a code to transform this data to the 2D matrix.

My desire output is:

tweetid  user1, user2, user3
10115    1,       1,     0
10190    1,       1,     0
10193    0,       0,     1
10320    1,       1,     0

Any help is much appreciated. Thank you.

RE: create matrix from csv data file

I would read the data

CODE

tweetid, workerid
10115,    user1
10115,    user2
10190,    user1
10190,    user2
10193,    user3
10320,    user2
10320,    user1
and create a hash, which keys are tweetid and values are sorted lists of corresponding workerids

CODE

hash = {
  10115 => [user1, user2],
  10190 => [user1, user2],
  10193 => [user3],
  10320 => [user1, user2]
}
Then it's not very difficult to create a matrix you need.
Some time ago I posted similar example in the Ruby forum. You can look at the working code here http://www.tek-tips.com/viewthread.cfm?qid=1555687 and use similar approach in Perl for your needs.  

RE: create matrix from csv data file

This will start you off:

CODE

use strict;
my ($tweetid,$workerid,%HoH);
while (<DATA>) {
    chomp;
    ($tweetid,$workerid)=split(/,/,$_);
    $HoH{ $tweetid }{ $workerid } = 1;
}

print "tweetid\tuser1,\tuser2,\tuser3\n";
for my $k1 ( sort keys %HoH ) {
     print "$k1 ";
     
     for my $k2 ( sort keys %{$HoH{ $k1 }} ) {
         print "$k2";
    }
    print "\n";
}

__DATA__
10115,    user1
10115,    user2
10190,    user1
10190,    user2
10193,    user3
10320,    user2
10320,    user1

This outputs;

CODE

tweetid user1,  user2,  user3
10115     user1    user2
10190     user1    user2
10193     user3
10320     user1    user2

You've just got to change the user1/2/3 etc to digits and input zeros where they aren't present.

RE: create matrix from csv data file

(OP)
Hi all,

thanks for your suggestion. Actually i have make some code before but it seem i can't get the output as i need.

my code so far

CODE

#!/usr/bin/perl
#use strict;
#use warnings;
use Text::CSV_XS;

# Store our CSV file name
my $file = 'apa2.csv';

open( CSV_XS, '<', $file )
  or die( 'Unable to open csv file ', $file, "\n" );

open MYFILE, ">matrix_user.txt";
select MYFILE;

my $csv = new Text::CSV_XS;
my %alldata=();    #at initialization

my@fields=('tweetid', 'workerid');

#for each line data
#my $index = 0;            

foreach my $line (<CSV_XS>) {
    if ( $csv->parse($line) ) {
        my @columns = $csv->fields();
    
    # filter to skip from processing the headers
    if($columns[0] ne "tweetid"){
            for ( my $i = 1 ; $i <= $#fields ; $i++ ) {
                    $alldata{ $columns[0]}[$i] = $columns[$i];
            }
    }
    }
    else {
        print 'Unable to parse CSV line: ', $line, "\n";
    }
    $index++;        
}
close(CSV_XS);

#for each column data seperate by ,
for ( my $i = 1 ; $i <= $#fields ; $i++ ) {
    for my $k ( sort keys %alldata ){
    # get tweetId
    my @tweetid = split(',',$k);
              print join( $tweetid[0], ",", $alldata{$k}[$i] ), "\n"
              if $alldata{$k}[$i];
        }
 }


tonykent: i will try to combine your code.

 

RE: create matrix from csv data file

(OP)
Hi tonykent,

i already got the right output. just edit a little bit. Thank you so much.

best regards
diera

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close