CODEWITHSUNDEEP
python-3.xpandaspython-2.7csvexport-to-csv
codeuser
codeuser

Reputation: 179

How to get a table in a format using pandas and export to csv?

I am new to pandas .I want the a table in a format which I need to export csv.

What I have tried is:

o_rg,o_gg,a_rg,a_gg are arrays

  df1=pd.DataFrame({'RED':o_rg,'GREEN':o_gg})
  df2=pd.DataFrame({'RED':a_rg,'RED':a_gg})
  df=df1-(df2)
  pop_complete = pd.concat([df, df1, df2], keys=["O-A", "O", "A"], axis=1)
  pop_complete.index = ['A1','A3','A8']
  df1 = pop_complete.stack(0)[['RED','GREEN']].reindex(["O", "A", "O-A"], axis=0, level=1)
  df1.to_csv("OUT.CSV")

What I get the output as:

       RED     GREEN       

    A1 O        14.0     14.0
       A        14.0     12.0
       O-A      0.0      2.0

    A3 O        12.0     9.0
       A        12.0     10.0
       O-A      0.0      -1.0

    A8 O        15.0     12.0
       A        15.0     12.0
       O-A      0.0      0.0

What I actually want is:

                RED     GREEN       
       A1
       O        14.0     14.0
       A        14.0     12.0
       O-A      0.0      2.0
       A3
       O        12.0     9.0
       A        12.0     10.0
       O-A      0.0      -1.0
       A8
       O        15.0     12.0
       A        15.0     12.0
       O-A      0.0      0.0

where 'A1','A3','A8' ... can be stored in array cases=[] How to get the actual output?

Upvotes: 2

Views: 55

Answers (1)

jezrael
jezrael

Reputation: 863291

Use custom functions:

#from previous answer
df1 = pop_complete.stack(0)[['RED','GREEN']].reindex(["O", "A", "O-A"], axis=0, level=1)
print (df1)
        RED  GREEN
A1 O     14     14
   A     14     14
   O-A    0      0
A3 O     12      9
   A     12     10
   O-A    0     -1
A8 O     15     12
   A     15     15
   O-A    0     -3

If need all numeric values:

def f(x):
    df2 = pd.DataFrame(columns=x.columns, 
                       index=pd.MultiIndex.from_tuples([(x.name, x.name)]))
    return df2.append(x)

df3 = df1.groupby(level=0, group_keys=False).apply(f).reset_index(level=0, drop=True)
print (df3)
     RED GREEN
A1   NaN   NaN
O     14    14
A     14    14
O-A    0     0
A3   NaN   NaN
O     12     9
A     12    10
O-A    0    -1
A8   NaN   NaN
O     15    12
A     15    15
O-A    0    -3

If need empty strings:

def f(x):
    df2 = pd.DataFrame('', columns=x.columns, 
                       index=pd.MultiIndex.from_tuples([(x.name, x.name)]))
    return df2.append(x)

df3 = df1.groupby(level=0, group_keys=False).apply(f).reset_index(level=0, drop=True)
print (df3)
    RED GREEN
A1           
O    14    14
A    14    14
O-A   0     0
A3           
O    12     9
A    12    10
O-A   0    -1
A8           
O    15    12
A    15    15
O-A   0    -3

Upvotes: 3

Related Questions