Wcześniej poszedłem za rozwiązaniem z tego pytanie, ale Wtedy zdałem sobie sprawę, że nie jest to samo z moim przypadkiem, chciałbym wyświetlić pewne wartości dla tego samego as_of_dates i ID w sekcji {X2}} w pliku JSON, mam dataframe w ten sposób:

     as_of_date create_date   ID  value_1   count   value_3
0    02/03/2021 02/03/2021  12345   5         2      55
1    02/03/2021 01/03/2021  12345   8         2      55
2    02/03/2021 01/03/2021  34567   9         1      66
3    02/03/2021 02/03/2021  78945   9         1      77
4    03/03/2021 02/03/2021  78945   9         1      22
5    03/03/2021 02/03/2021  12345   5         1      33

Gdzie count kolumna jest liczbą wierszy dla tego samego {x1}} & amp; as_of_date, na przykład, dla as_of_date=02/03/2021 i ID=12345 Istnieją dwa wiersze (każdy wiersz ma inny create_date, ale nie dbam o create_date) , więc count dla pierwszych dwóch wierszy są takie same: 2.

Oczekiwany JSON jest:

{
    "examples": [
        {
            "Id": 12345,
            "as_of_date": "2021-03-02 00:00:00", # this field is datetime format
            "value_3": 55, 
            "count": 2,    # for the same 'ID=12345'&'as_of_date=02/03/2021'
            "display_rows": [
                {
                    "value_1": 5,
                    "type": "int" # 'type' field will always be 'int'
                },
                {
                    "value_1": 8,
                    "type": "int"
                }
            ]
        },
        {
            "Id": 34567,
            "as_of_date": "2021-03-02 00:00:00",
            "value_3": 66,
            "count": 1,
            "display_rows": [
                {
                    "value_1": 9,
                    "type": "int"
                }
            ]
        },
        {
            "Id": 78945,
            "as_of_date": "2021-03-02 00:00:00",
            "value_3": 77,
            "count": 1,
            "display_rows": [
                {
                    "value_1": 9,
                    "type": "int" 
                }
            ]
        },
        {
            "Id": 78945,
            "as_of_date": "2021-03-03 00:00:00",
            "value_3": 22,
            "count": 1,
            "display_rows": [
                {
                    "value_1": 9,
                    "type": "int" 
                }
            ]
        },
        {
            "Id": 12345,
            "as_of_date": "2021-03-03 00:00:00",
            "value_3": 33,
            "count": 1,
            "display_rows": [
                {
                    "value_1": 5,
                    "type": "int" 
                }
            ]
        }
    ]
}

Zajęło mi prawie cały dzień, aby dowiedzieć się, ale nie działa ... czy ktoś może pomóc? Dzięki.

1
Cecilia 24 marzec 2021, 22:37

1 odpowiedź

Najlepsza odpowiedź

Użyj GroupBy.apply z funkcją lambda do przetwarzania value_1 jak:

import json

df['as_of_date'] = pd.to_datetime(df['as_of_date'], dayfirst=True, errors='coerce')


f = lambda x: [ {"value_1": y, "type": "int" } for y in x]
df = (df.groupby(['as_of_date','ID','value_3','count'])['value_1']
        .apply(f)
        .reset_index(name='display_rows'))
print (df)
  as_of_date     ID  value_3  count  \
0 2021-03-02  12345       55      2   
1 2021-03-02  34567       66      1   
2 2021-03-02  78945       77      1   
3 2021-03-03  12345       33      1   
4 2021-03-03  78945       22      1   

                                        display_rows  
