0

''' I am writing a code in pandas. Stuck on the below part where I need to use missing rows. ''' df

A B      C         D    E  E   G  H
0 US   BENIN      1995  5  10  15 40
1 US   BENIN      1996  6  12  12 12
2 US   BENIN      2000  4  13  12 12
3 US   Hungary    1998  5  19  23 23
4 US   Hungary    1999  3  23  12 3
5 UK   Chile      2000  5  10  15 40
6 UK   Chile      2002  6  12  12 12
7 UK   Chile      2004  4  13  12 12
8 UK   Iceland    2004  5  19  23 23
89UK   Iceland    2005  3  23  12 3

''' I want to add blank rows for missing years from 1995 to 2000 in between these rows using a loop ''' Desired output:

A B     C           D   E  F   G  H
0 US   BENIN      1995  5  10  15 40
1 US   BENIN      1996  6  12  12 12
2 US   BENIN      1997
3 US   BENIN      1998
4 US   BENIN      1999     
5 US   BENIN      2000  4  13  12 12
6 US   BENIN      2001
7 US   BENIN      2002
8 US   BENIN      2003
9 US   BENIN      2004
10US   BENIN      2005
11US   Hungary    1995
12US   Hungary    1996
13US   Hungary    1997
14US   Hungary    1998  5  19  23 23
15US   Hungary    1999  3  23  12 3
16US   Hungary    1999
17US   Hungary    2000
18US   Hungary    2001
19US   Hungary    2002
20US   Hungary    2003
21US   Hungary    2004
22US   Hungary    2005
23UK   Chile      1995 
24UK   Chile      1996 
25UK   Chile      1997 
26UK   Chile      1998 
27UK   Chile      1999 
28UK   Chile      2000  5  10  15 40
29UK   Chile      2001 
30UK   Chile      2002  6  12  12 12
31UK   Chile      2003 
32UK   Chile      2004  4  13  12 12
33UK   Chile      2005

:
:
:
: 
43 UK   Iceland    2004  5  19  23 23
44 UK   Iceland    2005  3  23  12 3
Improve this question
7
  • Welcome! Can you please show your attempt at solving the problem? Commented May 6, 2020 at 21:24
  • I created a different data frame with all the years in it. Tried to join using left join but I am not able to do it for all countries Commented May 6, 2020 at 21:26
  • I have also tried using a list. I created a list with all years in it. wrote a function to check if that column D contains the values from that list. If yes then ignore it and if not then add one row with missing year and countries in that. But it seems that Loops not working in pandas Commented May 6, 2020 at 21:44
  • This might not be the best way, but how about: append all of the rows you want to add (like 9 US BENIN 2004), sort the DataFrame, remove duplicates which have missing values and then reset index? Commented May 6, 2020 at 22:02
  • This is just a small part of my data frame. I have data frame which contains more than 60000 rows and I need to add missing years for all those B and C pair Commented May 6, 2020 at 22:41

2 Answers 2

Reset to default
1

New Solution:

import re

import pandas as pd

df: pd.DataFrame = pd.DataFrame([
    re.match('(\w+)\ +(\w+)\ +(\w+)\ +(\w+)\ +(\w+)\ +(\w+)\ +(\w+)\ +(\w+)', data).groups() for data in '''
A B      C         D    E  E   G  H
0 US   BENIN      1995  5  10  15 40
1 US   BENIN      1996  6  12  12 12
2 US   BENIN      2000  4  13  12 12
3 US   Hungary    1998  5  19  23 23
4 US   Hungary    1999  3  23  12 3
5 UK   Chile      2000  5  10  15 40
6 UK   Chile      2002  6  12  12 12
7 UK   Chile      2004  4  13  12 12
8 UK   Iceland    2004  5  19  23 23
89 UK   Iceland    2005  3  23  12 3
'''.split('\n')[1:-1]
], dtype='int32')


def consolidate(index, year_min, year_max):
    indexes: list = []
    last_country, last_county, last_year = None, None, year_min
    for country, county, year in index:
        for yr in range(last_year, year):
            indexes.append((country, county, yr))
        last_country, last_county, last_year = country, county, year
    if last_year <= year_max:
        for yr in range(last_year, year_max + 1):
            indexes.append((last_country, last_county, yr))
    return indexes


df.columns = df.iloc[0, :]
df = df.iloc[1:, :]
df.iloc[:, -5] = df.D.astype('int')
df = df.sort_values(['D', 'C'])
year_min, year_max = df.D.min(), df.D.max()
df.set_index(['B', 'C', 'D'], inplace=True)
df1 = df.groupby(['B', 'C']).apply(lambda x: x.reindex(consolidate(x.index, year_min, year_max)))
df1.index = df1.index.droplevel([0, 1])
df = df1.reset_index()
if __name__ == '__main__':
    print(df)

