Data frame misaligned using Pandas on Python3

Question

I have a data which I am trying to store in pandas dataFrame. But, it is appearing in a weird way. I know I am doing something wrong

Can somebody help me in finding whats wrong.

Code

root@optstra:~# cat pandas_1.py
import pandas as pd
import numpy as np

numberOfRows = 1

SYMBOL = 'ABB'
volume_increasing = True
price_increase = True
OI_CHANGE = True
closedAboveYesterday = False
Above_22SMA = False

data_frame = pd.DataFrame(index=np.arange(0, numberOfRows), columns=('SYMBOL','Volume', 'Price', 'OI','OHLC','22SMA') )

for x in range(0,numberOfRows):
    data_frame.loc[x] = [{SYMBOL,volume_increasing,price_increase,OI_CHANGE,closedAboveYesterday,Above_22SMA} for n in range(6)]

print(data_frame)

Output

root@optstra:~# python3 pandas_1.py
               SYMBOL              Volume               Price                  OI                OHLC               22SMA
0  {False, True, ABB}  {False, True, ABB}  {False, True, ABB}  {False, True, ABB}  {False, True, ABB}  {False, True, ABB}

If I change the line which writes the data to data frame as follows

for x in range(0,numberOfRows):
    data_frame.loc[x] = [(SYMBOL,volume_increasing,price_increase,OI_CHANGE,closedAboveYesterday,Above_22SMA) for n in range(6)]

Output changes to

root@optstra:~# python3 pandas_1.py
                                  SYMBOL                  ...                                                    22SMA
0  (ABB, True, True, True, False, False)                  ...                    (ABB, True, True, True, False, False)

What format is `data`? Can you [show us in your question](https://stackoverflow.com/posts/53248122/edit)? — jpp, Nov 11 '18 at 11:16
@jpp they are `boolean` and `string` if you see I have data which in the form of `variables` which I want to organise in tabular format and convert to csv. I am trying to map them like SYMBOL "variable" — Vinay Shukla, Nov 11 '18 at 11:18
No, I don't see... all I see is `SyntaxError` because you haven't defined any of your variables, `SYMBOL` etc [in your question](https://stackoverflow.com/posts/53248122/edit). — jpp, Nov 11 '18 at 11:19
Its a running code though I will edit it or create a pseudo code — Vinay Shukla, Nov 11 '18 at 11:20
@leeym its an argument I have updated the code and you can take a look — Vinay Shukla, Nov 11 '18 at 11:29

score 2 · Answer 1 · answered Nov 11 '18 at 11:44

2

Why don't you try this-- not sure if it's exactly what you're looking for since you took that part out in your edit:

for x in range(0,numberOfRows):
    data_frame.loc[x] = [SYMBOL,volume_increasing,price_increase,OI_CHANGE,closedAboveYesterday,Above_22SMA]

Output:

  SYMBOL Volume Price    OI   OHLC  22SMA
0    ABB   True  True  True  False  False

answered Nov 11 '18 at 11:44

user1394

538
1
6
17

1

Thank you !! It was a silly question as I am new to this language :) – Vinay Shukla Nov 11 '18 at 11:50

jezrael · Accepted Answer · 2018-11-11T12:02:26.087

2

Updating an empty frame (e.g. using loc one-row-at-a-time) is inefficient.

So better/faster is create list by append with DataFrame contructor:

data = []
for x in np.arange(numberOfRows):
    row = [SYMBOL,volume_increasing,price_increase,OI_CHANGE,closedAboveYesterday,Above_22SMA]
    data.append(row)

c = ('SYMBOL','Volume', 'Price', 'OI','OHLC','22SMA')
data_frame = pd.DataFrame(data, columns=c)

list comprehension alternative:

data = [[SYMBOL,volume_increasing,price_increase,OI_CHANGE,closedAboveYesterday,Above_22SMA] for x in np.arange(numberOfRows)]

edited Nov 11 '18 at 12:02

answered Nov 11 '18 at 11:55

jezrael

822,522
95
1,334
1,252

1

This should be the accepted answer. `pd.DataFrame.loc` in a loop is inefficient. – jpp Nov 11 '18 at 11:56

score 0 · Answer 3 · answered Nov 11 '18 at 12:13

It seems to me you're not quite indexing the dataframe properly. You can either do this:

for x in range(0, numberOfRows):
    data_frame['SYMBOL'][x] = SYMBOL
    data_frame['Volume'][x] = volume_increasing
    data_frame['Price'][x] = price_increase
    data_frame['OI'][x] = OI_CHANGE
    data_frame['OHLC'][x] = closedAboveYesterday
    data_frame['22SMA'][x] = Above_22SMA

which will give you your desired output, alternatively you can use dictionaries and avoid the for loop altogether:

columns = ['SYMBOL','Volume', 'Price', 'OI','OHLC','22SMA']
data = {'SYMBOL': 'AAB',
        'Volume': True,
        'Price': True,
        'OI': True,
        'OHLC': False,
        '22SMA': False}

data_frame = pd.DataFrame(data=data, index=np.arange(0, 1), columns=columns)

Data frame misaligned using Pandas on Python3

3 Answers3