I tried different stack overflow solutions using pd.read_csv for this file.
When I use Excel to text to and use ";" as delimiter in Excel, it gives exactly the output I need.
data:
| 'Balance Sheet;"'Package / Number";"Package Type";"Instrument";"Counterparty";"Opening Date";"Value Date";"Maturity Date";"'Nominal Amount";"'Interest Rate";"CCy";"'Funding Type";"Nominal Amount Local";"Interest Rate Local";"'Maturity Year";"'Maturity Quarter";"Tenor";"Tenor Range";"Date Basis" |
|---|
| Asset Finance;"2.915.239";;"IRS-FIX-TO-FLOAT";"X_SEL";"03/27/2019";"03/29/2019";"08/29/2023";"-20.000.000.000";"1 |
| Asset Finance;"2.915.239";;"IRS-FIX-TO-FLOAT";"X_SEL";"03/27/2019";"03/29/2019";"08/29/2023";"20.000.000.000";"2 |
| Asset Finance;;;"IRS-FIX-TO-FLOAT";;"03/27/2019";"03/29/2019";"08/29/2023";;;;"Payer Swap";"20.000.000.000";"-1 |
Code:
df = pd.read_csv(path2, sep='";"',engine='python')
df = df.apply(lambda x: x.replace('"','')) --\> doesnt seems to be working
The output columns are not split correct. It should be per above column 0: Balance Sheet, 1: Package / Number, 2: 'Package Type etc.. total 19 columns
pandas output:
If there is any other work around solutions, pls tell. Thanks!
";". If you notice the last row in your image, multiple columns are clubbed as first column value, and "Interest Rate" column's value is in "Instrument" column so on.