I need to merge the header column if it is empty with the nearby column.
For scenario-1, I need to merge column 3(Amount) and column 4(empty).
and I need the below result.
For scenario-2, I need to merge column 3(Empty) and column 4(Amount).
and I need the below result.
Any help would be really appreciated.
Note: The row header is dynamic name. It is not static names as mentioned above. i.e., Header names would be anything.
Example
Amount Empty
0 10 €
1 20 €
2 30 €
3 40 €
using np.where
df['Amount'] = np.where(df['Amount'].astype(str) == '€', df['Amount'].astype(str) + ' ' + df['Empty'].astype(str), df['Empty'].astype(str) + ' ' + df['Amount'].astype(str))
df.drop('Empty',1,inplace=True)
Amount
0 € 10
1 € 20
2 € 30
3 € 40
Use:
df = pd.DataFrame({'' : ['€','€','€','€'],
'col0' : [50,100,25,90],
'col':1,
" ": [50, 100, 25, 90],
"col2": ["€", "€", "€", "€"]}).rename(columns={' ':''})
print (df)
col0 col col2
0 € 50 1 50 €
1 € 100 1 100 €
2 € 25 1 25 €
3 € 90 1 90 €
You can check dtypes:
s = df.dtypes
print (s)
object
col0 int64
col int64
int64
col2 object
dtype: object
If column name is empty string and dtype is object it means column is filled by currency, logic is then replace these empty strings to missing values and forward filling them, last replace empty columns names with numeric and replace column name by back filling:
m = (s == object) & (s.index == '')
a = s.index.to_series().mask(m).ffill().replace({'':np.nan}).bfill()
Output are same columns names for currency and next numeric columns:
df.columns = a
print (df)
col0 col0 col col2 col2
0 € 50 1 50 €
1 € 100 1 100 €
2 € 25 1 25 €
3 € 90 1 90 €
Then use custom lambda function with groupby for join it together:
def f(x):
if len(x.columns) == 2:
if isinstance(x.iloc[0, 0], str):
return x.iloc[:, 0] + ' ' + x.iloc[:, 1].astype(str)
else:
return x.iloc[:, 1] + ' ' + x.iloc[:, 0].astype(str)
else:
return x.iloc[:, 0]
df = df.groupby(df.columns, axis=1).apply(f)
print (df)
col col0 col2
0 1 € 50 € 50
1 1 € 100 € 100
2 1 € 25 € 25
3 1 € 90 € 90
