CODEWITHSUNDEEP
pythonpython-3.xpandas
hargurjeet singh
hargurjeet singh

Reputation: 45

How to concatenate data from multiple vertical cells to one cell in python

I have a dataframe something like this

enter image description here

I want to combine row 0 to row 5 so that the dataframe should look like this

Formatted DF

Please find the unformatted DF below

{0: {0: 'Budget allocation for 1998-2019 financial years',
  1: 'In million',
  2: 'USD',
  3: '',
  4: '',
  5: '',
  6: 'Atyrau',
  7: 'Mangistau',
  8: 'Total'},
 1: {0: '',
  1: 'ITD VOWD',
  2: 'Dec',
  3: '',
  4: '2018',
  5: '',
  6: '318.5',
  7: '302.3',
  8: '620.8'},
 2: {0: '',
  1: 'YTD VOWD',
  2: 'February',
  3: '',
  4: '2019',
  5: 'Incl. VAT',
  6: '5.1',
  7: '2.0',
  8: '7.2'},
 3: {0: '',
  1: 'ITD VOWD',
  2: 'February',
  3: '',
  4: '2019',
  5: '[A]',
  6: '323.6',
  7: '304.3',
  8: '628.0'},
 4: {0: '',
  1: 'PSA obligation',
  2: 'as of Feb` 2019',
  3: '',
  4: '',
  5: '[B]',
  6: '362.6',
  7: '362.6',
  8: '725.3'},
 5: {0: '',
  1: 'Existing',
  2: 'commitments',
  3: '',
  4: '',
  5: '[C]',
  6: '43.9',
  7: '51.9',
  8: '95.8'},
 6: {0: '',
  1: 'Available',
  2: 'approved',
  3: 'budget',
  4: '[B-A-C]',
  5: 'Incl. VAT',
  6: '-5',
  7: '6',
  8: '2'}}

Upvotes: 1

Views: 57

Answers (1)

Mayank Porwal
Mayank Porwal

Reputation: 34086

You can do this:

## concat every column for 5 rows into a pd.Series
df = df.iloc[0:6, :].apply(' '.join) 

## convert Series to dataframe and Transpose it
df = pd.DataFrame(df).T

Upvotes: 2

Related Questions