I'm trying to add two dataframes using concat with axis = 0, so the columns stay the same but the index increases. One of the dataframes contains a specific columns with a serial number (going from one upwards - but not necessarily in sequence eg. 1,2,3,4,5, etc.)
import pandas as pd
import numpy as np
a = pd.DataFrame(data = {'Name': ['A', 'B','C'],
'Serial Number': [1, 2,5]} )
b = pd.DataFrame(data = {'Name': ['D','E','F'],
'Serial Number': [np.nan,np.nan,np.nan]})
c = pd.concat([a,b],axis=0).reset_index()
I would like to have column 'Serial Number' in dataframe C to start from 5+1 the next one 6+1.
I've tried a variety of things eg:
c.loc[c['B'].isna(), 'B'] = c['B'].shift(1)+1
But it doesn't seem to work.
Desired output:
| Name | Serial Number|
-------------------------
1 A | 1
2 B | 2
3 C | 5
4 D | 6
5 E | 7
6 F | 8
One idea is create arange by number od missinng values add maximal value and
1
: