Replace empty values in a column with 'UNKNOWN'one column from output column on awkDeleting rows based on the column values in a file using awk commandUsing awk to modify 3rd Column valuesMake variables show a column with awkIf two columns partially match, replace third with awkMerge values of consecutive rows if they have the same values on a different column (AWK)awk replace value in colunm p row i by value in same column next row
Formatting Date and TIME in French
Why is this missile positioned in this odd position? How can it be launched correctly?
Is it acceptable to mark off or comment on someone's presentation based on their non-standard English accent?
What means it for money to be 'paid out in rates'?
What does "set aside for x hours" imply?
Fantasy story about a knife that can cut holes to other dimensions
What is the difference between turbojet and turbofan engines?
MS in Mathematics, having trouble finding work outside teaching algebra
An employer is trying to force me to switch banks, which I know is illegal. What should I do?
Who was the first to weigh chemical reaction products?
Why is it possible to teach real numbers before even rigorously defining them?
Did Abraham and Noah know of each other?
How do I most effectively serve as group treasurer?
Stream of Thought/Creeping Chill Interaction?
Why was Wouter Basson never charged with crimes against humanity for Project Coast?
Being flown out for an interview, is it ok to ask to stay a while longer to check out the area?
Did medieval stores have names?
Do airplanes need brakes in the air?
Why is the block size is not filled with transactions
How do I protect myself from bad contracting jobs?
Novel where a serial killer lives (almost) forever by swapping bodies
SD Card speed degrading and doesn't work on one of my cameras: can I do something?
What deck size is ideal?
Why did dict.get(key) work but not dict[key]?
Replace empty values in a column with 'UNKNOWN'
one column from output column on awkDeleting rows based on the column values in a file using awk commandUsing awk to modify 3rd Column valuesMake variables show a column with awkIf two columns partially match, replace third with awkMerge values of consecutive rows if they have the same values on a different column (AWK)awk replace value in colunm p row i by value in same column next row
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;
I have a tab-delimited file with 3 columns in which I want to replace the empty values in column two with the string "UNKNOWN". Here is part of the file:
MSTRG.1 0
MSTRG.10 LOC102399924 331
MSTRG.100 GINS4 108
MSTRG.1000 UNKNOWN 0
MSTRG.10000 UNKNOWN 0
MSTRG.10001 BTBD19 86
MSTRG.10002 TCTEX1D4 8
MSTRG.10003 PLK3 627
MSTRG.10004 LOC112585853 21996
MSTRG.10005 LOC112585855 22
MSTRG.10006 LOC112585856 1
MSTRG.10007 LOC112585857 29
MSTRG.10008 KIF2C 151
MSTRG.10009 ARMH1 14
MSTRG.1001 UNKNOWN 0
MSTRG.10010 UNKNOWN 0
MSTRG.10011 TMEM53 43
MSTRG.10012 RNF220 4315
MSTRG.10013 LOC112585696 100
MSTRG.10014 234
MSTRG.10015 918
MSTRG.10016 DPH2 242
MSTRG.10017 LOC112577732 705
I tried awk -F't' '$2 && !$3 $2="UNKNOWN" 1' file but it did not replace the empty spaces in a few rows.
EDIT
As requested in the comments, here is part of the same file using commas instead of tabs for better readability. I have converted the tab-delimited file into CSV with sed 's/t/,/'g.
MSTRG.1,,0
MSTRG.10,LOC102399924,331
MSTRG.100,GINS4,108
MSTRG.1000,LOC112585730,0
MSTRG.10000,LOC112585902,0
MSTRG.10013,LOC112585696,100
MSTRG.10014,,234
MSTRG.10015,,918
MSTRG.10016,DPH2,242
awk
edited Oct 8 at 16:56
user3140225
2,4573 gold badges9 silver badges24 bronze badges
asked Jul 15 at 17:49
Kousalya DeviKousalya Devi
313 bronze badges
add a comment
|
I have a tab-delimited file with 3 columns in which I want to replace the empty values in column two with the string "UNKNOWN". Here is part of the file:
MSTRG.1 0
MSTRG.10 LOC102399924 331
MSTRG.100 GINS4 108
MSTRG.1000 UNKNOWN 0
MSTRG.10000 UNKNOWN 0
MSTRG.10001 BTBD19 86
MSTRG.10002 TCTEX1D4 8
MSTRG.10003 PLK3 627
MSTRG.10004 LOC112585853 21996
MSTRG.10005 LOC112585855 22
MSTRG.10006 LOC112585856 1
MSTRG.10007 LOC112585857 29
MSTRG.10008 KIF2C 151
MSTRG.10009 ARMH1 14
MSTRG.1001 UNKNOWN 0
MSTRG.10010 UNKNOWN 0
MSTRG.10011 TMEM53 43
MSTRG.10012 RNF220 4315
MSTRG.10013 LOC112585696 100
MSTRG.10014 234
MSTRG.10015 918
MSTRG.10016 DPH2 242
MSTRG.10017 LOC112577732 705
I tried awk -F't' '$2 && !$3 $2="UNKNOWN" 1' file but it did not replace the empty spaces in a few rows.
EDIT
As requested in the comments, here is part of the same file using commas instead of tabs for better readability. I have converted the tab-delimited file into CSV with sed 's/t/,/'g.
MSTRG.1,,0
MSTRG.10,LOC102399924,331
MSTRG.100,GINS4,108
MSTRG.1000,LOC112585730,0
MSTRG.10000,LOC112585902,0
MSTRG.10013,LOC112585696,100
MSTRG.10014,,234
MSTRG.10015,,918
MSTRG.10016,DPH2,242
awk
edited Oct 8 at 16:56
user3140225
2,4573 gold badges9 silver badges24 bronze badges
asked Jul 15 at 17:49
Kousalya DeviKousalya Devi
313 bronze badges
Since the site's formatting replaces tabs with spaces, it's hard to tell the structure of your data. Please consider posting an example with an alternate delimiter (such as,or|) that will be easier to work with.
– steeldriver
Jul 15 at 19:18
Pl. check the updated question.
– Kousalya Devi
Jul 16 at 4:27
2
I would suggestawk -F't' 'BEGINOFS=FS $2 == "" $2 = "UNKNOWN" 1' file
– steeldriver
Jul 16 at 11:26
add a comment
|
I have a tab-delimited file with 3 columns in which I want to replace the empty values in column two with the string "UNKNOWN". Here is part of the file:
MSTRG.1 0
MSTRG.10 LOC102399924 331
MSTRG.100 GINS4 108
MSTRG.1000 UNKNOWN 0
MSTRG.10000 UNKNOWN 0
MSTRG.10001 BTBD19 86
MSTRG.10002 TCTEX1D4 8
MSTRG.10003 PLK3 627
MSTRG.10004 LOC112585853 21996
MSTRG.10005 LOC112585855 22
MSTRG.10006 LOC112585856 1
MSTRG.10007 LOC112585857 29
MSTRG.10008 KIF2C 151
MSTRG.10009 ARMH1 14
MSTRG.1001 UNKNOWN 0
MSTRG.10010 UNKNOWN 0
MSTRG.10011 TMEM53 43
MSTRG.10012 RNF220 4315
MSTRG.10013 LOC112585696 100
MSTRG.10014 234
MSTRG.10015 918
MSTRG.10016 DPH2 242
MSTRG.10017 LOC112577732 705
I tried awk -F't' '$2 && !$3 $2="UNKNOWN" 1' file but it did not replace the empty spaces in a few rows.
EDIT
As requested in the comments, here is part of the same file using commas instead of tabs for better readability. I have converted the tab-delimited file into CSV with sed 's/t/,/'g.
MSTRG.1,,0
MSTRG.10,LOC102399924,331
MSTRG.100,GINS4,108
MSTRG.1000,LOC112585730,0
MSTRG.10000,LOC112585902,0
MSTRG.10013,LOC112585696,100
MSTRG.10014,,234
MSTRG.10015,,918
MSTRG.10016,DPH2,242
awk
edited Oct 8 at 16:56
user3140225
2,4573 gold badges9 silver badges24 bronze badges
asked Jul 15 at 17:49
Kousalya DeviKousalya Devi
313 bronze badges
I have a tab-delimited file with 3 columns in which I want to replace the empty values in column two with the string "UNKNOWN". Here is part of the file:
MSTRG.1 0
MSTRG.10 LOC102399924 331
MSTRG.100 GINS4 108
MSTRG.1000 UNKNOWN 0
MSTRG.10000 UNKNOWN 0
MSTRG.10001 BTBD19 86
MSTRG.10002 TCTEX1D4 8
MSTRG.10003 PLK3 627
MSTRG.10004 LOC112585853 21996
MSTRG.10005 LOC112585855 22
MSTRG.10006 LOC112585856 1
MSTRG.10007 LOC112585857 29
MSTRG.10008 KIF2C 151
MSTRG.10009 ARMH1 14
MSTRG.1001 UNKNOWN 0
MSTRG.10010 UNKNOWN 0
MSTRG.10011 TMEM53 43
MSTRG.10012 RNF220 4315
MSTRG.10013 LOC112585696 100
MSTRG.10014 234
MSTRG.10015 918
MSTRG.10016 DPH2 242
MSTRG.10017 LOC112577732 705
I tried awk -F't' '$2 && !$3 $2="UNKNOWN" 1' file but it did not replace the empty spaces in a few rows.
EDIT
As requested in the comments, here is part of the same file using commas instead of tabs for better readability. I have converted the tab-delimited file into CSV with sed 's/t/,/'g.
MSTRG.1,,0
MSTRG.10,LOC102399924,331
MSTRG.100,GINS4,108
MSTRG.1000,LOC112585730,0
MSTRG.10000,LOC112585902,0
MSTRG.10013,LOC112585696,100
MSTRG.10014,,234
MSTRG.10015,,918
MSTRG.10016,DPH2,242
awk
awk
edited Oct 8 at 16:56
user3140225
2,4573 gold badges9 silver badges24 bronze badges
asked Jul 15 at 17:49
Kousalya DeviKousalya Devi
313 bronze badges
edited Oct 8 at 16:56
user3140225
2,4573 gold badges9 silver badges24 bronze badges
asked Jul 15 at 17:49
Kousalya DeviKousalya Devi
313 bronze badges
edited Oct 8 at 16:56
user3140225
2,4573 gold badges9 silver badges24 bronze badges
edited Oct 8 at 16:56
user3140225
2,4573 gold badges9 silver badges24 bronze badges
edited Oct 8 at 16:56
user3140225
2,4573 gold badges9 silver badges24 bronze badges
2,4573 gold badges9 silver badges24 bronze badges
asked Jul 15 at 17:49
Kousalya DeviKousalya Devi
313 bronze badges
asked Jul 15 at 17:49
Kousalya DeviKousalya Devi
313 bronze badges
asked Jul 15 at 17:49
Kousalya DeviKousalya Devi
313 bronze badges
313 bronze badges
Since the site's formatting replaces tabs with spaces, it's hard to tell the structure of your data. Please consider posting an example with an alternate delimiter (such as,or|) that will be easier to work with.
– steeldriver
Jul 15 at 19:18
Pl. check the updated question.
– Kousalya Devi
Jul 16 at 4:27
2
I would suggestawk -F't' 'BEGINOFS=FS $2 == "" $2 = "UNKNOWN" 1' file
– steeldriver
Jul 16 at 11:26
add a comment
|
Since the site's formatting replaces tabs with spaces, it's hard to tell the structure of your data. Please consider posting an example with an alternate delimiter (such as,or|) that will be easier to work with.
– steeldriver
Jul 15 at 19:18
Pl. check the updated question.
– Kousalya Devi
Jul 16 at 4:27
2
I would suggestawk -F't' 'BEGINOFS=FS $2 == "" $2 = "UNKNOWN" 1' file
– steeldriver
Jul 16 at 11:26
Since the site's formatting replaces tabs with spaces, it's hard to tell the structure of your data. Please consider posting an example with an alternate delimiter (such as
, or |) that will be easier to work with.– steeldriver
Jul 15 at 19:18
Since the site's formatting replaces tabs with spaces, it's hard to tell the structure of your data. Please consider posting an example with an alternate delimiter (such as
, or |) that will be easier to work with.– steeldriver
Jul 15 at 19:18
Pl. check the updated question.
– Kousalya Devi
Jul 16 at 4:27
Pl. check the updated question.
– Kousalya Devi
Jul 16 at 4:27
2
2
I would suggest
awk -F't' 'BEGINOFS=FS $2 == "" $2 = "UNKNOWN" 1' file– steeldriver
Jul 16 at 11:26
I would suggest
awk -F't' 'BEGINOFS=FS $2 == "" $2 = "UNKNOWN" 1' file– steeldriver
Jul 16 at 11:26
add a comment
|
1 Answer
1
active
oldest
votes
In addition to steeldriver's comment, which should work fine, you could also just replace consecutive tabs (tt) with "UNKNOWN" surrounded by tabs (tUNKNOWNt).
With
sed:sed -i 's/tt/tUNKNOWNt/' file-imodifies the file in place.
With
awkversion > 4.1.0:awk -i inplace 'sub(/tt/, "tUNKNOWNt")1' file-i inplacemodifies the file in place.1in the end just prints the file contents.
With
awkversion < 4.1.0, the-i inplaceoption does not exist, so, to imitate-i inplace, you have to save the file to a temporary one and then rename it as your current one:awk 'sub(/tt/, "tUNKNOWNt")1' file > tmp && mv tmp file
answered Oct 8 at 16:49
user3140225user3140225
2,4573 gold badges9 silver badges24 bronze badges
add a comment
|
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "89"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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/4.0/"u003ecc by-sa 4.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
);
);
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%2faskubuntu.com%2fquestions%2f1158489%2freplace-empty-values-in-a-column-with-unknown%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
In addition to steeldriver's comment, which should work fine, you could also just replace consecutive tabs (tt) with "UNKNOWN" surrounded by tabs (tUNKNOWNt).
With
sed:sed -i 's/tt/tUNKNOWNt/' file-imodifies the file in place.
With
awkversion > 4.1.0:awk -i inplace 'sub(/tt/, "tUNKNOWNt")1' file-i inplacemodifies the file in place.1in the end just prints the file contents.
With
awkversion < 4.1.0, the-i inplaceoption does not exist, so, to imitate-i inplace, you have to save the file to a temporary one and then rename it as your current one:awk 'sub(/tt/, "tUNKNOWNt")1' file > tmp && mv tmp file
answered Oct 8 at 16:49
user3140225user3140225
2,4573 gold badges9 silver badges24 bronze badges
add a comment
|
In addition to steeldriver's comment, which should work fine, you could also just replace consecutive tabs (tt) with "UNKNOWN" surrounded by tabs (tUNKNOWNt).
With
sed:sed -i 's/tt/tUNKNOWNt/' file-imodifies the file in place.
With
awkversion > 4.1.0:awk -i inplace 'sub(/tt/, "tUNKNOWNt")1' file-i inplacemodifies the file in place.1in the end just prints the file contents.
With
awkversion < 4.1.0, the-i inplaceoption does not exist, so, to imitate-i inplace, you have to save the file to a temporary one and then rename it as your current one:awk 'sub(/tt/, "tUNKNOWNt")1' file > tmp && mv tmp file
answered Oct 8 at 16:49
user3140225user3140225
2,4573 gold badges9 silver badges24 bronze badges
add a comment
|
In addition to steeldriver's comment, which should work fine, you could also just replace consecutive tabs (tt) with "UNKNOWN" surrounded by tabs (tUNKNOWNt).
With
sed:sed -i 's/tt/tUNKNOWNt/' file-imodifies the file in place.
With
awkversion > 4.1.0:awk -i inplace 'sub(/tt/, "tUNKNOWNt")1' file-i inplacemodifies the file in place.1in the end just prints the file contents.
With
awkversion < 4.1.0, the-i inplaceoption does not exist, so, to imitate-i inplace, you have to save the file to a temporary one and then rename it as your current one:awk 'sub(/tt/, "tUNKNOWNt")1' file > tmp && mv tmp file
answered Oct 8 at 16:49
user3140225user3140225
2,4573 gold badges9 silver badges24 bronze badges
In addition to steeldriver's comment, which should work fine, you could also just replace consecutive tabs (tt) with "UNKNOWN" surrounded by tabs (tUNKNOWNt).
With
sed:sed -i 's/tt/tUNKNOWNt/' file-imodifies the file in place.
With
awkversion > 4.1.0:awk -i inplace 'sub(/tt/, "tUNKNOWNt")1' file-i inplacemodifies the file in place.1in the end just prints the file contents.
With
awkversion < 4.1.0, the-i inplaceoption does not exist, so, to imitate-i inplace, you have to save the file to a temporary one and then rename it as your current one:awk 'sub(/tt/, "tUNKNOWNt")1' file > tmp && mv tmp file
answered Oct 8 at 16:49
user3140225user3140225
2,4573 gold badges9 silver badges24 bronze badges
edited Oct 8 at 17:07
answered Oct 8 at 16:49
user3140225user3140225
2,4573 gold badges9 silver badges24 bronze badges
answered Oct 8 at 16:49
user3140225user3140225
2,4573 gold badges9 silver badges24 bronze badges
answered Oct 8 at 16:49
user3140225user3140225
2,4573 gold badges9 silver badges24 bronze badges
2,4573 gold badges9 silver badges24 bronze badges
add a comment
|
add a comment
|
Thanks for contributing an answer to Ask Ubuntu!
- 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.
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%2faskubuntu.com%2fquestions%2f1158489%2freplace-empty-values-in-a-column-with-unknown%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
Since the site's formatting replaces tabs with spaces, it's hard to tell the structure of your data. Please consider posting an example with an alternate delimiter (such as
,or|) that will be easier to work with.– steeldriver
Jul 15 at 19:18
Pl. check the updated question.
– Kousalya Devi
Jul 16 at 4:27
2
I would suggest
awk -F't' 'BEGINOFS=FS $2 == "" $2 = "UNKNOWN" 1' file– steeldriver
Jul 16 at 11:26