input:

> df
  Name Group.1 Name.1 Group.2 Name.2 Group.3
1    A       2      A       1      A       4
2    B       4      B       2      B       7
3    C       7      C       4      I       6
4    D       6      D       7      D       4
5    E       4      F       6      M       9
6           NA      H       4             NA

output:

> df %>%
+     rename_at(vars(starts_with("Name")), ~paste("Name", seq(1:(ncol(df)/2)),sep=".")) %>%
+     pivot_longer(everything(),names_to = c(".value", "set"),names_pattern = "(\\D+)(\\d)") %>%
+     drop_na()%>%
+     pivot_wider(names_from="set", values_from="Group.", names_prefix = "Group.", values_fill = 0) %>%
+     arrange(Name.)

# A tibble: 9 x 4
  Name. Group.1 Group.2 Group.3
  <chr>   <int>   <int>   <int>
1 A           2       1       4
2 B           4       2       7
3 C           7       4       0
4 D           6       7       4
5 E           4       0       0
6 F           0       6       0
7 H           0       4       0
8 I           0       0       6
9 M           0       0       9

example data

df <- structure(list(Name = c("A", "B", "C", NA), Group_1 = c(40L, 
3L, 28L, NA), Name.1 = c("A", "B", "H", "I"), Group_2 = c(38L, 
36L, 20L, 16L), Name.2 = c("A", "D", "G", NA), Group_3 = c(15L, 
47L, 33L, NA)), class = "data.frame", row.names = c(NA, -4L))

> df
  Name Group_1 Name.1 Group_2 Name.2 Group_3
1    A      40      A      38      A      15
2    B       3      B      36      D      47
3    C      28      H      20      G      33
4 <NA>      NA      I      16   <NA>      NA

Tidyverse solution. Someone may come up with a more elegant solution, but this works in the meantime.

library("tidyverse")

df <- seq(2, ncol(df), 2) %>%
  map(~select(df, .x-1, .x) %>% rename(Name=1)) %>%
  reduce(full_join, by="Name") %>%
  mutate(across(starts_with("Group"), ~replace_na(.x, 0))) %>%
  drop_na

And the results.

> df
# A tibble: 7 x 4
  Name  Group_1 Group_2 Group_3
  <chr>   <dbl>   <dbl>   <dbl>
1 A          40      38      15
2 B           3      36       0
3 C          28       0       0
4 H           0      20       0
5 I           0      16       0
6 D           0       0      47
7 G           0       0      33

edit: name of the script changed from weird_to_long.pl to weird_to_wide.pl.

I hacked a small Perl script to convert the data. Save the script as weird_to_wide.pl and run as (you may need to chmod +x weird_to_wide.pl):

./weird_to_wide.pl < input.tsv > output.tsv

In the process of writing the script, I got me wondering what program produces such weird tabular format? Or did you somehow joined the output of several programs? Do all lines have the same number of elements, i.e., the last line in your first example (H 4) has trailing tabs indicating it has additional empty columns?

Your original data seems awkward and error prone, I think it deserves some thinking in how to avoid it in the first place.

The script:

#!/usr/bin/env perl

use warnings;
use strict;

my @header;
my %hash;

while(<>) {
    s/\R//;
    next if ( /^\s$/ ); 
    unless( @header ) { 
        @header = split( /\t/, $_ );
        next;
    }
    my (@line) = split( /\t/, $_, -1 );
    for( my $i=1; $i<scalar( @line ); $i+=2 ) {
        my $name = $line[$i-1];
        next if !( length $name );
        @{$hash{$name}}[(($i+1)/2)-1] = $line[$i];
    }
}

print "$header[0]\t";
print join( "\t", map{$header[$_]} grep{$_ % 2 == 1} 1..$#header ), "\n";

for my $key (keys %hash){
    $#{$hash{$key}} = ( scalar( @header ) / 2 ) - 1;
    $_ //= 0 for @{$hash{$key}};
    print "$key\t";
    print join( "\t", @{$hash{$key}} ), "\n";
}

Using base R:

# reproducible example data
df <- read.table(text = "
Name    Group-1 Name    Group-2 Name    Group-3
A   2   A   1   A   4
B   4   B   2   B   7
C   7   C   4   I   6
D   6   D   7   D   4
E   4   F   6   M   9
        H   4       
", stringsAsFactors = FALSE, check.names = FALSE, header = TRUE, sep = "\t", na.strings = "")

# split on every 2 columns
x <- split.default(df, rep(1:3, each = 2))

# remove rows where name is blank
x <- lapply(x, function(i) i[ !is.na(i[, "Name"]), ])

# merge list of dataframes
res <- Reduce(function(...) merge(..., all = TRUE), x)

# convert NAs to zero (I would skip this step)
res[ is.na(res) ] <- 0

res
#   Name Group-1 Group-2 Group-3
# 1    A       2       1       4
# 2    B       4       2       7
# 3    C       7       4       0
# 4    D       6       7       4
# 5    E       4       0       0
# 6    F       0       6       0
# 7    H       0       4       0
# 8    I       0       0       6
# 9    M       0       0       9

Note: For readability I put every command separately, all could be bundled up in one row as below:

res <- Reduce(function(...) merge(..., all = TRUE), 
              lapply(split.default(df, rep(1:3, each = 2)),
                     function(i) i[ !is.na(i[, "Name"]), ]))

Courtesy Rpolicastro

Woking from csv file as below,

Name.1  Group_1 Name.2  Group_2 Name.3  Group_3
1222    2   1222    1   1222    4
1333    4   1333    2   1333    7
1334    7   1334    4   1334    6
1335    6   1335    7   1335    4
1336    4   1337    6   13699   9
1366    4

We can follow like this in RStudio

setwd("C:/Users/SHAKIL/Desktop/metagenome_data_R") #working_directory_RStudio
install.packages("writexl")
library(writexl)
library("tidyverse")
df = read.csv("bar_plot.csv", header = TRUE)

df <- seq(2, ncol(df), 2) %>%
  map(~select(df, .x-1, .x) %>% rename(Name=1)) %>%
  reduce(full_join, by="Name") %>%
  mutate(across(starts_with("Group"), ~replace_na(.x, 0))) %>%
  drop_na

write_xlsx(x = df, path = "df.xlsx", col_names = TRUE)

Output as .xlsx format

Name    Group_1 Group_2 Group_3
1222    2   1   4
1333    4   2   7
1334    7   4   6
1335    6   7   4
1336    4   0   0
1366    4   0   0
1337    0   6   0
13699   0   0   9