# 0    B        C     D    A    E    E    G    H
# 0   UK    Chile  1995  NaN  NaN  NaN  NaN  NaN
# 1   UK    Chile  1996  NaN  NaN  NaN  NaN  NaN
# 2   UK    Chile  1997  NaN  NaN  NaN  NaN  NaN
# 3   UK    Chile  1998  NaN  NaN  NaN  NaN  NaN
# 4   UK    Chile  1999  NaN  NaN  NaN  NaN  NaN
# 5   UK    Chile  2000    5    5   10   15   40
# 6   UK    Chile  2001  NaN  NaN  NaN  NaN  NaN
# 7   UK    Chile  2002    6    6   12   12   12
# 8   UK    Chile  2003  NaN  NaN  NaN  NaN  NaN
# 9   UK    Chile  2004    7    4   13   12   12
# 10  UK    Chile  2005  NaN  NaN  NaN  NaN  NaN
# 11  UK  Iceland  1995  NaN  NaN  NaN  NaN  NaN
# 12  UK  Iceland  1996  NaN  NaN  NaN  NaN  NaN
# 13  UK  Iceland  1997  NaN  NaN  NaN  NaN  NaN
# 14  UK  Iceland  1998  NaN  NaN  NaN  NaN  NaN
# 15  UK  Iceland  1999  NaN  NaN  NaN  NaN  NaN
# 16  UK  Iceland  2000  NaN  NaN  NaN  NaN  NaN
# 17  UK  Iceland  2001  NaN  NaN  NaN  NaN  NaN
# 18  UK  Iceland  2002  NaN  NaN  NaN  NaN  NaN
# 19  UK  Iceland  2003  NaN  NaN  NaN  NaN  NaN
# 20  UK  Iceland  2004    8    5   19   23   23
# 21  UK  Iceland  2005   89    3   23   12    3
# 22  US    BENIN  1995    0    5   10   15   40
# 23  US    BENIN  1996    1    6   12   12   12
# 24  US    BENIN  1997  NaN  NaN  NaN  NaN  NaN
# 25  US    BENIN  1998  NaN  NaN  NaN  NaN  NaN
# 26  US    BENIN  1999  NaN  NaN  NaN  NaN  NaN
# 27  US    BENIN  2000    2    4   13   12   12
# 28  US    BENIN  2001  NaN  NaN  NaN  NaN  NaN
# 29  US    BENIN  2002  NaN  NaN  NaN  NaN  NaN
# 30  US    BENIN  2003  NaN  NaN  NaN  NaN  NaN
# 31  US    BENIN  2004  NaN  NaN  NaN  NaN  NaN
# 32  US    BENIN  2005  NaN  NaN  NaN  NaN  NaN
# 33  US  Hungary  1995  NaN  NaN  NaN  NaN  NaN
# 34  US  Hungary  1996  NaN  NaN  NaN  NaN  NaN
# 35  US  Hungary  1997  NaN  NaN  NaN  NaN  NaN
# 36  US  Hungary  1998    3    5   19   23   23
# 37  US  Hungary  1999    4    3   23   12    3
# 38  US  Hungary  2000  NaN  NaN  NaN  NaN  NaN
# 39  US  Hungary  2001  NaN  NaN  NaN  NaN  NaN
# 40  US  Hungary  2002  NaN  NaN  NaN  NaN  NaN
# 41  US  Hungary  2003  NaN  NaN  NaN  NaN  NaN
# 42  US  Hungary  2004  NaN  NaN  NaN  NaN  NaN
# 43  US  Hungary  2005  NaN  NaN  NaN  NaN  NaN
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for this code But I need to add missing blank rows from 1995 to 2005 for each B and C pair.
A B C D E F G H 0 US BENIN 1995 5 10 15 40 1 US BENIN 1996 6 12 12 12 2 US BENIN 1997 3 US BENIN 1998 4 US BENIN 1999 5 US BENIN 2000 4 13 12 12 6 US BENIN 2001 7 US BENIN 2002 8 US BENIN 2003 9 US BENIN 2004 10US BENIN 2005
Is this solution work for you? Also, please accept the answer if it does suit your use case.
0

Im not sure how optimal this is but since you asked for details on this approach, here it is.

Assuming you have the data you want to add in a DataFrame like this:

print(df_to_add)
#     B        C     D
# 0  US  Hungary  1995
# 1  US  Hungary  1996
# 2  US  Hungary  1997
# 3  US  Hungary  1998
# 4  US  Hungary  1999
# .
# .
# .
# 39  UK  Iceland  2001
# 40  UK  Iceland  2002
# 41  UK  Iceland  2003
# 42  UK  Iceland  2004
# 43  UK  Iceland  2005

And your data in a DataFrame:

print(df.head())
#    A   B        C     D  E   F   G   H
# 0  0  US    BENIN  1995  5  10  15  40
# 1  1  US    BENIN  1996  6  12  12  12
# 2  2  US    BENIN  2000  4  13  12  12
# 3  3  US  Hungary  1998  5  19  23  23
# 4  4  US  Hungary  1999  3  23  12   3

This should do what you requested:

# Concatenate the data
df = pd.concat([df, df_to_add])
df = df.sort_values(by=['B', 'C', 'D']).reset_index(drop=True)
df['A'] = df.index.values

# remove nan duplicates
def filter_dup_nans(df: DataFrame, row):
    # if it's a duplicate
    if df[(df['B'] == row['B']) & (df['C'] == row['C']) & (df['D'] == row['D'])].shape[0] > 1:
        # return false if it's the nan one
        return not row.isnull().values.any()
    # not a duplicate -> don't remove it
    return True

to_remove = list(filter(lambda i: i >= 0, map(lambda row: row[0] if not filter_dup_nans(
    df, row[1]) else -1, df.iterrows())))
df = df.drop(to_remove).reset_index(drop=True)
df['A'] = df.index.values
print(df)

Prints:

     A   B        C     D    E     F     G     H
0    0  UK    Chile  1995  NaN   NaN   NaN   NaN
1    1  UK    Chile  1996  NaN   NaN   NaN   NaN
2    2  UK    Chile  1997  NaN   NaN   NaN   NaN
3    3  UK    Chile  1998  NaN   NaN   NaN   NaN
4    4  UK    Chile  1999  NaN   NaN   NaN   NaN
5    5  UK    Chile  2000  5.0  10.0  15.0  40.0
6    6  UK    Chile  2001  NaN   NaN   NaN   NaN
7    7  UK    Chile  2002  6.0  12.0  12.0  12.0
8    8  UK    Chile  2003  NaN   NaN   NaN   NaN
9    9  UK    Chile  2004  4.0  13.0  12.0  12.0
10  10  UK    Chile  2005  NaN   NaN   NaN   NaN
11  11  UK  Iceland  1995  NaN   NaN   NaN   NaN
12  12  UK  Iceland  1996  NaN   NaN   NaN   NaN
13  13  UK  Iceland  1997  NaN   NaN   NaN   NaN
14  14  UK  Iceland  1998  NaN   NaN   NaN   NaN
15  15  UK  Iceland  1999  NaN   NaN   NaN   NaN
16  16  UK  Iceland  2000  NaN   NaN   NaN   NaN
17  17  UK  Iceland  2001  NaN   NaN   NaN   NaN
18  18  UK  Iceland  2002  NaN   NaN   NaN   NaN
19  19  UK  Iceland  2003  NaN   NaN   NaN   NaN
20  20  UK  Iceland  2004  5.0  19.0  23.0  23.0
21  21  UK  Iceland  2005  3.0  23.0  12.0   3.0
22  22  US    BENIN  1995  5.0  10.0  15.0  40.0
23  23  US    BENIN  1996  6.0  12.0  12.0  12.0
24  24  US    BENIN  1997  NaN   NaN   NaN   NaN
25  25  US    BENIN  1998  NaN   NaN   NaN   NaN
26  26  US    BENIN  1999  NaN   NaN   NaN   NaN
27  27  US    BENIN  2000  4.0  13.0  12.0  12.0
28  28  US    BENIN  2001  NaN   NaN   NaN   NaN
29  29  US    BENIN  2002  NaN   NaN   NaN   NaN
30  30  US    BENIN  2003  NaN   NaN   NaN   NaN
31  31  US    BENIN  2004  NaN   NaN   NaN   NaN
32  32  US    BENIN  2005  NaN   NaN   NaN   NaN
33  33  US  Hungary  1995  NaN   NaN   NaN   NaN
34  34  US  Hungary  1996  NaN   NaN   NaN   NaN
35  35  US  Hungary  1997  NaN   NaN   NaN   NaN
36  36  US  Hungary  1998  5.0  19.0  23.0  23.0
37  37  US  Hungary  1999  3.0  23.0  12.0   3.0
38  38  US  Hungary  2000  NaN   NaN   NaN   NaN
39  39  US  Hungary  2001  NaN   NaN   NaN   NaN
40  40  US  Hungary  2002  NaN   NaN   NaN   NaN
41  41  US  Hungary  2003  NaN   NaN   NaN   NaN
42  42  US  Hungary  2004  NaN   NaN   NaN   NaN
43  43  US  Hungary  2005  NaN   NaN   NaN   NaN
Improve this answer

3 Comments

Thank you for the code. But I do not have DF_to _add data frame because the database is too big and
"I have also tried using a list. I created a list with all years in it." I thought this means you had this data. Does R Bute's answer not work for you?
Yes R Bute's Answer worked. Thank you everyone for helping.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.