Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Insert missing values

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Insert missing values
# 1  
Old 05-03-2014
Insert missing values
Hi, please help with this, I need to insert missing values into a matrix for a regression analysis.

I have made up an example. The first three columns are variables with levels and the next 3 are values, the 4th column missing values should be replaced by 0s, and 5th and 6th column missing values replaced by NA. The final table should have all combinations of the variables in columns 1 , 2 and 3.
The example has just 2 or 3 levels of each variable, the actual data might have upto 50 levels.

Input

Code:
Var1	Var2	Var3	Val1	Val2	Val3
X1	L1	G1	1	4	7
X2	L2	G2	2	5	8
X2	L3	G1	3	6	9

Output

Code:
Var1	Var2	Var3	Val1	Val2	Val3
X1	L1	G1	1	4	7
X1	L1	G2	0	NA	NA
X1	L2	G1	0	NA	NA
X1	L2	G2	0	NA	NA
X1	L3	G1	0	NA	NA
X1	L3	G2	0	NA	NA
X2	L1	G1	0	NA	NA
X2	L1	G2	0	NA	NA
X2	L2	G1	0	NA	NA
X2	L2	G2	2	5	8
X2	L3	G1	3	6	9
X2	L3	G2	0	NA	NA

# 2  
Old 05-03-2014
Hi, what have you tried so far?
# 3  
Old 05-05-2014
try:
Code:
awk '
NR==1 {$1=$1; print $0; next;}
{ c1[$1]=$1;
  c2[$2]=$2;
  c3[$3]=$3;
  co[$1,$2,$3]=$4 "\t" $5 "\t" $6;
}
END {
  for (i in c1) {
     for (j in c2) {
        for (k in c3) {
           printf i "\t" j "\t" k "\t";
           if (co[i, j, k]) {
              print co[i, j, k];
           } else {
              print "0\tNA\tNA";
           }
        }
     }
  }
}' OFS="\t" infile

# 4  
Old 05-06-2014
Code:
perl -lane 'BEGIN {$" = "\t"}
  if($. == 1) {print; next}
  {$A[$.] = $F[0] if (! grep(/$F[0]/, @A));
  $B[$.] = $F[1] if (! grep(/$F[1]/, @B));
  $C[$.] = $F[2] if (! grep(/$F[2]/, @C));
  $H{$F[0] . $" . $F[1] . $" . $F[2]} = $_}
  END {for($i = 2; $i <= $#A; $i++) {
    for($j = 2; $j <= $#B; $j++) {
      for($k = 2; $k <= $#C; $k++) {
        $T = ($A[$i] . $" . $B[$j] . $" . $C[$k]);
        if(exists $H{$T}) {print $H{$T}}
        else
          {print ($T . $" . 0 . $" . "NA" . $" . "NA")}}}}}' file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Fill in missing values

Hi, I have a data sample as shown below. I want to fill in the left column so that the line will be continuous. For example, between 1 and 5 should be 2,3,4. And corresponding values in the right column will be 0. Thus the expected data should look like that: 1 1 1 10 1 2 1 3 1 5 1 6 2 0... (6 Replies)
Discussion started by: theanh0508
6 Replies

2. Shell Programming and Scripting

awk to insert missing string based on pattern in file

Using the file below, which will always have the first indicated by the digit after the - and last id in it, indicated by the digit after the -, I am trying to use awk to print the missing line or lines in file following the pattern of the previous line. For example, in the file below the next... (4 Replies)
Discussion started by: cmccabe
4 Replies

3. Shell Programming and Scripting

Get both common and missing values from multiple files

Hi, I have 5 files with two columns. I need to merge all the 5 files based on column 1. If any of them are missing then corresponding 2nd column should be substituted by missing value. I know hoe to do this for 2 files. but how can I implement for 5 files. I tried this based on 5 files but it... (2 Replies)
Discussion started by: Diya123
2 Replies

4. UNIX for Dummies Questions & Answers

How to combine and insert missing consecutive numbers - awk or script?

Hi all, I have two (2) sets of files that are based on some snapshots of database that I want to merge and insert any missing sequential number. Below are example representation of these files: file1: DATE TIME COL1 COL2 COL3 COL4 ID 01/10/2013 0800 100 ... (3 Replies)
Discussion started by: newbie_01
3 Replies

5. Shell Programming and Scripting

Insert missing field using perl,sed,awk

sample file (comma as field separators) MessageFlow,1,BusIntBatchMgr,a OOBEvent,1,BusIntBatchMgr,a TaskEvents,1,,a MTTrace,1,,a MTWarning,,1,a MessageFlow,1,Batch,a OOBEvent,1,Batch,a TaskEvents,1,,a EAISAPIdocWizard,1,BusIntMgr,a EAISAPBAPIWizard,1,BusIntMgr,a... (3 Replies)
Discussion started by: vrclm
3 Replies

6. Shell Programming and Scripting

Insert values

HI Guys, I have a data in a file in the below format 45783 23457 23556 54584 Now i want to convert this data into the below format reader='45783' or reader='23457' or reader='23556' or reader='54584' Please help how to convert as i am applying loop but not able to get the data... (6 Replies)
Discussion started by: jaituteja
6 Replies

7. Shell Programming and Scripting

How do i find the first number in each line and insert dummy string into the missing columns?

Hi, I have one input file with the following content: MY_inpfile.txt Aname1 Cname1 Cname2 1808 5 Aname2 Cname1 1802 47 Bname1 ? 1819 22 Bname2 Cname1 1784 11 Bname3 1817 9 Zname1 Cname1 1805 59 Zname2 Cname1 Cname2 Cname3 1797 27 Every line in my input file have a 4 digit... (5 Replies)
Discussion started by: Szaffy
5 Replies

8. Shell Programming and Scripting

Fill missing values with 2

Hi All, I have 100 .txt files which look like this: 3 4 5 6 7 Now, some files have some numbers missing in them and they look like this: 4 5 6 (6 Replies)
Discussion started by: shoaibjameel123
6 Replies

9. Shell Programming and Scripting

Scan two files and print values missing

Dear Experts, Have been seraching this forum from this morning for my query but dint find hence posting it her... Basically i have two input files BSS and MSS which has a unique string , hence i hav tried and seperated the text to compare frm both files .. Any my present input files look like... (6 Replies)
Discussion started by: shaliniyadav
6 Replies

10. Shell Programming and Scripting

Insert blanks for missing fields and reformat to csv

I am trying to process inventory addition files for insertion into a MySQL database. The format convention is book UIEE. If the field for the add file has no data, the field is NOT included in the upload file, so I need to add a blank/empty for any missing fields. The I need to create a csv or tsv... (9 Replies)
Discussion started by: LoveSquid
9 Replies
Login or Register to Ask a Question