0

Trying to use pandas read_fwf, but in my case I have start position named position in the json and column-size having size and I want to use fill values from flatfile in to the multiple columns from the same position. Below is the sample json and flatfile and expected output.

Sample Json:

          "copybook": {
          "item": [
        {
          "column-size": 3,
          "position": 1,
          "column-name": "SAMPLE_1",
          "column-type": "STRING"
        },
        {
          "column-size": 3,
          "position": 4,
          "column-name": "COL_2",
          "column-type": "STRING"
        },
        {
          "redefines": "COL_2",
          "item": [
          {
            "column-size": 2,
            "position": 4,
            "column-name": "COL_3",
            "column-type": "INT"
          },
          {
            "column-size": 1,
            "position": 6,
            "column-name": "COL_4",
            "column-type": "STRING"
          }
        ]
      }
    ]
  }
}```
 
Sample Data is CCCRRR
    
expected output is 
    
SAMPLEE_1 COL_2 COL_3 COL_4
CCC       RRR   RR    R
Arvind
  • 1

1 Answers1

0

Let's assume sample data is ABCDEF. If you want ABC/DEF/int(DE)/F, you can do:

df = pd.read_fwf('filename', widths=[3,3], header=None)
df.columns = ['SAMPLE_1', 'COL_2']
df['COL_3'] = df['COL_2'].str[:2].astype(int)
df['COL_4'] = df['COL_2'].str[-1]

NB. I assumed no header, but change the code if this is incorrect

mozway
  • 194,879
  • 13
  • 39
  • 75
  • Hi @Mozway I am thinking of generic solution, if I have more than 500 column and json may have variable columns as per the case. – Arvind Aug 11 '21 at 05:56