Add the missing numbers in the table in order-CodePudding

I need modify a csv file with pandas. I have the following table:

Interface   Description
1           Used
2           Used
3           Used
4           Used
6           Used
8           Used
12          Used
17          Used

I need to match the "Interface" column with a range of 1, 20, complete the table with the missing numbers and place the word "free" in the "Description" column and order it like this:

Interface   Description
1           Used
2           Used
3           Used
4           Used
5           free
6           Used
7           free
8           Used
9           free
10          free
11          free
12          Used
13          free
14          free
15          free
16          free
17          Used
18          free
19          free
20          free

CodePudding user response：

Use merge in combination with fillna

df = pd.DataFrame({
    'Interface': [1, 2, 3, 4, 6, 8, 12, 17],
    'Description': 'Used'})
df2 = pd.DataFrame({'Interface': range(1, 21)}).merge(df, how="left").fillna("free")

CodePudding user response：

Another way using reindex

df.set_index('Interface').reindex(range(1,21)).fillna('free').reset_index()



    Interface Description
0           1        Used
1           2        Used
2           3        Used
3           4        Used
4           5        free
5           6        Used
6           7        free
7           8        Used
8           9        free
9          10        free
10         11        free
11         12        Used
12         13        free
13         14        free
14         15        free
15         16        free
16         17        Used
17         18        free
18         19        free
19         20        free

CodePudding user response：

One option is with complete from pyjanitor, to expose missing rows:

# pip install pyjanitor
import pandas as pd
import janitor
interface = {'Interface' : range(1, 21)}
df.complete(interface, fill_value='free')
    Interface Description
0           1        Used
1           2        Used
2           3        Used
3           4        Used
4           5        free
5           6        Used
6           7        free
7           8        Used
8           9        free
9          10        free
10         11        free
11         12        Used
12         13        free
13         14        free
14         15        free
15         16        free
16         17        Used
17         18        free
18         19        free
19         20        free