0  [{'value_1': 5, 'type': 'int'}, {'value_1': 8,...  
1                    [{'value_1': 9, 'type': 'int'}]  
2                    [{'value_1': 9, 'type': 'int'}]  
3                    [{'value_1': 5, 'type': 'int'}]  
4                    [{'value_1': 9, 'type': 'int'}]  

j = json.dumps({"examples":df.to_dict(orient='records')}, default=str)

print (j)
{"examples": [{"as_of_date": "2021-03-02 00:00:00", "ID": 12345, "value_3": 55, "count": 2, "display_rows": [{"value_1": 5, "type": "int"}, {"value_1": 8, "type": "int"}]}, {"as_of_date": "2021-03-02 00:00:00", "ID": 34567, "value_3": 66, "count": 1, "display_rows": [{"value_1": 9, "type": "int"}]}, {"as_of_date": "2021-03-02 00:00:00", "ID": 78945, "value_3": 77, "count": 1, "display_rows": [{"value_1": 9, "type": "int"}]}, {"as_of_date": "2021-03-03 00:00:00", "ID": 12345, "value_3": 33, "count": 1, "display_rows": [{"value_1": 5, "type": "int"}]}, {"as_of_date": "2021-03-03 00:00:00", "ID": 78945, "value_3": 22, "count": 1, "display_rows": [{"value_1": 9, "type": "int"}]}]}

EDYTOWAĆ:

#added some another column
df['value_7'] = 52
print (df)
   as_of_date create_date     ID  value_1  count  value_3  value_7
0  02/03/2021  02/03/2021  12345        5      2       55       52
1  02/03/2021  01/03/2021  12345        8      2       55       52
2  02/03/2021  01/03/2021  34567        9      1       66       52
3  02/03/2021  02/03/2021  78945        9      1       77       52
4  03/03/2021  02/03/2021  78945        9      1       22       52
5  03/03/2021  02/03/2021  12345        5      1       33       52

#added type column for last value in dict
df = (df.assign(type='int')
        .groupby(['as_of_date','ID','value_3','count'])[['value_1', 'value_7','type']]
        .apply(lambda x:  x.to_dict('records'))
        .reset_index(name='display_rows'))
print (df)
   as_of_date     ID  value_3  count  \
0  02/03/2021  12345       55      2   
1  02/03/2021  34567       66      1   
2  02/03/2021  78945       77      1   
3  03/03/2021  12345       33      1   
4  03/03/2021  78945       22      1   

                                        display_rows  
0  [{'value_1': 5, 'value_7': 52, 'type': 'int'},...  
1     [{'value_1': 9, 'value_7': 52, 'type': 'int'}]  
2     [{'value_1': 9, 'value_7': 52, 'type': 'int'}]  
3     [{'value_1': 5, 'value_7': 52, 'type': 'int'}]  
4     [{'value_1': 9, 'value_7': 52, 'type': 'int'}]  

j = json.dumps({"examples":df.to_dict(orient='records')}, default=str)

EDYTOWAĆ:

df = (df.assign(example_placeholder='xyz')
        .groupby(['as_of_date','ID','value_3','count'])[['value_1', 'value_7','example_placeholder']]
        .apply(lambda x:  x.to_dict('records'))
        .reset_index(name='display_rows'))
print (df)
   as_of_date     ID  value_3  count  \
0  02/03/2021  12345       55      2   
1  02/03/2021  34567       66      1   
2  02/03/2021  78945       77      1   
3  03/03/2021  12345       33      1   
4  03/03/2021  78945       22      1   

                                        display_rows  
0  [{'value_1': 5, 'value_7': 52, 'example_placeh...  
1  [{'value_1': 9, 'value_7': 52, 'example_placeh...  
2  [{'value_1': 9, 'value_7': 52, 'example_placeh...  
3  [{'value_1': 5, 'value_7': 52, 'example_placeh...  
4  [{'value_1': 9, 'value_7': 52, 'example_placeh...  

df = (df.assign(aa='xyz', type='int')
        .groupby(['as_of_date','ID','value_3','count'])[['value_1', 'value_7','aa', 'type']]
        .apply(lambda x:  x.to_dict('records'))
        .reset_index(name='display_rows'))
print (df)

   as_of_date     ID  value_3  count  \
0  02/03/2021  12345       55      2   
1  02/03/2021  34567       66      1   
2  02/03/2021  78945       77      1   
3  03/03/2021  12345       33      1   
4  03/03/2021  78945       22      1   

                                        display_rows  
0  [{'value_1': 5, 'value_7': 52, 'aa': 'xyz', 't...  
1  [{'value_1': 9, 'value_7': 52, 'aa': 'xyz', 't...  
2  [{'value_1': 9, 'value_7': 52, 'aa': 'xyz', 't...  
3  [{'value_1': 5, 'value_7': 52, 'aa': 'xyz', 't...  
4  [{'value_1': 9, 'value_7': 52, 'aa': 'xyz', 't...  
1
jezrael 25 marzec 2021, 13:49